time lag in TRN file names.

Question

time lag in TRN file names.

shen-dest

SSCrazy

Points: 2498
More actions
April 20, 2010 at 5:56 am

#231875

Hi,
In our production system TRN file names are being created as "dbname_yyyymmddhh24miss.trn".
files names used be created with 2 hour earlier names and now after Daylight saving time change our TRN files happen to be 4 hours
back of the current time. this sometimes confuses us about which file has been coppied or restored to Standby databases.
Is there any way to overcome this situation.
thanks.
sample file name
dbname_20100420115115.trn
current datetime 201004201453
Regards,
MShenel

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Couple of questions as it sounds like you are running log shipping. How did you setup log shipping and what is your time zone?

shen-dest SSCrazy Points: 2498 More actions · Answer 2

Time Zone is GMT+2 and LogShipping has been configured via SSMS with default next-next setup.

Regards,
MShenel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

shen-dest (4/20/2010)
Time Zone is GMT+2 and LogShipping has been configured via SSMS with default next-next setup.

Thank you.

Based on this:

dbname_20100420115115.trn

current datetime 201004201453

The time difference is 3 hours, not 4 hours. This fits nicely into what is happening. The datetime stamp you are seeing is UTC, prior to daylight savings time, it was two hours behind, and since the start of daylight savings time it is now 3 hours behind.

I don't know if there is a way to change this.

shen-dest SSCrazy Points: 2498 More actions · Answer 4

thanks,

You are right time difference is 3 hours. I have configured LS for a new db, no change it still uses UTC time as you said.

Can UTC be changed somewhere? or is it only available while installing SQL Server?

Regards,
MShenel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Not that I am aware. I am not familiar with log shipping as I have not worked in an environment where it is used at this time.

shen-dest SSCrazy Points: 2498 More actions · Answer 6

I see ,thank you. I will try to figure it out.

Regards,
MShenel

WayneS SSC Guru Points: 95460 More actions · Answer 7

If you're using a monitor server, the "Transaction Log Shipping Status" report (available in SSMS, right-click the server, select reports | Standard Reports | Transaction Log Shipping Status), and you can see the last file(s) backed up, copied, and restored. Will this handle your need to determine which files have been copied/restored to the standby database?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 736211 More actions · Answer 8

In terms of confusion, is this just because of restores? Or is there something else?

You ought to be copying all these to another location. You can use the RESTORE HEADERONLY to check LSNs and build a chain of sorts. A script to do that ought to be fairly easy to write, load the LSNs into a table, find gaps.

shen-dest SSCrazy Points: 2498 More actions · Answer 9

We have 3 Standby databases,2 of them are for disaster recovery one in same location and one at remote.

the other one is used for reporting. For the reporting database sometimes we have to do manuel operations like renaming trn files to stop recovering, and open DB in read only mode.Finding the right file sometimes takes time.

For monitoring we have in-house scripts mailing us about the status of LS with the below information.

Status Primary Server-Secondary ServerTime Since Last Backup Time Since Last Copy Time Since Last Restore

thank you all

Regards,
MShenel