Uptime history without Monitoring tool

  • 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!

  • 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

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

    1. Define your measurements and queries to get the value.
    2. Create a generic table structure to hold your measurements (such as [measure], [value], [timestamp])
    3. Define the measurement frequency and create job to capture the measurement.
    4. 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
  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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