3

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?

1
  • 2
    Which DBMS are you using? (SQL is a query language, not a database product)
    – user1822
    Feb 1, 2018 at 16:09

2 Answers 2

2

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.

2
  • Is it possible to add a secondary log file while the db is still in recovery?
    – BradC
    Feb 1, 2018 at 22:52
  • You have to be in master db to be able to add additional log file.
    – Kin Shah
    Feb 1, 2018 at 23:12
1

Assuming MS-SQL server, the answer is yes:

  1. Stop SQL services
  2. Move the LDF file
  3. Start SQL services. SQL will bring the other databases online, but will complain about ABCD since it can't find the files.
  4. *Do an ALTER DATABASE ABCD MODIFY FILE (name='abcd_log',filename='N:\abcd_log.ldf')
  5. Stop and restart SQL

*I'm mostly sure this step will work in this scenario, it definitely works when you manually set a database OFFLINE. If it doesn't work try doing a ALTER DATABASE ABCD SET OFFLINE first, then SET ONLINE afterwards. Someone else will surely correct me if I'm wrong.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.