PowerShell Usage in DBA Work — Case Study 3/7

As a DBA, sometimes we may need to demonstrate to some stakeholders when a sql server instance was last rebooted, i.e, just to prove the sql instance weekly/monthly availability . I know there are quite a few blogs / posts online talking about how to do this check, such as this one:

http://gallery.technet.microsoft.com/scriptcenter/Script-to-check-when-was-5d0fe4e8

However, this t-sql solution needs to connect to each sql server instance to run the query.

Case Scenario:

How can I find out the last time each of the 150+ sql server instances was rebooted.

PowerShell solution:


'Server1', 'Server2' | % { $svr=$_; $p=gwmi -class win32_service -filter "started=$true and (name = 'mssqlserver' or name like 'mssql$%')" -computer $svr; `
$p | SELECT __Server, NAME, @{l='StartTime'; e={$_.ConvertToDatetime((gwmi -comp $svr -class win32_process -filter "Processid = $($_.ProcessID)").CreationDate)}} `
| ft –AutoSize

Advantage Analysis:

1. We only need to provide a list of servers (separated by comma, and each server can have multiple sql instances), and then we will get the results. This is very simple yet efficient. Also the result can be easily exported to text file or CSV file if needed.

2. We even do not need to care about the named instance names.

Other Thoughts:

With this PS solution, we can answer lots of other interesting questions, such as how many sql instances have been running for <X> hours, the average/max continuous availability hours of the sql instances etc.

Also just for fun, I googled online and it seems no one asked/talked about the question of when  SQL Server Agent service was restarted last time. Using the same PS solution, all I need to do is change –filter clause from:

-filter “started=$true and (name = ‘mssqlserver’ or name like ‘mssql$%’)”

to

-filter “started=$true and (name = ‘sqlserveragent’ or name like ‘sqlagent$%’)”

Bonus question: Do you know how to find when sql server agent service was last started using t-sql (assuming we do not check sql agent log)?

About these ads
This entry was posted in Administration, Uncategorized and tagged . Bookmark the permalink.

One Response to PowerShell Usage in DBA Work — Case Study 3/7

  1. jxhyao says:

    For my bonus question: here is one t-sql solution (tested in sql2005 +) :

    select max(agent_start_date) from msdb.dbo.syssessions

    Of course, this assumes the sql agent service is running.

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