Sql-server – Database is not accessible as transaction log file growing to full

sql serversql-server-2008-r2

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.

Best Answer

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.

Related Solutions

Sql-server – restore could not start database

Your restore is failing because there is no E:\ or F:\ on your machine.

Perform a RESTORE FILELISTONLY FROM DISK = 'path\file'; - this will tell you the logical names of the data and log files.
Construct a RESTORE DATABASE command based on this. For example (assuming the data files are named ECP_Data,ECP_Data_2, etc.):
```
RESTORE DATABASE ECO FROM DISK = 'path\file'
  WITH REPLACE, RECOVERY,
  MOVE 'ECP_Data'   TO 'C:\...path to MSSQL\Data\ECP_Data.mdf',
  MOVE 'ECP_Data_2' TO 'C:\...path to MSSQL\Data\ECP_Data2.ndf',
  MOVE 'ECP_Data_3' TO 'C:\...path to MSSQL\Data\ECP_Data3.ndf',
  MOVE 'ECP_Data_4' TO 'C:\...path to MSSQL\Data\ECP_Data4.ndf',
  MOVE 'ECP_Log'    TO 'C:\...path to MSSQL\Data\ECP_Log.ldf';
```
In addition to correcting the logical file names, replace C:\...path to MSSQL\Data\ with whatever path(s) you do want to use to store the data/log files. You don't need an individual folder per file, but you may still want to separate data and log files onto different disks. Note that it still might fail if whatever drive(s) you choose lack sufficient space to perform the restore.

Sql-server – Shrinking a SQL Server Transaction Log

The transaction log for that database contains all transactions since the last transaction log backup, or the last time it was switched from simple recovery mode. Execute the following to get the definitive answer as to why SQL Server can not truncate the log and subsequently why the log is growing.

SELECT  d.Name
        ,d.log_reuse_wait_desc
FROM    sys.databases d
ORDER BY
        d.name

If you want point in time recovery then leave the DB in the full recovery model and take frequent log backups. Each log backup will contain all transactions since the last log backup. The log backup process is also responsible for clearing the log and marking the space for reuse i.e. the next transaction made in the DB will be written to the start of the truncated log in a circular fashion. This backup and reuse of the log is what prevents the log file from growing.

If you are not interested in point in time recovery and want to simplify the administration of the database. Then set the database to the simple recovery model and do not take t-log backups. SQL Server will automatically truncate the transaction log after each transaction is committed. Meaning that once the transaction has been committed to the log the record is overwritten by the next transaction etc.

Either way, once you've made one of these two decisions you can then shrink the log file down to a more reasonable size. Note ideally you want to make it large enough so it doesn't grow but not so large that you'll need to shrink it again. Also note that you can not shrink the active part of the log.

Download and deploy https://ola.hallengren.com/ database administration solution to cover backups, index fragmentation, statistics and CHECKDB.

You might also find the 'disk usage' report returned by right clicking the DB in Object Explorer > Reports > Standard reports > 'disk usage' useful for returning the free space in the t-log.

I also recommend that you Google why it's so important to keep the log chain intact from a DR point of view, and how switching from full to simple breaks the chain leaving you exposed to data loss.

Best Answer

Related Solutions

Sql-server – restore could not start database

Sql-server – Shrinking a SQL Server Transaction Log

Related Question