cannot free up unallocated space sql server 2005

Question

Hi I have a very large database (40 gig) and have run the procedure

sp_space_used 

and found that there is 10 gig of unallocated space. Obviously this is a lot and the .mdf file is taking up most of the disk. I have looked into running

DBCC SHRINKDATABASE (db, TRUNCATEONLY);

Do I also need to shrink the transaction log or will shrinkdatabase take care of this? What are the negative implications of running this procedure? Can I run this while the db is in use? Please help! I've tried running the shrinkdatabase command but still have lots of unallocated space...

database_size   unallocated space
49575.06 MB   8393.49 MB

reserved       data        index_size   unused
42170328 KB 22704672 KB 19099160 KB  366496 KB

NOTE: the db has a simple recovery model so i'm guessing i don't need to backup the log at all...

What is the difference between running

DBCC SHRINKFILE (datafile, TRUNCATEONLY)

to

DBCC SHRINKDATABSE (db, TRUNCATEONLY)?

Answer 1

You will need to backup the transaction log FIRST, then shrink it, otherwise the transaction log wont free up the space.

Creating the backups will still guarantee you can rollback to previous version of the database.

Use can use DBCC SHRINKFILE 2005 after you backup the transaction log

Answer 2

First before making any structural changes to your database take a FULL Database Backup.

If, as you say, the majority of the unallocated space resides within your SQL Server data file (.mdf) then you should look to use DBCC SHRINKFILE, rather than DBCC SHRINKDATABASE.

So for example:

USE UserDB;
GO
DBCC SHRINKFILE (LogicalDataFileName, target_sizeInMB );
GO

Additionally consider using the TRUNCATEONLY option to free available space from the end of the data file only. This is less resource intensive option but may not free as much space. Keep in mind target_size is ignored if specified with TRUNCATEONLY.

You can run this maintenance on a production server however you may experience increased blocking and so should look to schedule the maintenance during a period of low activity if downtime is not an option to you. Users can however still query the database.

In light of your edit:

DBCC SHRINKDATABASE will endeavour to shrink all files within your database, that is both data and log files.

DBCC SHIRNKFILE on the other hand provides a finer level of control in that it applies to a specific file.

Answer 3

Hi Jeff,

The sp_spaceused command includes the log file free space as well. Even though your database log model is simple, SQL uses your log file to track transactions and the log file grows and it does not shrink until you run a backup. I know that you mentioned that the mdf file is taking up most of the disk, but have you check your ldf file size? Have you checked your clustered indexes fragmentation as well?

If you find that the log file is big too, try the following script to see if your database shrinks. It worked for me with simple model logs.

use [db_name]
backup log [db_name] with truncate_only
dbcc shrinkfile ([db_log_filename], 1)

This statement would not run an actual backup, it will just truncate the log and allow the shrinkfile remove the free space that the log file is using. If you are planning to run it in Production, I would recommend you to do it in an off peak time. Even though it does not take long. This only affects the log file size.

Hope this helps and that is clear why I would recommend you to check the log file as well.

Answer 4

i ended up using DBCC SHRINKDATABASE (db, %); specifying a target percentage which seemed to solve my problem. Is there anyway of not specifying anything and just getting it to free up the maximum amount of space, or do I always have to guess how much space I want it to free up? Thanks everyone for your help.