Shrinking the transaction log file?
Xeronimo
Posts: 9
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
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
Comments
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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
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.
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
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
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
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!
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
Chris
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
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