Friday, May 15, 2009

How to find when SQL Server was last started?

When ever SQL Server is (re)started ‘TempDB’ gets recreated. So the below query should give us the time of when ‘SQL Server’ was last started.

Select create_date from sys.databases where [name] = 'tempdb'

When a SQL Server starts all the tasks which it initiates makes an entry in sysprocesses table with its ‘login time’. That said, all we need to do is find the least login_time which has got recorded in that table!

1. Select min(login_time) from master.dbo.sysprocesses
OR
2. Select login_time from master.dbo.sysprocesses where spid = 1
OR
3. Select login_time from sys.dm_exec_sessions where session_id = 1

Technorati Tags:

3 comments:

rakesh khan said...

Thank you for your nice blog. Everything is OK but,
Pls hidden your extreme tracker and Neocounter from ur blog becoz it take too much time to load.Your good deed will hide for this counter.Also the visitors does not choose that they are tracked. I hope u will not mind for this tip.You can email me rakeshkhan20@gmail.com for any kind of reponse.

Sincerely Yours Supporter's

Madhivanan said...

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/06/09/when-was-my-server-restarted.aspx

Vadivel said...

Checking the creation date of TEMPDB will also do the trick. But that's not a guaranteed method.

Bcoz the PSS while troubleshooting some tempdb issues could have used T3609 to recover tempdb instead of recreating it.

So the best method and the only method to trust 100% is to check the time in sysprocesses as I have shown in my blog post.