Sql-server – Change database file path with SQL not running

recoverysql servertransaction-log

I've an urgent problem.

Due to some heavy delete queries, my database transaction log has grown and it has filled the disk (126GB). I've found out that backup plan wasn't working during last month.

Now, the database is recovering.

Recovery of database 'ABCD' (5) is 1% complete (approximately 45395 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

I've added a new disk to the server and I would like to move the .ldf file and then restarting the recovery of database.

Is it possible?

Best Answer

Do not restart sql server service or try to offline the db

What you can do is ADD a secondary log file using

ALTER DATABASE [dbname] ADD LOG FILE ( NAME = N'dbname_log2', FILENAME = N'M:\dbname_log2.ldf' , SIZE = 20480KB , FILEGROWTH = 1024KB )

Check log file usage :

DBCC SQLPERF(LOGSPACE);

This way the db can be online and you can start taking log backups (or if point in time restore is not required, then change to simple recovery) to truncate the log file.

Related Question