Merry Christmas!
May the Lord Bless you and keep you in this time of remebrance of the ultimate gift. :)
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
6/09/2006
Replication troubleshooting
If you are getting errors in any of the replication agents (SQL 2000), and the GUI isn't giving you the details you need, turn on logging.
This is not an intuitive process, and when you are done you will want to make sure you turn it off or the log file can eventually fill up your drive given enough time.
To log an agent's activities, right-click on the Agent in Replication Monitor in Enterprise Manager. Select Agent Properties. You should get the Job info for the agent. Click the steps tab and Edit the "Run Agent" step.
You should see a command line with a bunch of parameters. Add these at the end:
-output c:\Agent_log.txt -outputverboselevel 3
Change the path and file name of the first one to an appropriate drive on the Distribution server.
The outputverboselevel parameter is documented as taking 0, 1 and 2 in Books Online. 3 is also an option and records everything.
If you wind up calling Microsoft SQL Server support, they will likely ask you for this info.
Good luck, and happy replicating!
Kevin3NF
If you are getting errors in any of the replication agents (SQL 2000), and the GUI isn't giving you the details you need, turn on logging.
This is not an intuitive process, and when you are done you will want to make sure you turn it off or the log file can eventually fill up your drive given enough time.
To log an agent's activities, right-click on the Agent in Replication Monitor in Enterprise Manager. Select Agent Properties. You should get the Job info for the agent. Click the steps tab and Edit the "Run Agent" step.
You should see a command line with a bunch of parameters. Add these at the end:
-output c:\Agent_log.txt -outputverboselevel 3
Change the path and file name of the first one to an appropriate drive on the Distribution server.
The outputverboselevel parameter is documented as taking 0, 1 and 2 in Books Online. 3 is also an option and records everything.
If you wind up calling Microsoft SQL Server support, they will likely ask you for this info.
Good luck, and happy replicating!
Kevin3NF
6/06/2006
Bike stuff
Had a great ride Saturday...19.2 mph with the Richardson Bike Mart 34 mile group. Stayed with the main bunch the whole time...never done that before.
Happy Birthday Kaitlyn!!!
Had a great ride Saturday...19.2 mph with the Richardson Bike Mart 34 mile group. Stayed with the main bunch the whole time...never done that before.
Happy Birthday Kaitlyn!!!
5/23/2006
5/19/2006
5/08/2006
4/29/2006
4/28/2006
So a customer asked me yesterday:
"How can I search all my DTS packages in SQL 2000 for certain field/table names? Peoplesoft patches sometimes change these at the schema level."
Answer: You can't, using any native SQL Server utility. The best you can do is save the DTS packages as a Visual Basic File (.bas or .txt) and either aggregate them or index them via some other utility.
Alternative Answer: DTS Compare from www.red-gate.com might do it, but I have not tested it.
Happy searching!
Kevin3NF
"How can I search all my DTS packages in SQL 2000 for certain field/table names? Peoplesoft patches sometimes change these at the schema level."
Answer: You can't, using any native SQL Server utility. The best you can do is save the DTS packages as a Visual Basic File (.bas or .txt) and either aggregate them or index them via some other utility.
Alternative Answer: DTS Compare from www.red-gate.com might do it, but I have not tested it.
Happy searching!
Kevin3NF
4/27/2006
Interesting issue I'm working on...
Company with SQL Servers all over the world applies MS06-014 and SQL Server 2000 SP4 close together.
Now none of their applications can make a TCP connection. Named Pipes is fine, but the application requires TCP.
Did all the normal stuff like checked the Server Network Utility (SNU), checked the firewalls to ensure the appropriate ports were open, explicity specified tcp in the servername (tcp:servername, port). No go.
Duh moment...check the SQL Server ERRORLOG. Nope...SQL Server was not listening on TCP, even though the SNU had it enabled. No errors in the log, or in the application log...just not listening on that protocol.
Resolution:
Check the value of the tcpport parameter in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp
In this instance, it was NULL. SNU was reporting 1433. Apparently SNU caches the port, or if the registry is NULL, it uses a default value to fill in the blank.
So we set the value manually, and all was good.
Lesson learned:
Check the ERRORLOG earlier in the process, and never trust a GUI.
Kevin3NF
Company with SQL Servers all over the world applies MS06-014 and SQL Server 2000 SP4 close together.
Now none of their applications can make a TCP connection. Named Pipes is fine, but the application requires TCP.
Did all the normal stuff like checked the Server Network Utility (SNU), checked the firewalls to ensure the appropriate ports were open, explicity specified tcp in the servername (tcp:servername, port). No go.
Duh moment...check the SQL Server ERRORLOG. Nope...SQL Server was not listening on TCP, even though the SNU had it enabled. No errors in the log, or in the application log...just not listening on that protocol.
Resolution:
Check the value of the tcpport parameter in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp
In this instance, it was NULL. SNU was reporting 1433. Apparently SNU caches the port, or if the registry is NULL, it uses a default value to fill in the blank.
So we set the value manually, and all was good.
Lesson learned:
Check the ERRORLOG earlier in the process, and never trust a GUI.
Kevin3NF
4/26/2006
I got a call today from a customer that wanted to upgrade SQL Server 6.0 to SQL 2000.
Problem...old tired server that may not survive a reboot, and no direct upgrade path.
Solution:
BCP the data out to text file, and find some 3rd party utility that can reverse engineer the schema.
We agreed to speak no more of this need. :-)
Problem...old tired server that may not survive a reboot, and no direct upgrade path.
Solution:
BCP the data out to text file, and find some 3rd party utility that can reverse engineer the schema.
We agreed to speak no more of this need. :-)
So...2.5 years after I started this, its time to add a second post :-)
This for now, is going to be a day-to-day account of various things I run across in my professional life as a SQL Server DBA, IT guy, Consulting firm owner, etc. I may toss in some cycling related items from time to time, just because I can.
I am currently working in my second contract for Microsoft SQL Server Support, on the Premier Integration team. My team handles issues such as Clustering, Replication, DTS, Security, Connectivity and BCP. Currently, I only deal with SQL Server 2000 and 7.0. No 2005 for the contractors.
Hope you like databases :)
Kevin3NF
This for now, is going to be a day-to-day account of various things I run across in my professional life as a SQL Server DBA, IT guy, Consulting firm owner, etc. I may toss in some cycling related items from time to time, just because I can.
I am currently working in my second contract for Microsoft SQL Server Support, on the Premier Integration team. My team handles issues such as Clustering, Replication, DTS, Security, Connectivity and BCP. Currently, I only deal with SQL Server 2000 and 7.0. No 2005 for the contractors.
Hope you like databases :)
Kevin3NF
Subscribe to:
Posts (Atom)