Windows Reboot time in sys.dm_os_sys_info does not match systeminfo
-
I've got an unusual situation.
to get the Operating System Reboottime (NOT the SQL Service restart time) i've used this query for years:
SELECT dateadd(ss,[ms_ticks]/-1000,getdate()) FROM sys.dm_os_sys_info;You can spot check the value by comparing it to The systeminfo by running this at a command line:
systeminfo |find "System Boot Time"I sometimes see variations of a minute or two, but today, i found a server that is wildly different. four and a half months different actually, and I cannot explain it:
Pretty much all servers are part of a cluster in my test harness, but it should not matter: the node under the cluster was defintely started within the last 30 days, but sys.dm_os_sys_info does not reflect it.
Has anyone else seen this before?
here is a complete test harness, which toggles xp_cmdshell on and then off again, so the caveat about running code from the interwebs certainly applies here.
feel free to manually test systeminfo instead!
i ran this against small number of servers in Central Management Servers for the screenshot above.IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL
DROP TABLE [dbo].[#Results]CREATE TABLE [dbo].[#Results] (
[ResultsId] INT IDENTITY(1,1) NOT NULL,
[ResultsText] VARCHAR(8000) NULL,
[CleanedText] AS (ltrim(rtrim(replace([ResultsText],'System Boot Time:','')))))DECLARE @OperatingSystemRestartTime DATETIME;
SELECT @OperatingSystemRestartTime = dateadd(ss,[ms_ticks]/-1000,getdate()) FROM sys.dm_os_sys_info;
EXECUTE sp_configure 'show advanced options', 1 ;
EXECUTE('RECONFIGURE') ;
EXECUTE sp_configure 'xp_cmdshell', 1;
EXECUTE('RECONFIGURE with override;');
DECLARE @sql NVARCHAR(1000);
SELECT @sql = N'exec master..xp_cmdShell ''systeminfo |find "System Boot Time"'' ';
INSERT INTO [#Results]([ResultsText])
EXECUTE sp_executesql @sql;
EXECUTE sp_configure 'xp_cmdshell', 0;
EXECUTE('RECONFIGURE with override;');
EXEC sp_configure 'show advanced options', 0 ;
EXECUTE('RECONFIGURE ');
SELECT [CleanedText] ,
@OperatingSystemRestartTime AS OperatingSystemRestartTime,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
WHEN '14.0' THEN 'SQL 2017'
WHEN '15.0' THEN 'SQL 2019'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END As SQLVersion
FROM [#Results] WHERE [CleanedText] > ''Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - Lowell - Monday, October 8, 2018 3:18 PMI've got an unusual situation.
to get the Operating System Reboottime (NOT the SQL Service restart time) i've used this query for years:
SELECT dateadd(ss,[ms_ticks]/-1000,getdate()) FROM sys.dm_os_sys_info;You can spot check the value by comparing it to The systeminfo by running this at a command line:
systeminfo |find "System Boot Time"I sometimes see variations of a minute or two, but today, i found a server that is wildly different. four and a half months different actually, and I cannot explain it:
Pretty much all servers are part of a cluster in my test harness, but it should not matter: the node under the cluster was defintely started within the last 30 days, but sys.dm_os_sys_info does not reflect it.
Has anyone else seen this before?
here is a complete test harness, which toggles xp_cmdshell on and then off again, so the caveat about running code from the interwebs certainly applies here.
feel free to manually test systeminfo instead!
i ran this against small number of servers in Central Management Servers for the screenshot above.IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL
DROP TABLE [dbo].[#Results]CREATE TABLE [dbo].[#Results] (
[ResultsId] INT IDENTITY(1,1) NOT NULL,
[ResultsText] VARCHAR(8000) NULL,
[CleanedText] AS (ltrim(rtrim(replace([ResultsText],'System Boot Time:','')))))DECLARE @OperatingSystemRestartTime DATETIME;
SELECT @OperatingSystemRestartTime = dateadd(ss,[ms_ticks]/-1000,getdate()) FROM sys.dm_os_sys_info;
EXECUTE sp_configure 'show advanced options', 1 ;
EXECUTE('RECONFIGURE') ;
EXECUTE sp_configure 'xp_cmdshell', 1;
EXECUTE('RECONFIGURE with override;');
DECLARE @sql NVARCHAR(1000);
SELECT @sql = N'exec master..xp_cmdShell ''systeminfo |find "System Boot Time"'' ';
INSERT INTO [#Results]([ResultsText])
EXECUTE sp_executesql @sql;
EXECUTE sp_configure 'xp_cmdshell', 0;
EXECUTE('RECONFIGURE with override;');
EXEC sp_configure 'show advanced options', 0 ;
EXECUTE('RECONFIGURE ');
SELECT [CleanedText] ,
@OperatingSystemRestartTime AS OperatingSystemRestartTime,
CASE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
WHEN '9.00' THEN 'SQL 2005'
WHEN '10.0' THEN 'SQL 2008'
WHEN '10.50' THEN 'SQL 2008R2'
WHEN '11.0' THEN 'SQL 2012'
WHEN '12.0' THEN 'SQL 2014'
WHEN '13.0' THEN 'SQL 2016'
WHEN '14.0' THEN 'SQL 2017'
WHEN '15.0' THEN 'SQL 2019'
ELSE Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 4)
+ '.'
+ Parsename(CONVERT(VARCHAR(30), Serverproperty('productversion')), 3)
END As SQLVersion
FROM [#Results] WHERE [CleanedText] > ''I've seen a few complaints about systeminfo being incorrect and ran into it once or twice. I remember on one I hit, it was due to a shut down and start up not updating the time - rather than a reboot which updates the time. Maybe that's still the case.
I'm curious though...on the ones with the incorrect times, do you get the correct time if you use net statistics from the command line: net statistics server
I thought that one was accurate.Sue
-
That is the funny part of this dilemma Sue;
I also found references that systeminfo can be incorrect.
in this case, it is the value from sys.dm_os_sys_info that is incorrect.these three commands all match within a few seconds of each other:
systeminfo |find "System Boot Time"
net statistics server
Get-CimInstance -ComputerName $Server.MachineName -ClassName win32_operatingsystem | select csname, lastbootuptimeLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Just to be sure sure... if you execute those commands in all of the cluster nodes does any of them give a similar result to that of the dmv?
- Lowell - Wednesday, October 10, 2018 1:40 PM
That is weird...there are all the CPU, hyperthreaded/socket/core blah issues with that DMV but I've never heard of ms_ticks being a problem.
Are there any system time differences, any errors with the time server or something like that between those nodes? I would guess not as you'd usually see cluster issues. I'm just wondering if it isn't something with those particular nodes.Sue
-
ok more info for everyone.
I scanned all 100+ sql instances in my Central Management Servers.
I have seven servers that have a variance of over one day. one is a cluster and a physical machine, running on it's primary node, with a difference if 26 hours and 15 minutes.
the other six servers are all VM's and all stand alone instances.
from this query:SELECT
OperatingSystemRestartTime = (SELECT dateadd(ss,[ms_ticks]/-1000,getdate()) FROM sys.dm_os_sys_info),
SQLServiceRestartTime = (SELECT create_date FROM sys.databases WHERE name = 'tempdb')the server with the biggest difference is not clustered, and here's a screenshot of all four sources for the reboot time.
this all got started for a handy reference report, when someone identified that they KNEW a server was patched and could not be 100+ days out from the last reboot, which prompted this whole investigation.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
intriguing - are all servers on same SP/CU level?
And same for OS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply