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
  

 

• 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.

Powershell Windows Mount Point Free Space

I needed a quick script to monitor the free space on a cluster server using Windows Mount points. Here is the script:

Get-WmiObject win32_volume | 
Where-object {$_.DriveLetter -eq $null} | 
Select Name,Capacity,FreeSpace, 
@{Name="PctFree";Expression={ "{0:N2}" -f (($_.FreeSpace / $_.Capacity)*100)}}

Output