Today I went through the fun exercise of Restoring a multi-gig SQL database from Backup.
5+ hours later, I knew I didn’t want to go through it again! Anywise, long story short, we had some scripts at work that truncates unused tables, truncates the transaction log, and shrinks the data file. I slightly modified this script to work with the currently selected database.
-- truncate unneeded tables
--TRUNCATE TABLE {tablename}
--GO-- truncate transaction log
DECLARE @LOG_FILENAME VARCHAR(200)
SELECT @LOG_FILENAME = FILE_NAME (2)
BACKUP LOG NAO WITH Truncate_only
DBCC SHRINKFILE (@LOG_FILENAME)
GO
-- shrink data file
DECLARE @DATA_FILENAME VARCHAR(200)
SELECT @DATA_FILENAME = FILE_NAME (1)
DBCC SHRINKFILE (@DATA_FILENAME)
GO
