Find out when a SQL Server Instance Started with udf_SQL_StartDT

				Sometimes you want to know when the SQL Server Instance was

started.  You'll find an event in the NT event log, and a new

SQL Server Log file is created each time the instance starts so

you could easily look in Enterprise Manager to figure out when

it started.



If you want to know this information from inside T-SQL it's a 

little bit trickier.  I recently had to find this information

for a client.  I located the information in a 1998 posting in 

the microsoft.public.sqlserver.programming newsgroup by 

Med Bouchenafa of France.



SQL Server won't tell you it's start time directly but you can

query the login time of one of the system processes.  Since

they start when the instance starts the process's login time 

is a good proxy for when the SQL Server instance started.



CREATE FUNCTION dbo.udf_SQL_StartDT ()



    RETURNS datetime -- Date/time the SQL Server Instance started

/* 

* Returns the data/time that the SQL Server instance was started.

*

* Example:

select dbo.udf_SQL_StartDT() as [System Started AT]

*

* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com

* You may use this function in any of your SQL Server databases

* including databases that you sell, so long as they contain 

* other unrelated database objects. You may not publish this 

* UDF either in print or electronically.

*

* Published as Vol 1 #11 of The T-SQL UDF of the Week Newsletter

http://www.NovickSoftware.com/UdfofWeek/UDFofWeek.htm

****************************************************************/

AS BEGIN



    DECLARE @WorkingVariable datetime



    SELECT @WorkingVariable = login_time

        FROM master..sysprocesses

        WHERE cmd='LAZY WRITER'



    Return @WorkingVariable

END





GRANT  EXECUTE  ON [dbo].[udf_SQL_StartDT]  TO [PUBLIC]

GO





So let's try it:



select dbo.udf_SQL_StartDT() as [System Started AT]

GO



(Results)

System Started AT

------------------------------------------------------ 

2003-01-23 11:40:37.283

(End of results)



After I wrote udf_SQL_StartDT I thought of one other way to get

the system up time.  The built-in system function 

fn_virtualfilestats returns a Timestamp column that is the number

of milliseconds since the system come up.  



The next query uses SQL Server's MIN aggregate function on the 

Timestamp column because fn_virtualfilestats returns the same 

value for every row.  It then converts it to seconds and 

subtracts it from the current time to compute the start time of

the SQL Server instance:



SELECT DATEADD(ss, -1 * min(Timestamp)/1000 , getdate())

                  AS [Start per fn_virtualfilestats]

    , dbo.udf_SQL_StartDT() 

                  AS [Start per udf_SQL_StartDT]

   FROM ::fn_virtualfilestats(-1, -1)

GO



(Results)

Start per fn_virtualfilestats  Start per udf_SQL_StartDT      

------------------------------ ------------------------------ 

2003-01-23 11:38:50.200        2003-01-23 11:40:37.283

(End of results)



As you can see, there's a minute and 47 second difference between

the two times.  Which one is better?  It's possible to make a 

case for either number.  I'll stick with the later one based on

the reasoning that "While the SQL Server might have been starting

earlier, no application work was done before the LAZY WRITER 

process's start time so that's a better indication of when the

system was available."