Script – Generate DBCC ShrinkFile for each datbase files
Posted by Jugal Shah on November 6, 2009
You can use below query to generate DBCC Shrikfile command for data and log file.
Query
select 'use ' + db_name(dbid) + char(13) + 'dbcc shrinkfile (' + quotename(sf.name,'''') + ' ,truncateonly)' from sysaltfiles sf
inner join sys.databases sd on sf.dbid = sd.database_id
where state_desc = 'online'
paul s said
There was a missing “sys” in your select for sys.sysaltfiles table. Here is the corrected syntax.
–Generate DBCC SHRINKFILE commands
select ‘use ‘ + db_name(dbid) + char(13) + ‘dbcc shrinkfile (‘ + quotename(sf.name,””) + ‘ ,truncateonly)’
from sys.sysaltfiles sf
inner join sys.databases sd on sf.dbid = sd.database_id
where state_desc = ‘online’
Jugal Shah said
thanks, I haven’t add sys because it can work with SQL 2000 as well.