Generate truncate script

Standard

truncate all the tablesLet me first say that I never truncate my production databases! It’s a bad practice and in most cases you don’t win anything. A good article about this: Stop Shrinking Your Database Files. Seriously. Now.

Besides the downsides of truncating your files I’ve had situations where I had to restore several databases for read-only purposes to a server.

When you deal with a lot of databases and want to shrink all the files you have to query the values or look at the properties of each database. This is not a efficient way to do this and therefore I’ve created the a little script to generate a truncate script to do everything for you.

For SQL Server 2000

SELECT 
	'USE [' + d.name + ']; PRINT ''Trunking ' + af.filename + ' '' DBCC SHRINKFILE (N''' + f.name + ''' , 0, TRUNCATEONLY)'
FROM 
	sysaltfiles af 
INNER JOIN sysdatabases d
	ON f.dbid = d.dbid
WHERE
	d.name NOT IN ('master', 'msdb', 'model', 'tempdb')

For SQL Server 2005 and up

SELECT
	'USE [' + d.name + ']; PRINT ''Trunking ' + mf.physical_name + ' '' DBCC SHRINKFILE (N''' + mf.name + ''' , 0, TRUNCATEONLY)'
FROM 
	sys.master_files mf 
INNER JOIN sys.databases d 
	ON mf.database_id = d.database_id 
WHERE
	d.name NOT IN ('master', 'msdb', 'model', 'tempdb')

I left out the system databases because I didn’t want to mess with them.

Leave a Reply

Your email address will not be published.