June 17, 2008 at 9:37 am
Transaction log fills about 90 GB with a few minutes. Is there a way I can check which process fills the transaction log.
Which object should I select in profile to check what process fills the transaction log.
June 17, 2008 at 10:23 am
I would say you have some process that is updating every row in your large table.....
do this to identify....this will identify the top disk IO users for the past minute.....change the wait time down from a minute to 15 or 30 seconds if you need it to run shorter and change the sort to "9 desc, 11 desc..." if you want to list CPU offenders....
Select d.name as 'DatabaseName', spid, p.status, cmd,
p.loginame, nt_username, hostname, program_name,
cpu, physical_io, memusage, blocked
into ##FirstLook
from master.dbo.sysprocesses p (nolock)
join master.dbo.sysdatabases d (nolock)
on p.dbid = d.dbid
order by D.name, nt_username
waitfor delay '00:01:00'
Select d.name as 'DatabaseName', spid, p.status, cmd,
p.loginame, nt_username, hostname, program_name,
cpu, physical_io, memusage, blocked
into ##SecondLook
from master.dbo.sysprocesses p (nolock)
join master.dbo.sysdatabases d (nolock)
on p.dbid = d.dbid
order by D.name, nt_username
Select b.DatabaseName, b.spid, b.status, b.loginame,
b.nt_UserName, b.hostName, b.Program_name, b.spid,
B.cpu - isnull(A.cpu,0) as MinuteCPU,
b.cpu as TotCPU,
b.Physical_io - isnull(a.physical_io,0) as MinuteIO,
b.physical_IO as totIO,
b.memusage - isnull(a.memusage,0) as MinuteMem,
b.memusage as TotMem, b.blocked as BlkBy
from ##firstLook a
right outer join ##secondLook b
on a.spid = b.spid
and a.databasename = b.databaseName
and a.loginame = b.loginame
order by 11 desc, 9 desc 13 desc
--select * from ##firstLook
--select * from ##secondLook
drop table ##firstLook
drop table ##SecondLook
June 17, 2008 at 10:25 am
oh, yes one more thing....once you know the SPID, doa DBCC Inputbuffer(SPID) to get the text of the offending message...
June 17, 2008 at 12:38 pm
Thanks a lot.
This script looks great. I wil create a job and run on Saturday and Sunday to catch the process which fills the transaction log to 90 GB in 12 hours.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply