I ran into problems compare two 8 gig plus database, but data wasn’t important. I wanted to clear all the data from the database and use SQL Compare to show the difference. And make changes where needed. There were situations where I needed to restore the database(s) and at 15 minutes it took pretty long. I decided to clear all the data from the database and do a backup of the empty database and restore the empty databases instead. This seems like a easy endeavor, but took me a while to figure it out. With that said,here’s how to Shrink and Trunate Database, Logs and Indexes
Very little of the following code is my own. I used the resources at the end of the blog to create the following statement.
EXEC
sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO
EXEC
sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
GO
EXEC
sp_MSForEachTable
‘BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;’
GO
EXEC
sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO
EXEC
sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
GO
–DBCC LOGINFO
EXEC
sp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 100)"
DBCC
SHRINKFILE(DataFileName, 1)— OR DBCC SHRINKDATABASE (DatabaseName, 1)
BACKUP
LOG DatabaseName WITH TRUNCATE_ONLY
DBCC
SHRINKFILE(DatabaseLogName, 1)
–INFO
DBCC
SQLPERF(logspace)
EXEC
sp_spaceused
DBCC
LogInfo
Simple example of sp_msForEachTable:
exec
sp_msforeachtable @command1=‘PRINT ”?”’, @whereand=‘ and o.name like ”%Loan_%”’
Resource:
SQL SERVER - Shrinking Truncate Log File - Log FullIntroduction to the Transaction Log
Great Article Why you want to be restrictive with shrink of database files
