JamesOliver.us

Most of us are taking over the reigns of being a DBA instead of being the first. One thing we do first is assess what has already been done. For SQL Server, the maintenance plans offer a wonderful way to schedule those necessary tasks to keep our DB’s happy. However, as I was investigating an unusually high I/O, and run time issue, I decided to look a little closer into what the pre-existing plans were doing. I used a free E-Book called Brad’s Sure Guide to SQL Server Maintenance Plans by Brad McGehee. Within the first pages of explaining each task’s definition, I discovered that almost all of the plans were rebuilding indexes AND updating statistics. Which as I now know, is completely redundant because rebuilding indexes also updates statistics. However, if you reorganize your indexes, you will want to update the stats.

Often I need to write things down so that I can refer to them later.  I usually don’t and then I recreate the wheel.  So, I thought I would post here and maybe you can find them useful too.

1. On a Windows 2008 server, my usual routine of giving my SQL Agent job a domain account for network share backups was not working.
   

   Problem
   SQL Backup failed with exit code: 880  SQL error code: 15157 [SQLSTATE 42000] (Error 50000).  The step failed.

   
   

   Solution
   Add this to the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\(local) or SQL Instance Name.
   Create a new DWORD type key called SkipChecks and give the key a data value of 1.
   NOTE: If you’re using a 32-bit version, omit the “Wow6432Node” part.

   Ref. http://www.red-gate.com/messageboard/viewtopic.php?p=33750#33750

2. After changing the account under which my SQL Server service runs, I received another error:

   Problem
   SQL Backup failed with exit code: 5150 SQL error code: 0 [SQLSTATE 42000] (Error 50000). The step failed.

   
   

   Solution
   Easy one… Restart your Red Gate service AFTER you restart your SQL Server service

   Ref. http://www.red-gate.com/messageboard/viewtopic.php?p=26440#26440

Speaking of page life expectancy, I know that analyzing existing indexes is an important start.  So, I found some good scripts for showing me index fragmentation. (There are millions out there and I’m still trying different ones.  I’ll post the one I use once I’ve gotten it nailed down)  As any new DBA should expect, there are some indexes that have been neglected and are up in the 80-90% range.  Yikes.  So, I read the rules that anything up to 30% should be reorganized and over that should be rebuilt.  You can read more about that here.  And, with the wonderful ONLINE option we have for rebuilding now, (reorgs are always online), I tried to defrag a few.  Results were beautiful!  Well, for the most part.  Some indexes didn’t seem affected by a rebuild.  So my question is… why?  How can a rebuild not reduce fragmentation?  Also, what can I look at to see how frequently indexes are used so I can get rid of their unnecessary overhead?

Thanks!

Still not officially the DBA, but still getting to do some really cool DBA stuff.  I have a couple of hot issues to look into, but for the most part I’m gearing up to be proactive.  I’m reading Tom LaRock’s new book DBA Survivor:Become a Rock Star DBA, which is great so far if you are at day 1 of your DBA career like I am.  I’ve also just installed a trial of Spotlight on SQL Server Enterprise by Quest Software .  Like I said, I’ve just installed it, but it has some serious geeky eye candy right out of the box!  Graphical coolness aside, this is powerful software.  Although I have a lot to learn, even a newbie DBA like myself can quickly and visually spot performance bottlenecks and other signs of danger.  And what’s more, I can see all of my databases in one spot.  It monitors the SQL instances and the OS instances of the DB servers too.  It’s completely customizable in terms of alerts and their thresholds.  Plus, it is always collecting data, so when someone comes to you with one of those “what happened 2 days ago about 3am when everything ran slow?” questions, you can view the historical data and see what happened. (Of course, the software would have alerted you back when the issues occurred if the alerts were setup.  Oh, and of course the alleged “issue” does have to exist.)  So, I don’t know anyone at Quest or anything, but I wanted to start my blogging contributions to the SQL community and I thought, from the perspective of a n00b, that I would share my delight in some really well made tools.  I’ll be talking more about this as I look to solve some good ol’ issues like disk length queue and page life expectancy.

Well, the best case scenario happened.  The owner of the expensive job told me that it was no longer needed.  Sigh.  But, he had no idea of the trouble it was causing, and since our DBA position had been vacant, no one even knew the job was the root of our issues.  So it goes to show you, that often times I’m guessing the problem will be solved by non-technical means.  Always try the easiest solution first.

I’ve disabled the job, shrank my tempdb data and log files down to size with dbcc shrinkfile, and used the modify file command to reset their initial sizes.  Now, my resting folder size has went from almost 30GB to 4GB.  I’ve started a perfmon monitor on log file usage, log file size, and data file size to see what happens throughout a production day.  Here’s hoping that the files won’t need to grow much!

Before I’ve even officially gotten the DBA position at my work, I was asked to look into an ‘out of space’ alert.  (It is an internal posting, so I’m already working there as a developer.)  The database server was reporting this alert because the SQL instance’s tempdb was filling up the allotted volume.  To the credit of whomever set it up, the tempdb was spread across multiple files which were separated from the user databases’ data files.  The head sysadmin guy told me the alert happens every night, beginning around 11pm and by 12am, the drive is full.  He knew he could reset the tempdb by restarting sql server but I was asked to investigate why this was happening.  Long story short, there was a fairly massive data warehouse sql job that made heavy use of the log file.  It grew from around 9GB to 29GB in 35 minutes.  After extensive research on tempdb capacity planning I realize that as long as we are running this job in its current state (3rd party created), then tempdb will simply have to be given more room.  However, it appears that for the rest of the time except for that hour, the tempdb is a fraction of that size.  So, I’m thinking maybe to save the cost of more disk space, that we should just restart the sql server service after that job completes.  I have that luxury since it’s not a 24/7 SLA server.  Still wondering though;  is this the best solution?

I have officially started my DBA training.  Starting off with the MCTS (Microsoft Certified Technology Specialist) for installing and maintaining SQL Server 2008.  So far, the training kit book by MS Press is serving me well.  I think DBA is going to be a good move for me from application development.