Post reply

How do i identify last SQL Services start date time?

  • March 10, 2009 at 6:50 am

    #131997

    Hi all,

    I need the last SQL Server stop/start date time, but i do not know how to get?

    sys.dm_io_virtual_file_stats(DB_ID(),NULL) dmv displays some useful data. It does not match what i want.

  • March 10, 2009 at 6:55 am

    #956739

    I can't find anywhere this is stored by default, but this article shows how you can configure it manually:

    http://www.mssqltips.com/tip.asp?tip=1574

    hope this helps.

  • March 10, 2009 at 7:20 am

    #956763

    Isn't there any dmv or system table to save this kind of data since binaries wer installed?

  • March 10, 2009 at 9:21 am

    #956884

    Start time is the same as tempdb database creation date. Stop time-->you can get that from sql error logs. To get this information in a table use the method mentioned under link posted by another poster.

    MJ

  • March 10, 2009 at 9:29 am

    #956889

    select

    [SQL Server Start Time]= convert(varchar(23),a.SQL_Start,121),

    [SQL Agent Start Time]= convert(varchar(23),a.Agent_Start,121),

    [SQL Uptime] =

    convert(varchar(15),

    right(10000000+datediff(dd,0,getdate()-a.SQL_Start),4)+' '+

    convert(varchar(20),getdate()-a.SQL_Start,108)) ,

    [Agent Uptime] =

    convert(varchar(15),

    right(10000000+datediff(dd,0,getdate()-a.Agent_Start),4)+' '+

    convert(varchar(20),getdate()-a.Agent_Start,108))

    from

    (

    Select

    SQL_Start = min(aa.login_time),

    Agent_Start =

    nullif(min(case when aa.program_name like 'SQLAgent %' then aa.login_time else '99990101' end),

    convert(datetime,'99990101'))

    from

    master.dbo.sysprocesses aa

    where

    aa.login_time > '20000101'

    ) a

    Results:

    SQL Server Start Time SQL Agent Start Time SQL Uptime Agent Uptime

    ----------------------- ----------------------- --------------- ---------------

    2009-02-01 08:56:39.153 2009-03-10 11:08:27.317 0037 02:31:17 0000 00:19:29

    (1 row(s) affected)

  • March 10, 2009 at 9:32 am

    #956892

    First line of xp_readerrorlog gives the start time.

  • March 10, 2009 at 9:32 am

    #956894

    SQL Server Error logs and Windows Event Logs provide both start and stop time

    Regards,Yelena Varsha

  • March 10, 2009 at 9:37 am

    #956899

    and you can also check the login_time of session_id 1 in sys.dm_exec_sessions. Since the system sessions connect at startup and never disconnect, that will be the system start time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 10, 2009 at 9:43 am

    #956905

    Thanks a lot

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply