No disk space on SQL Server 2005 / Windows Server 2003

Over the past few months we’ve been having increasing problems with running out of disk space on the system drive of our SQL Servers. We use Dell PowerEdge servers with Windows Server 2003 x64 pre-installed, to date we have always left the defaults system partition of 12Gb and just gone with it. I install program files to the system drive but set SQL Server to use a separate partition for the database data/log files (depending on number of disks etc).

We havr had issues where SQL Server 2005 SP1 wouldn’t install, this took a long time to get to the bottom of and so every night the automatic update would retry. Eventually we noticed we were running out of disk space. Thanks to some help from MSDN Connect http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=136636 we (eventually) got this resolved (for the record I have no idea how the permissions ever got changed nor how SQLServer carried on running fine).

So for the record, how to get some more disk space:

1.I found that a lot of our disk space was being used by files in C:\Windows\Installer (hidden directory) where there were loads of files all of the same size. Some hunting around later I discovered that each time the SS SP was failing it was leaving an MSP file behind. So, I deleted all of the ones with the identical size (except one or two – keep the one with the latest date modified!) and marvelled at my new found disk space. For the record this caused me problems when we later tried to install SQL Server 2005 SP2, since it stated that it was missing an MSP – since they use some kind of hex filenames it was a cryptic but I guessed that the one I left behind wasn’t in fact the one that eventually got installed. Thus I just renamed the file I had kept earlier to the name that was missing and all was well again…

2.Due to the failed installs in (1) I had lots of large log files in the directories below, I can’t see these being useful after a confirmed successful SP installations:

• C:\Windows\Hotfix\SQLTools9\Logs (335Mb)
• C:\Windows\Hotfix\SQL9\Logs (211Mb)
• C:\Windows\Hotfix\RS9\Logs (187Mb)
• C:\Windows\Hotfix\DTS9\Logs (173Mb)
• C:\Windows\Hotfix\NS9\Logs (125Mb)
• C:\Windows\Hotfix\OLAP9\Logs (117Mb)
• C:\Windows\Hotfix\Redist9\Logs (40Mb)

3.OK, scraping the barrel now but I’d definately move my paging file off of the system drive. There are issues in doing this (like not being able to debug BSOD) but it’s a small price to pay for at least a gig of space.

4.Set the system Temp path to a separate partition (Start->Settings->Control Panel->System->Advanced->Environment Variables->SYSTEM VARIABLES).

5.Remove any old user profiles (Control Panel->System->Advanced-> Settings (User Profiles) ).  Shoot any of your colleagues who logged on as themselves and wiped out hundreds of meg with their roaming profiles 😉 )

6.Use something like TreeSize to find anywhere else you’re losing space.

7. And don’t install anything you really don’t need such as BOL in the first place!

It seems that SQL Server 2005 Service Packs, Visual Studio SP1, Windows 2003 SP2 (not to mention .NET updates, patches and Native Images) have really taken a huge toll on the free disk space on our server. In the past you could remove the uninstall files for these service packs via Add/Remove programs or even not save them initially (NT / Win2k SPs). However it looks like SS2005 SP2 is reliant on the uninstall (MSP) file for SP1 which makes this a no-go. I would recommend that anyone building a new SQL Server server allocate at least 20Gb to the system partition, as 12Gb just doesn’t cut it anymore!!

Tracking Schema changes on SQL Server 2005

I know this is covered all over the place with various solutions and possibly somewhere in books online.
However, I found this code snippet in the AdventureWorks database which was disabled, I’ve changed it so that it didn’t disable at the end but other than that it’s all intact. DISCLAIMER – I DID NOT WRITE THIS, SOMEONE AT MS DID!

I’m sure to anyone who is going to use this the SQL is self-explanatory but very basically it creates a database trigger which inserts rows in to a table called dbo.DatabaseLog on every DDL command.

The script is called createddltrigger-sql.doc just drop the .DOC extension and replace with .SQL it’s a plain text file.

‘only’ 15 faults with SSIS (SQL Server Integration Services 2005)

A colleague pointed me towards an article called SSIS’ 15 Faults on Oren Eini’s blog.

The points made there about the current state of SSIS will ring true for anyone who has tried anything non-trivial using the platform.  I hope MS address some of them when they come to release SQL Server 2008, however looking at the current CTP it’s not looking too promising – lets hope I’m missing something!

Error V-79-57344-33938 on backing up large SQL Server 2005 DB with Veritas Backup Exec 10d

An error occurred on a query to database **YourDB**
V-79-57344-33938 – Write on “**YourDB**_00__7091c250_56c1_4f5e_9bf8_3eced26e1926_” failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)
We’ve been getting this error backing up our main SQL Server 2005 (SP1) database which has Full recovery mode with a 4GB data file and 35Gb transaction log. Searched about a bit and found various articles referring to the the VDI and guessed it was due to SQL Server not being able to put the temporary backup file to disk – I was write.
Basically ensure that there’s sufficient space on %TEMP% to hold it!
We had ours set to a partition with just 20Gb space – I had %TEMP% set to a couple of 15k rpm 36Gb SCSI disks.

Outlook 2007 RTM using 12000 + handles !?

We’ve been having some issues with Outlook 2007 slowing down to a complete crawl on several of our [development] machines. All are running XP Pro fully patched up and are connected to Exchange 2003.

On my PC I find that quite often the new e-mail window can’t keep up with the rate I’m typing, often I get a whole sentence ahead of it before I see anything on the screen.

We’ve all noticed that Outlook 2007 seems to be a bit of a hangle-hog using anywhere between 9,000 and 14,000 handles with no particular pattern to it. You’ll notice my PC runs the MS CRM plugin, however we see similar behaviour on machines not running with that plugin installed.

Any ideas greatly appreciated!