September 9, 2008 at 8:28 am
We ran Performance Monitor to determine a performance baseline,
and it caused the transaction logs to increase from a couple MBs to 40+ GBs
The counters we used were:
Processor Object:
-% Processor Time
System Object:
-Processor Queue Length
Memory Object:
-Pages/sec
-Available MBytes
PhysicalDisk Object:
-Avg. Disk Queue Length
-% Idle Time
SQL Server Buffer Manager Object
-Buffer Cache Hit Ratio
SQL Server Locks Object
-Average Wait Time
Would any one of these cause the transaction logs to increase exponentially?
This is on a server running Windows Server 2003 and SQL Server 2000 with latest Service Pack
EDIT: We ran the Performance Monitor from 12:00PM to 4:30PM,
it saved as one .csv (Text with Comma Delimited).
Any help would be greatly appreciated
Mike
--Mike
September 10, 2008 at 1:14 am
Sorry Mike,
but running performance monitor has no effect on you're database size or the transaction log unless you write the results directly to a database. Since you used a csv file, there must be some other proces inside SQL server which is causing that growth.
[font="Verdana"]Markus Bohse[/font]
September 10, 2008 at 10:51 am
Looks like it was the Performance Monitor.
When we removed
SQL Server Buffer Manager Object
-Buffer Cache Hit Ratio
SQL Server Locks Object
-Average Wait Time
from the counter list, the problem seems to be solved.
The Transaction Logs went back down to their usual size and have maintained their size for a day.
--Mike
September 10, 2008 at 11:40 am
Mike,
I have to agree with Markus on this one. Performance Monitor does not do any writing to the database so it won't affect log size. I think removing those counters and seeing the Log stay the same size was coincidence. I would think you had something else happening on the server when you first ran performance monitor that caused the log to grow. I'd investigate that by looking at job schedules and history. Maybe a backup log job failed so your transaction log had to grow instead of wrap.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 10, 2008 at 3:51 pm
The transaction log will not auto shrink. It sounds like you have a maintenance job or plan tha that is running overnight. I would investigate that as a source of your log file size changing.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply