Datafile and Logfile size is always a problem with SQL Server. I have an easy script that I run as a post backup task.
use <database name>
go
BACKUP LOG <database name> WITH TRUNCATE_ONLY
go
dbcc shrinkfile (‘<datafile name>‘,100)
go
If you get a Truncate not supported message (greater that sql2008) try
use <database name>
go
BACKUP LOG <database name> TO DISK=’NUL:’
go
dbcc shrinkfile (‘<datafile name>‘,100)
go
The dbcc shrinkfile can be used for each datafile within the database. The 100 gives a minimum size in Mb but obviously, it will only get this small is the datafile has less than this in it.
You can find a script to shrink all database logfiles for an instance here. Just copy the results from a text window back into the management studio and run.
I have also found that clustered indexes sometimes don’t want to give up their space, you can convince them to using :-
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”
DBCC SHRINKDATABASE (<database name>,10)