August 16, 2009 at 10:39 pm
Hi,
I have configured Log shipping in Sql server 2005. I'm confused with date format that the transaction log backups showing. The transaction log backup ran through the backup jobs created by Log shipping configuration at 11:00 AM showing the date format as below.
Mydb1_20090816180001.trn
Mydb2_20090816180001.trn
Why its showing 16180001 (4:18 PM, I'm assuming), when the backup job ran at 11:00 AM??Both Primary & secondary are on same domain.
What is the date format for the log shipping backups??
thanks
August 16, 2009 at 11:09 pm
rambilla4 (8/16/2009)
Hi,I have configured Log shipping in Sql server 2005. I'm confused with date format that the transaction log backups showing. The transaction log backup ran through the backup jobs created by Log shipping configuration at 11:00 AM showing the date format as below.
Mydb1_20090816180001.trn
Mydb2_20090816180001.trn
Why its showing 16180001 (4:18 PM, I'm assuming), when the backup job ran at 11:00 AM??Both Primary & secondary are on same domain.
What is the date format for the log shipping backups??
thanks
Just curious, what time zone are you in?
August 17, 2009 at 12:12 am
pacific time (US)
August 17, 2009 at 1:31 am
Pls. run getdate() and then check this time with the logs its generating.
Both the times should be same. (there can be a small diff. but that much diff. is not possible. I think this may be possible where you are using some third party tool for backup)
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 17, 2009 at 2:15 am
This is something really interesting.
Can you pl. check with the other backup or check the latest backup for confirmation and compare the datetime.
One possiblilit will be make sure manually file name has not been changed.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
August 17, 2009 at 6:28 am
it's supposed to be UTC time (by design) I believe for consistency between servers in different timezones, etc. So I'd check the timezone on your server to start. I did check one of the log shipping jobs I manage and notice that my timestamps are 4 hours after backup time. But I'm in Eastern (-5). So that's odd 🙂
August 17, 2009 at 6:40 am
jamie (8/17/2009)
it's supposed to be UTC time (by design) I believe for consistency between servers in different timezones, etc. So I'd check the timezone on your server to start. I did check one of the log shipping jobs I manage and notice that my timestamps are 4 hours after backup time. But I'm in Eastern (-5). So that's odd 🙂
I see you were thinking the same thing I was, that the filename is datestamped with the UTC datetime. If I remember correctly, however, UTC time does not adjust for daylight savings time. IF correct, then a 4 hour difference makes sense when the clocks are adjusted forward.
August 17, 2009 at 7:04 am
So rambilla being +5 hours, that would put the timezone at -6 (with DST)? Putting that server in Central time zone perhaps.
How long did the TL backup take to run?
August 17, 2009 at 11:34 am
I did not understand what to do exactly regarding the time settings on the servers. Please see the attachment for the clock settings on both servers.
Select getdate() on Primary
Result:2009-08-17 10:34:12.193
Select getdate() on Secondary
Result:2009-08-17 10:34:12.150
Mydb1_20090817150001.trn . This is the log backup taken at 8:00 AM by the Backup job created by Log shipping wizard?
Here is the information from error log:
2009-08-17 08:00:00.85 Backup Log was backed up. Database: Mydb1, creation date(time): 2009/07/24(19:52:39), first LSN: 237:7319:1, last LSN: 237:7319:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\sql1vs\Logshipping_Tlogs\Mydb1_20090817150000.trn'}). This is an informational message only. No user action is required.
Here sql1vs is the virtual server name. Actually it is A\P cluster setup. So I created a file share in the sqlgroup in the Cluster Administrator so that log shipping won't break if the failover instance moves to the passive node. I aslo loged into the virtual server sqlvs1 and verified the Date Time in control panel and it is aslo same as the Physical nodes & the secondary server.
Is this time stamp normal? If it not what exactly I need to change?
Thank you
August 17, 2009 at 1:53 pm
Run the following and see what you get:
SELECT getdate(), getutcdate();
SELECT datediff(hour, getdate(), getutcdate());
SELECT dateadd(hour, 7, '20090817 08:00:01');
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2009 at 2:53 pm
On both primary & secondary:
SELECT getdate(), getutcdate();
SELECT datediff(hour, getdate(), getutcdate());
SELECT dateadd(hour, 7, '20090817 08:00:01');
Results:
----------------------- -----------------------
2009-08-17 13:56:28.993 2009-08-17 20:56:28.993
(1 row(s) affected)
-----------
7
(1 row(s) affected)
-----------------------
2009-08-17 15:00:01.000
(1 row(s) affected)
August 17, 2009 at 3:03 pm
And, after running those - what are your conclusions? Or did you just run them and paste the results here for me to interpret?
This was not an exercise for me to review the results, it was an exercise for you to review the results and come to a conclusion about what you are seeing.
I am also in the Pacific time zone - so I already know what the results are and have formed my own conclusions. I will share those conclusions with you after you share yours with us...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2009 at 3:43 pm
thank you,
My conclusion from the results is..Log shipping backup job, giving time stamp to the .trn files as UTC data format, which is 7 hrs a head than the pacific.
Can we change any settings to to reflect the actual time stamp instead of UTC on .trn file?
August 17, 2009 at 3:50 pm
Here is what I think. Even if you could, should you? This is one of those things that I think should be left alone. There is a reason the files are named with the UTC Date Time, it is to be sure the files are properly processed on the destination system and that the destination system could be in another timezone, even half way around the world. If it were using local time to create the files, there could be problems.
August 17, 2009 at 3:56 pm
Jeffrey Williams (8/17/2009)
And, after running those - what are your conclusions? Or did you just run them and paste the results here for me to interpret?This was not an exercise for me to review the results, it was an exercise for you to review the results and come to a conclusion about what you are seeing.
I am also in the Pacific time zone - so I already know what the results are and have formed my own conclusions. I will share those conclusions with you after you share yours with us...
Correct - and no, I don't think you can configure the timestamp for the files to be anything other than UTC. I believe someone earlier in this thread stated it was by design - but, I have not had the pleasure of actually setting up log shipping so I cannot say for sure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply