August 12, 2013 at 7:10 pm
We are using Microsoft's System Center to monitor everything. One of the issues I am having with maintaining our DBs is the reports that run against fail after a few days when the log space percentage comes close to 100% (usually at about 98% is when they start to fail). The log file is set to 15,000 MB, but the size of the log is only about 350 MB. The only way I can get the reports running again is to set the database to backup the DB and transaction logs, set the DB to simple, truncate the log file, set the DB back to full and cross my fingers.
Can anybody think of any reason why the this would be occurring? I can upload the set of reports on the logs' size and percentage if that will help clarify things. If you have any other questions in relation to this, please feel free to ask.
Thank you for all of your help.
August 13, 2013 at 1:09 am
Is your log file set for autogrowth or restricted to some size?
what is your log file size when the error thrown
what is the log drive size and 15000 MB you have mentioned is that initial size you are talking about?
Regards
Durai Nagarajan
August 13, 2013 at 1:54 pm
Thank you for your reply.
They are all set to grow by 10% and restricted to 15,000 MB.
The log file size will be anywhere from 250 MB to700 MB, much, much less than its limit.
Initial size is 150 MB after shrinking the transaction log. The drive size is 140 GB, of which about 56 GB is being used.
I have attached an Excel file to show the percentage growth over a couple of hours while the logs have not changed in size.
August 14, 2013 at 4:37 am
agordon 35530 (8/13/2013)
Thank you for your reply.They are all set to grow by 10% and restricted to 15,000 MB.
The log file size will be anywhere from 250 MB to700 MB, much, much less than its limit.
Initial size is 150 MB after shrinking the transaction log. The drive size is 140 GB, of which about 56 GB is being used.
I have attached an Excel file to show the percentage growth over a couple of hours while the logs have not changed in size.
How can you say 250 MB to700 MB can you give exact size and any reason behind restricted to 15,000 MB
Regards
Durai Nagarajan
August 14, 2013 at 1:49 pm
Because is you look at the Excel spreadsheet from above, you'll see there are several databases (12)on this server.
I put the 15,000 MB log restriction in place because these logs chewed threw all the free space on the server several months ago and almost brought down the server. That should be more than enough room for a month's worth of transactions before needing to be flushed.
August 14, 2013 at 9:24 pm
agordon 35530 (8/14/2013)
Because is you look at the Excel spreadsheet from above, you'll see there are several databases (12)on this server.I put the 15,000 MB log restriction in place because these logs chewed threw all the free space on the server several months ago and almost brought down the server. That should be more than enough room for a month's worth of transactions before needing to be flushed.
What Recovery Model are you normally using for the database and are you doing transaction log backups?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2013 at 9:30 pm
Recovery Model is Full.
These databases are part of System Center 2012. I do backup the DB and logs as that is the only surefire way to get the percentage down.
Right now I have four DBs at 153.625 MB each but are at 43.11559%, 62.02928%, 50.46849% and 9.974128%. This is just bizaar, as one was at 62% then 10 minutes later dropped to 24% without me doing a thing except for running the script to view the size of all the DBs on this server.
August 15, 2013 at 6:26 am
Are you sure a log backup didn't run in that 10 minutes? That would explain it.
August 15, 2013 at 6:34 am
Steve Jones - SSC Editor (8/15/2013)
Are you sure a log backup didn't run in that 10 minutes? That would explain it.
Agreed.
Now the only problem as to why a 15,000MB log file would fill up. That's going to take a wee bit o' time to figure out. It could be something as simple as doing a rebuild on a clustered index of one of the larger tables or it could be some, ummm... performance challenged batch code that's simply doing too many writes to the database inside of a large transaction (or single "busy" query) while a full backup is running (for example). It could also be that the FULL back is taking long enough that a whole bunch of queries that write to the log run without the log being truncated by the transaction log backups.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2013 at 2:25 pm
The only backups that run are the ones that I run manually and the automated backup at 11 PM. I created a maintenance plan yesterday to automate a backup to hopefully keep the percentage at a reasonable level until I can figure out something more.
I don't see the actual size of the log increasing much at all, but for some reason SQL thinks it is. The file size on the hard drive and the size listed when I run the scipt are the same.
When I run a manual backup (both Full and Transaction Log) the total time it takes is about five minutes from start to finish, this includes making all the setting changes and adjustments.
I think there are evil spirits living in the SQL server causing me to lose more of my hair or maybe aliens. I think I'll have to wrap the server in aluminum foil to keep them out.
August 15, 2013 at 4:13 pm
agordon 35530 (8/15/2013)
The only backups that run are the ones that I run manually and the automated backup at 11 PM. I created a maintenance plan yesterday to automate a backup to hopefully keep the percentage at a reasonable level until I can figure out something more.I don't see the actual size of the log increasing much at all, but for some reason SQL thinks it is. The file size on the hard drive and the size listed when I run the scipt are the same.
When I run a manual backup (both Full and Transaction Log) the total time it takes is about five minutes from start to finish, this includes making all the setting changes and adjustments.
I think there are evil spirits living in the SQL server causing me to lose more of my hair or maybe aliens. I think I'll have to wrap the server in aluminum foil to keep them out.
But you're collecting a whole day's worth of logging until the backup occurs. You need to take transaction log backups much more often than that. I automatically run mine every 10 minutes.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2013 at 4:52 pm
Ok. I'll change my maintenance plan to run once an hour right now and see if that helps things out. Thank you for your suggestions.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply