This is an old topic, and there have been quite a few blogs (and following comments) that illustrate how to quickly find the information, mainly by checking tempdb creation time (http://www.sqlservercentral.com/blogs/sqldbauk/archive/2010/04/29/when-was-sql-server-last-restarted_3F00_.aspx) and check session_id=1 information (http://www.sqlservercentral.com/blogs/joewebb/archive/2010/04/28/how-to-tell-when-sql-server-started_3F00_.aspx). However, as a DBA, one of the common post-event reports I have to write, after an unplanned SQL Server service restarts, is “damage assessment report”, in which I need to report not only when the SQL Server last started, but also how long the service is down.

Here is my approach (applicable to SQL Server 2005 / 2008 only)

In SS2K5/SS2K8, there is a default trace (which is enabled by default) that automatically starts when the SQL Server service starts, and each SQL Server restarts will start a new trace file, so by checking the current trace file and the last trace file, we should have sufficient information to tell when the SQL Server service started and how long the down time window is.

Here is the key code:

declare @trace_common_path varchar(250), @trace_file_number int;

declare @curr_trace_file varchar(300), @prev_trace_file varchar(300);

declare @curr_start_time datetime, @prev_start_time datetime;

if exists (select 1 from sys.traces where id=1 and path like '%log[_][1-9]%.trc')

begin -- default trace exists

  select @trace_file_number=cast(substring(right(path, charindex('\', reverse(path))-1),5, len(right(path, charindex('\', reverse(path))-1-4-4))) as int) -- -4 = len('log_') and next -4 = len('.trc')

        , @trace_common_path = substring(path, 1, len(path)-charindex('\', reverse(path))+1)

        , @curr_trace_file = path

 from sys.traces where id=1;

 set @prev_trace_file = @trace_common_path + 'log_' + cast(@trace_file_number-1 as varchar) + .trc';

 select @curr_start_time =min(starttime)

 from fn_trace_gettable(@curr_trace_file, 1) where starttime is not null;

 select @prev_start_time = max(starttime)

 from fn_trace_gettable(@prev_trace_file, 1) where starttime is not null;

 if datediff(second, @prev_start_time, @curr_start_time) > 2

 begin -- restart occurred

    print 'The downtime window is: ' + convert(varchar,@prev_start_time, 120) + ' TO ' + convert(varchar, @curr_start_time, 120)

    print 'The duration is: ' + cast(datediff(second, @prev_start_time, @curr_start_time) as varchar) + ' seconds'; 

 end -- restart occurred

end -- default trace exists

If we create a stored procedure that can email us (vis sp_send_dbmail) the above-mentioned information , and set it as a startup procedure, we can always know the SQL Server service restarting event in the first place. When manage clustered SQL Server systems, without proper detecting mechanism, we may not even be aware of a failover.