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)?