May 19, 2011 at 7:50 am
One of the servers I manage has over 100 SharePoint databases. It does Transaction Log backups every 3 hours from 4am until 7pm. Between 7pm and 4am almost no users are on SharePoint.
But the file size for the Transaction Log backups taken at 4am are massive, for all databases. How can I determine what is happening between 7pm and 4am?
I have thoroughly checked all my other Maintenance Plans and Jobs, and there is nothing scheduled to run during those hours. I dug through all the other SQL logs and Windows logs to try to find something relevant that is logged, but I see nothing.
Is there a Trace I should set up? If so, what would I look for?
Or are there DMVs or DMFs I could look into?
Any other ideas??
May 19, 2011 at 7:51 am
I has to be a reindex job. It's always that or a massive import of data.
May 19, 2011 at 7:56 am
why don't you run the t-log backups 24 hours a day?
May 19, 2011 at 8:35 am
I know sharepoint has it's own index crawl which is I believe is scheduled to run after the app pool is recycled but not sure if this would effect the DB, check in sharepoint central admin console. But easy way.. load up profiler and capture whats going on.
May 19, 2011 at 8:35 am
steveb. (5/19/2011)
why don't you run the t-log backups 24 hours a day?
SharePoint is used by office workers during regular business hours, so there should hardly be any transactions happening during the night. I don't think there would be anything to be gained by doing more TLog backups off-hours.
May 19, 2011 at 8:40 am
jpSQLDude (5/19/2011)
steveb. (5/19/2011)
why don't you run the t-log backups 24 hours a day?SharePoint is used by office workers during regular business hours, so there should hardly be any transactions happening during the night. I don't think there would be anything to be gained by doing more TLog backups off-hours.
log backups are not about gains but about preventing loss.
Takes you 1 minute to extend the schedule to 24 / 7 and it can save someone's all nighter if something bad happens.
May 19, 2011 at 9:09 am
Ninja's_RGR'us (5/19/2011)
jpSQLDude (5/19/2011)
steveb. (5/19/2011)
why don't you run the t-log backups 24 hours a day?SharePoint is used by office workers during regular business hours, so there should hardly be any transactions happening during the night. I don't think there would be anything to be gained by doing more TLog backups off-hours.
log backups are not about gains but about preventing loss.
Takes you 1 minute to extend the schedule to 24 / 7 and it can save someone's all nighter if something bad happens.
I didn't want to get side-tracked on this, but those 100 databases are over 1TB in size, and we retain a month of backups(Full+Diff+TLog), so that is over 5TB of extremely expensive SAN space, so the customer is willing to take that chance by reducing the size of the backups by not doing backups at night when very few people are working.
So... any idea how you can find out what is happening to cause TLogs to grow during the night, when nothing seems to be showing up in the SQL Logs?
May 19, 2011 at 9:11 am
Check your SQL jobs. Highly likely there's an index rebuild scheduled for that time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2011 at 9:16 am
jpSQLDude (5/19/2011)
Ninja's_RGR'us (5/19/2011)
jpSQLDude (5/19/2011)
steveb. (5/19/2011)
why don't you run the t-log backups 24 hours a day?SharePoint is used by office workers during regular business hours, so there should hardly be any transactions happening during the night. I don't think there would be anything to be gained by doing more TLog backups off-hours.
log backups are not about gains but about preventing loss.
Takes you 1 minute to extend the schedule to 24 / 7 and it can save someone's all nighter if something bad happens.
I didn't want to get side-tracked on this, but those 100 databases are over 1TB in size, and we retain a month of backups(Full+Diff+TLog), so that is over 5TB of extremely expensive SAN space, so the customer is willing to take that chance by reducing the size of the backups by not doing backups at night when very few people are working.
So... any idea how you can find out what is happening to cause TLogs to grow during the night, when nothing seems to be showing up in the SQL Logs?
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).
May 19, 2011 at 9:28 am
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.
May 19, 2011 at 9:30 am
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??
May 19, 2011 at 9:34 am
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??
They will be the same overall as it is backing up the transactions that happened since last transaction backup, otherwise when you do the morning t-log backup it will be larger than the others as it's backing up all the overnight transactions.
May 19, 2011 at 9:34 am
jpSQLDude (5/19/2011)
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??
No, and that's not what he's saying.
If you take log backups from 7am - 5pm or if you take log backups from midnight to midnight the sum of the file sizes will be much the same.
That's because, if there are no transactions in the log needing backing up, the log backup just contains headers and is a few kb in size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 19, 2011 at 9:38 am
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.
May 19, 2011 at 9:43 am
Michael Valentine Jones (5/19/2011)
The point is that with many applications, you do not know exactly when activity is taking place, or exactly what is happening.
Can we run and analyze a Trace to pinpoint what activity is happening when?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply