Transaction log fills about 90 GB

  • 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.

  • 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

    Selectb.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

  • oh, yes one more thing....once you know the SPID, doa DBCC Inputbuffer(SPID) to get the text of the offending message...

  • 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