SQL Server Shrink and Truncating Database, Log and Indexes

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)

Leave a Reply

Your email address will not be published. Required fields are marked *