Transactional log is full.. space recovery lead to BLocking

  • one of my database's tran log got full on dev server . (recovery doesnt matter)

    here are the details

    total drive : 180 gb

    log file on same drive : 28gb

    data file on same drive : 68 gb

    free space : 84gb

    what i did :

    1) try to take log backup with

    backup log testdb to disk = 'nul' .. ran to 30 minutes . i stopped it when i see it was getting blocked by some internal prcesses (spid < 50)

    2) then tried

    Dbcc shrinkdatabase ('testdb') .. same blocking happened .. i stopped it

    3) tried to take database offline (so that log file can be removed by attach .detach and remove log file)

    btu again query got blocked by internale porceess .

    Any help ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • If recovery dont matter then set recovery to simple and leave it in simple.

    Once in simple issue a checkpoint in the DB, then shrink the file.

    Taking offline detach attach can cause you a lot more problems as it may not always be possible to recreate the log file.

  • Wow, two ways to mismanage the log. If you don't care about recovering to a point in time then put the DB in simple recovery model and leave it alone.

    Deleting the log is one of those things that should never be done. The log is not an optional file (regardless of recovery model), deleting it can leave the database transactionally inconsistent, structurally inconsistent or completely unusable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i missed to post here but i also tried to change the recovery model to simple but again this also cause blocking (seen by sp_who2) so i stopped in after ~20 min.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Did you investigate what the SPIDs where doing which blocked you?

    Did you try setting the DB into single_user mode?

  • s it's a DEV box, try running this then retrying

    DECLARE @DBName NVARCHAR(200);

    SET @DBName = '<Enter your db name here>';

    DECLARE @spid INT;

    DECLARE IHateCursors CURSOR FOR

    SELECT

    spid

    FROM

    master.dbo.sysprocesses

    WHERE

    dbid = DB_ID(@DBName) and spid > 50

    UNION

    SELECT DISTINCT

    request_session_id

    FROM

    sys.dm_tran_locks

    WHERE

    resource_database_id = DB_ID(@DBName) AND

    request_session_id > 50;

    OPEN IHateCursors;

    FETCH NEXT FROM IHateCursors

    INTO @spid;

    WHILE @@fetch_status = 0

    BEGIN

    EXEC ('kill ' + @spid);

    FETCH NEXT FROM IHateCursors INTO @spid;

    END

    CLOSE IHateCursors;

    DEALLOCATE IHateCursors;

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • andrew gothard (1/30/2013)


    s it's a DEV box, try running this then retrying

    DECLARE @DBName NVARCHAR(200);

    SET @DBName = '<Enter your db name here>';

    DECLARE @spid INT;

    DECLARE IHateCursors CURSOR FOR

    SELECT

    spid

    FROM

    master.dbo.sysprocesses

    WHERE

    dbid = DB_ID(@DBName) and spid > 50

    UNION

    SELECT DISTINCT

    request_session_id

    FROM

    sys.dm_tran_locks

    WHERE

    resource_database_id = DB_ID(@DBName) AND

    request_session_id > 50;

    OPEN IHateCursors;

    FETCH NEXT FROM IHateCursors

    INTO @spid;

    WHILE @@fetch_status = 0

    BEGIN

    EXEC ('kill ' + @spid);

    FETCH NEXT FROM IHateCursors INTO @spid;

    END

    CLOSE IHateCursors;

    DEALLOCATE IHateCursors;

    Or to do it in 1 line

    ALTER DATABSE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    Will kill all SPIDS, roll them back and if yur in that database context, leave you with access.

  • anthony.green (1/30/2013)


    andrew gothard (1/30/2013)


    s it's a DEV box, try running this then retrying

    DECLARE @DBName NVARCHAR(200);

    SET @DBName = '<Enter your db name here>';

    DECLARE @spid INT;

    DECLARE IHateCursors CURSOR FOR

    SELECT

    spid

    FROM

    master.dbo.sysprocesses

    WHERE

    dbid = DB_ID(@DBName) and spid > 50

    UNION

    SELECT DISTINCT

    request_session_id

    FROM

    sys.dm_tran_locks

    WHERE

    resource_database_id = DB_ID(@DBName) AND

    request_session_id > 50;

    OPEN IHateCursors;

    FETCH NEXT FROM IHateCursors

    INTO @spid;

    WHILE @@fetch_status = 0

    BEGIN

    EXEC ('kill ' + @spid);

    FETCH NEXT FROM IHateCursors INTO @spid;

    END

    CLOSE IHateCursors;

    DEALLOCATE IHateCursors;

    Or to do it in 1 line

    ALTER DATABSE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    Will kill all SPIDS, roll them back and if yur in that database context, leave you with access.

    Good point.

    And with no cursor

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • If it's a system process (which are not necessarily under session id 50), then wait or restart SQL. Probably something like the checkpoint that can just be left to finish.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • anthony.green (1/30/2013)


    Did you investigate what the SPIDs where doing which blocked you?

    THe were some internal processes spid < 50

    anthony.green (1/30/2013)


    Did you try setting the DB into single_user mode?

    YEs i tried that too but again that got blocked

    but i was not sure why everything got blocked by internal processes.

    Should i have been waited for sometime ? (for commmand which i executed to change the db to single user mode) ?

    can a alter database statement take around 15 minutes to execute ? (though i stopped it after 15 minutes)

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • stop the sql service cleanly, start it up the engine in single user mode, then try shrinking.

  • Bhuvnesh (1/30/2013)


    but i was not sure why everything got blocked by internal processes.

    Should i have been waited for sometime ? (for commmand which i executed to change the db to single user mode) ?

    can a alter database statement take around 15 minutes to execute ? (though i stopped it after 15 minutes)

    Did you see the command run by internal process (DBCC INPUTBUFFER) ? Or in case DBCC INPUTBUFFER was not providing any result atleast the Command column's value in sp_who2 result ?


    Sujeet Singh

  • i still have one confusion,

    Why i was getting "blocking by internal processes" for all above mentioned commands , is it normal (though i havent experienced in past) behaviuor ?

    i know the best apporach on that moments was to wait for command to get completed.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Divine Flame (1/31/2013)


    Did you see the command run by internal process (DBCC INPUTBUFFER) ? Or in case DBCC INPUTBUFFER was not providing any result atleast the Command column's value in sp_who2 result ?

    no i missed that . actually blocking by internal processes puzzled me there (i kknow patience was the key there 🙂 )

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Depends what the commands where going.

    You should really debug what they are going using the sql text dmv.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply