SQL Server T-SQL User-Defined Function of the Week

When did SQL Server Start?

Volume 1 Number 11   January 28, 2003


Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

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.

/------------- Copy From Below this line ----------------------\
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.
*
* Common Usage:
select dbo.udf_SQL_StartDT() as [System Started AT]
*
* © Copyright 2002 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
\------------ Stop copying above this line --------------------/

So let's try it:
/------------- Copy From Below this line ----------------------\
select dbo.udf_SQL_StartDT() as [System Started AT]
GO
\------------ Stop copying above this line --------------------/
(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:

/------------- Copy From Below this line ----------------------\
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
\------------ Stop copying above this line --------------------/
(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."  

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+