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!!

Advertisements

6 responses to “No disk space on SQL Server 2005 / Windows Server 2003

  1. Pingback: No disk space on SQL Server 2005 (pt 2) « Just trying to make it all work… and then stay that way

  2. I am having similar issues with space on my SQL server. I have a few questions. What is the best practice for backing up and removing large tables from our applications database? do I detach them, remove the referrences to them, and then copy the actuall files elswhere and then reattach if I need them, or is there a better way?

    Also, I noticed you mentioned that you set a different drive to host the actual SQL tables. How do I do that? And now that I have my tables on one drive now, How do I migrate them to a new location, and specify that location going forward?

  3. Backing up individual tables isn’t really possible in a strict sense without some third party backup tools. I’d suggest one of two ways with a vanilla install:
    1) Export the tables to an MS Access DB, CSV etc. using the export option in SS Management Studio (Right-click the database, Tasks, Export Data)
    or 2) Create a new database, move the tables in to it (SELECT INTO, followed by a DROP table). Then detach the DB and move it elsewhere on the network. – this is what you suggested. Right-click the tables and do view dependencies for a rough guide to what needs repointing.

    I basically set a different drive letter as the default drive for my SQL Server data / log files. You can simply detach your database, move the files to the new drive and re-attach them. You can put specific tables in specific files (and hence specific drives) by defining file groups.
    All tables will go in to the PRIMARY group by default.

    I would strongly recommend reading up on file groups etc, a good place to start is the Sybex MCTS 70-431 book (chapter 2).

  4. Thank you, I appreciate your response. I was mistaken in the description of my situation..

    Its actually not individual tables that I need to backyup/remove, its databases. The application we use created an initial configuration database when it was installed. This database conatains the configuration information for the application and references to new projecs/jobs that are created in the application. The appliation creates a new database for each new project/job we create in the application. It keeps a reference to the new database/project in the original configruation database. so right now we have on our sql server the original database, which must remain, and a large number of added “project” databases, one for each project. As the disk space runs out and the projects age, I need to backup/remove these databases/projects from the sql server and store them elsewhere, preferrably an external drive. We also need to have the ability to restore these databases if necessary.

    Is there any way to check the dependancies by database the same way you mentioned for the tables?

    And what would be the best way to actually remove and store these databases elsewhere?

    I guess that I also would need to delete the reference to these removed databases in the configuration database so the application wont error out when looking for them?

  5. Sorry for the delay.
    My suggestion would be to try and keep the databases ‘online’ such that you don’t have to worry about updating any references to them.

    I would suggest doing this by detaching the databases and moving them to a network drive. I would recommend an iSCSI solution (check out openFiler or StarWind for iSCSI target solutions).

    Alternatively you can use a standard UNC path but checkout http://support.microsoft.com/kb/304261 for a list of reasons why not.

  6. Thanks

    WinDirStat is good program for reclaiming diskspace. Search for *.log files is good one.

    Jamie

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s