March 13, 2007 at 7:58 am
The Windows system time is wrong on my server. It is 12 hours ahead. i.e Instead of 8am it is 8pm. The database is in use 24x7 running an accounting / trucking system.
Can anyone recommend a method to change the time back 12 hours without incuring any data loss or other negative anomolies?
I don't mind stopping SQL Server for a couple hours to correct the problem. As a worst case scenario, I wondered if I stopped SQL Server, reset the system time back 12 hours, and started SQL Server 12 hours later, if that would work... what do you think?
I backup the transaction logs offsite every 15 minutes and restore them to a recovery server at the offsite location.
Any help would be greatly appreciated. TIA
Configuration:
Windows Server 2003
SQL Server 2000
March 13, 2007 at 8:32 am
I would think this would be impossible to answer from a data loss or strange side effects without knowing the entire process that the server is used for. My gut feeling would be that if you can from an operational standpoint be without the server for 12 hours, then changing the time would most likely not be a big deal to change the time while the server is running (no warranties granted or implied in that statement!)
From a SQL standpoint it will simply take the time from Windows (with a few seconds delay from my experience).
March 13, 2007 at 3:13 pm
After speaking with the programmers, I'm going to reset the time by 4 hour increments on 3 days, to minimize the effect on users. During each reset, no updates to the DB will occur for 4 hours, to insure that there will be no duplicate keys created. After 3 days the time should be fully corrected.
To correct the system time I'll take the following actions:
Do a full backup of the DB
Stop SQL Server (since there is only 1 DB on the server)
Reset the Windows System Time
Start SQL Server
Update the DB via the application
Ship the log to the backup / recovery server
Apply the transaction log file to the backup / recovery server
Verify the transactions on the backup server
If you think of a better way, or something I might be missing, please let me know.
Thanks again for your help.
March 13, 2007 at 4:32 pm
Do you know when things changed? If so, you might be able to correct out some of the other data.
If you change the Windows system time, the SQL Server time will reset within 1 minute to the new time. No need to stop SQL Server. The best thing you might do is get people off the system or schedule it to change late at night.
March 13, 2007 at 9:08 pm
As you suggested, I'll start the time correction tonight and not allow access to the DB until the 4 hour outage is complete. So, by Friday morning, the time should be corrected. Thanks for your help.
March 14, 2007 at 4:16 am
I am surprised that your server can be 12 hours wrong without AD complaining. I would have expected the computer account to have been disabled in this situation. You should talk to your infrastructure people before changing the system time on your server to avoid any problems with AD.
Is your time zone setting correct? If this is wrong, you can change the time zone without impacting on AD or interrupting SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 14, 2007 at 5:16 am
Why is it wrong? Is it synchronizing to a time service somewhere? You don't want it changing back again !
March 14, 2007 at 2:33 pm
AD time issue should only be an issue if you use Kerberos though. Or at least so I have been led to believe.
But then I am not an infrastructure person...
March 16, 2007 at 10:00 am
Thanks for the kind advice.
My clients server is in a small business with only one server, so he never implemented AD.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply