January 30, 2009 at 8:00 am
Hi
A server of mine is running multiple jobs fairly continuosly all of which are hitting the transaction log.
Infrequently and in no pattern I can recognise the log blows up rapidly and I can guess whats causing it from sp_who2
Im trying to script a way that can identify and display by spid the % contribution to filling up the log
i.e.
spid
1 20%
2 80%
Is this possible?
February 2, 2009 at 6:37 am
Not without a third party log reading tool.
Why not run a server side trace and capture the transactions being run. Then you can figure out the bad actor.
"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
February 2, 2009 at 3:14 pm
Check BOL for sys.dm_db_task_space_usage. It displays tempdb usage by spid (session_id).
Once you get the session_id of some of the worst offenders, you can join up to sys.dm_exec_sessions, sys.dm_exec_requests, and/or sys.dm_exec_connections to determine specific procs/calls/users/whatever.
Note that the amount of space allocated does not necessarily equate to log space used. For example, if a spid had allocated a temp table, and then repeatedly updated it without adding more rows, it would cosume significantly more log than the file allocation. Even so, data returned by the above DMV will highlight most of the tempdb-log-consuming spids.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply