Find SQL Server Last Restart Time and Resource Information with Useful DMV sys.dm_os_sys_info

by Manzoor Siddiqui · Published October 10, 2015 · Updated December 4, 2015

SQL Server provides DMVs and system tables to find out when SQL Server was last restarted. We may need this information many times to troubleshoot or trace issues which occurs on database servers. So, here I am listing few simple and useful DMVs and tables which can provide you this information.

sys.dm_os_sys_info

DMV sys.dm_os_sys_info provides information about system and resources consumed by SQL Server. You will also get information about last restart time of SQL Server from column sqlserver_start_time. You can also find total count of virtual and physical CPUs.

SELECT
DATEADD(s,((-1)*([ms_ticks]/1000)),GETDATE()) AS LastRestartTime,
CAST([ms_ticks]/1000/60/60/24.0 AS DECIMAL(15,2)) AS TotalDays,
CAST([ms_ticks]/1000/60/60.0 AS DECIMAL(15,2)) AS TotalHours,
CAST([ms_ticks]/1000/60.0 AS DECIMAL(15,2)) AS TotalMinutes
FROM sys.dm_os_sys_info

Above query will provide you result for SQL Server last restart time, total days of restarting and total hours and total minutes as shown below.

You can refer below query to find out Total SQL Server CPUs, max_workers_count, scheduler_count from object sys.dm_os_sys_info.

SELECT cpu_count AS TotalCPU, max_workers_count, 
scheduler_count, scheduler_total_count 
FROM sys.dm_os_sys_info

As you can see in the above output from my test server there are 24 CPUs which you can also manually verify from Windows Task Manager –> View –> CPU History –> One Graph per CPU and select Performance tab.

Also, if you want to check total physical memory (RAM) or assigned Virtual Memory of your server then you can execute the below query.

SELECT 
[physical_memory_kb]/1024/1024.0 AS PhysicalMemoryInGB, 
[virtual_memory_kb]/1024/1024.0 AS VirtualMemoryInGB
FROM sys.dm_os_sys_info

SQL output is as follows from my test server.

Different ways to find SQL Server last Restart Time is given below.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info
GO
SELECT login_time FROM sys.dm_exec_sessions WHERE session_id = 1
GO
SELECT start_time from sys.traces where is_default = 1 
GO
SELECT crdate FROM sysdatabases WHERE name='tempdb' 
GO
SELECT create_date FROM sys.databases WHERE name = 'tempdb'
GO

Output for the same is given below.