--variables
DECLARE @dbname nvarchar(128);
DECLARE @sql nvarchar (1000);
DECLARE @logfile_ID nvarchar(5), @logname nvarchar(128);

--load all databases; ignore system DBs
DECLARE DbFind_Cursor CURSOR FAST_FORWARD
	FOR SELECT name FROM sys.databases 
		WHERE name <> 'master' 
			AND name <> 'model' 
			AND name <> 'msdb' 
			AND name <> 'tempdb';
			
			
--open cursor
OPEN DbFind_Cursor;

--loop through; set all DBs to SIMPLE except system DBs
FETCH NEXT FROM DbFind_Cursor
	INTO @dbname;
	
WHILE @@FETCH_STATUS = 0
BEGIN
	--auto gen SQL code
	SET @sql = N'ALTER DATABASE ' + QUOTENAME(RTRIM(@dbname)) + N' SET RECOVERY SIMPLE';
	PRINT 'Executing: ' + @sql;

	--execute SQL
	EXEC (@sql);
	
	-- find logfile; shrink logfile for each db
	DECLARE LogFind_Cursor CURSOR FAST_FORWARD
		FOR SELECT file_id,name FROM sys.database_files
			WHERE type = '1';
			
	--open cursor
	OPEN LogFind_Cursor;

	--loop through; shrink all logfiles
	FETCH NEXT FROM LogFind_Cursor
		INTO @logfile_ID, @logname;
		
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		--auto gen SQL code; set database to use and shrinkfile
		--exec commands has its own scope; so everything needs to be encapsulated into one command
		SET @sql = N'USE ' + QUOTENAME(RTRIM(@dbname)) + '; DBCC SHRINKFILE (' + RTRIM(@logfile_ID) + N', 10)';
		PRINT '	Executing (' + RTRIM(@logname) + '): ' + @sql;

		--execute SQL
		EXEC (@sql);
		
		--loop next db
		FETCH NEXT FROM LogFind_Cursor
			INTO @logfile_ID, @logname;

	END

	--close cursor
	CLOSE LogFind_Cursor
	DEALLOCATE LogFind_Cursor

	--loop next db
	FETCH NEXT FROM DbFind_Cursor
		INTO @dbname;
	
END

--close cursor
CLOSE DbFind_Cursor
DEALLOCATE DbFind_Cursor

quote:

---

Originally posted by tkizer
By the way, you should not be performing DBCC SHRINKFILE as a cleanup task on all databases. You need to read about this topic to understand why this is so bad. Read this for starters: http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

---