The SMS 2003 infrastructure I am managing has one singe SMS database which became 4 GB. 1GB data and 3GB log. Today I planned the database maintenance and managed to shrink the files with 3GB. Open the Enterprise Manager or Microsoft SQL Server Management tools and make sure the database option is set to Auto Shrink = Auto (enhancement for the future).

Run the following sql script:

USE [SMS_123]

GO

DBCC SHRINKFILE (N‘SMS_123_log’, 0, TRUNCATEONLY)

GO

Another useful script can be found here which will create a stored procedure and show all table space sizes with rows and MB used.

exec sp_show_huge_tables