August 23, 2005 at 8:33 am
I've noticed that the transaction logs for my SQL Server databases just keep growing and growing. I know that there is an option to constrain the log file size, but this sounds like a terrible idea - what happens when all the allocated space is used up?
Here is what I want to do -
1) Set up a system whereby the transaction logs are backed up nightly.
2) Once the logs have been backed up, shrink the actual transaction logs down to a reasonable size (like a couple of gigs or something)
Is this possible? If so, how do I do it? Keep in mind that I am not a DBA.
Thanks.
August 23, 2005 at 8:50 am
First thing is that you want to avoid using auto grow on your transaction logs. Set a hard limit and have SQL report when it is within 10-15% hitting that max. You can then grow it accordingly.
Once the transaction log is backed up, it will truncate the log to the firt open transaction. Keep in mind, the file size remains but the log has that space to use again.
If you want to shrink the file, you can use dbcc shrinkfile but I would, set my log size to max size, and backup my transaction log more frequently (every 4 hours). This should keep you within the bounds of your established max size.
I forgot to ask, what do you define as reasonable?
August 23, 2005 at 9:06 am
First thing is that you want to avoid using auto grow on your transaction logs. Set a hard limit and have SQL report when it is within 10-15% hitting that max. You can then grow it accordingly.
If I set a hard limit, what happens when we hit that limit? Wouldn't the database stop allowing transactions at that point? I'm pretty sure I've seen this error around before :
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database 'nCommunity' is full. Back up the transaction log for the database to free up some log space.
Once the transaction log is backed up, it will truncate the log to the firt open transaction. Keep in mind, the file size remains but the log has that space to use again.
As far as backing up the transaction log goes - we currently have a sql server agent "maintenance plan" job that creates static dumps of our database nightly. Do we need to modify this job so that it backs up our transaction logs at the same time? Or is it safe to back up the transaction log seperately, after our backup job has finished?
August 23, 2005 at 9:11 am
You sure will receive that error if you transaction fills up. That is why you try to find the max size of your transaction log and set an alert to warn you of your log filling up.
As for backing up, the most common way to do this is to:
1. Back up the entire data as sometime during the day.
2. Perform transaction log backups at specified intervals during the day.
3. Repeat process each day.
For example:
Each day at 0500 hrs, one of my databases performs a complete database backup.
Every hour after that, I do a transaction backup.
Does that help you at all? If not, shoot me a message, I can try to help you through it.
August 23, 2005 at 9:43 am
You may also want to be sure to run a transaction log backup prior to any full backup. The Full backup does not remove commited transactions from the log, even if you have the settings set to do so.
August 23, 2005 at 12:11 pm
Since it doesn't sound like you are currently backing up the transaction logs, do you want to do it for "point in time" or "point of failure" recovery or are you just interested in keeping the logs from growing too much? Are the database recovery models set to "Full"?
If recovery model is "Full" and you don't care about using the log backups for recovery, set the recovery model to "Simple" and the logs will be truncated after each full backup.
Greg
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply