Shrinking the transaction log file?

Xeronimo

Hi,

I've just made a backup of a DB. Shouldn't that result in a smaller transaction log file?

I've also tried to use the 'shrink files' option in SQL SERVER Management Studio to shrink the transaction log but even though I don't get an error message nothing happens ... the log stays at its (huge) size.

How could I solve this?

Thanks,

Jerome

Eddie D

Thank you for your post into the forum.

Performing a backup of a database does not result in a smaller transaction log file using native SQL Server Backup or Red Gate SQL Backup.

Performing a log backup of the database will allow the committed transactions to be overwritten creating free space within the transaction log. It will not reduce the size of the transaction log.

I am assuming that your database is using the FULL recovery model (or Bulk-Logged), as this will allow the DBA or user to be able to perform log backups of the database.

If your database is using the SIMPLE recovery model you will not be able to perform log backups. SQL Server manages the transaction log and prevents you from taking a log backup.

To reduce the size of the transaction log you need to perform the following actions:

1. Take a log backup of the database. This will allow the committed transactions to be overwritten by new transactions.

For instance, if the log is backed up every day, but on a particular day, millions of updates were performed on the database and the log file is unusually large. Once a transaction log backup is performed there is a lot of free space in the container, but the container itself does not shrink.

2. Run DBCC Shrinkfile to reduce the size of the log file by removing the free space in the transaction log.

For further information on DBCC Shrinkfile, please refer to the SQL Server Books On-Line documentation.

I hope the above answers your question.

Many Thanks
Eddie

Xeronimo

Thank you for this elaborate answer.

But so there is no way of reducing it automatically?

Using the Maintenance Plans in SQL Server Management Studio I was able to schedule 'shrink database' and thus also get the transaction log reduced in size. Could I still do this even though I'm using the Red-Gate SQL Backup for the actual backup of the databases (and not via a Maintenance Plan)?

Thanks!

Jerome

Xeronimo

Update:

I've manually backed up and shrunk the transaction log file using MSSQL Server Management Studio now and that worked.

But the shrinking did not work after a RedGate Backup of the transaction log ... So it seems like I still need to create a maintenance plan to backup and shrink the transaction log from time to time via MSSQL SMS, parallel to my backup schedules in RedGate backup?

That'd be a bit unfortunate since I thought I could do all of this exclusively in RedGate Backup ...

Any comments?

Thanks.

ChrisAVWood

If your database log is getting to large then you need to do at least 2 things.

One is too run your log backup more often, as this is the only thing that will take data out of the log.
Two would be to better size your log. It must be too small to hold the data from the transactions that get applied before you run your log backup. We run regular log backups as well as have a percent full alert that triggers the log backup.

Chris

Xeronimo

Thanks Chris,

So backing up the transaction log using RedGate Backup regularly will keep the log size down automatically?

How would I better size a transaction log then?

Cheers,

Jerome

ChrisAVWood

OKAY.

What is the size of the database and the definition of the log?

What release of SQL Server are you running?

If SQL2005 or higher just right click on the database and under Properties/Files you will see the actual sizes and the autogrow if you are letting it expand.

How often do you run the full and the log backups?

Then I can help you from there.

Chris

Xeronimo

Size of the DB: 3.4 Gb
Size of the transaction log (currently): 25 Mb (the 2-hourly transaction log backups vary between 4 and 250 Mb)

SQL Server 2005

DB initial size: 3.3 Gb - autogrowth: 10%, unrestricted
Log initial size: 25 Mb - autogrowth: 10%, unrestricted

Full backup once a night
2-hourly log backups

Thanks for your help!

ChrisAVWood

Thanks.

Our normal rule of thumb is to make the log file, at initial creation, 50% of the data. Based on that you are way off here as the data is over 100 times bigger.

Also I would suggest changing the increment from % to a fixed size in MB because 10% of 100 is 10 and then it becomes 10% of 110 and so on.

Based on your log output I would make the log close to 400Mb and grow in 50Mb chunks.

If I understand your info you run the log backup every two hours. If this is correct then why not add an alert to fire the log backup if the log fills to over say 65% (what we use).

HTH

Chris

Xeronimo

Ok, thanks. I'll try that!

ChrisAVWood

Had any luck in making changes yet?

Chris

Xeronimo

Yes, I have and it works fine! Thanks again.

Xeronimo

Or so I thought ... the log is now back to 3.6 GB!? I've also run a transaction log backup via RedGate SQL Backup again as a test right now and the backup file itself is only 101 KB (the previous backup of the transaction log was an hour ago ... its size is 660 MB though!)

Here are the sizes of the last 10 transaction log backups (in KB; and I'm backup up every two hours during work hours):

101
652697
559566
643
109
367
166
76
121
1404

Some thing still seems wrong ... argh, this transaction log is driving me crazy!?

Anything else I could try?

Cheers!

Jerome

ChrisAVWood

I would suggest adding the percent log full alert firing the log backup as you have at least 2 fairly large log backups. Ours is set to 65% full.

if you add this then see how often it gets fired and the size of your log backups when it runs from the alert.

Chris