Archive for the ‘Re-Generate LDF’ Tag

Best way to build LDF   Leave a comment

Generally, when the log file size become very huge and even after shrinking the same still we face file size issue in that case we take an action to regenerate the new LDF file for our database. There are different ways to achieve the same, but the best way to achieve it is as follow:

  1. use sp_helpdb yourdbname and note down the path where .mdf and .ldf files are stored.
  2. execute the sp_detach_dp yourdbname command to detach the database Syntax
    sp_detach_db [ @dbname = ] 'dbname' 
                 [ , [ @skipchecks = ] 'skipchecks' ]

    example:

    EXEC sp_detach_db 'youdbname', 'false'
  3. Move the original database files to some other location or rename them.
  4. Create database with same name using CREATE DATABASE yourdbname command.
  5. Stop SQL server and replace the .mdf file.
  6. Start SQL server.
  7. Database will be shown in pending recovery mode.
  8. Issue below list of commands to bring the database online

Use Master

Go

Alter Database yourdbname Set Emergency

Go

Alter Database yourdbname Set Single_User

Go

DBCC CheckDB(yourdbname, Repair_Allow_Data_Loss)

Go

Alter Database yourdbname Set Multi_User

Go

Alter Database yourdbname Set Online

Go

 

Note*: You will receive error messages but database will be online.

 

Advertisements

Posted March 13, 2018 by Izharuddin Shaikh in SQL

Tagged with

%d bloggers like this: