August 10, 2010 at 2:27 pm
i)Can someone please tell me is there a way to find out how much would be committed and uncommitted transaction in a logfile. Lets says i have a DB in full recovery mode with log file size as 1 gb , is there a way to find out how much of 1gb has committed and how much uncommitted?
ii)Also when a log back up happens it would back up the uncommitted transactions ONLY ( since committed will be written after the log backup" and the flush out all the committed transcations right? Please advice
August 10, 2010 at 2:34 pm
Log backups back up everything in the log (active and inactive portions) and then mark the inactive portions of the log as reusable. Inactive meaning there's no uncommitted transactions, all the changed data pages have been hardened into the data file, the log records are not needed for replication, CDC or similar.
Uncommitted literally just means open transactions (where there's been a begin tran but no commit or rollback yet). Log records may be written to the log file before the commit, or they may be written as part of the commit, depends what else is going on.
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
August 10, 2010 at 2:50 pm
This is an article Paul Randal wrote on how Checkpoints work in SQL Server:
How do checkpoints work and what gets logged
In the post he goes into how he views what is the transaction log.
To expand on what Gail mentioned on the log reuse, another post by Paul that illustrates how the log is reused:
Inside the Storage Engine: More on the circular nature of the log
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 10, 2010 at 8:02 pm
so Gail as per your statement size of log file and size of trn file should be the same? I tested it, my log file is 1 gb and trn is just 300 mb? Am i missing something here?
August 10, 2010 at 10:58 pm
iqtedar (8/10/2010)
so Gail as per your statement size of log file and size of trn file should be the same?
No, not at all. Where did I say that?
I tested it, my log file is 1 gb and trn is just 300 mb? Am i missing something here?
How full was the log at the time? Around 30%?
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
August 11, 2010 at 10:54 am
i take it back you are right. Now lets says my logfile size is 1 gb out of that 800mb is unused and 200mb has committed and uncommitted. If i take backup my trn would be 200 but can i shrink the 200mb part?like can i shrink the 1 gb ldf to less than 200. We have a process which shrinks log file before shipping. Is thart right..
August 11, 2010 at 11:52 am
iqtedar (8/11/2010)
like can i shrink the 1 gb ldf to less than 200.
No, because there's 200MB of the log in use.
We have a process which shrinks log file before shipping. Is thart right..
Very, very, very bad idea. You'll be causing log fragmentation, you'll be forcing the log to grow again next time changes are made. Size the log appropriately for the workload and frequency of log backups, and leave it alone.
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
August 14, 2010 at 5:18 pm
thanks. Gail but the reason why we are shrinking the logfile is because sometimes the logfile is huge and takes lot of time to copy for logshipping. I think i should only shrink the logfile when it is too big instead of doing it everytime?What do you say.
August 14, 2010 at 7:16 pm
I don't really believe you have taken in Gail's advice completely. You might want to go back and read through from the beginning.
Find out why your logfile is big "sometimes" and then if that is on a scheduled time or within a certain window increase your log backup for that window during heavy processing. Which is exactly what she advised you to do:
GilaMonster (8/11/2010)
We have a process which shrinks log file before shipping. Is thart right..
Size the log appropriately for the workload and frequency of log backups, and leave it alone.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 15, 2010 at 2:24 am
iqtedar (8/14/2010)
thanks. Gail but the reason why we are shrinking the logfile is because sometimes the logfile is huge and takes lot of time to copy for logshipping.
Shrinking the file is NOT going to decrease the size of the log backups. Shrinking just removes unused space from the log. Backups only backup used space. If you want to decrease the size of your log backups, take log backups more frequently and work out what's making them so large (index rebuilds is a common one)
I think i should only shrink the logfile when it is too big instead of doing it everytime?What do you say.
You are hindering the performance of your server, the speed of your backups, the time taken to recover the DB. Are you happy that you're decreasing the overall performance of SQL every time you do that?
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
August 15, 2010 at 12:19 pm
No. So no matter if i shrink the file or not back up log file (i.e trn) file would be the same. Probably i should try to take more log backups. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply