Search Engine Q&A #18: What’s the current uptime of SQL Server?

Here's a quickie just before we head off to SQL Connections in Orlando.

On one of the internal MS forums was the question – how can I tell through T-SQL the last time SQL Server restarted (i.e. the current 'uptime')? The answer relies on the fact that all the background tasks that start when SQL Server starts must record a 'login time'.

You can get this from:

SELECT [login_time] FROM sysprocesses WHERE spid = 1;
GO

Or more simply:

SELECT MIN ([login_time]) FROM sysprocesses;
GO

Pretty neat trick!

As with the last few conferences, I'll try to blog every day during SQL Connections under the Conference Questions Pot-Pourri category.

Hope to see a bunch of you there!

PS Some people have suggested that checking the creation date of tempdb will also do the trick. That's not a *guaranteed* method as PSS could have used T3609 to recover tempdb instead of recreating it (if they're troubleshooting some tempdb issue). In that case the creation date of tempdb will *not* be the time the server started. Checking the time in sysprocesses is the only infallible method.

9 thoughts on “Search Engine Q&A #18: What’s the current uptime of SQL Server?

  1. Hello,

    Could I come in with a not related question? Recently I’ve been took part in a discussion regarding the user defined data types usage and noticed that there were a great mismatch in understanding of how SQL Server resolves them. Initially the question is SQL Server locks database schema resolving user types or not and is there any overhead using them in comparison to system data types in terms of performance and schema locks?

    Thanks!
    Leonid

  2. Hi Uri – that usually works too but isn’t guaranteed to be accurate. See my update to the bottom of the post. Thanks!

  3. Hi Leonid,

    I’m afraid I don’t know the answer to that – not my area of expertise or internals knowledge. Kimberly says ‘Probably but nothing I’ve ever heard of. Depends on how complicated the rules and defaults are’

    Hope that helps.

  4. BTW, this also only works if you have admin access to sysprocesses. Running as a normal non-elevated user (Vista SP1) and connecting to an idle 2005 (SP2 Express), sysproccesses only shows 1 process (spid 52, SSMS). The minimum login_time for that process is when the machine last woke up, not when the service started (my machine hibernates occasionally). Could also add "AND (spid <= 20)" to limit it to the "boot" processes.

  5. SELECT sqlserver_start_time
    FROM sys.dm_os_sys_info;
    From Kalen Delaney’s book ,also I think it will work only for 2008 edition onwards

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.