Home > Geeky stuff > Truncating Microsoft sql transaction logs

Truncating Microsoft sql transaction logs

I rarely touch MS SQL but I have to administer 2 instances, so I have had to learn some of the administrative tasks that have to be done from time to time. One of the less obvious task is truncating transaction logs. The MS SQLs transaction logs can get out of hand on databases that do a lot of writes. MS SQL does not purge transactions that have been committed in case you want to roll the database back to a point in time.  At some point after a back up you will probably want to get rid of the old data as it can quickly fill your hard drive. You have to manually truncated the old transactions by running

BACKUP LOG databasename WITH TRUNCATE_ONLY


The easiest way to do this is to open up query optimizer and run it from there.

That will flush out all the old transactions that have already been committed to the database.  This however does not resize the file.  You will have to manually do this in enterprise manager by choosing shrink database.  If you have a scheduled maintenance plan that covers resizing; it will take place on its own the next time it is scheduled.

Advertisements
Categories: Geeky stuff
  1. No comments yet.
  1. No trackbacks yet.

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: