Database is not accessible as transaction log file growing to full

Question

On one of our current prod servers, we have one of the data drives going out of space.[Somehow we have created 2 GB space for now]

Now we are planning to move one of the database in different drive which is almost 20 GB. But this database is not accessible state. I was thinking of backup/restore way to move, but it wont as it shows in unavailable state.

When I checked the SQL logs, it says transactional log of this database got full. So I checked its state using log_reuse_wait_desc which says "ACTIVE TRANSACTION".

How can I get the database available and ready for move so that I can move via backup/restore?

Additional Info: Database status shows "SUSPECT" Please suggest.

Answer 1

First things first, if you need to kill the active user(s) immediately and make sure no more connections are made, you'll want to put the DB in single user mode.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Second you'll need to address the SUSPECT mode and hope it's an easy to fix file access issue. Try these suggestions to fix your SUSPECT mode

Once that is fixed, do what you need to do with the database - CheckDB, backup, etc. By keeping it in single user mode other users, including applications and any other user but you, will not be able to access the database.

To put it back in multi user mode use

ALTER DATABASE MyDatabase SET MULTI_USER

EDIT - I should say this does not resolve why you have an ever growing transaction log. If it is of concern, you should try to resolve that prior to killing an active transaction with sp_whoisactive, monitoring tools, etc. But don't let the log fill the drive or it will kill the transaction anyway and roll it back.

Answer 2

Download and install WhoIsActive - it will tell you which SPID's have open transactions and the amount of time they have been open. You'll need to cancel the SPID that is the 'active transaction' and wait for it to roll back. Then take a transaction log backup and you should be able to access the database and proceed with your backup and restore.

A sledgehammer approach I have used in the past would be to blow away all active connections to the database using this SP that I've been using for years. I create it in a UTILITY database on all of our servers

CREATE PROCEDURE [dbo].[KillActiveConnectionsForDatabase] 
    @DatabaseName varchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SPId int
    DECLARE @SQL nvarchar(100)

    DECLARE my_cursor CURSOR FAST_FORWARD FOR
    SELECT SPId FROM MASTER..SysProcesses
    WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

    OPEN my_cursor

    FETCH NEXT FROM my_cursor INTO @SPId

    WHILE @@FETCH_STATUS = 0
    BEGIN
     SET @SQL = 'KILL ' + CAST(@SPId as nvarchar(10))
     print @SQL
     EXEC sp_executeSQL @SQL

     FETCH NEXT FROM my_cursor INTO @SPId
    END

    CLOSE my_cursor
    DEALLOCATE my_cursor 

END