Category Microsoft SQL Server 2005

SQL Server 2005 database has grown too big and filled the hard disk

Microsoft SQL Server 2005 database has grown too big and filled the hard disk

How much available free disk space ?
How much available free database space ?
How to free up some disk space by shrinking the database?

A quick run through

My Computer shows
C:\ OS with free space
E:\ The DB drive FULL
L:\ The logs drive with free space

On E:\ identify which database has grown and filled the disk

Open MS SQL Management Studio
Login with domain account or a SQL database admin credentials – (SA will have these rights)

Right click the offending database

Select tasks -> shrink – > files

It will then show you how much is available to shrink

ie. E:\ Large DB is 39999.00MB
DB allocated space is 19999.00MB
Available space with in the DB is 20000.00MB (50%)


The DB will have stopped working as there was no space to extend the file on the main disk


In this example we can now click ok to shrink the Large DB, due to having 20,000MB free space with in the DB.
Note : If there was no free space, then you should consider looking at the application for purge options or adding additional storage

A database can shrink while running. The shrink will work in the background, IF there is space. However it might slow things down because its actually moving data off pages onto others and then deleting the empty pages when it moves the data it has to reset catalog so that the db knows where that data is in the indexes etc. (reset  means update)
This is just and example, in my own words and you should understand the tasks this article talks about and make your own decisions. The author holds NO responsibility for anything which is done based on the topic described above.