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 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 usage
Reducing SQL Server Index Fragmentation
How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable
SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb (Great Article)