Remove Log file from SQL Server database

Background Info
I was executing a large INSERT statement the other day and started to run out of disk space. I thought I had properly calculated the space needed but it turns out my calculations were wrong. Since I was 46 minutes into the INSERT I did not want to run out of space. A Log backup was running but it would not complete before I ran out of space. Therefore I decided to kill the backup and add a log file on another drive as a temp solution so the INSERT would work. The insert took 1 hour and 11 minutes and completed. Now I had a log file I didn’t need. Here are the steps I used to delete the log file.

Steps

  • Check to see how much log file is being used.
    DBCC SQLPERF(logspace)
    
  • Check the status of the log files for my target database
    DBCC LOGINFO(DBName)
    
  • If status is 0 then try to run the ALTER DATABASE REMOVE FILE command
    ALTER DATABASE DBName REMOVE FILE LogFileName
    
  • Received an error that the log file was not empty
    Clipboard01

 

  • Executed a LOG Backup on that database
    BACKUP LOG DbName TO DISK="C:\YourBackupPath\File.bak"
    
  • Tried the Remove File command again
    ALTER DATABASE DBName REMOVE FILE LogFileName
    
  • Log file was removed. Executed a SELECT from sys.database_files
    SELECT * FROM YourDB.sys.database_files
    
  • Log file is showing OFFLINE
  • Executed another Log Backup
    BACKUP LOG DbName TO DISK="C:\YourBackupPath\File.bak"
    
  • Offline file was removed.
    SELECT * FROM YourDB.sys.database_files
    

Good to go.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s