garychu
asked on
How to control growing size of an MSSQL database transaction log file
Recovery mode of a MSSQL 2000 SP3 database is set to full.
Size of database is about 80GB.
Backup of the transaction log takes place every 2 hours daily.
Transaction log file is set to grow by 500MB at a time, with no restriction on size.
The transaction log file seems to be growing out of control. Presently at about 45GB! and growing (or possibly stabilising at this size). I once shrank it, using the shrink-file option in the data maintenance wizard, to 1GB. But within a fortnight, it re-generated to its present size.
I had always thought that by regular backups of the transaction log, it will keep the physical file small.Without the need to manually shrink it. And if I do, how would I know what size would be appropriate? I have tried arbitrarily setting a maximum size to allow it to grow to (1GB). But within hours the application crash, complaining of inadequate log space.
Would really appreciate some expert advice, for which my thanks in advance.
Size of database is about 80GB.
Backup of the transaction log takes place every 2 hours daily.
Transaction log file is set to grow by 500MB at a time, with no restriction on size.
The transaction log file seems to be growing out of control. Presently at about 45GB! and growing (or possibly stabilising at this size). I once shrank it, using the shrink-file option in the data maintenance wizard, to 1GB. But within a fortnight, it re-generated to its present size.
I had always thought that by regular backups of the transaction log, it will keep the physical file small.Without the need to manually shrink it. And if I do, how would I know what size would be appropriate? I have tried arbitrarily setting a maximum size to allow it to grow to (1GB). But within hours the application crash, complaining of inadequate log space.
Would really appreciate some expert advice, for which my thanks in advance.
Check if not executed during the growth of transaction log any operations "bulk insert"? Probably worth a try at least a day to put the recovery model "bulk-logged" and see how it will grow the transaction log.
You are right on account of the fact that the regular execution of a transaction log backup will keep its size is not large, but the backup log overlap only those transactions which are recorded on the disc is already confirmed, look for Performing backup when you have some kind of long processing time Transactions that do not give the opportunity to reduce the size of SQLServer.
You are right on account of the fact that the regular execution of a transaction log backup will keep its size is not large, but the backup log overlap only those transactions which are recorded on the disc is already confirmed, look for Performing backup when you have some kind of long processing time Transactions that do not give the opportunity to reduce the size of SQLServer.
Agree with rrjegan17 on this. Increasing the frequency would be the best solution(try scheduling for every 30 mins /15 mins) to keep the T-log size under control. After which, shrinking the log file will make the log file size to be set to the required value.
>>And if I do, how would I know what size would be appropriate?
You can estimate the maximum size of the log file only during the course of time by observing the log file size periodically. Keep checking the log file size and based on the maximum size the log file has grown, add 20% to the max size and set the max limit.
>>And if I do, how would I know what size would be appropriate?
You can estimate the maximum size of the log file only during the course of time by observing the log file size periodically. Keep checking the log file size and based on the maximum size the log file has grown, add 20% to the max size and set the max limit.
are you only backing up the logs or what's the rest of the backup schedule look like
>> kindly schedule a Transactional Log backup Maintenance plan to run more frequently.
That doesn't mean you should schedule only Transactional Lob backup Maintenance plan alone, you also need to schedule your Full backup too..
For eg.
Create a Maintenance plan for Full Backup daily and Transactional log backup every 15 or 30 minutes.
That doesn't mean you should schedule only Transactional Lob backup Maintenance plan alone, you also need to schedule your Full backup too..
For eg.
Create a Maintenance plan for Full Backup daily and Transactional log backup every 15 or 30 minutes.
ASKER
Thank you very much for the useful inputs, experts.
Present maintenance regime, among other things, backup the database on a daily basis. The transaction log is backup every 2 hours during the working day. May increase the frequency of transaction log backup.
The tran log file seems to grow by leaps and bounds. In a fortnight, it grew from 1GB to 47GB against a database of 70GB!
So backup alone will not truncate the tran log file. I will have to schedule a shrinkfile as well.?
Out of interest - is there a difference between using the suggested script and doing it via
All tasks > Shrink Database > Files > database_log ............?
Present maintenance regime, among other things, backup the database on a daily basis. The transaction log is backup every 2 hours during the working day. May increase the frequency of transaction log backup.
The tran log file seems to grow by leaps and bounds. In a fortnight, it grew from 1GB to 47GB against a database of 70GB!
So backup alone will not truncate the tran log file. I will have to schedule a shrinkfile as well.?
Out of interest - is there a difference between using the suggested script and doing it via
All tasks > Shrink Database > Files > database_log ............?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again for your patience, experts.
It was because backup of the trans log every 2 hours (without doing a shrinkfile) does not seem to truncate the physical log file that started my question.
Please let me get it right.
1)Backup of trans log - frequently (say every 15 minutes) will truncate the log file.
Only at some level of frequency, truncation will happen?
2) And on the other hand, regular shrinkfile is not advisible?
Still stilightly perplexed.
It was because backup of the trans log every 2 hours (without doing a shrinkfile) does not seem to truncate the physical log file that started my question.
Please let me get it right.
1)Backup of trans log - frequently (say every 15 minutes) will truncate the log file.
Only at some level of frequency, truncation will happen?
2) And on the other hand, regular shrinkfile is not advisible?
Still stilightly perplexed.
In any normal environment every time you do a Transaction Log backup the inactive transactions are truncated (This does not shrink the file, nor should it) and makes the space available for new transactions.
If you are going to use Full Recovery Model, then it is your resposibility to find the adequated frequency of your Transaction Log backups. So your question is: How frequente is adequate? Only you know the answer to that. Since two hours does not appear sufficient I would increase it, until the file does not increase in size.
If all of this is over-whelming to you or you do not have sufficient expertise in your team, than simply change to the Simple Recovery Model (no pun intended), just understand that you will no longer be able to do point-in-time restores.
If you are going to use Full Recovery Model, then it is your resposibility to find the adequated frequency of your Transaction Log backups. So your question is: How frequente is adequate? Only you know the answer to that. Since two hours does not appear sufficient I would increase it, until the file does not increase in size.
If all of this is over-whelming to you or you do not have sufficient expertise in your team, than simply change to the Simple Recovery Model (no pun intended), just understand that you will no longer be able to do point-in-time restores.
ASKER
I think I finally got it.
I had until now confused truncation of the trans log file with physical reduction in the log file.
It stands to reason therefore;
- Regular shrinkfile may not be a good idea (much the same way as its advantageous to keep the OS pagefile.sys permanent).
- Frequent trans log backup will over time, prevent the physical size of the log file from rapidly growing unnecessorily.
Thanks experts, for the help.
I had until now confused truncation of the trans log file with physical reduction in the log file.
It stands to reason therefore;
- Regular shrinkfile may not be a good idea (much the same way as its advantageous to keep the OS pagefile.sys permanent).
- Frequent trans log backup will over time, prevent the physical size of the log file from rapidly growing unnecessorily.
Thanks experts, for the help.
>> - Frequent trans log backup will over time, prevent the physical size of the log file from rapidly growing unnecessorily.
Isn't what I mentioned in my first comment..
At least a split would have been fair
Isn't what I mentioned in my first comment..
At least a split would have been fair
In order to keep your Transaction file size under control, kindly schedule a Transactional Log backup Maintenance plan to run more frequently.
And once you take a Transactional log file backup, shrink your log file using hte script below:
USE ur_db_name
GO
DBCC SHRINKFILE (ur_db_log_filename, 1000);
to shrink your log file size to 1000 MB ie., 1 GB
>> Recovery mode of a MSSQL 2000 SP3 database is set to full.
Applying SP4 is recommended.