Wednesday, April 16, 2008
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 (see here for all out pre/post cons and sessions).

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.
General | Search Engine Q and A
Wednesday, April 16, 2008 5:27:19 PM (Pacific Standard Time, UTC-08:00)  #    Comments [7]  | 
Wednesday, April 16, 2008 10:41:43 PM (Pacific Standard Time, UTC-08:00)
Or just check crdate column for tempdb

select crdate from master..sysdatabases
where dbid=2

Uri Dimant
Thursday, April 17, 2008 2:52:17 AM (Pacific Standard Time, UTC-08:00)
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
Leonid Shirmanov
Thursday, April 17, 2008 3:06:23 AM (Pacific Standard Time, UTC-08:00)
Hi Uri - that usually works too but isn't guaranteed to be accurate. See my update to the bottom of the post. Thanks!
Paul Randal
Thursday, April 17, 2008 9:27:33 AM (Pacific Standard Time, UTC-08:00)
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.
Paul Randal
Friday, April 18, 2008 6:02:05 AM (Pacific Standard Time, UTC-08:00)
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.
John Bates
Monday, April 21, 2008 8:46:58 PM (Pacific Standard Time, UTC-08:00)
Thanks Paul. You are right , I completely forgot about T3609.
Uri Dimant
Tuesday, April 22, 2008 10:41:23 AM (Pacific Standard Time, UTC-08:00)
How about

exec master..xp_readerrorlog

Tony Zessin
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

Enter the code shown (prevents robots):
Live Comment Preview

Theme design by Jelle Druyts

Pick a theme: