Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stupid DBA Tricks

Well, I just learned a valuable lesson about something you should not do as a DBA…

About a month ago, a client of mine called me to report that their main database server was running critically short of disk space on the C: drive.  Whoever had installed the operating system, had decided to make the C: partition a little too small (only 20GB), and enough stuff had been installed since then that the C: drive was, in fact down to less than 50MB free.

I went through and uninstalled SQL Server BOL (which was fine), cleaned out the Windows\Temp directory, and we were still quite low on disk space. So then, I got the bright idea of cleaning out the SQL Server Install directory, specifically "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install”. I figured that SQL Server was already installed, so why would it need that stuff?

Big mistake.  Oh, there was no immediate effect. SQL Server was just fine, blissfully unaware of what I had just done to it. The problem came up when I tried to install SQL Server 2008 SP1 CU4 on that server this morning. The CU went through, and appeared to complete with no issues. After it was done, I tried to logon to the server in SSMS, and I was told that the server did not exist, or access was denied.

So, I started up SQL Configuration Manager, and sure enough the SQL Server service was not running. I started it, and it appeared to be fine, so I tried to logon again, and I got the same error. Looking at SQL Configuration Manager, the service was stopped. That did not look good.

Next, I opened up the SQL Error log, and saw this at the end:

2009-10-04 08:32:56.82 spid7s      Uploading data collector package from disk: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx
2009-10-04 08:32:56.82 spid7s      Error: 4860, Severity: 16, State: 1.
2009-10-04 08:32:56.82 spid7s      Cannot bulk load. The file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx" does not exist.
2009-10-04 08:32:56.82 spid7s      Error: 912, Severity: 21, State: 2.
2009-10-04 08:32:56.82 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2009-10-04 08:32:56.82 spid7s      Error: 3417, Severity: 21, State: 3.
2009-10-04 08:32:56.82 spid7s      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.
2009-10-04 08:32:56.82 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Oh-oh. That did not look good at all. After my initial panic subsided, I remembered about my clever move of deleting the files in the installation directory. I had a test server that had SQL Server 2008 SP1 CU4 installed, so I zipped up those install files, and e-mailed them to myself. Then I logged on to Outlook Web Access from the client’s DB server, and pulled down that zip file and copied the missing files to the install directory.

Finally, I restarted the SQL Server service, and he was able to complete the script level upgrade process of the Cumulative Update, and everything was fine. Minor catastrophe averted by some quick thinking. Now, I am older but wiser, and I won’t make that mistake again.

Still, one could argue that the CU setup program could check for the existence of those script files, and/or lay them down if it needed them. Of course I should not have deleted those files either…

Technorati Tags:

Comments

Posted by Eric Wilson on 20 May 2010

Just had a very similar error but the same script failed with error 598 instead. Turns out it was due to the user-database default folder location not existing at the time the upgrade ran.

More here:

datazulu.com/.../Sql2008-R2-Upgrade-Fail.aspx

Posted by lorisj33 on 6 April 2011

I just had a very similar situation, as Eric did, on SQL 2008 post SP2 install and the info at datazulu.com/.../default.aspx was a real lifesaver.  Worked like a charm, although I had to check the registry entries for default data & log locations.

Thanks!!

Posted by rmharvey on 30 August 2011

Another thanks, that fixed a problem for me too.  If only I knew how the files got wiped!

Leave a Comment

Please register or log in to leave a comment.