Find SQL Server Last Restart Time and Resource Information with Useful DMV sys.dm_os_sys_info
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.
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.
If you know any other way also to find out SQL Server restart time with T-SQL then please share with us.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]