Uptime history without Monitoring tool

Question

Uptime history without Monitoring tool

Admingod

SSCertifiable

Points: 5997
More actions
April 29, 2020 at 11:29 pm

#3747517

I could able to view the last restart of sql server using dm_os_sys_info. But checking is there a way to see the history of uptime of sql server something like graph?My understanding is that DMV loses the information after the restart of the server. Also looking to see the CPU resources likes historical information . There are some jobs failed want to see if this db is the cause of not available? Any advise? Thanks in Advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16041 More actions · Answer 1

as you were advised on other threads you created here over the last 2 years you either use a third party tool to monitor and log this type of events or you have to create your own monitoring.

all the third party tools use the same DMV's that you can use for different purposes - and each tool also uses some specific SQL's to get particular details of the servers - all this can be done by you but there is no magic bullet and there is a lot of work involved to do it in-house.

things that any tool have in common are

using dmv's to get data
have a repository databases to store the retrieved data
have reporting capability over the stored data

All that you can do yourself (with a fair amount of work)

so as others have said previously get a third party tool - RedGate SQL Monitor is the one I recommend but there are others.

it will make your life a lot easier if you do

This reply was modified 4 years ago by frederico_fonseca.
This reply was modified 4 years ago by frederico_fonseca.

kcecil 47772 Grasshopper Points: 17 More actions · Answer 2

For quick and dirty monitoring when you can't afford something else you can just follow the advice to:

Define your measurements and queries to get the value.
Create a generic table structure to hold your measurements (such as [measure], [value], [timestamp])
Define the measurement frequency and create job to capture the measurement.
Use your favorite flavor of tool for creating the reports/graphs (Power BI, Excel, etc.)

Here is an example of polling for the up time in seconds that you could schedule to run every five minutes. You could do the same for other measures (like CPU).

INSERT INTO [dbo].[SQLMonMeasurement] ([Measure], [Value], [Timestamp])
SELECT 'sqlserver_uptime_seconds'
, DateDiff(ss, sqlserver_start_time, GETDATE())
, GETDATE()
FROM sys.dm_os_sys_info
GO

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

A very crude way of getting uptime history without any tooling (third-party or otherwise) is to query the errorlog. It'll only go back as far as the errorlogs do, and, if your event logs are large, won't perform very well. Apart from that, as others have suggested, either get a third-party tool, or roll your own by capturing and storing.

John

Jeff Moden SSC Guru Points: 1003904 More actions · Answer 4

Another way is to just periodically sample the Create_Date of TempDB from sys.Databases and do a DATEDIFF. That will actually let you know if the SQL Service was restarted whether or not the server itself was restarted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)