Wednesday, February 5, 2014

When was the last time your SQL Server Service was restarted?

Every DBA needs to know the last time the SQL Server Service was restarted! Best case, your restarts are scheduled, known events.  But... we all know that is not always the case.  How do you know when your service was last restarted?  See the statements below.  The first couple are using the newer DMV objects, and that last one is just looking at the create_date on the TempDB. No matter what version you have, that one will always work.

I am actually working with another DBA right now, across the country, on the same instance.  Waiting for him to apply a patch and restart the service.  This is great!  Now I can just run this, and know when he has restarted the service!

    -- 1. sys.dm_os_sys_info  
   SELECT sqlserver_start_time
   FROM sys.dm_os_sys_info; 

   -- 2. sys.dm_exec_sessions  
   SELECT login_time
   FROM sys.dm_exec_sessions
   WHERE session_id = 1; 

   -- 3. TempDB
  
   SELECT create_date
   FROM sys.databases
   WHERE name = 'tempdb'



Useful information regarding your SQL Server instance and available resources:
sys.dm_os_sys_info

Details regarding all active user connections:

No comments:

Post a Comment