Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

When was SQL Server Restarted?

While doing some performance testing recently and utilizing some of the DMV’s I figured, I better find out when the SQL Server Service was restarted. The reason being, the DMV’s get cleared out when SQL Server is restarted! Otherwise, the server I was doing performance testing on could have been restarted so recently, that the data stored so far may not provide a true representation of the performance of the server. To that end, I had to find out when the TEMPDB was created (which also occurs on restart). The easiest way for me to find that out was to write the below script.

Script: When was SQL Server Restarted?

SELECT

      create_date AS SQL_Last_Started

FROM

      sys.databases

WHERE

      name = 'tempdb'

Not a difficult script by any means, but it could help in determining if you should even use the results of the DMV’s to make performance related decisions! There are other ways to determine when the server was restarted, but this was the quickest that I could think of off the top of my head.

I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald

Comments

Posted by Angelo on 20 October 2010

Now if only I could determine how long a server was down for....

Posted by neelagirichandra on 21 October 2010

hi Brian K. McDonald,  

this is related to only SQL Server Service restarted.

Posted by Tahir on 21 October 2010

Excellent, very easy to remember and very helpful.

Thanks you.

Posted by JosuaJou on 21 October 2010

This should also be correct:

SELECT login_time FROM master..sysprocesses WHERE spid = 1

Since the first SPID would have been created the moment SQL Was Started.

Posted by Malcolm Burtt on 21 October 2010

Hi Brian

That's a handy way to find the restart time. The only trouble with it in the context of assessing permformance data is that if you're looking at the query stats DMV's you can't tell if a DBCC FREEPROCCACHE has been run since then.

I get around this by looking for the oldest entry in the query cache using...

SELECT min(creation_time) from sys.dm_exec_query_stats

... of course this might not be the first query run since a restart or since DBCC FREEPROCCACHE was run if older queries have been pushed out of the cache to make way for more frequently used ones.

Malcolm

Posted by ALZDBA on 21 October 2010

You will even find session 1 to have started _later_ than tempdbs create_date.

;-)

Posted by work4larry on 21 October 2010

If you are looking for the number of days since the last service restart:

SELECT CONVERT(int,GETDATE() - create_date) AS SQL_Last_Started

FROM sys.databases

WHERE name = 'tempdb'

Posted by shankar.k on 21 October 2010

Hi Brian,

       This is a simple yet great idea to determine the uptime. If you are using SQL Server 2008, an easier option would be to use the SQLServer_Start_Time column value from the DMV SYS.DM_OS_SYS_INFO. Hence, a query such as below would get you the desired result:

SELECT SQLServer_Start_Time, DATEDIFF(DAY, SQLSERVER_START_TIME,GETDATE()) AS "Uptime in Days" FROM SYS.DM_OS_SYS_INFO

Posted by sathishmcc on 22 October 2010

Hi,

Could you tell about performance monitoring and database tuning detailed..please

Posted by garayy on 22 October 2010

That script can calculate downtime:

--Results

DECLARE @last_start_time datetime

DECLARE @last_stop_time datetime

DECLARE @resault varchar(500)

--Getting SQL server version

DECLARE @sql_ver varchar(1)

CREATE TABLE #SQLVersion (

Number tinyint,

Name nvarchar(255),

IntVal integer,

Version nvarchar(255))

insert #SQLVersion exec master..xp_msver 'ProductVersion'

set @sql_ver = (select left(Version,1) from #SQLVersion)

drop table #SQLVersion

--getting log files list

CREATE TABLE #ListErrorLogs (

Number tinyint,

LastEvent datetime,

LogSize integer)

INSERT #ListErrorLogs EXEC sp_enumerrorlogs

--Search for service last start time

DECLARE logs_cursor CURSOR

for select Number, LastEvent, LogSize

from #ListErrorLogs

order by Number

open logs_cursor

DECLARE @log_id integer

DECLARE @log_time datetime

DECLARE @log_size integer

FETCH NEXT FROM logs_cursor INTO @log_id, @log_time, @log_size

WHILE (@@FETCH_STATUS <> -1)

BEGIN

  IF (@@FETCH_STATUS <> -2)

begin

--SQL 2005

if (@sql_ver = '9')

begin

CREATE TABLE #ErrorLog9 (

LogDate datetime,

ProcStr varchar(255),

TextStr varchar(1024))

if (@log_id=0)

begin

INSERT #ErrorLog9 EXEC master..xp_readerrorlog

end

else

begin

INSERT #ErrorLog9 EXEC master..xp_readerrorlog @log_id

end

select @last_start_time=LogDate from #ErrorLog9

where TextStr='Starting up database ''master''.'

drop table #ErrorLog9

if (@last_start_time IS NOT NULL)

begin

FETCH NEXT FROM logs_cursor INTO @log_id, @log_time, @log_size

set @last_stop_time = @log_time

goto end_all

end

end

--SQL 2000

if (@sql_ver = '8')

begin

CREATE TABLE #ErrorLog (

TextStr varchar(255),

ContinuationRow tinyint )

if (@log_id=0)

begin

INSERT #ErrorLog EXEC master..xp_readerrorlog

end

else

begin

INSERT #ErrorLog EXEC master..xp_readerrorlog @log_id

end

select @last_start_time=cast(left(TextStr,19) as datetime) from #ErrorLog

where TextStr like '%Starting up database ''master''.'

drop table #ErrorLog

if (@last_start_time IS NOT NULL)

begin

FETCH NEXT FROM logs_cursor INTO @log_id, @log_time, @log_size

set @last_stop_time = @log_time

goto end_all

end

end

FETCH NEXT FROM logs_cursor INTO @log_id, @log_time, @log_size

end

END

end_all:

CLOSE logs_cursor

DEALLOCATE logs_cursor

--Delete temporary table with list of log files

drop table #ListErrorLogs

--return resault

set @resault='Last stop time '+CONVERT(varchar(40), @last_stop_time, 20)

+' last start '+CONVERT(char(20), @last_start_time, 20)

+' down time is '+CAST(CAST(CAST(@last_start_time-@last_stop_time as real)*1440

as integer) as varchar(15))

+' min'

if (@last_start_time > (getdate()-2))

begin

set @resault = '!'+@resault

end

select @resault

Posted by SanjayAttray on 22 October 2010

Excellent script garayy.

Posted by Brian K. McDonald on 22 October 2010

:) Thanks for sharing that script garayy. I'm sure it will come in handy for a bunch. I would prefer not to have cursors myself, but if it works, why break it right?

Posted by Brian K. McDonald on 22 October 2010

Thanks for the modified script work4larry.

Posted by Brian K. McDonald on 24 October 2010

sathishmcc,

Performance Tuning and Monitoring is a massive topic and some have written books around the subject. I will ponder this request on the "tree of woe" and see if I can set some time aside to dedicate to a series of blogs on this subject. For now, you can always refer to any that Brad McGehee and Grant Fritchey have written. I pretty sure that both of them have books on the subjects.

Best of luck,

Brian

Posted by aruopna on 28 October 2010

That was neat! BTW SQL Server 2008 onwards we can make use of sys.dm_os_sys_info.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Thx!

Leave a Comment

Please register or log in to leave a comment.