System Time is Wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

System Time is Wrong

Author

Topic

ceje
Starting Member

3 Posts

Posted - 2007-03-13 : 09:41:33

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 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

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-03-13 : 11:24:24

Changing the time zone at the OS level should be relatively easy. Getting the jobs to recognize that they should run for 12 hours will a bit harder. Finding any reference in your code to getdate() will be outright difficult. The transaction logs themselves will not mind, as they go by a System Change Number, and don't care what time a transaction came in according to the clock on your wall, or the system clock in your coffee maker.

For the SQL Server Agent jobs, I would just open each one, make a dummy change to each schedule, and save the "new" schedule, and the jobs should pick up again with the new NextRunDate (check msdb..sysjobschedules to be sure, though).

ceje
Starting Member

3 Posts

Posted - 2007-03-13 : 12:13:46

Thank you very much for your reply. I appreciate your answers to each of the concerns, especially regarding the transaction logs not using the system time.

I'll check with the application programmers to determine if any negitive side effects are possible with the following approach.

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
Do a transaction log backup
Apply the transaction log file to the backup / recovery server
Verify the transactions on the backup server

Would you have any other suggestions to cover myself in the event of a problem?

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2007-03-13 : 13:17:31

Without knowing your system, it is a bit hard to tell, but those steps look good for a general case. You can probably keep SQL Server running for the duration, unless you are worried about transactions coming in during the change.

ceje
Starting Member

3 Posts

Posted - 2007-03-13 : 17:06:44

Thanks for your help. It's gives me more confidence that this will work knowing that an expert has at least looked at my plan. I know there are no guarantees though.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources