17803 errors all over the place....
We have close to 400 SQL Server instances to manage. Within the last month we are starting to see 17803 errors at least once a week on completely different servers, different customers, different hardware, etc. We used to get one maybe every 6 months. SQL 2000, SP4 + .2187 hotfix rollup on most.
Anyone else seeing this? I'm wondering if something at the O/S level is the commonality (assuming there is one).
10/30/2007
10/25/2007
Do what?
So....I've got this job that backs up the T-log every 15 minutes (SQL 2005). Works like a champ across all servers.
Out of the blue, it starts failing with a "No full backup detected", despite the fact that one occurred less than an hour ago. Its recorded in the msdb system tables and the file is right where the full backup job left it.
The customer had decided for reasons unknown to create a job that does this (every hour):
Use MyDB
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
BACKUP LOG MyDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
go
I kid you not. And no...there is not a second file to EMPTY the log contents into.
For those of you new to the game, when you Truncate a T-log, the sequence is broken and SQL Server barfs on future log backups until you run a full.
Kudos to my co-worker (code name Freakshow) for catching this as I was headed to the ERRORLOG to see what was going on. He nailed in 10 seconds what would have taken me 7 minutes. And then he knew it was a new job while I was still reading the log. Scary smart, that dude.
No fries, but ketchup squirted all over that client.
Kevin3NF
So....I've got this job that backs up the T-log every 15 minutes (SQL 2005). Works like a champ across all servers.
Out of the blue, it starts failing with a "No full backup detected", despite the fact that one occurred less than an hour ago. Its recorded in the msdb system tables and the file is right where the full backup job left it.
The customer had decided for reasons unknown to create a job that does this (every hour):
Use MyDB
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
BACKUP LOG MyDB WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDB_Log, EMPTYFILE)
go
I kid you not. And no...there is not a second file to EMPTY the log contents into.
For those of you new to the game, when you Truncate a T-log, the sequence is broken and SQL Server barfs on future log backups until you run a full.
Kudos to my co-worker (code name Freakshow) for catching this as I was headed to the ERRORLOG to see what was going on. He nailed in 10 seconds what would have taken me 7 minutes. And then he knew it was a new job while I was still reading the log. Scary smart, that dude.
No fries, but ketchup squirted all over that client.
Kevin3NF
10/24/2007
9/12/2007
Performance on a clustered SQL 2005 instance
Customer sez: I've been getting timeouts all morning
Kevin3NF sez: CPUs are good, memory is fine (buffer cache hit ratio and page life expectancy...my two favorite memory counters for quick hit analysis..)
Kevin3NF: Uh-oh....why is the disk queue so high for drive N? Houston, we found the problem...need clearance for root cause.
Root cause: Someone decided to expand the volume on the SAN that included the data drive, T-log drive and backup drive while the instance was running, and at HIGH priority. The second it finished, everything went back to normal.
Sadly, I needed a Windows/Storage guy to tell me what was going on after I identified I/O as the resource bottleneck.
Kevin3NF
Customer sez: I've been getting timeouts all morning
Kevin3NF sez: CPUs are good, memory is fine (buffer cache hit ratio and page life expectancy...my two favorite memory counters for quick hit analysis..)
Kevin3NF: Uh-oh....why is the disk queue so high for drive N? Houston, we found the problem...need clearance for root cause.
Root cause: Someone decided to expand the volume on the SAN that included the data drive, T-log drive and backup drive while the instance was running, and at HIGH priority. The second it finished, everything went back to normal.
Sadly, I needed a Windows/Storage guy to tell me what was going on after I identified I/O as the resource bottleneck.
Kevin3NF
8/20/2007
2005 Log Shipping
ok...I'm behind the times and just started working with SQL Server Log Shipping instead of writing my own custom scripts (Backup, Copy, Restore...just ain't that hard...)
Messing with a dead LS database this weekend, I tried to script out the existing configuration from the Log Shipping GUI. Has anyone else seen this thing drop all of the information on the destination server when it created the script?
I didn't bother to Google it and just re-created from scratch.
It bothers me that you have to drop and re-create to "re-initialize" a log shipped database.
No fries for me :(
ok...I'm behind the times and just started working with SQL Server Log Shipping instead of writing my own custom scripts (Backup, Copy, Restore...just ain't that hard...)
Messing with a dead LS database this weekend, I tried to script out the existing configuration from the Log Shipping GUI. Has anyone else seen this thing drop all of the information on the destination server when it created the script?
I didn't bother to Google it and just re-created from scratch.
It bothers me that you have to drop and re-create to "re-initialize" a log shipped database.
No fries for me :(
8/15/2007
LazyWriter: warning, no free buffers found.
SQL 2005, 9.0.3042
Got this in my ERRORLOG, along with a ton a memory messages not worth repeating here.
8GB RAM in the box, SQL Server set to dynamically allocate, and using 6.3 gb.
SQL was not allowing connections, giving the misleading "remote connections are not allowed/TCP" messages (translated: "something bad happened")
Solution (as seen in other places, but not many)...hard reboot of the server, bring up SQL, configure max memory to 6GB (anything other than dynamic, but leave some for the O/S).
No fries...took 30 minutes to get to the resolution. Dessert maybe :)
Kevin3NF
SQL 2005, 9.0.3042
Got this in my ERRORLOG, along with a ton a memory messages not worth repeating here.
8GB RAM in the box, SQL Server set to dynamically allocate, and using 6.3 gb.
SQL was not allowing connections, giving the misleading "remote connections are not allowed/TCP" messages (translated: "something bad happened")
Solution (as seen in other places, but not many)...hard reboot of the server, bring up SQL, configure max memory to 6GB (anything other than dynamic, but leave some for the O/S).
No fries...took 30 minutes to get to the resolution. Dessert maybe :)
Kevin3NF
6/21/2007
4/24/2007
2/26/2007
2/06/2007
Enterprise Manager hang
My customer's SQL 2000 .818 box has started timing out when he tries to use Enterprise manager to backup or restore a database.
After giving him the command to run in Query Analyzer, I took a look at his backup tables in MSDB and found roughly 3.8 million records in each...dating back to mid 2005.
I'll be running sp_delete_backuphistory tonight. :)
If you don't want to use the cursor driven proc provided by MSFT, click here for Tara Kizer's custom script:
isp_DeleteBackupHistory
Sorry for the long delay between postings...its been slow :)
Kevin3NF
My customer's SQL 2000 .818 box has started timing out when he tries to use Enterprise manager to backup or restore a database.
After giving him the command to run in Query Analyzer, I took a look at his backup tables in MSDB and found roughly 3.8 million records in each...dating back to mid 2005.
I'll be running sp_delete_backuphistory tonight. :)
If you don't want to use the cursor driven proc provided by MSFT, click here for Tara Kizer's custom script:
isp_DeleteBackupHistory
Sorry for the long delay between postings...its been slow :)
Kevin3NF
1/24/2007
1/16/2007
Database in Read-Only after detach/attach
I got a "middle of the night panic call" this morning. My customer was moving 2 user databases to a new drive on the same server.
After detach and copy, he re-attached successfully. But, the databases are in read-only mode and cannot be changed (Error 5105, Device activation error. The physical file name '%.*ls' may be incorrect.)
SQL 2000, post Sp3, Windows 2003, sp1.
Additional errors:
2007-01-16 03:06:41.02 spid61 Starting up database 'MyDatabase'.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:\SQLData\MyDatabase_Data.mdf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:\SQLData\MyDatbase_Data.mdf for virtual device number (VDN) 1.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:\SQLData\MyDatabase_Log.ldf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:\SQLData\MyDatabase_Log.ldf for virtual device number (VDN) 2.
Turned out that the SQL Server startup account did not have the correct security permissions on the folder or the files. Whoops. :)
Kevin3NF
I got a "middle of the night panic call" this morning. My customer was moving 2 user databases to a new drive on the same server.
After detach and copy, he re-attached successfully. But, the databases are in read-only mode and cannot be changed (Error 5105, Device activation error. The physical file name '%.*ls' may be incorrect.)
SQL 2000, post Sp3, Windows 2003, sp1.
Additional errors:
2007-01-16 03:06:41.02 spid61 Starting up database 'MyDatabase'.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:\SQLData\MyDatabase_Data.mdf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:\SQLData\MyDatbase_Data.mdf for virtual device number (VDN) 1.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:\SQLData\MyDatabase_Log.ldf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:\SQLData\MyDatabase_Log.ldf for virtual device number (VDN) 2.
Turned out that the SQL Server startup account did not have the correct security permissions on the folder or the files. Whoops. :)
Kevin3NF
1/12/2007
New Year's resolutions kill Log Shipping!
ok...funniest issue I've run across in a while :)
We have some custom log shipping script that was working just fine until the end of 2006. Starting around January 2, the restore process couldn't restore fast enough to keep the standby server current.
I looked at the T-Log files for the last 2 weeks and saw that the T-log backups between 3 and 5 am were 2 to 3 times the size they used to be.
Why? Because the customer here is a national Fitness center business that had a huge influx of memberships and activities :)
People trying to lose weight broke log shipping.
The fix? Run the restore process more often (it was only running off hours).
You get fries AND a low-fat shake with this one...
Kevin3NF
ok...funniest issue I've run across in a while :)
We have some custom log shipping script that was working just fine until the end of 2006. Starting around January 2, the restore process couldn't restore fast enough to keep the standby server current.
I looked at the T-Log files for the last 2 weeks and saw that the T-log backups between 3 and 5 am were 2 to 3 times the size they used to be.
Why? Because the customer here is a national Fitness center business that had a huge influx of memberships and activities :)
People trying to lose weight broke log shipping.
The fix? Run the restore process more often (it was only running off hours).
You get fries AND a low-fat shake with this one...
Kevin3NF
Autogrow continued:
re: 12/19/2006 SQL 2005 Autogrow issue
I have confirmed with Microsoft that the bug will be fixed in SP2, and that there is no workaround other than monitoring.
I have written a script for my customer that will check the 'growth' and 'status' values in the sysfiles table of each database on their system. If the growth number is over x and the status is over 'y'...we have a problem.
The best way to avoid this whole mess:
MANAGE YOUR DATAFILES!!
If you rely on Autogrow, you are not properly administering your database. Autogrow is a failsafe mechanism for when you get unexpected growth, and nothing more.
Kevin3NF
re: 12/19/2006 SQL 2005 Autogrow issue
I have confirmed with Microsoft that the bug will be fixed in SP2, and that there is no workaround other than monitoring.
I have written a script for my customer that will check the 'growth' and 'status' values in the sysfiles table of each database on their system. If the growth number is over x and the status is over 'y'...we have a problem.
The best way to avoid this whole mess:
MANAGE YOUR DATAFILES!!
If you rely on Autogrow, you are not properly administering your database. Autogrow is a failsafe mechanism for when you get unexpected growth, and nothing more.
Kevin3NF
12/26/2006
12/19/2006
SQL 2005 Autogrow issue
See my 12/7 entry about database being in transition for some history...
Have a SQL 2005 box that occasionally decides to change the Autogrow method from 500 MB (64000 pages) to %, which SQL sees as 64000%.
Apparently, this is a known bug since the SQL 7 days:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Let me know if you cannot see the page...
The only workaround I've found is:
Alter Database MyTestDB
Modify File (name=mytestdb, filegrowth = 500 mb)
Problem is that this is generating 3am phone calls to the DBA team when Autogrow kicks in and blows out the data drive.
Grr.
Supposedly this will be fixed in SP2.
UPDATE 6/4/2008 - SP2 does in fact resolve this issue
Kevin3NF
See my 12/7 entry about database being in transition for some history...
Have a SQL 2005 box that occasionally decides to change the Autogrow method from 500 MB (64000 pages) to %, which SQL sees as 64000%.
Apparently, this is a known bug since the SQL 7 days:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Let me know if you cannot see the page...
The only workaround I've found is:
Alter Database MyTestDB
Modify File (name=mytestdb, filegrowth = 500 mb)
Problem is that this is generating 3am phone calls to the DBA team when Autogrow kicks in and blows out the data drive.
Grr.
Supposedly this will be fixed in SP2.
UPDATE 6/4/2008 - SP2 does in fact resolve this issue
Kevin3NF
12/13/2006
Priceless...
I have a customer that is trying to throw my DBA team under the bus for their server going nuts. By nuts, I mean all 8 CPUs pegged at 100%, causing the application to crash.
I'm very sympathetic to the customer issues, as this is an eCommerce site and they lose money when its not available...but please...
What my team did: Backup Database 'Foo'....
That's it.
Side issue: Their datafile went from 30gb of data in the file to 13 gb. I sat there and refreshed Enterprise Manager and watched it go down, thinking to myself "Good, they are purging some data." They deny it, and we'll probably get blamed for this too :P
Kevin3NF
I have a customer that is trying to throw my DBA team under the bus for their server going nuts. By nuts, I mean all 8 CPUs pegged at 100%, causing the application to crash.
I'm very sympathetic to the customer issues, as this is an eCommerce site and they lose money when its not available...but please...
What my team did: Backup Database 'Foo'....
That's it.
Side issue: Their datafile went from 30gb of data in the file to 13 gb. I sat there and refreshed Enterprise Manager and watched it go down, thinking to myself "Good, they are purging some data." They deny it, and we'll probably get blamed for this too :P
Kevin3NF
12/07/2006
Error 952 Database 'Foo' is in transition...
I have yet to find a reliable source of the definition of 'transition' in SQL Server 2005.
My customer had this error come up in conjunction with a database that got set to 64000% Autogrow and went nuts.
The only reference I found was here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=209294&SiteID=1
I used his resolution and it worked. This must be a horrible design issue when closing the client utility affects the availability of a database.
Ugh. No fries with this one, since the database was offline for 4 hours.
Please post a comment if you can explain more about this error to me...
Thanks
I have yet to find a reliable source of the definition of 'transition' in SQL Server 2005.
My customer had this error come up in conjunction with a database that got set to 64000% Autogrow and went nuts.
The only reference I found was here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=209294&SiteID=1
I used his resolution and it worked. This must be a horrible design issue when closing the client utility affects the availability of a database.
Ugh. No fries with this one, since the database was offline for 4 hours.
Please post a comment if you can explain more about this error to me...
Thanks
Subscribe to:
Posts (Atom)