How can I figure out what my databases are doing at night? Why the massive Log File growth?!
-
Michael Valentine Jones (5/19/2011)
One of the reasons to run transaction log backups more often is to control the size of the transaction log files. As was said in another post, it will not increase the total storage requirements for backups.We usually run them every 15 minutes, 24x7. Sometimes we run them more often, like every 5 minutes.
Also, running the transaction log backups more often will help you to pinpoint the exact time that the activity is occuring. Just look at the backup file sizes for when they start getting larger.
A picture is worth 1000 words :
-
As I said before, I would increase the transaction log frequency to every 15 minutes, 24x7. That will help control transaction log file growth and help you identify exactly when the activity is taking place.
Once you identify when the activity is happening, you can schedule a trace to look at the activity that is happening at that time.
-
^^ Looks like you get a LOT of activity at 8.15am, must be eveyone logging in or something??
Not to keep banging on about the 24 t-log backups, but i dont think i could sleep at night knowing my 100 dbs are not being backed up over night...
-
Michael Valentine Jones (5/19/2011)
jpSQLDude (5/19/2011)
Ninja's_RGR'us (5/19/2011)
That shows a basic lack a comprehension in logs. Wether you take 100 log backups in 24 hours or only 1 the total size will be exactly the same (except for ± 2-3 mb of extra headers).So if you take TLog backups during the time when people are making changes to the database, and you also take TLog backups when no one is making any changes to the database, those TLog backups will be the same size? Really??
The point is that with many applications, you do not know exactly when activity is taking place, or exactly what is happening.
That is what is happening with your application, or you would not be here posting questions about it.
Run the logs every 5 minutes and that'll give you a pretty good idea.
I'd also let a trace run all night to see what was happening at the exact time the problem starts.
-
steveb. (5/19/2011)
^^ Looks like you get a LOT of activity at 8.15am, must be eveyone logging in or something??Not to keep banging on about the 24 t-log backups, but i dont think i could sleep at night knowing my 100 dbs are not being backed up over night...
I loaded a 500 MB trace files a couple times in the prod db. Even my reindex job doesn't create that much activity :w00t:.
-
I have a database with about 95 GB of data that produces compressed transaction log backups totaling about 37 GB in a span of about 40 minutes when the weekly re-index job runs. I have 160 GB allocated for the transaction log file, and it uses almost all of it when that job runs, even with transaction log backups running every 5 minutes.
Assuming 80% compression, that would be about 185 GB of uncompressed transaction log backups.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply