Shrinking database

Question

I have been tasked to shrink a SQL server 2005 database. This ia a live database and is runing at about 35gb.

1. How do I shrink a database?
2. Can this be done while that database is live?
3. How big will that database bw when it has been shrunk?

Thanks in advance.

Answer 1

These should have most of the information you need: http://msdn.microsoft.com/en-us/library/ms189035.aspx http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111857

Answer 2

1. Open SQL Server Management Studio. Right Click your database -> Tasks -> Shrink -> Database

2. Yes. The database remains live.

3. Hard to say. It depends on how much free space there is in the 35Gb file. If the database is actually using all 35Gb for storage (rather than holding free space), then your database isn't going to shrink at all.

Answer 3

Firstly, if you can avoid shrinking a production database then do so. Buying additional disk storage is almost always the more practical solution in the long run.

There is a reason that your database data/log files have grown to their current size and unless you have purged data from your database then it is very likely (if not a certainty) that your database will grow to the current size once again, post shrink exercise.

With this in mind you should look to identify the cause of your database growth.

Finally, if you absolutely must shrink your database, choose the time to do so wisely, i.e. perform this maintenance at a time when your live system typically experiences lower workload. Shrinking data files causes a significant amount of disk I/O, especially if the data pages are to be reorganized.

Then identify which data files or log files contain the most free space and target these to be shrunk individually. There is no point in performing a database wide shrink exercise if for example it is only the log file that has a significant amount of free space.

In order to do this, consult the documentation for the DBCC SHRINKFILE command.

Useful Information:

Indentify the amount of free space in the database overall:

EXEC sp_spaceused

Identify the amount of free log space:

DBCC SQLPERF('logspace)

Identify the amount of free space per data/log file:

SELECT 
    name AS 'File Name' , 
    physical_name AS 'Physical Name', 
    size/128 AS 'Total Size in MB',
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
    *
FROM sys.database_files;