Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

How to tell when the SQL Server service was started

Ever wondered if it is possible to programmatically determine when the SQL Server service for an instance was last started? 

It would be nice to have a built-in function that explicitly reports the time of the last service start, but one doesn't exist.

But we can still estimate the time of the service start to within a few seconds, which should be good enough for anything less than the exact calculations required for most strict Service Level Agreement (SLA).

Since we know that tempdb is recreated each time the SQL Server service starts, we can look to the creation date of that system database to provide us the answer.

The following query returns the datetime that the tempdb database was created, allowing us to estimate to within a few seconds the date and time that the service was started.

SELECT         
crdate AS Datetime_Instance_Started
FROM
sysdatabases
WHERE
name = 'tempdb'

If you're running SQL Server 2005, you can also use a Dynamic Management View (DMV) to return the number of milliseconds that have elapsed since the the service was started. The following query illustrates this technique.

USE master
GO

SELECT
TOP 1
sample_ms AS Millisecond_Since_Start
FROM
sys.dm_io_virtual_file_stats(DB_ID(),NULL)

Of course you can convert the milliseconds column to seconds by dividing by 1000, or to minutes by dividing by 60,000.  Or you can get really creative and convert to days, hours, minutes, and milliseconds.  I'll leave that to you or to another post.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Wednesday, October 03, 2007 6:21 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: How to tell when the SQL Server service was started

slick!
10/3/2007 12:18 PM | Mladen
Gravatar

# re: How to tell when the SQL Server service was started

An even easier why is to query the sys.dm_exec_sessions where session_id = 1:

select login_time from sys.dm_exec_sessions where session_id = 1
10/9/2007 1:47 AM | Davide Mauri
Gravatar

# re: How to tell when the SQL Server service was started

Querying the sysprocesses is the fool-proof method for finding when SQL Server was last started. Check out http://vadivel.blogspot.com/2009/05/how-to-find-when-sql-server-was-last.html
5/14/2009 9:32 PM | Vadivel
Gravatar

# re: How to tell when the SQL Server service was started

this information is useful for me,thanks
9/12/2010 2:02 PM | canada goose outlet
Gravatar

# re: How to tell when the SQL Server service was started

The following query returns the datetime that the tempdb database was created, allowing us to estimate to within a few seconds the date and time that the service was started.
10/15/2010 12:46 AM | columbia jackets
Gravatar

# re: How to tell when the SQL Server service was started

Since we know that tempdb is recreated each time the SQL Server service starts, we can look to the creation date of that system database to provide us the answer.

snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:39 AM | furry boots
Gravatar

# re: How to tell when the SQL Server service was started

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under simple video editing function embedded, support TRIM, CUT, CROP, and Join video fil
10/26/2010 1:39 AM | hanly
Gravatar

# vuitton mahina

ethod for finding when SQL Server was last started.
10/29/2010 3:41 AM | vuitton mahina
Gravatar

# re: How to tell when the SQL Server service was started

I'd like to read the articles in this website.May be you can go to louis vuitton,there will be some new and cheap louis vuitton products for choosen.You know louis vuitton didn't have the outlet store himself,but we provide the discount louis vuitton handbags,shoes,wallets,belts for Factory Direct price.So,enjoy yourself on our online shop.
12/8/2010 8:18 PM | louis vuitton
Gravatar

# re: How to tell when the SQL Server service was started

We must remember that viagra take the juice before cialis under identical conditions viagra so you can keep each other
1/18/2011 11:32 AM | Boss
Gravatar

# Great!


I appreciate the project that you have been achieved on the time.
I think this idea is better for the future also.
2/8/2011 9:59 AM | billiga flygstolar
Gravatar

# re: How to tell when the SQL Server service was started

That question always crossed my mind. Excellent tip you gave to combining SQL, my database was perfectly clear and efficient.
I convert my column to days, hours, minutes as you said and worked. Great tips!
3/9/2011 2:24 PM | called reverse lookup
Gravatar

# mac makeup

Since we know that tempdb is recreated mac concealer
each time the SQL mac eyeshadow
Server service starts, mac eye shadow
we can look to the creation date of mac makeup brushes
that system database to provide us the answer.
4/13/2011 8:10 AM | dear
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET