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

hi Brian K. McDonald,  

this is related to only SQL Server Service restarted.

Excellent, very easy to remember and very helpful.

Thanks you.

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.

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

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

;-)

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'

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

Hi,

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

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

Excellent script garayy.

:) 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?

Thanks for the modified script work4larry.

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

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!