Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Finding and Cleaning Up Old SQL Server Job Log Files


By:   |   Read Comments   |   Related Tips: More > Maintenance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Due to SQL Server sprawl, you may have hundreds of servers that may be accumulating job logs that you are unaware of. You may have HUNDREDS of THOUSANDS of job log files floating about. It would take days to go thru hundreds of servers and their log directories to check on the accumulation.  So, let's automate this collection process.

Solution

With a list of the servers, and a handy dandy PowerShell script, you can get a report of how many files are in your default job log areas. You can then quickly tackle the problem areas.

If you manage only 10 or so SQL Servers, it may not be as big of an issue to check each server, but I manage 250-300 SQL Servers (there is a question as to which ones I'm responsible for), and one day I was checking an old log file on one of the servers and I noticed there were 50,000 old transaction log job logs lying about. Whoa! Are any other servers harboring a collections of ancient, not useful files?

The space savings are not big since most files are 1K or so in size. But what about the overall file system for Windows? Or worse yet, how much of this is getting logged on our Tivoli backup system? How many of my SQL Servers have been accumulating masses of log files? From what I could tell, all of these renegades are from backup or maintenance jobs that someone forgot to account for. Since a goodly number of my servers were vendor installed, I'm going to blame the vendor. Yeah that's it...it's the vendor's fault.

I have some PowerShell scripts that can loop thru my servers, so it wasn't a big deal to get started on a script to check how many log files are about. I needed to find out the location of where log files went and then to see how many files are in them. After some searching online, I found the query that returns the location and name of the log file. It is:

SELECT SERVERPROPERTY('ErrorLogFileName') 

With this, all I needed was a PowerShell command to read a directory and get the number of files in it. Since I'm not exactly a PS guru this one took a bit of time. Here is the basic PowerShell script that gives you the number of files in a directory.

Get-ChildItem -Path "c:\somepath...." | Measure-Object | %{$_.Count} 

With these two scripts let's put it all together in the final program.

Note that this script is connecting to a SQL Server (YOURSERVER) with a database called is_dba and a table called is_sql_servers. It reads the table and for each row it connects to a server and accesses its file system. Here is code to create the table and note that the field contains the server name only when it's a default installation and server\instance when you're using a named instance. A caveat: this script ASSUMES that you can access the hidden shares that windows provides for volumes. For example, it will use C$ and D$ as the share when it creates the directory string. I'm a local admin for all the boxes I accessed and had no trouble using the hidden volume shares. It's my understanding that you can disable them using some sort of group policy. If they're disabled, I'm pretty sure this script won't work.

--------------------------------SQL Script to create a simple server table.  Each record will be in the form: SERVERNAME or SERVERNAME\INSTANCE for name instances
CREATE TABLE is_sql_servers (
  is_sqlserver VARCHAR(200) NOT NULL  )

--------------------------------PowerShell script to read above table and produce simple report in C:\Junk\ERROR_Log_Location.txt.  You have my permission to change it. 

$SQLServer = "YOURSERVER" #use Server\Instance for named SQL instances! 
$SQLDBName = "is_dba"

$SqlQuery = "select is_sqlserver from is_sql_servers  "

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet, "Server_Names") | Out-Null

$SqlConnection.Close() 

#### Read thru the contents of the is_sql_servers table

foreach ($row in $DataSet.Tables["Server_Names"].rows)
{
 #######next line determines whether its just a server name or server\instance.
      $instance = $row.is_sqlserver
      $d = $instance.contains("\")

   if ($d -EQ "TRUE")
{
$LocalPath = c:\Temp\scripts\Test.ps1
[array]$PathArray = $instance -Split [regex]::Escape(\)
  write-host $Patharray[0]
  write-host $Patharray[1]
  $tserver = $Patharray[0].trim()

  write-host "is true"
}
   else
 { write-host "is false"
   $tserver = $instance.trim()

}
Write-Host $instance

      #######################################################
     #This script gets SQL Server database information using PowerShell
     [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
     # Create an SMO connection to the instance
 $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance


#####the minus 9 in the next line is to omit "\ERRORLOG" which is the filename

$STR="select  '\' + LEFT( replace(Cast( SERVERPROPERTY('ErrorLogFileName') as char(200)), ':\', '$\') ,  LEN(RTRIM( CAST(SERVERPROPERTY('ErrorLogFileName') AS CHAR(200) )  ) )-9 ) AS TTTT"

$Result = Invoke-Sqlcmd -ServerInstance $s -Database "MASTER" -Query $STR 

####write-host $STR
####write-host $Result

foreach($item in $Result){

###$Line_out = $instance + " " + $item.TTTT
$Line_out = $tserver + " " + $item.TTTT
write-output $Line_out | out-file -append "C:\Junk\ERROR_Log_Location.txt" 

  $DIRECT= "FileSystem::" +"\\" + $tserver + $item.TTTT
#####Write-Host $DIRECT
  Get-ChildItem -Path $DIRECT | Measure-Object | %{$_.Count} | Out-File -append -filePath "C:\Junk\ERROR_Log_Location.txt"
write-output "-----------------------------------------------" | out-file -append "C:\Junk\ERROR_Log_Location.txt"
  
}

}

The script will stick the results in a text file here: "C:\Junk\ERROR_Log_Location.txt". Change this to match where you want the report to be created.

Below is what this report will generate. Of course the server's names were changed to protect the innocent. This shows the server, the folder path and the number of files in this folder.

server1 \\server1\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
1486
-----------------------------------------------
server2 \\server2\F$\MSSQL10_50.MSSQLSERVER\MSSQL\Log
6204
-----------------------------------------------
server3  \\server3\F$\MSSQL12.MSSQLSERVER\MSSQL\Log
55
-----------------------------------------------
server4 \\server4\C$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
875
-----------------------------------------------
server5 \\server5\D$\Program Files\MSSQL10_50.MSSQLSERVER\MSSQL\Log                                            
11906
-----------------------------------------------
server6 \\server6\D$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log            
7012
-----------------------------------------------
server7 \\server7\C$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log           
12106
-----------------------------------------------
server8 \\server8\C$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log
2790
-----------------------------------------------
server9 \\server9\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
1728
-----------------------------------------------
server10 \\server10\E$\MSSQL.1\MSSQL\LOG                                                           
36521
-----------------------------------------------
server11 \\server11\F$\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log
1109
-----------------------------------------------
server12 \\server12\F$\Data\MSSQL10_50.MSSQLSERVER\MSSQL\Log                                           
13615
-----------------------------------------------
...
...
...(hundreds more)


You can probably cut and paste the above server UNC paths to a run line to get to where you need. From there you can delete at leisure, but be sure to NOT DELETE the active SQL Server ERRORLOG file. That's a no-no. This report is also a good pointer to where the default SQL locations are for your servers.

Next Steps
  • Add some filtering and only print the problem directories
  • SQL Server ERROR Logs are here, make some changes to the code and print them out with their sizes.
  • Change the GET-CHILDITEM and delete the files based on the date and .txt file extension (BE CAREFUL).
  • Here are some other useful tips:


Last Update:


signup button

next tip button



About the author
MSSQLTips author Bernard Black Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools