Handling Databases log overgrowth due to Recovery Model being “Full” when not required


Sometimes your database log fill up and the reason is that you have either not Setup a log backup or you legitimately do not need / want it due to your recovery point specs.

In this case you want to change the recovery model of a number of databases all at once.

Here is the code I use (tested on SQL 2008 onwards, should work in 2005 but not sure about earlier)

-- This script will generate for all databases in Full recovery mode TSQL which will
-- List all databases and log file sizes
-- 1. Shrink Log file(s)
-- 2. Change the Recovery Model

-- Following code can be used to introduce a carriage return / new line
-- char(13) + ' Go' + char(13) +
SELECT dbs.name Database_Name, Mf.size Log_File_Size ,
' Use ' + DBS.NAME + char(13) +
' DBCC SHRINKFILE (N''' + MF.NAME + ''' , 0, TRUNCATEONLY)' + char(13)
+ ' ALTER DATABASE ' + dbs.name + ' SET RECOVERY simple WITH NO_WAIT ' TSQL_For_Changes
FROM SYS.DATABASES DBS, SYS.master_files MF
WHERE DBS.DATABASE_ID = MF.database_id
AND MF.type = 1 -- If its a log file
AND DBS.recovery_model =1 -- If the recovery model is Full
AND DBS.database_id > 4 -- Skip the system databases
Order by Log_File_Size desc -- See the biggest Log file first

Advertisements
About

IT professional with wider interest in technology

Tagged with: ,
Posted in sql, tsql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: