未加星标

Finding and Cleaning Up Old SQL Server Job Log Files

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二04 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLPowerShellERPSQL ServerWindowsSURIM
分页:12
转载请注明
本文标题:Finding and Cleaning Up Old SQL Server Job Log Files
本站链接:http://www.codesec.net/view/522673.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(17)