Q: My SQL Server database shows a very large size, but the sum size of all the tables is nowhere near the size of the database. What should I do?

A: The first step to try is to shrink the database, which is done through SQL Server Management Studio. Right-click the database, and select Tasks, Shrink Database, as the screen shot shows below.

sqlshrink1-Copy 

You can accept the default options and click OK to perform the shrink. (See screen shot below.)

 
sqlshrink2-Copy

If this doesn’t help, then jump to Explorer and look at the file system. The most likely problem is that you have a very large transaction log file that’s never shrinking because you don't take backups. This could be a huge problem if this is production data. (One hopes that this is because it’s in a lab where you don't really need backups.)

When you created the database you specified a location for the database and a location for the transaction log. These are normally different drives, to avoid the chance of a physical disk problem affecting both your database and transaction logs. If you didn't set a custom location, the database files and transaction logs will be in the same location, which is C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA for SQL Server 2008 R2. If you are unsure of the location, right-click a database within SQL Server Management Studio and select Properties, and in the Files section you should see the exact path of the log and database as the screen shot shows below.

 
sqldbprop-Copy

In my example I looked at the files and noticed that while I had a 90MB database for ReportServer, I had a 24GB transaction log file, which you can see in the screen shot below. This is because I never performed a backup of the database, which would have cleared up the transaction file.

 
sqltranslogbig-Copy

To fix this problem, I needed to perform a backup, but I didn’t want to actually perform a backup, so I backed up to a null device as shown below. Then I performed a shrink on the log file. I ran these commands in a query window within the SQL Server Management Studio:

backup database ReportServer to disk = 'nul:'backup log ReportServer to disk = 'nul:'dbcc shrinkfile(ReportServer_log,TRUNCATEONLY)

I then looked at my files again and my transaction log was now 4MB, which is much better. (See screen shot below.)

 
sqlfilesafter-Copy

To avoid this happening again, since I don't intend to back up this database, I can change the recovery mode of my database from Full to Simple. This means the transaction logs will automatically truncate at checkpoint times, which are performed automatically. To look at the current recovery mode, run this command:

select name,recovery_model_desc from sys.databases

To change the mode of the database to Simple, use this command:

alter database ReportServer set recovery simple

I can then check whether the change took effect by rerunning the select recovery_model command. I should see its type as SIMPLE, as the screen shot shows below.

sqlrecoverymode-Copy