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;
Or more simply:
SELECT MIN ([login_time]) FROM sysprocesses;
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.