Find SQL Server Downtime Window After Reboot

/*
Created: Jun 1, 2014 by Jeffrey Yao

Function: Send email notification about the sql service restart
and report the down time window.
It is applicable to sql server 2005 and above version only.

Assumption: The database mail is already configured and workable on the sql server instance.
*/

use master
if object_id('dbo.uspRestartAlert', 'P') is not null
 drop proc dbo.uspRestartInfo;
go
create proc dbo.uspRestartAlert
as
begin
 set nocount on;
 declare @curr_trc_file varchar(256), @prev_trc_file varchar(256);
 declare @trc_file_name varchar(128), @trc_file_folder varchar(256);
 declare @trc_num int, @slash_pos int;
 declare @stopTime datetime, @startTime datetime;
 declare @msg varchar(500), @subject varchar(100), @crlf char(2);
 declare @recipients varchar(100), @sqlcmd varchar(max);

 set @recipients = '<your email account>'; -- change to the proper account

 set @crlf = nchar(0x0d) + nchar(0x0a);
 select @curr_trc_file = '', @msg='';

 -- check downtime window details
 select @curr_trc_file = [path] from sys.traces
 where id =1 and status =1;

 set @subject= 'Server ' + quotename(@@servername, '[]') + ' has restarted';

 if @curr_trc_file <> ''
 begin
 -- the following is to try to get the number in the current default trace file name,
 -- default trace file has a naming convention like <path>\log_<num>.trc
  set @curr_trc_file = REVERSE(@curr_trc_file);
  set @slash_pos = CHARINDEX('\', @curr_trc_file);
  set @trc_file_folder = reverse(SUBSTRING(@curr_trc_file, @slash_pos, 256));
  set @trc_file_name = reverse(SUBSTRING(@curr_trc_file, 1, @slash_pos-1));
 
  set @trc_num = cast(SUBSTRING(@trc_file_name, 5, len(@trc_file_name)-8) as int) -- 8 = length of "log_" plus ".trc"

  set @curr_trc_file = REVERSE(@curr_trc_file);
  set @prev_trc_file = @trc_file_folder + 'log_' + CAST((@trc_num-1) as varchar(12)) + '.trc'
  select @stopTime=max(starttime) from fn_trace_gettable(@prev_trc_file, 1) -- get the last StartTime of the previous trace 
  select @startTime=min(starttime) from fn_trace_gettable(@curr_trc_file, 1) -- get the first StartTime of the current trace

  set @msg =  'The downtime window is from ' + CONVERT(varchar(30), @stopTime, 120) + ' to ' +  CONVERT(varchar(30), @startTime, 120) + ';  ' + @crlf + @crlf
  + 'The total down time duration is: ['
  + cast(DATEDIFF(second, @stopTime, @startTime)/60 as varchar(20)) + ' minutes, '  
  + cast(DATEDIFF(second, @stopTime, @startTime)%60 as varchar(20)) + ' seconds]';
  exec msdb.dbo.sp_send_dbmail @recipients = @recipients, @subject = @subject, @body = @msg--, @body_format='HTML'
 end  
 else -- there is no trace file
  exec msdb.dbo.sp_send_dbmail @recipients = @recipients, @subject = @subject, @body = 'Please check';
end
go

if exists (select * from sys.procedures where name='uspRestartAlert')
 exec sp_procoption @ProcName='dbo.uspRestartAlert', @OptionName='startup', @OptionValue='on';
go

add-type -AssemblyName "microsoft.sqlserver.smo, version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";#version should be changed according to your installed sql version, sql2008=10.0.0.0
 
$info=get-eventlog -logname System -entrytype information -source User32 -computer . -after (get-date).addhours(-12) -message "The process * has initiated * on behalf of *"  | select timegenerated, message -first 1
 
if ($info -ne $null)
{
    $info.message -match "on behalf of user (.*?) for"
    [string]$user=$matches[1];
 
    $svr = new-object 'microsoft.sqlserver.management.smo.server' 'SQLInstance_Name' # change <SQLInstance_Name> accordingly
    $curr=$svr.ReadErrorLog(0) | Where-Object {$_.Text -match 'Recovery is complete'} | select LogDate -last 1;
 
    [string]$msg='';
    if ($curr -ne $null)
    {
        $prev = $svr.ReadErrorLog(1) | select LogDate -last 1;
 
        $d=$curr.logdate - $prev.LogDate;
        $msg += "The downtime window is from $(($prev.LogDate).ToString('yyyy-MM-dd HH:mm:ss')) to $(($curr.Logdate).toString('yyyy-MM-dd HH:mm:ss')) `r`n";
        $msg += "Total downtime is $($d.hours) hours, $($d.Minutes) minutes and $($d.Seconds) seconds `r`n`r`n";
    }
 
    $msg +=  $info.Message;
 
    [string]$qry=@"
    exec msdb.dbo.sp_send_dbmail @recipients='<your_email_acct>', @subject='Server is rebooted at $($info.TimeGenerated) by $($user)'
    , @body= '$($msg)';
"@;
 
    $svr.databases['msdb'].ExecuteNonQuery($qry);
   
}