December 9, 2009 at 2:47 pm
Not sure of Trace will trace system-issued rollbacks or not. Engineer a full transaction log with a long running transaction and you can check it out.
You can look for rollbacks in the log using fn_dblog and filter on the LOP_ABORT_XACT log record - see my blog for more details.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 9, 2009 at 3:23 pm
Thanks Paul; will give that a spin ...
I don't know if you had a chance to review the dmv stats I posted. In your SQL adventures, have you ever seen such high reads on the log files?
December 9, 2009 at 3:32 pm
Yes - it's entirely feasible. How much transaction log do you generate in a day? How big is each log backup? The total log backups for a day should roughly equal the amount you're seeing being read from the file, if nothing else is causing any reads (from my list above).
Also - how big is the database, and how much log is being generated during the time the full backup is running? All that log will be read twice - once for inclusion in the full backup, and a second time for the log backups themselves.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 9, 2009 at 6:34 pm
Thought this would make a good blog post: see What can cause log reads and other transaction log questions
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 10, 2009 at 1:47 am
G33kKahuna (12/9/2009)
@Gail: The log size is about 5G on disk. I apologize, should have given the numbers. The reported number are as below
How much of that is in use at the time a log backup occurs? Or how large are your log backups.
In your SQL adventures, have you ever seen such high reads on the log files?
Not speaking for Paul, but in my experience those are not large numbers. I used to work on a DB that had a 20GB log file. During some overnight operations were could and did mostly fill that log. Log backups were every 15 min and were 200-600 MB each during the day, much larger at night. Plus transactional replication. We had log read numbers that reached easily reached over 100GB a day.
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
December 10, 2009 at 7:55 am
Gail, Paul, thanks for sharing your knowledge and experience ...
The trend except for the # of reads on the log file hasn't changed for this app
Full backup - 60G runs about 3AM
Hourly Txn backup - roughly 1 G except @11pm 3-4G depending on how many transaction a nightly batch job crunches
I went back through some of the old file stats and back logs; the backups sizes are about the same but none of the file captures come close to the recent capture in terms of the number of reads. Below is a snapshot of an old capture (a month ago) showing the most # of reads among the old captures
----------------------------------------------------------
| # of Reads | MBs Read | # of Writes | MBs Written |
----------------------------------------------------------
| 26,316 | 15,470.48 | 514,273 | 11,112.40 | Log file
----------------------------------------------------------
Below is the recent for a side by side look
----------------------------------------------------------
| # of reads | MBs Read | # of writes | MBs Written |
----------------------------------------------------------
| 190,691 | 19,002.90 | 703,059 | 12,520.35 | Log File
-----------------------------------------------------------
the jump seems pretty steep ...
December 10, 2009 at 9:06 am
If your log is backing up 1 GB every hour, that's probably accounting for the majority of those 19GB.
Why are you so focused on the log writes? Are you seeing high read/write stalls? Are you seeing high disk usage times? Are you seeing high read and write times?
Even if there's a large number of reads, if the reads and writes are serviced fast enough there's no major reason to worry.
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
December 10, 2009 at 10:16 am
I'm sorry may be I didn't state clearly. Size is not my concern just the # of reads against the log file. I've a different DB with XML data that dumps about 250G of log size; so log size doesn't scare me as much.
The higher # of reads does concern me. It may be false positive but I want to make sure I can account for it and eliminate it.
My wait stats does show IO pressure; higher on the reads and log activity than writes
December 10, 2009 at 10:39 am
What's the read and write stall look like for 24 hours?
What's the % idle time, avg sec/read and avg sec/write for the log drive? For the data drive? (perfmon)
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply