Team Discovery Channel wins the Tour of Georgia!
Sorry folks...I just haven't done much of interest in the SQL Server world lately....pretty much just the same old same old :)
Kevin3NF
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
12/04/2006
11/14/2006
SSIS subsystem failed to load
Customer's weekly maintenance plan failed to run:
...Maintenance Plan,Subplan,,Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed
Setup:
SQL 2005, RTM, 2 node single instance cluster on Windows 2003
Lots of checking later, the problem turned out to be very simple:
Install SSIS on the node it was failing on.
Kevin3NF
Customer's weekly maintenance plan failed to run:
...Maintenance Plan,Subplan,,Unable to start execution of step 1 (reason: The SSIS subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed
Setup:
SQL 2005, RTM, 2 node single instance cluster on Windows 2003
Lots of checking later, the problem turned out to be very simple:
Install SSIS on the node it was failing on.
Kevin3NF
11/02/2006
SQL 2005 Performance
Got a call on a new SQL 2005 install (RTM), with pegged CPUs. All 4 running at 100%. Task manager says we are using 1.6 GB of our 8GB RAM. Database is 1.5GB, so I figure that's pretty close.
Nope.
Anyone want to guess what the final issue turned out to be after a total of 8 hours of troubleshooting by 3 different people?
Answer tomorrow...post your guess as a comment...
No winner today (i.e. no comments)....AWE option wasn't selected.
Kevin3NF
Got a call on a new SQL 2005 install (RTM), with pegged CPUs. All 4 running at 100%. Task manager says we are using 1.6 GB of our 8GB RAM. Database is 1.5GB, so I figure that's pretty close.
Nope.
Anyone want to guess what the final issue turned out to be after a total of 8 hours of troubleshooting by 3 different people?
Answer tomorrow...post your guess as a comment...
No winner today (i.e. no comments)....AWE option wasn't selected.
Kevin3NF
Hi folks....sorry no update recently. I've changed jobs from Microsoft PSS (contract) to somewhere else I can't name :D
I am still in SQL Server support, but have moved up a notch on the food chain. I now take only the more complex issues that the front-line crew can't sort out fairly quickly. All versions, all issues, which is going to be a challenge, since I've not supported SQL 2005 yet.
Wish me luck!
Kevin3NF
I am still in SQL Server support, but have moved up a notch on the food chain. I now take only the more complex issues that the front-line crew can't sort out fairly quickly. All versions, all issues, which is going to be a challenge, since I've not supported SQL 2005 yet.
Wish me luck!
Kevin3NF
9/26/2006
Old stuff can still work...
Got called to a "server down" the other day. SQL 7.0, running on Windows NT 4.0, on a dual PII 333 server maxed out at 1GB RAM.
Seems that the SQL Server for some reason was extremely slow. Customer thought disk space was an issue, but he had over a gig free and wan't autogrowing.
Ran sp_updatestats, which took over an hour on 12gb of data.
Looked into task manager....found SQL Server only using 50MB of RAM.
As it turns out, Microsoft Message Queue was taking over 800MB of the 1GB of memory. Whoops.
Solution (3 hours later): Purge the MSMQ journal.
SQL Server is now flying.
You want fries with that?
Kevin3NF
Got called to a "server down" the other day. SQL 7.0, running on Windows NT 4.0, on a dual PII 333 server maxed out at 1GB RAM.
Seems that the SQL Server for some reason was extremely slow. Customer thought disk space was an issue, but he had over a gig free and wan't autogrowing.
Ran sp_updatestats, which took over an hour on 12gb of data.
Looked into task manager....found SQL Server only using 50MB of RAM.
As it turns out, Microsoft Message Queue was taking over 800MB of the 1GB of memory. Whoops.
Solution (3 hours later): Purge the MSMQ journal.
SQL Server is now flying.
You want fries with that?
Kevin3NF
9/15/2006
SQL 2000 cluster with Veritas Volume Manager
Ran into an issue yesterday where the RTM SQL 2000 install on a Windows 2003 cluster would freeze or hang between the IP addresss entry and the Disk Selection screens. No error...just hang.
Looked in Cluster Admin and found that the SQL group was named "data" and the disk resource was named "data"
We changed the disk resource to "data1" and everything was fine after that.
Not sure if the Veritas Volume manager software had anything to do with it, but it did present some issues earlier in the install.
Takeaway: Name your SQL Group something obvious, like "SQL Group" or "SQL2000"
Kevin3NF
Ran into an issue yesterday where the RTM SQL 2000 install on a Windows 2003 cluster would freeze or hang between the IP addresss entry and the Disk Selection screens. No error...just hang.
Looked in Cluster Admin and found that the SQL group was named "data" and the disk resource was named "data"
We changed the disk resource to "data1" and everything was fine after that.
Not sure if the Veritas Volume manager software had anything to do with it, but it did present some issues earlier in the install.
Takeaway: Name your SQL Group something obvious, like "SQL Group" or "SQL2000"
Kevin3NF
8/30/2006
Outta control log file
Customer sez: My log file is 50GB...help!
Kevin3NF: Back it up
Customer: I did, only 46 mb used, still won't shrink
Kevin3NF: Open Query Analyzer, type Checkpoint and hit F5
Customer: ok...now what?
Kevin3NF: Shrink it now
Customer: Hey...cool!
Kevin3NF: That'll be $2.99 please :)
Elapsed time: 4 minutes
We were going to go through the "Dummy table, load it with transactions" bit, but decided to try checkpoint first...
Customer sez: My log file is 50GB...help!
Kevin3NF: Back it up
Customer: I did, only 46 mb used, still won't shrink
Kevin3NF: Open Query Analyzer, type Checkpoint and hit F5
Customer: ok...now what?
Kevin3NF: Shrink it now
Customer: Hey...cool!
Kevin3NF: That'll be $2.99 please :)
Elapsed time: 4 minutes
We were going to go through the "Dummy table, load it with transactions" bit, but decided to try checkpoint first...
7/01/2006
SQL Server 2000 Notification Services
OK folks....it seems a lot of people are scared of implementing and troubleshooting SQL 2000 Notification Services. Perhaps its thought of as something complicated like replication can be?
I'd like your thoughts on this
My take on it (which combined with $.35 will get you a phone call) is that this is a very simple process:
1. Something happens in your database (add a row, sell a stock, whatever)
2. Information about that event moves to the Events table of the separate NS database
3. Periodically, a query runs to see if anyone cares about that Event (i.e. has Subscribed to it)
4. If so, a record for each match of Event and Subscriber goes into the Notification table
5. Notification gets Delivered (via SMTP,phone,etc.)
That's it.
Troubleshooting:
Most common complaint: "I didn't get notified when Oracle stock dropped $20..."
1. Look in the Application and System Event logs on the SQL Server running the NS database for errors
2. Look in the Notifications table to see if there are rows there that match. If so, your delivery system is hosed
3. If not, look for the event in the EVENTS table. If not there, you will NOT get notified. If they are, see if you have a valid Subscription to the Event (check the appADF.xml file for the query that does this...run it)
4. If the Events are not in the Events table, you need to look at whatever process is in place to move data from your source database to the NS database. This could be triggers, stored procs, custom DLL/EXE....just about anything.
Note for MOM 2005 users: If you see events in the MOM console but not in the Events table...good luck figuring out why. Try re-starting the service (Notification Workflow I think...). This is a black box that not even Microsoft understands really well, yet.
Final note...NS databases are SQL databases just like any other...please maintain your indexes and vacuum your old useless data (see NS Books Online for vacuuming setup)
Please add comments as necessary...I've tried to "English" this a bit for clarity.
Kevin3NF
OK folks....it seems a lot of people are scared of implementing and troubleshooting SQL 2000 Notification Services. Perhaps its thought of as something complicated like replication can be?
I'd like your thoughts on this
My take on it (which combined with $.35 will get you a phone call) is that this is a very simple process:
1. Something happens in your database (add a row, sell a stock, whatever)
2. Information about that event moves to the Events table of the separate NS database
3. Periodically, a query runs to see if anyone cares about that Event (i.e. has Subscribed to it)
4. If so, a record for each match of Event and Subscriber goes into the Notification table
5. Notification gets Delivered (via SMTP,phone,etc.)
That's it.
Troubleshooting:
Most common complaint: "I didn't get notified when Oracle stock dropped $20..."
1. Look in the Application and System Event logs on the SQL Server running the NS database for errors
2. Look in the Notifications table to see if there are rows there that match. If so, your delivery system is hosed
3. If not, look for the event in the EVENTS table. If not there, you will NOT get notified. If they are, see if you have a valid Subscription to the Event (check the appADF.xml file for the query that does this...run it)
4. If the Events are not in the Events table, you need to look at whatever process is in place to move data from your source database to the NS database. This could be triggers, stored procs, custom DLL/EXE....just about anything.
Note for MOM 2005 users: If you see events in the MOM console but not in the Events table...good luck figuring out why. Try re-starting the service (Notification Workflow I think...). This is a black box that not even Microsoft understands really well, yet.
Final note...NS databases are SQL databases just like any other...please maintain your indexes and vacuum your old useless data (see NS Books Online for vacuuming setup)
Please add comments as necessary...I've tried to "English" this a bit for clarity.
Kevin3NF
Subscribe to:
Posts (Atom)