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.
| Author |
Topic |
|
hvdtol
Yak Posting Veteran
50 Posts |
 Posted - 2007-11-09 : 02:55:08
|
| Hello,I would like to know if somebody is familiar with underneath problem.We have a self-written procedure to restore transaction logs on a standby server.This is log shipping, but because we are using Ss 2000 sp4 standard edition, I wrote my own scripts. We are facing the problem that occasionally a transaction log backup is not registered in table msdb..backupmediafamily.For example. For database PJAVA every 15 minutes we make a transaction log backup.Underneath example show that the 19:15 hours backup is not registered, but it has been created on disk.select media_set_id, physical_device_name from backupmediafamily where physical_device_name like '%PJAVA%2007103119%' media_set_id physical_device_name1115347 H:\Mssql2000MSSQL\BACKUP\PJAVA_tlog_200710311900.TRN1115432 H:\Mssql2000MSSQL\BACKUP\PJAVA_tlog_200710311930.TRN1115475 H:\Mssql2000MSSQL\BACKUP\PJAVA_tlog_200710311945.TRNMissing record for backup made on 2007-10-31 19:15 hours !!!!No errors have occurred. The restore function on the Enterprise manager show on 19:15 a backup taken, but with a complete different file name, one from a different database.Hope somebody can help me.hvdtol |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-09 : 05:20:00
|
| Presumably you get an error if you attempt to restore PJAVA_tlog_200710311900.TRNand thenPJAVA_tlog_200710311930.TRNi.e. missing out the 19:15 TRN file?(It might be that the backup failed, and the 19:30 file is inclusive from 19:00, and thus the MSDB record is "complete" - I have my doubts but I thought it worth asking!)Alternative is to use the physical files on the disk as the basis for the "restore list" on the log-shipped-server?Kristen |
 |
|
|
hvdtol
Yak Posting Veteran
50 Posts |
Posted - 2007-11-09 : 07:26:20
|
| Kristen,Maybe you are right, i didn't tried that.The output I get on 19:15 hours is the following... which suggests it should be ok...[29] Database PJAVA: Transaction Log Backup...Destination: [H:\Mssql2000MSSQL\BACKUP\PJAVA_tlog_200710311915.TRN] ** Execution Time: 0 hrs, 0 mins, 1 secs **Actually i had hoped somebody would recognized this as i bug.Me however did not find it on Microsoft support site.hvdtol |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-10 : 03:42:15
|
| Maybe the MSDB database was doing some maintenance (purging old entries) when the backup ran.I got this today trying to do a backup whilst purging the MSDB:Server: Msg 1205, Level 13, State 54, Line 17Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.Server: Msg 3009, Level 16, State 2, xxxxxxxCould not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.The backup file was still created.Kristen |
|
|
|
hvdtol
Yak Posting Veteran
50 Posts |
Posted - 2007-11-10 : 09:41:43
|
| Thanks,I will come back to this.hvdtol |
|
|
|
hvdtol
Yak Posting Veteran
50 Posts |
Posted - 2007-11-13 : 02:50:37
|
| Kristen,I want to confirm that there were no locks on the database during the time the transaction log backup ran.Also there were no job running on msdb.The backup file was created 19:15, so i have to restore this one, followed by the 19:30 backup, because pysically they are created, but not administred.I still do not know what is going on.I want to make a support call to MS.thank anyway.hvdtol |
|
|
|
|
|
|
|
|
msdb table backupmediafamily missing backup entry