Joy Smith San (9/19/2012)
..Something came up my mind this morning..Suppose a transaction was started before the backup process starts, only one transaction for example.
Backup process back up the pages.. transaction is still open..
But before the backup completes the above said transaction commits/Roll Back.
So backup will also include the part of LOG for "Roll forward/Roll Back" while restoring the database.
Now my question is that, backing up the LOG is an overhead here is it ? So if I disconnect all the users before the backup starts, ensuring there's no any transaction open which may or may not commit before the backup process ends, would it have been more faster ?. (I know I just can't disconnect all the users ofcourse ! 🙂 )
Thanks.
Yeah, if you give the backup process less work to do, handling rollback/rollforward, then yeah, it'll be faster. But I wouldn't suggest disconnecting all users was a viable tuning method.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning