Cannot free up unallocated space in SQL Server 2005

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 database is in use? 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 database 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)?

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.

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.

Fastest way to clear up transaction log space (after a FULL backup though) is to set to Simple mode, wait a minute or two, and then shrink the transaction log. That will shrink it to 1MB. Then turn it back on to FULL mode instead of SIMPLE.

I ended up using DBCC SHRINKDATABASE (db, %); specifying a target percentage which seemed to solve my problem.