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.
EXECsp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXECsp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
EXECsp_MSForEachTable ‘BEGIN TRY
TRUNCATE TABLE ?
DELETE FROM ?
EXECsp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
EXECsp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
EXECsp_MSforeachtable @command1="print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 100)"
DBCCSHRINKFILE(DataFileName, 1)— OR DBCC SHRINKDATABASE (DatabaseName, 1)
BACKUPLOG DatabaseName WITH TRUNCATE_ONLY
Simple example of sp_msForEachTable:
execsp_msforeachtable @command1=‘PRINT ”?”’, @whereand=‘ and o.name like ”%Loan_%”’
SQL SERVER - Shrinking Truncate Log File - Log Full
Introduction to the Transaction Log
Great Article Why you want to be restrictive with shrink of database files
How to determine SQL Server database transaction log usageReducing SQL Server Index FragmentationHow to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtablehttp://devpinoy.org/blogs/keithrull/archive/2007/09/07/how-to-truncate-multiple-tables-in-sql-server-and-the-magic-of-sp-msforeachtable.aspxSQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb (Great Article)