SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 System Time is Wrong
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ceje
Starting Member

3 Posts

Posted - 03/13/2007 :  09:41:33  Show Profile  Reply with Quote
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 - 03/13/2007 :  11:24:24  Show Profile  Reply with Quote
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).
Go to Top of Page

ceje
Starting Member

3 Posts

Posted - 03/13/2007 :  12:13:46  Show Profile  Reply with Quote
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?
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 03/13/2007 :  13:17:31  Show Profile  Reply with Quote
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.
Go to Top of Page

ceje
Starting Member

3 Posts

Posted - 03/13/2007 :  17:06:44  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000