How to shrink simple recovery log file in SQL Server

Question

I am having an issue,

My log file on a large database keeps getting large. Its set to simple recovery and right now the log file is 40GB big.

It got to the stage where its filled up the HDD, and when I try and delte older rows in the databse, it tries to record this to the log file, and it cant cause the hdd is full so it cant delete the entry.

So how can I a?

Scrap / delete / empty the current log file, i have no need for the data in it.

or b:

Delete from from the table in a manner that doesn't log it in the log file?

I have tried:

DBCC SHRINKFILE(FSK_LoggingServer__log, 200);
GO

but it returns this:

bdid, FieldId, CurrentSize, MinimumSize, UsedPages, EstimatedPages
32  2   4292352 512 4292352 512

and it does not shrink the file.

try DBCC SHRINKDATABASE({DatabaseName}, 200, TRUNCATEONLY); – David Shorthose Nov 26 '14 at 19:19 — David Shorthose, Nov 26 '14 at 19:19

score 0 · Answer 1 · answered Nov 26 '14 at 20:04

It sounds like you may have a long running query that is preventing the log from being truncated. Try running this:

DECLARE @LT TABLE
        (
          spid INT ,
          ttext VARCHAR(MAX) ,
          TranBeginTime DATETIME
        )
    DECLARE @CaptureDate DATETIME = DATEADD(minute,DATEDIFF(minute, 0, DATEADD(second, 30 - DATEPART(second, GETDATE() + '00:00:30.000'), GETDATE())), 0)

    INSERT  INTO @LT
            ( spid ,
              ttext ,
              TranBeginTime
            )
            SELECT  sessions.session_id ,
                    statements.text ,
                    at.transaction_begin_time
            FROM    sys.dm_tran_database_transactions db_trans
                    JOIN sys.dm_tran_session_transactions sessions ON db_trans.transaction_id = sessions.transaction_id
                    JOIN sys.dm_exec_connections conns ON conns.session_id = sessions.session_id
                    LEFT OUTER JOIN sys.dm_tran_active_transactions at ON db_trans.transaction_id = at.transaction_id
                    CROSS APPLY sys.dm_exec_sql_text(conns.most_recent_sql_handle)
                    AS statements
            WHERE   at.transaction_begin_time < DATEADD(Minute,-30,GETDATE())

    CREATE TABLE #sp_who2
        (
          SPID INT ,
          Status VARCHAR(1000) NULL ,
          Login SYSNAME NULL ,
          HostName SYSNAME NULL ,
          BlkBy SYSNAME NULL ,
          DBName SYSNAME NULL ,
          Command VARCHAR(1000) NULL ,
          CPUTime INT NULL ,
          DiskIO INT NULL ,
          LastBatch VARCHAR(1000) NULL ,
          ProgramName VARCHAR(1000) NULL ,
          SPID2 INT ,
          RequestID INT NULL ,
          rundate DATETIME DEFAULT ( GETDATE() )
        ) 


    INSERT  INTO #sp_who2
            ( SPID ,
              Status ,
              Login ,
              HostName ,
              BlkBy ,
              DBName ,
              Command ,
              CPUTime ,
              DiskIO ,
              LastBatch ,
              ProgramName ,
              SPID2 ,
              RequestID
            )
            EXEC sp_who2


            SELECT  who.SPID ,
                    who.login ,
                    who.HostName ,
                    who.DBName ,
                    DATEDIFF(SECOND, LT.TranBeginTime, GETDATE()) AS elapsedseconds ,
                    who.ProgramName ,
                    'Transaction' AS notetype ,
                    LT.ttext
            FROM    #sp_who2 who
                    INNER JOIN @LT LT ON LT.spid = who.SPID

Does it return any results?

How to shrink simple recovery log file in SQL Server

1 Answers1