About Me

Barking, Essex, United Kingdom
MCITP DBA ,MCITP BI & Oracle OCP 11G

Tuesday, August 26, 2014

sql server restart time

Option 1:

SELECT sqlserver_start_time FROM sys.dm_os_sys_info


Option 2:


sp_readerrorlog 0,1,'Copyright (c)'

Option 3:

 SELECT create_date FROM sys.databases WHERE name = 'tempdb'

Option 4:

SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1

Option 5:

Right click on the server name and look for the reports > standard reports > Server Dashboard

Finding out who restarted the sql:


SELECT  TE.name AS [EventName] ,
        v.subclass_name ,
        T.DatabaseName ,
        t.DatabaseID ,
        t.NTDomainName ,
        t.ApplicationName ,
        t.LoginName ,
        t.SPID ,
        t.StartTime ,
        t.SessionLoginName
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1
                                                              f.[value]
                                                      FROM    sys.fn_trace_getinfo(NULL) f
                                                      WHERE   f.property = 2
                                                    )), 4)-- 4 is the default number of trace log files
        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
        JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
                                            AND v.subclass_value = t.EventSubClass

WHERE   te.name IN ( 'Audit Server Starts and Stops' )



No comments:

Post a Comment