I have always wanted to learn a bit of scripting and I was always searching for a good reason to learn something new. As I said, this is my learning and I am open to learning some scripting tricks from you too. So do let me know how the below script can be optimized in your comments. Now, what was my reason to learn this script?
Someone asked me, “How can I find out when SQL Server was started?” There are multiple ways to find out, but I took the simple route and said – “Why don’t you check the SQL Server Error logs?” I know, the SQL Server error logs can get recycled and this data maynot be available. But in most cases, this gives us some indication.
So the other question was how can we script this requirement? So the below script is written to read SQL Server ErrorLog, find a specific text and report the number of times the string was found. We also show the time when the last/recent occurrence was found.
param
(
$serverinstance = ".", #Change this to your instance name
$stringToSearchFor = "Starting up database ''master''"
)
# Load Assemblies
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
# Server object
$server = New-Object -TypeName "Microsoft.SqlServer.Management.Smo.Server" -ArgumentList $serverinstance
$searchCriteria = "Text like '%{0}%'" -f $stringToSearchFor
$SQLErrorLogs = $Server.EnumErrorLogs()
$SearchEntry = @()
ForEach ($SQLErrorLog in $SQLErrorLogs)
{
$SearchResult = $server.ReadErrorLog($SQLErrorLog.ArchiveNo).select($searchCriteria) | Select-Object -Property LogDate, Text
$SearchEntry = $SearchEntry + $searchResult
}
$MeasureOccurences = $SearchEntry | Measure-Object -Property LogDate -Minimum -Maximum
$SQLSearchInfo = New-Object psobject -Property @{
SearchText = $stringToSearchFor
Occurances = $MeasureOccurences.Count
MinDateOccurred = $MeasureOccurences.Minimum
MaxDateOccurred = $MeasureOccurences.Maximum
}
Write-Output $SQLSearchInfo | FT -AutoSize Do let me know if you ever used such scripting capability to search your errorlogs? You can change the search string and the default instance to your environment.
I wrote this generically because I was planning to use this to find error messages inside ErrorLogs like “I/O requests taking longer than 15 seconds to complete”. The script can be modified to your needs. Do share your scripts too over the comments so that others can benefit from the sharing.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
looking at the error log is not bullet-proff as you can periodically recycle the error log.
A much more efficient and bullet-proof version is to take the creation date of tempdb is this db is created each time the SQL Server is started.
You are so right Daniel and I mention the same. This method can be used to search other errors that might come into errorlogs which can be of great interest. So this is just my exploration to scripting and thought was worth a share.
Also we can found this info by the T-SQL like this :
SELECT create_date FROM sys.databases WHERE name = ‘tempdb’
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
sqlserver_start_time
SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1
All your method would tell the current SQL startup. The script which I provided will read ALL ERRORLOG(s) and tell you how many time the string has appeared.
Hi Pinal,
But I am not able to execute the same in powershell as well as SQL Server.
Thank you for bringing to my notice. I fixed it.