March 13, 2002 at 10:59 pm
If we change the System time it takes a while for SQL to sync the time with the system. Is there a function to immediatly sync the time from SQl or is there a function or a procedure to Change the SYSTEM TIME directly from SQL ??.
Jesus My Saviour
March 14, 2002 at 4:48 am
Odd, never noticed, just checked and it takes just over a minute to resync. Based on BOL this should be the OS's system time. Apparently there is an internal clock somewhere in SQL that resyncs every so often. Unfortunately I cannot see anything TSQL related doing this so it has to be a process within the application layer of SQL taht hooks the systime. I will see if I can find more details.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 10:21 am
The only way is to use a cmdshell.
See this:
select getdate()
exec xp_cmdshell 'time
'
it gets the time. To set or change the time, you'd have to pre=submit the time to the xp_cmdshell, whchi I'm not sure would work.
If I change the time on my dev server through Terminal Services and run the above script, the time is synched immediately.
Steve Jones
March 14, 2002 at 10:42 am
EXEC xp_cmdshell 'net time /domain:xxxx /set /y'
SELECT getdate()
EXEC xp_cmdshell 'time'
Steve what version of SQL did you test on along with SP.
That holds true on my SQL 2000 servers, but on my SQL 7 SP2 (no SP3 rigth now) servers after doing a net time /domain:xxxx /set /y this is what happend.
--Right after run
-----------------------------------------------------
2002-03-14 12:19:16.873
(1 row(s) affected)
output
-----------------------------------------------------
The current time is: 12:16:15.32
(1 row(s) affected)
--Last difference I was able to catch
-----------------------------------------------------
2002-03-14 12:19:40.670
(1 row(s) affected)
output
-----------------------------------------------------
The current time is: 12:16:39.10
(1 row(s) affected)
--Now close to synced
-----------------------------------------------------
2002-03-14 12:16:43.747
(1 row(s) affected)
output
-----------------------------------------------------
The current time is: 12:16:43.88
(1 row(s) affected)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 14, 2002 at 11:06 am
Sorry, SQL 2k, SP1, W2K, SP2.
Interesting. On my SQL 7 server on NT 4, I think SP1 and SP6 respectively, I get a difference. I checked it a couple times by moving the server by 1 hr and it appears it takes about 40 seconds to synch up.
Not a big deal for me, but if you were a 24x7 processing shop, the daylight saving might wreck havoc with you.
Steve Jones
March 16, 2002 at 12:44 pm
This is a good reason to have an NTP server. Using tools from the resource kit, NT4 servers can access NTP servers. All others can get their time from a properly sync'ed domain controller.
In Win2K, individual stations can be set to hit an NTP server using
net time /setsntp:<server list>
and that'll keep things in-sync.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 16, 2002 at 1:41 pm
But that still doesn't correct tha fact that SQL is 40+ seconds to sync with the OS, NTP only affects the time in the OS until SQL syncs. The problem I see with this is that SQL states GETDATE() returns the value of the system time. I have always thought that the system time is from the OS but apparently that is not true.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 16, 2002 at 2:01 pm
Without seeing some code behind the GETDATE() function, it's hard to say. I've also seen the case where it takes some time even using the net time command to get the value recognized by the system (e.g. Have the clock displaying on the start bar. Change the time at the command prompt. Check the clock on the start bar to see if it is in sync). So I don't know if it's necessarily SQL Server's fault.
Usually, though, if the system got gets out of sync, it's because a long period has passed since it's been compared to whatever it's being synchronized against. Sync'ing against an NTP server tends to happen on a regular basis and as a result, the delta never grows to large.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 17, 2002 at 12:14 pm
Yeah I have seen thst too, seems to a be a toolbar refresh problem. If you double click the time and open the diaglog it shows the right time and the toolbar is still showing wrong. But press ok and all is well with the toolbar again. So I do believe there is something going on under the hood of NT/2000 that Microsofts statement on how those items works is not telling.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 17, 2002 at 12:24 pm
A yup, par for the course. That's why I spend a lot of time with lower level utils troubleshooting things.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 18, 2002 at 8:40 am
If any of you dig further on this, I'd be interested to know what you find. I suspect there is some process in SQL Server that checks the time on the host server every 40 sec or so.
Steve Jones
April 12, 2002 at 10:13 am
Using the example from Anteres686 on a box with verions "Microsoft SQL Server 7.00 - 7.00.961" I had to rerun the query three times before the times were in "synch".
Very interesting indeed.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply