Wednesday, May 20, 2009

T-SQL Script to Generate SHRINKFILE statements for all DBs

This a t-sql script I use to generate DBCC SHRINKFILE statements for all databases on a SQL Server 2005/2008. Microsoft recommends that you change the recovery model to simple shrink the file and change back to full if the recovery model is full to begin with. You cannot shrink readonly dbs (or offile dbs obviously)

The script is provided AS IS.

use master;
GO
create procedure [dbo].[udp_genShrinkStatements] as begin
declare @sql nvarchar(max);
declare @filename varchar(256);
declare @dbid int;
declare @dbname varchar(256);
declare @recoveryModel int; -- 3 simple; 1 full
declare dbcursor cursor for SELECT     database_id,[name], recovery_model --, recovery_model_desc
FROM         sys.databases
WHERE     (database_id > 4) AND (is_read_only = 0) and (state = 0) --skip system, readonly and offile dbs
ORDER BY   [name]
open dbcursor;
set @sql = '';
fetch next from dbcursor into @dbid,@dbname,@recoveryModel;
while @@FETCH_STATUS = 0
begin
declare filecursor cursor for SELECT [name] from sys.sysaltfiles where dbid = @dbid;
open filecursor;
select @dbname = '['+@dbname+']';
--select @sql = @sql + @dbname;
-- print '--'+@dbname;
--select @sql = @sql + '--';
-- print 'USE ' + @dbname +';';
select @sql = @sql + 'USE ' + @dbname +';' ;
-- print 'GO';
--select @sql = @sql + ' GO ';
if @recoveryModel = 1
begin
-- print 'ALTER DATABASE '+@dbname +' SET RECOVERY SIMPLE WITH NO_WAIT;';
select @sql = @sql + 'ALTER DATABASE '+@dbname +' SET RECOVERY SIMPLE WITH NO_WAIT;';
end
fetch next from filecursor into @filename
while @@FETCH_STATUS = 0
begin
-- print 'DBCC SHRINKFILE('''+ @filename +''');';
select @sql = @sql + 'DBCC SHRINKFILE('''+ @filename +''');';
fetch next from filecursor into @filename
end
close filecursor
deallocate filecursor
if @recoveryModel = 1
begin
-- print 'ALTER DATABASE '+@dbname +' SET RECOVERY FULL WITH NO_WAIT;';
select @sql = @sql + 'ALTER DATABASE '+@dbname +' SET RECOVERY FULL WITH NO_WAIT;';
end
-- print '---------------';
--select @sql = @sql + '---------------';
fetch next from dbcursor into @dbid,@dbname,@recoveryModel;
end
close dbcursor;
deallocate dbcursor;
--exec sp_executesql @stmt = @sql;
SELECT @sql;
end

1 comment: