Auto-shrink all Database logs

One of the key differences between an OLTP system and a Data Warehouse, is that a DW can be completely truncated and reloaded from the OLTP, since it is purely an aggregation and reporting area. So while Log files are important for OLTP, you don't need have heavy logging on the DW. I always set my DW's to "Simple". However, even in "Simple" mode some logging does occur, and log files grow over time. Not all my customers have the luxury of a DBA, and in order to help them out, I have written this script to automatically shrink log files.

Some notes before you use it:

• You must have the necessary permissions on the databases
• Change the "SomeNameFilter" in the code to filter out only databases you want to filter. 

EXEC sp_MSForEachDB
' use ?;
declare @SQL varchar(2000);
select @SQL = ''DBCC SHRINKFILE ('' + name + '')''
from sys.database_files
where type = 1
and name like ''SomeNameFilter%'';

if @SQL <> ''''
begin;
exec (@SQL);
end '

Enjoy!!

Shrink inactive database log files

A friend of mine recentrly wrote a script that will step through ALL the NON-Active databases on a SQL Server, Truncate the Log files and then shrink all the database files. In the current state of the script, it will leave NO free space in any of the database’s, but there is a value to change the percentage you would like to have free. I have added comments to the script as well. I ran it on my laptop and it took about 14 seconds to run across 47 databases and freed up about 20GB + of space.

--Creates a table variable and inserts all the non active databases into the the Variable
DECLARE @DatabaseList TABLE (DatabaseID INT IDENTITY(1,1), DatabaseName VARCHAR(50))
INSERT INTO @DatabaseList
SELECT Name FROM sys.Databases
WHERE STATE = 0

--Stores the SQL String to execute in the Loop
DECLARE @SQL VARCHAR(255)

--Holds the Count of Databases to Step through
DECLARE @DatabaseCount INT
SET @DatabaseCount = (SELECT MAX(DatabaseID) FROM @DatabaseList)

PRINT 'Databse Count: ' + CAST(@DatabaseCount AS VARCHAR(4))

--WHILE Loop
WHILE @DatabaseCount <> 0
BEGIN
IF @@ERROR = 0
BEGIN
--Builds the truncation query for the database
SET @SQL = (SELECT 'BACKUP LOG ' + DatabaseName + ' WITH TRUNCATE_ONLY'
FROM @DatabaseList
WHERE DatabaseID = @DatabaseCount)
--Executes the Query
EXEC (@SQL)
--Shows the Status
PRINT @SQL + ' : HAS BEEN EXECUTED SUCCESSFULLY'

--Prepares for the next database
SET @DatabaseCount = @DatabaseCount - 1
END
ELSE
--RETURNS ERROR MESSAGE
RAISERROR ('Either the state of a database has change while you were executing this statement or a DEADLOCK has occured.',16,1)
END

--Creates a Cursor (Please note, this statement should not be run often that is why i created a cursor.
--Cursor's should be avoided if possible. You can't execute a DBCC statement that returns results within
--a WHILE statement that is why i used a cursor here.
DECLARE @DatabaseName VARCHAR(50)
DECLARE @DatabaseCursorCount INT
DECLARE @DBCC CURSOR
SET @DBCC = CURSOR FOR
SELECT Name
FROM sys.databases
WHERE STATE = 0
OPEN @DBCC

FETCH NEXT FROM @DBCC
INTO @DatabaseName

WHILE (@@FETCH_Status = 0)
BEGIN
SET @DatabaseCursorCount = COUNT(@DatabaseName)
WHILE @DatabaseCursorCount <> 0
BEGIN
--Shrinks the database, the ( 0 ) in the parameter list is the amount of free space that
-- should be left in the database after the shrink. Change it to 10 if you want 10% free afterwards
DBCC SHRINKDATABASE (@DatabaseName,0,TRUNCATEONLY)
PRINT @DatabaseName
SET @DatabaseCursorCount = @DatabaseCursorCount - 1
END
SET @DatabaseCursorCount = @DatabaseCursorCount - 1
FETCH NEXT FROM @DBCC
INTO @DatabaseName
END

CLOSE @DBCC
DEALLOCATE @DBCC

Shrink TempDB

Hi

Just a quick note: One of my colleagues ran massive updates through SSIS. Eventually the package "fell over" with the error message that the TEMPDB couldn't allocate more hard drive space to the page - the hard drive space. We got techie on the problem, but no DBCC Shrink_XXX commands helped. Eventually he found the (OH SO OBVIOUS) solution - restart the SQL Service...

Now you know too...

Z