SQL Server Log

SQL Server Log file Maintenance

In SQL Server when you have your recovery mode set to full or bulk logged you have to maintain the size of the log file; otherwise it will go unmanageable and very big. You can control the file size by running this sample procedure periodically. Note that you have to run this procedure after a full backup as it is going to shrink the log files.

This sample procedure accepts two parameters; the first one would be database name that you need to shrink the log file as string and the second one would be the intended file size in mega bytes as integer.

CREATE PROCEDURE shrinkLog (@paramDb as varchar(255), @paramSize as int) AS

DECLARE @fileId as int
DECLARE LogCursor CURSOR FOR SELECT fileid from sysfiles
where (status & 0×40) = 0×040

OPEN LogCursor

FETCH NEXT FROM LogCursor INTO @fileId

WHILE @@FETCH_STATUS = 0
BEGIN
IF @paramSize = 0 or (SELECT size * 8 /1024 FROM sysfiles WHERE fileId=2) > @paramSize
DBCC SHRINKFILE ( @fileID ) — default shrink

ELSE
DBCC SHRINKFILE ( @FileID, @paramSize)

FETCH NEXT FROM LogCursor INTO @fileID
END

Close LogCursor
DEALLOCATE LogCursor

BACKUP LOG @paramDb WITH TRUNCATE_ONLY — frees the size

RETURN

GO

Advertisements

2 Comments »

  1. […] SQL Server […]

  2. […] SQL Server Log […]

RSS feed for comments on this post · TrackBack URI

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: