Service start time

Question

Service start time

Gail Shaw

SSC Guru

Points: 1004504
More actions
December 18, 2006 at 6:31 am

#175295

Is there any way to tell from T-SQL when the sql server service was last started?
If not from T-SQL, then is there way to do it in a CLR function?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Gail

I don't know whether this works in SQL Server 2005, but in 2000 you can insert the result of xp_errorlog into a temp table, then query to find the earliest row. Of course, if your log file is big then it might not be quick.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I thought of that, and it's certainly an option. I'm hoping for a quicker way.

With login failures and backup logs and the like, my error logs often reach a couple megs before they're cycled

Also that require a check to see in the server has been restarted, or if the error log has been cycled.

What we're also considering is a startup procedure that logs the current datetime to a table in master

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 3

SPID1 is the SQL Server Service, so:

SELECT (datediff(mi, login_time, getdate()))/'1440'

FROM master..sysprocesses WHERE spid = 1

Cath

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

Event Viewer also shows. Not sure of the entry, but you can filter on the service and check on the #.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Can you read those from T-SQL? CLR?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

That's brilliant. Thanks. I didn't think of looking there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass