We will start by using every DBA’s favorite PowerShell module… dbatools
Basics
For this example, I will introduce you to the Get-DbaUpTime function in the dbatools PowerShell module. This function has all the information we are attempting to gather.
|
1 |
Get-DbaUptime -SqlInstance DemoServer |
This example displays when Windows and SQL Server were last started. These are critical pieces of information, depending on what you are troubleshooting.
For a different view of the output, we can pipe the command to
Out-Gridview.
|
1 |
Get-DbaUptime -SqlInstance DemoServer | Out-GridView |
Now you have a graphical interface to filter records to find what you are looking for as your investigation grows.
Enhanced
What if your Infrastructure team just finished their monthly Windows Patching of all development servers? Do you know if the SQL Servers have been rebooted? Do you want to RDP each of them to see when the last reboot was? No, you sure do not?
Combining SQL Server Central Management Server and the
Get-DbaUpTime function work well together.
|
1 2 |
$servers = Get-DbaRegisteredServer -SqlInstance CMS -Group "SDLC\Dev" Get-DbaUptime -SqlInstance $servers | Out-GridView |
From the output, you can see that one server has been up four days less than the rest of the servers. So if you had maintenance six days ago, that one server would stand out as something that might need investigation.
You get the idea of where you can go with this command and how it can be very beneficial to monitor your SQL Server environment.
Extra
Lastly, let me show you how to take this information to another level and save it to a report format that can be sent to a website or e-mailed to a list of managers.
I ran across this freely distributed module that allows you to send your PowerShell output to an HTML file. As part of the PSWriteHTML module, you have the Out-HtmlView, an HTML replacement for
Out-GridView.
|
1 |
Get-DbaUptime -SqlInstance $servers | Out-GridHtml -Title "Up Time Report" -FilePath C:\Temp\DbaUpTime.html |
Look at that beautiful HTML output. Not only do you get a clean HTML report, but it is also sortable, searchable, and exportable. Attach this to an e-mail or a service ticket, and you have your validation of SQL Server uptime.
Post it to a website, and you automatically have a web page that shows SQL Server Uptime.
Enjoy and see you on the next blog.
Since the tempdb will be created every time your SQL server service will be started, you could also do a simple
SELECT d.create_date FROM sys.databases AS d WHERE d.name = ‘tempdb’
the know, when it was started the last time…
Of course this returns only a datetime and you have to do all the fancy formating, DateDiff()-stuff of the PowerShell by yourself (if you really need it). And of course this works only for a single instance, except you use the Registered Server Feature in SSMS (allows you to run queries on multiple instances)