SQL Server Database Engine Forum

Sql Server Dev Edition Not connecting on Windows 2003 Server from Win 7

Thu, 26 Nov 2009 10:03:18 Z

I have found another issue with Win 7..

Management Studio of Sql Server Dev Edition 2005 installed on Win 7 is not able to connect on Windows 2003 Server where standard edition of sql server 2005 is installed.

The connection is working when I use the Sa password but when I use windows authentication then it is showing err msg that "user is not associated with a trusted sql server connection"..

I'm using a local admin password on win 7 and in passwords server password is saved (i.e. the domain password), I'm even able to get exchange mails so domain pass is working fine from other places but sql server is not able to connect..

Pls help..

IMP : There might be a delay in posting replies due to time difference (GMT +2:00) and Friday's Holiday instead of Sunday.

What is tail of log backup

Sat, 28 Nov 2009 11:42:34 Z

what is tail of log backup and how to take it

Strange Errors on SQL Server Error Log

Fri, 27 Nov 2009 14:59:39 Z

I am seeing the error logs below on my SQL server, I'm not sure if they are related to trace flag 3604. I also see something to do with memory dump as well. Please advice.

 15:15:53.81 spid594   m_pageId = (1:1172094)    m_headerVersion = 1       m_type = 2|0
 15:15:53.81 spid594   m_typeFlagBits = 0x0      m_level = 0               m_flagBits = 0x0|0
 15:15:53.81 spid594   m_objId = 954538534       m_indexId = 6             m_prevPage = (1:1102845)|0
 15:15:53.81 spid594   m_nextPage = (1:1172095)  pminlen = 21              m_slotCnt = 212|0
 15:15:53.81 spid594   m_freeCnt = 2584          m_freeData = 5880         m_reservedCnt = 0|0
 15:15:53.81 spid594   m_lsn = (119582:10616:47) m_xactReserved = 0        m_xdesId = (0:753243376)|0
 15:15:53.81 spid594   m_ghostRecCnt = 0         m_tornBits = -2147483127  |0
 15:15:53.81 spid594   |0
Allocation Status|0
-----------------|0
 15:15:53.81 spid594   GAM (1:1022464) = ALLOCATED                         |0
 15:15:53.81 spid594   SGAM (1:1022465) = NOT ALLOCATED                    |0
 15:15:53.81 spid594   PFS (1:1164672) = 0x40 ALLOCATED   0_PCT_FULL       DIFF (1:1022470) = CHANGED|0
 15:15:53.81 spid594   ML (1:1022471) = NOT MIN_LOGGED                     |0
 15:15:53.81 spid594   |0
DATA:|0
-----|0
 15:15:53.81 spid594   |0
Memory Dump @0x3CFF0000|0
-----------------------|0
 15:15:53.81 spid594   3CFF0000:  01020000 00000600 fdd31000 01001500 7fe21100  ....................|0
 15:15:53.81 spid594   3CFF0014:  0100d400 261ae538 180af816 7ee21100 01000000  ....&..8....~.......|0
 15:15:53.81 spid594   3CFF0028:  1ed30100 78290000 2f000000 f094e52c 00000000  ....x)../......,....|0
 15:15:53.81 spid594   3CFF003C:  09020080 00000000 00000000 00000000 00000000  ....................|0
 15:15:53.81 spid594   3CFF0050:  00000000 00000000 00000000 00000000 1a2c0d00  .................,..|0
 15:15:53.83 spid594   3CFF0064:  009c0300 00e30300 0097f79a 10000000 00040000  ....................|0

Problem in full-text index SQL Server 2005 Express Edition

Sat, 28 Nov 2009 06:47:49 Z

Hi guys,

I have a strange problem in my SQL Server 2005 Express Edition.

I am using Express Edition with Advanced Services SP3 with one table was full-text indexed. The table is rather huge and it is around 1.8 GB in size. Everything was okay, the population was successful and the state of the full-text index is working normally.

-- Return state of 5 which is working normally
select * from sys.dm_fts_index_population

-- This returns 2.8 million+ so it was populated successfully
SELECT fulltextcatalogproperty('esgportaluat_catalog', 'ItemCount')

Now, here is the funny things.

-- Returns 23000+ records within 1 second
select count(1)
from esg_product_latest
where result_approved_dt >= CONVERT(DATETIME, '08/01/09', 1)
AND   result_approved_dt <= CONVERT(DATETIME, '10/29/09', 1)
AND   CONTAINS((SAM_DESC), '"test*"')

-- Returns 12000+ records within 1 second (WITHOUT full-text index)
select count(1)
from esg_product_latest
where result_approved_dt >= CONVERT(DATETIME, '10/30/09', 1)
AND   result_approved_dt <= CONVERT(DATETIME, '10/30/09', 1)

-- HANG! I have waited for 40 mins but it never returned anything...it just said that "Executing Query..."
select count(1)
from esg_product_latest
where result_approved_dt >= CONVERT(DATETIME, '10/30/09', 1)
AND   result_approved_dt <= CONVERT(DATETIME, '10/30/09', 1)
AND   CONTAINS((SAM_DESC), '"test*"')

-- Combining with the date range that works fine (before 30 Oct 09, it works fine)
-- Return 800+ records within 1 second
select count(1)
from esg_product_latest
where result_approved_dt >= CONVERT(DATETIME, '10/28/09', 1)
AND   result_approved_dt <= CONVERT(DATETIME, '10/30/09', 1)
AND   CONTAINS((SAM_DESC), '"test*"')

I have tested that the query will never complete if specify the date range after 10/30/09 i.e. Nov 2009 and so on. What could be the problem on this?

In the log file, there is nothing wrong except one thing below. Could this be related to the HANG problem that I had?

2009-11-27 08:23:20.42 spid20s     The full-text catalog health monitor reported a failure for full-text catalog "esgportaluat_catalog" (5) in database "esguat" (7). Reason code: 0. Error: 0x800706bf(The remote procedure call failed and did not execute.). The system will restart any in-progress population from the previous checkpoint. If this message occurs frequently, consult SQL Server Books Online for troubleshooting assistance. This is an informational message only. No user action is required.

Thanks
David

How to open a xdl file

Thu, 26 Nov 2009 22:34:47 Z

I am trying to get the graphic deadlock information using the WMI provider, I was able to store the data on a table and then I bcp out naming the file with a .xdl extension, after that I open the studio management and tried to open the file but I recieve this message:

there is no editor available for 'C:\Program Files\xmlfile.xdl' Make sure the application for the file type (.xdl) is installed.

Do you know what should I install/configure?

Help understanding DBCC memorystats

Sun, 15 Nov 2009 14:41:23 Z

I have sql server 2005 sp3 enterprise edition with awe enabled and max server memory set to 6 GB. The buffer part of the dbcc memorystatsus is given below...

Buffer Distribution Buffers

------------------------------ -----------

Stolen 7142

Free 149

Cached 106156

Database (clean) 540704

Database (dirty) 113840

I/O 0

Latched 9

(7 row(s) affected)

Buffer Counts Buffers

------------------------------ --------------------

Committed 768000

Target 768000

Hashed 654458

Stolen Potential 65245

External Reservation 6740

Min Free 424

Visible 195072

Available Paging File 1700209

What I have figured out from the above values is that The committed value is (768000*8)/(1024*1024) which comes out to be 6 GB approx...does this mean that Buffer pool is consuming 6GB of memory....

Cheers!!! SqlFrenzy

Confusing performance issue

Fri, 27 Nov 2009 19:27:38 Z

I was trying to improve performance of my little project and found a lot of instances where the optimizer was doing a partial index seek and then a key lookup/index scan. Aha! I thought and went about tweaking my indexes.
I now have the situation where everything is accomplished with index seeks, great yes? NO. Without getting into a debate about how inefficient my coding is ( I am a newbie ) prior to the tweaks the system was churning through 10 whole rows of data a second but now it has dropped to about 5.
I have not changed a single line of code so I am really confused as how more efficient indexes have slowed it down. These are not even new extra indexes just originals I had already created but extended and they are obviously correct due to only index seeks being done.

Some further info regarding my problem.
I broke down my code into individual chunks and ran them seperately against a 1000 records. Most of the chunks completed in 10 to 15 seconds and then were a couple that took about 45 seconds. I went back to run them a second time just to make sure of the timings and then those taking 10/15 now completed in less than a second and the others in around 5 seconds.
I started from scratch and rebuilt evrything and had exactly the same results.First time any query was run took about 20 times longer than subsequent queries, I could even hear my system crank up to full tilt on the first round and then sit like a mouse for round two.
For one final check I then ran the same queries on the same table but using different data rows and wham bam! back to snail pace.
The same indexes are being shown as used in the actual execution plan but it seems to me that although the index 'exists' it doesn't have any data in it, as though when I initially create it the structure is there but it's not populated with anything. So I run the query first time and the index gets updated, but only for that data.

What am I missing? I build the table, bulk import my data and then create the indexes. Do I have to create before importing the data?

Any king gurus got any pointers it would be greatly appreciated. Non-gurus also welcome.

Are SQL Server Deadlocks Possible Without Transactions?

Thu, 26 Nov 2009 00:11:59 Z

Can one have deadlocks without the offending SQL being in transactions? We have deadlocks occuring and one process is picked as the victim (1205 errors) but cannot find where the transaction is opened for either process. These processes are being spawned by web services and they either execute stored procs or execute dynamic SQL via ADO.NET. There are no SQL level transactions in the procs or dynamic sql. We do not see implicit transactions being invoked anywhere.

From the deadlock trace, the situation appears to be one where Proc A has an update lock on Page 1 and wants an update lock on Page 2, while Statement B has an insert lock on Page 2 and wants an insert lock on Page 1. The proc is made the victim, but as I said earlier, we cannot find in the C# calling code or the SQL itself anyplace where a transaction is invoked.

Database Engine Services

Sat, 28 Nov 2009 00:06:12 Z

How to start Database Engine services start what are steps we have to follow colud any body plz help me

because i have instaleld in MS QL SERVER 2005 in My system but DE services is not started

what are steps we have to follow to start these services

subu

Deadlock problem

Mon, 07 Jan 2008 22:40:54 Z

I am experiencing an intermittent deadlock error in a databse. The deadlock is hard to produce but I did manage to capture the deadlock graph to an XML file. All the examples I have seen of this show both the victim process and the winner process. Unless I am reading this wrong all I get is the victim. Can a procedure deadlock itself? Does SQL functions that use recursion have an effect? Can deadlocks occur in tempdb?

Any ideas?

Here is the XML.

<deadlock-list>
<deadlock victim="process6e9018">
  <process-list>
   <process id="process6e9018" taskpriority="0" logused="0" waitresource="OBJECT: 2:1560079227:0 " waittime="15" ownerId="18728298" transactionname="TVTruncate" lasttranstarted="2008-01-07T16:12:54.740" XDES="0x1a266258" lockMode="Sch-M" schedulerid="1" kpid="2996" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2008-01-07T16:12:54.570" lastbatchcompleted="2008-01-07T16:12:54.477" clientapp=".Net SqlClient Data Provider" hostname="CONNELLY1" hostpid="7700" loginname="sa" isolationlevel="read uncommitted (1)" xactid="18728018" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    <executionStack>
     <frame procname="mexico.dbo.BilledToDate" line="83" stmtstart="4516" stmtend="20946" sqlhandle="0x03000500d830e523e115d500df9900000000000000000000">
select
  @billedtodate=sum(billedtodate)
from
(
select
  billedtodate=0-b.amount
from
  (select projectid from projectchildren(@projectid) union select projectid=@projectid) a
  join adjustmentitems b on a.projectid=b.projectid
  and b.isposted=1
  join adjustments c on b.gjid=c.gjid
  join glaccounts d on b.glid=d.glid
  join glbasecodes e on d.baseid=e.baseid
  and
  (
  (e.metrictypeid=@billedtype and @isrevenue=1) or
  (e.metrictypeid=@retainagetype and @incretainage=1) or
  (e.metrictypeid=@retainertype and @incretainer=1)
  )
  and
  (
  (e.pmtypeid=@labortype and @incdirectlabor=1 and laborrevtypeid=@dltype) or
  (e.pmtypeid=@labortype and @incdpeoh=1 and laborrevtypeid=@dpeohtype) or
  (e.pmtypeid=@labortype and @incprofit=1 and laborrevtypeid=@prtype) or
  (e.pmtypeid=@labortype and @incfixedfee=1 and laborrevtypeid=@fftype) or
  (e.pmtypeid=@icctype and @incicc=1) or
  (e.pmtypeid=@odctype and @incdirectodc=1 and exprevtypeid=@dexptype) or
  (e.pmtypeid=@odctype and     </frame>
     <frame procname="mexico.dbo.CUSInv_PFF_Labor_Sav" line="34" stmtstart="2178" stmtend="4250" sqlhandle="0x03000500105aff7b2cd0b3001c9a00000100000000000000">
insert #projs
(
projectid,
factid,
projectlevel,
projectcode,
projectpath,
projectname,
fixedfee,
fixedfeepc,
fixedfeebilledtodate
)
select
projectid=a.projectid,
factid=b.factid,
projectid=b.projectlevel,
projectcode=b.projectcode,
projectpath=b.projectpath,
projectname=b.projectname,
fixedfee=b.fixedfee,
fixedfeepc=b.fixedfeepc,
fixedfeebilledtodate=dbo.billedtodate(b.projectid,@asofdate,0,0,0,1,1,0,0,0,0,0,0)
from projectchildren(@projectid) a
join projects b on a.projectid=b.projectid
where b.projectlevel<=@projectlevel+@contractlevel-1
and (b.fixedfee>0 or 1=0)
and dbo.isinvfilter(a.projectid,'F')=1
union
select
projectid=a.projectid,
factid=a.factid,
projectid=a.projectlevel,
projectcode,
projectpath,
projectname,
fixedfee=a.fixedfee,
fixedfeepc=a.fixedfeepc,
fixedfeebilledtodate=dbo.billedtodate(a.projectid,@asofdate,0,0,0,1,1,0,0,0,0,0,0)
from projects a
where projectid=@projectid
and (a.fixedfee>0 or 1=0)
and dbo.isinvfilter(a.proje     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 2080332304]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <objectlock lockPartition="0" objid="1560079227" subresource="FULL" dbid="2" objectname="tempdb.dbo.#5CFCEB7B" id="lock83e0b00" mode="Sch-S" associatedObjectId="1560079227">
    <owner-list>
     <owner id="process6e9018" mode="Sch-S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6e9018" mode="Sch-M" requestType="wait"/>
    </waiter-list>
   </objectlock>
  </resource-list>
</deadlock>
</deadlock-list>

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance

Sun, 12 Mar 2006 06:29:50 Z

No wonder Microsoft is giving it away for free. It’s not worth anything. I am very disappointed in this version of SQL server. I decided to start learning VB so I downloaded and installed VB 2005 express edition and SQL 2005 express. I tried to build a personal website using the starter kit. I kept getting the following error message, for which I have seen numerous postings on this forum.

“Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.”

I went out and bought the Standard version of VS 2005 thinking there may be a feature that the free express version is lacking. I uninstalled the express version, cleaned the registry, deleted all the folders it created and installed the Standard edition. Then I tried to create a personal web using the starter kit and it does the same thing again.

I can not create a database file, connect to a database file. I can not create or attach a database on the SQL 2005 express from VS 2005. The SQL server Express seems to be working OK since I can attach to it using Microsoft SQL Server Management Studio Express. There is something about the way VS tries to connect to the database server that is not working. VB does recognize the local computer as a SQL server but can not connect to it.

SQL server is set to accept Shared memory, TCP/IP and named pipes for local clients.

What is causing this?

This is some information on the system I am using:

MS XP (SP2)

Microsoft Visual Studio 2005

Version 8.0.50727.42 (RTM.050727-4200)

Microsoft .NET Framework

Version 2.0.50727

Installed Edition: Standard

Microsoft SQL Server Management Studio Express 9.00.1399.00

Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)

Microsoft MSXML 2.6 3.0 4.0 5.0 6.0

Microsoft Internet Explorer 6.0.2900.2180

Microsoft .NET Framework 2.0.50727.42

Operating System 5.1.2600

I am now to programming but nor computer illiterate. I have been working with MS SQL servers since version 6.5 and have MCSE, Network+, A+ and Security plus certifications. Installing and using VS should not be this problematic. What is wrong with Microsoft?

TROUBLE LOGIN SQL SERVER 2005 ENTERPRISE

Fri, 27 Nov 2009 03:58:06 Z

Hi I am new to SQL SERVER 2005, I installed the SQL SERVER 2005 ENTERPRISE EDITION in my windows xp machine, there is no problem in installation, the problem is while connecting to the server it asks three details for validation

server name:
Authentication:

I dont konw the default server name? can anybody help me with this briefly how to login for the first time into the sql server 2005, Thanks in advance

Autogrow of log file - Error

Fri, 20 Nov 2009 12:55:12 Z

Hi Experts

I am using SQL Server 2000 (v8.0.2039). I am receiving the below error message.

Autogrow of file 'XXX_Log' in database 'XXXX' cancelled or timed out after 203 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.

Can you please advise what actions needs to be taken?

Kind Regards
Bhavesh

Bhavesh

Cross database and Distributed Transactions

Fri, 27 Nov 2009 16:25:33 Z

"Using database mirroring for Distributed Trasactions and cross database transactions are not supported in SQL Server 2005."

Is this still an issue after SP3 (2005)?

Is there any change in SQL 2008?

CHEKPOINT QUEUE

Fri, 27 Nov 2009 12:34:07 Z

Hi All,

I was observing the wait_type CHECKPOINT_QUEUE using the DMV SYS.DM_OS_WAITING_TASK.

As per BOL it is documented that CHECKPOINT_QUEUE "Occurs while the checkpoint task is waiting for the next checkpoint request."

As all of you know that the Full Bakcup should initiate a Checkpoint on the database. and i could see there is a check point happend due to full backup from the DBCC LOG command. But the CHECKPOINT_QUEUE wait type still continuous to be growing (Did not reset after the Full Backup)

Any thoughts on this?

Keys disappearing

Wed, 25 Nov 2009 22:26:23 Z

Several times now the primary key on one of my SQL Server 2008 tables has disappeared. I go to generate LINQ to SQL objects using SQLMetal, and it tells me that the table has no primary key, so I pull open SQL Management Studio, set the primary key (on an identity integer column, nothing fancy), save my change, re-generate code, and everything seems to be fine again. But later I'll try and re-generate and the primary key will be gone again. On top of that, I've now just experienced a foreign key reference going missing. The relationship is between two tables different from the table with the primary key problem.

I've tried restarting SQL Server, as well as re-generating code, and neither of these actions reproduces my problem. I'm running Windows 7, Visual Studio 2010 Team Edition (Beta 2) (formerly using it to generate my objects in a DBML file, but that was before I switched to SQLMetal), and I've got a SQL Database project that I use to track my database changed in TFS. Running that does not reproduce the problem, either.

Does anyone have any ideas what might possibly be causing these errors with my database Schema? I'll provide more information if needed.

MSSQLSERVER won't start event 9003 logged master corrupt

Fri, 27 Nov 2009 11:47:25 Z

Hi,

I am running SBS2003 R2 as a test server - no backup and I know I should have. Following Patch Tuesday this week the server was stuck in "Server is shutting down" process. I left this for 24 hours or so but eventually had to hit the reset button due to performance issues.

Since this reboot SQLServer fails to start. EventID 9003 is logged which points to a corrupt master db. I have googled and found sites stating to use DBCC but as the service wont start I can not connect to it to run DBCC.

eventvwr events
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9003
Date:  27/11/2009
Time:  11:25:32
User:  N/A
Computer: SBS2K3R2
Description:
The log scan number (225:112:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 2b 23 00 00 14 00 00 00   +#......
0008: 09 00 00 00 53 00 42 00   ....S.B.
0010: 53 00 32 00 4b 00 33 00   S.2.K.3.
0018: 52 00 32 00 00 00 00 00   R.2.....
0020: 00 00                     ..

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3417
Date:  27/11/2009
Time:  11:25:33
User:  N/A
Computer: SBS2K3R2
Description:
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 59 0d 00 00 0a 00 00 00   Y.......
0008: 09 00 00 00 53 00 42 00   ....S.B.
0010: 53 00 32 00 4b 00 33 00   S.2.K.3.
0018: 52 00 32 00 00 00 00 00   R.2.....
0020: 00 00                     ..

ERRORLOG
2009-11-27 11:25:28.20 Server Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Workgroup Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2009-11-27 11:25:28.24 Server      (c) 2005 Microsoft Corporation.
2009-11-27 11:25:28.24 Server      All rights reserved.
2009-11-27 11:25:28.24 Server      Server process ID is 3300.
2009-11-27 11:25:28.24 Server      Authentication mode is MIXED.
2009-11-27 11:25:28.25 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG'.
2009-11-27 11:25:28.25 Server      This instance of SQL Server last reported using a process ID of 6440 at 27/11/2009 10:52:37 (local) 27/11/2009 10:52:37 (UTC). This is an informational message only; no user action is required.
2009-11-27 11:25:28.34 Server      Registry startup parameters:
2009-11-27 11:25:28.36 Server      -d C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\master.mdf
2009-11-27 11:25:28.36 Server      -e C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG
2009-11-27 11:25:28.36 Server      -l C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\mastlog.ldf
2009-11-27 11:25:28.45 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-11-27 11:25:28.45 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2009-11-27 11:25:29.34 Server      Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2009-11-27 11:25:30.52 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-11-27 11:25:32.41 Server      Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-11-27 11:25:32.50 Server      Database mirroring has been enabled on this instance of SQL Server.
2009-11-27 11:25:32.66 spid5s      Starting up database 'master'.
2009-11-27 11:25:32.94 spid5s      Error: 9003, Severity: 20, State: 1.
2009-11-27 11:25:32.94 spid5s      The log scan number (225:112:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
2009-11-27 11:25:33.01 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

Thanks in advance

Cheers

Nigel

Cannot shrink log file because all logical log files are in use

Thu, 05 Oct 2006 09:23:34 Z

SQL 2005 9.0.2153 EE English
Database has recovery model = Full

I have job which is executed at night time. It executes these main tasks (in order of execution)
1. Renames transaction log backup of previous day.
2. Executes maintenance plan (check database -> rebuild indexes -> update statistics)
3. Makes fake backup of transaction log (to remove it later).
4. Shrinks log (dbcc shrinkfile(...,0, TRUNCATEONLY).
5. Deletes fake backup from step 3.
6. Makes full backup of database.

There is another job that executes every 15 minute at work time which backups transaction log.

Step 4 finishes with message "Cannot shrink log file ... because all logical log files are in use". And log size remains unchanged.

If I try to shrink log at work time using same dbcc shrinkfile I don't get message and log becomes small.

I added 2 steps with checkpointing. Now job executes these steps:

1. Renames transaction log backup of previous day.
2. Executes maintenance plan (check database -> rebuild indexes -> update statistics)
3. Checkpoints database
4. Makes fake backup of transaction log (to remove it later).
5. Checkpoints database
6. Shrinks log (dbcc shrinkfile(...,0, TRUNCATEONLY).
7. Deletes fake backup from step 3.
8. Makes full backup of database.

But message occures yet and log is not shrinked.
What I need to do to shrink log automatically. I need to shrink it because it grows very much after maintenence plan.

backup SQL Server 2005 database without data

Fri, 27 Nov 2009 07:08:57 Z

I have one stored procedure to backup the database. It will backup metadata as well as data.

Is there any option to back up the database with out data. ie, back up only the schema (Empty tables).

I dont want scripting the database.

SQL Server connection terminate

Fri, 27 Nov 2009 10:34:40 Z

When I start SQL Server it terminates the connection. I cannot rebuild the connection in subsequent attempts. Even i have not got any error message.

how to create automatic refresh every 30 minutes

Wed, 11 Nov 2009 20:50:09 Z

i Need create automatic refresh every 30 minutes. help from chile

Indexed Views on Tables in another Database

Fri, 27 Nov 2009 04:21:54 Z

HI ,

Sql Server does not allow to create Indexed Views on tables in another Database.
Is there a way around this??
OR Is there some other concept which can provide similiar functionality?

I am pretty new to SQL Server 2008.

Thanks.

SQL 2005 and LDF files. Are the following command acceptable and sufficient?

Sun, 08 Nov 2009 22:54:08 Z

I inherited a SQL 2005 server which has a database SBO-COMMON in full recovery model.

sbo-common, d:\sql data\sbo-common.mdf, initial size 596 MB
sbo-common_log, d:\sql data\sbo-common_log.ldf, initial size 30000 MB

As of now,

sbo-common.mdf 609KB
sbo-common_log.ldf 29322533KB

If I schedule the following commands every night before the tape backup runs:

---------------------------------------------------

use [sbo-common]
go
backup database [sbo-common] to disk = 'f:\sql backup\sbo-common.bak'
go
backup log [sbo-common] to disk = 'f:\sql backup\sbo-common_log.bak'
go
dbcc shrinkfile ('sbo-common_log',emptyfile)
go

------------------------------------------------------------------------

Would the above be acceptable and sufficient? I want the sbo-common_log.bak file to shrink.

What if I did the following?

----------------------------------------------------

use [sbo-common]
go
dbcc shrinkfile ('sbo-common_log',emptyfile)
go
backup database [sbo-common] to disk = 'f:\sql backup\sbo-common.bak'
go
backup log [sbo-common] to disk = 'f:\sql backup\sbo-common_log.bak'
go

------------------------------------------------

How will that affect my ability to restore a DB from tape backup if something happens?

Robo Copy command to transfer sql server logshipping files

Tue, 24 Nov 2009 15:45:20 Z

Hi Experts

I want to Copy log shipping files between primary and secondary servers.
Can some one help in providing the robocopy command for copying the files on to secondary server.There is no other go except roby copy as i have network related issues.

As a known fact, the logshipping file names will be dynamic with the datestamp included in the filename.

It has to pick only the files which are not existing on the secondary server.

This process to be scheduled in sql server agent.
Windows server 2003 with sql 2005 64-bit

Or is there any other way to handle this with xp_cmdshell (with dynamic file names and check if the files exist on secondary)

Thanks
Naga

transcation log suddenly growing

Thu, 26 Nov 2009 15:04:56 Z

Good Afternoon

My database is 60GByte.

The transaction Log is normally about 2Gbyte.

it's backed up every 4hrs.

The database is in full recovery mode and a full backup is done every 24hrs.

Occasionally the transaction Log grows to 80Gbyte.

How can I find out what's causing this?

any ideas much appreciated

Thanks

David Hills

deadlock alert implementation

Thu, 26 Nov 2009 17:10:12 Z

I followed the WMI provider approach, to obtain my database deadlock information, accourding to the following link:

http://msdn2.microsoft.com/en-us/library/ms186385.aspx

Now I have the data stored as XML on the table, I also configured my database mail settings to get e mails. But I have one question, how can I view the xml field as a deadlock graphic on the e mail?

Dropping an old legacy system table - syskeys

Thu, 26 Nov 2009 13:24:38 Z

We have a database that has been migrated from 2000 to 2005 a few years ago, when we try and do a dbcc checkdb we get a check catalog msg 3854, the offending object is a system table syskeys and the unique constraint UQ__syskeys__081660B5, i have done some research and i think that syskeys is an old sybase/sql server 6.5 system table, the sys.objects table indicates that this table is no longer a sql server system table (is_ms_shipped = 0)

Can someone confirm that this is in fact a legacy table and it can be dropped and how i can drop it, i have tried it on a copy of the database and because it has a type of 'S' it still thinks it's a system table and hence won't let me drop it.

Thanks in advance

Kevin.....

Linked Server Does Not Work while OpenDataSource/OpenRowSet does

Mon, 23 Nov 2009 20:25:21 Z

Okay,

I'm doing some massive imports and manipulations of data from an Excel file into multiple tables of a database. Originally I had all my scripts set up to use OpenRowSet() adhoc queries, because I've used them and they are tried and true tested to always work the way i've been using them.

However, in the fun of experimenting on LInked servers, I found I could linke the different instances of SQL Server together, and it was most beneficial for certain situations, and I thought: Why not do this with my Excel Files, so I DOn't need to Search/Replace the FIle name every time the file gets updated on the server.

So, the SQL SErver instance is running on my personal PC. (For Development and Debugging purposes. I later migrate my creations to the Network SQL Server Instance).

The File Is on my Personal PC. (don't feel like dealing with the occasional glitch with the SPN's and all that headache, so I'm just dealing with a LOCAL file for the moment.)

OpenRowSet:
        SELECT *
        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=C:\Source\Excel\BOL Account #s Master-Edits 11.20.09.xls', [SortCenters$])

        SELECT *
        FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; Database=C:\Source\Excel\BOL Account #s Master-Edits 11.20.09.XLS', [AllPoolCorbi$])

OpenDataSource:
Select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\Source\Excel\BOL Account #s Master-Edits 11.20.09.xls";Extended Properties=Excel 8.0')...[SortCenters$] a
Select *
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\Source\Excel\BOL Account #s Master-Edits 11.20.09.xls";Extended Properties=Excel 8.0')...[AllPoolCorbi$] a

Linked Server:
EXEC sp_addlinkedserver 'XLS', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'C:\Source\Excel\BOL Account #s Master-Edits 11.20.09.xls', NULL, 'Excel 8.0'
GO

select * from XLS...SortCenters$

Results:

OpenRowSet - Works
OpenDataSrouce - Works
Exec sp_addLinkedServer - Comes back with Success Message
Select * from XLS...SortCenters$
   ERROR: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "XLS" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

Now, A) Why in the world does an Excel file need a Login. It's a File. There IS no security context for the file. This is a LOCAL Server, LOCAL file, and it still fails on some stupid superfluous security issue. What's the point of a linked server that points to an Excel file when it doesn't work. if OPENROWSET always works and the add linked server is so finicky it apparently defeats the purpose, traumatizing my life and wasting my time for even trying. (so far I've got an hour down the drain because the LInked server doesn't work the way it's supposed to)

If I could I'd rather use a CTE, but apparently I can't apply a CTE to an entire execution block (BEGIN...END for multiple insert statements into multiple tables from one cte)

As it is, it will be simpler for me to SELECT * INTO XLS from OPENROWSET() and work with it that way, but frankly that does NOTHING to help my respect or opinion of SQL Server. Every step I take in one direction causes more stress, due to certain security rights when there is no NEED for them. (ESPECIALLY WHEN I'M LOGGED IN AS SA!!!) If I am SA I am GOD, and the Server should never tell me no. *chuckle* well, within reason, of course. But I think my frustration is clear as to why.

Sorry for the frustration, but I've followed the "MSDN " described steps for adding an excel file as a linked server, and it doesn't work. So either the MSDN help is wrong in telling me how to do it, it is wrong that SQL Server even can do it, or most likely: It is incomplete, not giving ALL information needed to execute a command, which means yet again, MSDN - fix your documentation. Until that happens (like never will) could someone point out how to link and excel file as a Linked Server - WITH NO SECURITY CONTEXT BS. Just treat it like "OPENROWSET" but have it constant so I don' need to write out the full open row set command for all 100 SQL statements i'm executing.

Jaeden "Sifo Dyas" al'Raec Ruiner

"Never Trust a computer. Your brain is smarter than any micro-chip."

Limit access from allowed clients

Wed, 25 Nov 2009 10:44:10 Z

I need to limit access to my sql server 2005 engine only from allowed IPs.

I can't use firewall because the clients have to stay in the same network so I have to find another way.

Can you help me ?

Thanks.

indexed view - The view contains a self join ON dimDate

Tue, 02 Dec 2008 09:57:28 Z

hi folks,

i have just pick up on this annoying problem with INDEXED VIEWS.

i have a view like

create view vw_carServices_idx

with schemmabinding
as

select

t.carType,
c.carColor,
sDt.serviceDt,
cDt.completitionDt,
f.totalCost,

count_big(*) AS countingBig

from

factServices f

inner join dimCarType AS t ON t.carTypeKey = f.carTypeKey
inner join dimColor AS c ON c.colorKey = f.colorKey
inner join dimDate AS sDt ON sDt.dtKey = f.serviceDtKey
inner join dimDate AS cDt ON cDt.dtKey = f.completitionDtKey

group by

t.carType,
c.carColor,
sDt.serviceDt,
sDt.completitionDt,
f.totalCost

the above compiles fine but when i try to create an index against this view, i get the following message.

Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "myDB.vw_carServices_idx". The view contains a self join on "myDB.dimDate".

so is there someway around this problem?

thanks,

Nicolas

PPS 64 bit installation

Thu, 26 Nov 2009 07:51:18 Z

Hi,

We have 64 bit machines installed with SQL Server 2008.
We are trying to install PPS 2007 on these machines.

All the pre requisites that are required for PPS are installed.
However, when configuring the Monitoring server, in the Database section, the SQL Server location errors out with the following error message:
"Connection to SQL Server location failed. "
It tries to evaluate Microsoft SQL Server 2005.
And all our servers are 2008.

Is the error because of the Version of the SQL Server that we have?

Any suggestions to resolve this error?

Thanks in advance

Bhavana

standard view with multiple inner joins. selecting a single column what happens in the background?

Wed, 25 Nov 2009 20:34:20 Z

I cant find a diffinitive answer, so lets ask here..

the background...
say a view exists so to generate a 'virtual table' to which joins many columns from other tables. those other tables are lookup tables for multiple choice options .. ie store ID, but reference a descriptive field as ID means nothing to a user. the view itself contains a large number of columns but 90% of the time, only a handful of columns from the view will be referenced .. be it different column on each query.

the reason for the view is security access... (nope, it not index due to 'other' restriction stopping schema binding at this time)

if i select a few columns, but no columns that refer to any inner joins in the view .. what does sql server actually fetch in the background??
does it fetch every column in the view's select statement, then return just the column i asked for?? (note view itself does not contain an order by statement)
or does it satisfy all joins (inner and outer??)
or even satisfy every column mentioned in the where clause and just select columns i ask for .. before returning results??

from an exaplin plan it looks like the engine certainly has to satisfy some joins but which ones? .. plus i dont know if in the background its returning internaly every column ??

performance is the issue, but need to understand whats occuring in the background to decide my next step!

Thanks.

Creating a Linked Server with 64 bit SQL Server 2008 to MS Access

Tue, 24 Nov 2009 04:07:32 Z

I have SQL Sever 2008 64 bit installed. I am trying to create a linked server to a MS Access database.
I have been unsuccessfull and getting this done. Does anyone have anyh suggesstions on what has worked?

MSDN Virtual Lab

Thu, 26 Nov 2009 03:59:00 Z

Dear DBAs,

I tried MSDN Virtual Lab on my laptop and kept getting this error.

"This site requires the following ActiveX control: 'Virtual Server VRMC Advanced Control' from Microsoft"

Could you point me to an URL where I can download?

Thank you,

Database FAIL: transaction log maxed out.

Wed, 25 Nov 2009 22:44:39 Z

I have a database that for any task (inlcuding trying to enlarge or clear the transaction logs) gives a message that the transaction log is full and to check sys.databases field log_reuse_wait_description. That table gives the log_reuse_wait code as 2 and the description as BACKUP. This database is being backed up only by Microsoft DPM and any attempt to either do a consistency check (fail) or drop protection of the database (success) has had no effect on this. Is there any way to force the database to clear this field?

Error message on SQL counters (Event ID 1021)

Thu, 19 Nov 2009 04:27:21 Z

I am trying to monitor databases of a MSSQLServer2000 on a W2003R2 64 bit server. No instances are found in wmi and MSSQL or SQL objects do not appear in Performance Monitor. Therefore they are not to be find in wmi.
Please, give me some directions how to follow up on the error message that appears in Event Viewer, Event ID 1021: Windows cannot open the 32-bit extensible counter DLL MSSQLSERVER in a 64-bit environment. Contact the file vendor to obtain a 64-bit version

sys.dm_db_index_physical_stats execution time

Wed, 25 Nov 2009 22:06:47 Z

I'm trying to understand what factors that are important for the execution time for a query against sys.dm_db_index_physical_stats.

As I undestand it, it is scanning the pages on the index level above the leaf level if you're running it in LIMITED mode (default).

How does this work when there are LOB data types?

I'm looking at a table with two nvarchar(max) columns, where it takes a very long time (15 minutes) to run a query against sys.dm_db_index_physical_stats (LIMITED mode).

index_type_desc alloc_unit_type_desc index_depth index_level page_count

CLUSTERED INDEX IN_ROW_DATA 3 0 13642

CLUSTERED INDEX LOB_DATA 1 0 3308649

Ola Hallengren

http://ola.hallengren.com

Warning: SQL cache memory usage: %d (pages)

Wed, 25 Nov 2009 10:53:10 Z

Reading through as much documentation as I can and searching through Books Online and forums I cannot find any reference for this SQL Server log message for SQL Server 2005 or 2008.

The SQL Server 7 reference is mentioned at the end this page: http://technet.microsoft.com/en-us/library/cc917590.aspx

The SQL Server 2000 reference is mentioned here: http://technet.microsoft.com/en-us/library/aa226378%28SQL.80%29.aspx

Is there any Microsoft documentation relating to this message available for SQL Server 2005 and SQL Server 2008?

Kind Regards,
Martin

Sometime sql server locks down/freezes

Sat, 21 Nov 2009 11:31:48 Z

Hi,

This weird thing happens when I try to read the table using my program. It does not happen all the time.
This is what happens:
When I tell the program to give me the list of objects, for example invoices, it waits for unusual times and even when I go to Management studio to run the query the same thing happens, but whn I quit my program and use the management studio that wil not happen.

This happnes sometimes and not all the times with the same procedues. Even when I am not writing any data and only read the data. I am working on a single computer and the computer does not have any user to lock it down. But, I have some backgroud threads that priodically,using timers, connects to SQl server with the same user id to get some data such as the reminder. I am wondering it these threads could be causing the problem. If so, how can I fix it, so the threads can use their jobs and my program does its job as well.

Does SQL Server Support the "MINUS" Keyword?

Wed, 22 Mar 2006 23:33:05 Z

Maybe there's another better way to do this... Anyway, here's what I'm trying to do:

I have two tables CarType & Cars. Table CarType has a column CarTypeId which is the primary key (int, identity). Table Cars includes a column CarTypeId (int) which is a foreign key into the CarType table.

Now I am trying to build a list of all those CarTypeId entries (from the CarType table) for which there are no Cars (i.e. there is no corresponding entry in the Cars table).

I read that there is an SQL "MINUS" keyword that you can use like this:

SELECT CarTypeId FROM CarType
MINUS
SELECT CarTypeId FROM Cars

So if CarType contains records with the keys {1, 2, 3, 4, 5} and Cars contains records with the foreign keys {2, 4} (using set notation), your result should be the set {1, 3, 5}. (Note: I have tried alternate versions of this where I selected the DISTINCT CarTypeId entries from Cars, and where I sorted them; results same in both cases).

I tried it in SQL Server 2005 Express. The result is just {1, 2, 3, 4, 5}.

My questions:

Is the MINUS capability supported by SQL Server? I know that there are all sorts of different SQL dialects & implementations and that SQL has evolved & transmutated over the years; perhaps they opted to leave this out of SQL Server...
If not supported, well then I guess I have to throw myself at the feet of the real SQL gurus and ask if there's another way to accomplish this.

Here's a reference to the website where I initially found out about MINUS:
http://www.1keydata.com/sql/sql-minus.html

Thanks in advance for your time/comments.