MS SQL Server - Shrinking the transaction log

Author

Shrinking the transaction log

Brendon B

2006-01-30, 9:23 am

Hi Everyone

Recently the space on our SQL server started running out.
Upon investigation I found that the our billing database has an extremely
big LDF file which is apparantly the transaction log for that database.
(Please bare with me as I am not an SQL administrator)
I found this article which gives possible causes for this:
http://support.microsoft.com/kb/317375/%20#5

I don't know where to start to try and determine what is causing the log
file to grow. Since it is a billing database, the transactions are rather a
lot and this could be why.
I had a look at this article which describes how to shrink the transaction
log manually:
http://support.microsoft.com/kb/272318/EN-US/
-What kind of problems could this cause?
-Is there a way that I can check what size it should be able to shrink to?
i.e Virtual log size etc?
-Is there anything else I should be aware of?
-Shouldnt this file shrink during backup and maintenance plans?

Thanks for your help

Regards
Brendon

Steen Persson (DK)

2006-01-30, 9:23 am

Brendon B wrote:
> Hi Everyone
>
> Recently the space on our SQL server started running out.
> Upon investigation I found that the our billing database has an extremely
> big LDF file which is apparantly the transaction log for that database.
> (Please bare with me as I am not an SQL administrator)
> I found this article which gives possible causes for this:
> http://support.microsoft.com/kb/317375/%20#5
>
> I don't know where to start to try and determine what is causing the log
> file to grow. Since it is a billing database, the transactions are rather a
> lot and this could be why.
> I had a look at this article which describes how to shrink the transaction
> log manually:
> http://support.microsoft.com/kb/272318/EN-US/
> -What kind of problems could this cause?
> -Is there a way that I can check what size it should be able to shrink to?
> i.e Virtual log size etc?
> -Is there anything else I should be aware of?
> -Shouldnt this file shrink during backup and maintenance plans?
>
> Thanks for your help
>
> Regards
> Brendon
>
>
>
Hi Brendon

Before you start to trying shrinking your log file, then read up on
BACKUP/RESTORE and Log file architecture in Books On Line.

First of all, you can run your database in SIMPLE of FULL Recovery mode.
Simple means that the logfile will truncate it self hence
"automatically" reuse the space in the file. In simple mode you can't
backup the logfile which means that you loose some ability to RESTORE
data up to just before things went wrong.
In FULL recovery, the log file is only being truncated when you perform
a log backup. FULL recovery gives you the ability to restore to a point
in time if somethnig goes wrong. FULL recovery also means that you'll
have to do log backups on a regular basis since this is the only thing
that will truncate your logfile hence "releasing" space in the file to
be reused.
SInce your logfile is very big, it's most likely that your database are
running in FULL recovery mode, but that you're not backing up your
logfile on a regular basis.

In you case I'd first of all run a backup of the logfile, so it can be
truncated. That will "buy" you some time to read up on how the logfile
works, and with that knowledge find out how you want to treat your
logfile in the future. You should basically decide if it's ok to run in
SIMPLE recovery mode with the lack of restore possibility but less
"maintenance" or if you need the restore ability that FULL recovery
gives you but with the extra work to maintain a log backup plan.

When you have considered the above, you can check the size of your
logfile and then find out if you can shrink it to a more appropriate
size or not.

Tibor has a quite good article that covers the "shrinking
considerations" - http://www.karaszi.com/sqlserver/info_dont_shrink.asp

Regards
Steen

Brendon B

2006-01-31, 7:23 am

"Steen Persson (DK)" wrote:

> Brendon B wrote:
> Hi Brendon
>
> Before you start to trying shrinking your log file, then read up on
> BACKUP/RESTORE and Log file architecture in Books On Line.
>
> First of all, you can run your database in SIMPLE of FULL Recovery mode.
> Simple means that the logfile will truncate it self hence
> "automatically" reuse the space in the file. In simple mode you can't
> backup the logfile which means that you loose some ability to RESTORE
> data up to just before things went wrong.
> In FULL recovery, the log file is only being truncated when you perform
> a log backup. FULL recovery gives you the ability to restore to a point
> in time if somethnig goes wrong. FULL recovery also means that you'll
> have to do log backups on a regular basis since this is the only thing
> that will truncate your logfile hence "releasing" space in the file to
> be reused.
> SInce your logfile is very big, it's most likely that your database are
> running in FULL recovery mode, but that you're not backing up your
> logfile on a regular basis.
>
> In you case I'd first of all run a backup of the logfile, so it can be
> truncated. That will "buy" you some time to read up on how the logfile
> works, and with that knowledge find out how you want to treat your
> logfile in the future. You should basically decide if it's ok to run in
> SIMPLE recovery mode with the lack of restore possibility but less
> "maintenance" or if you need the restore ability that FULL recovery
> gives you but with the extra work to maintain a log backup plan.
>
> When you have considered the above, you can check the size of your
> logfile and then find out if you can shrink it to a more appropriate
> size or not.
>
> Tibor has a quite good article that covers the "shrinking
> considerations" - http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
> Regards
> Steen
>
>
Hi Steen

Thank you for the excellent response. I'm definetly going to do a lot of
reading.
I took your advice and ran a Transaction Log backup on the applicable
database from enterprise manager. The backup completed succesfully, but the
LDF file did not change size at all. Am I doing something wrong or is there
something more I need to do?

Thanks again for the response

Steen Persson (DK)

2006-01-31, 7:23 am

Brendon B wrote:
>
> "Steen Persson (DK)" wrote:
>
> Hi Steen
>
> Thank you for the excellent response. I'm definetly going to do a lot of
> reading.
> I took your advice and ran a Transaction Log backup on the applicable
> database from enterprise manager. The backup completed succesfully, but the
> LDF file did not change size at all. Am I doing something wrong or is there
> something more I need to do?
>
> Thanks again for the response

Hi Brendon

When you run a log file backup, the size of the physical logfile will
not be changed. The system is truncating the log which means that it's
removing "incative" entries in the logfile and it can then re-use the
space in the file. If you want to make the physical file smaller, you'll
have to run DBCC SHRINKFILE. I'd suggest that you read up the stuff I
mentioned in first reply before you run this command though. Not that
the command is "dangerous" in any way, but in my opinion you shouldn't
run commands like this unless you know what they do.

Regards
Steen

Sponsored Links