sql backup files: maintence

Showing posts with label maintence. Show all posts

Wednesday, March 7, 2012

Backup of transaction log disappears

Hi All
I have this weird problem, and i cant find out what is happening.
I have a a database maintence plan that backsup the transaction log for one
of our databases. The job runs, i watch the trn file get created, then when
the job is complete, the trn file disappers, and the job fails becasue it
cannot open the log file to verify the backup.
I have no idea where the trn files are going.
here is the DBmaintance log file.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
[1] Database riverred: Transaction Log Backup...
Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
** Execution Time: 0 hrs, 0 mins, 11 secs **
[2] Database Riverred: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
SQL2000 with SP3a - in a w2k cluster environment
Any ideas?
thx in advanceHi
Run the backup from T-SQL and see if it also occurs. Check your retention
period of dumps in the maintenace plan.
Regards
Mike
"David" wrote:
> Hi All
> I have this weird problem, and i cant find out what is happening.
> I have a a database maintence plan that backsup the transaction log for one
> of our databases. The job runs, i watch the trn file get created, then when
> the job is complete, the trn file disappers, and the job fails becasue it
> cannot open the log file to verify the backup.
> I have no idea where the trn files are going.
> here is the DBmaintance log file.
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> [1] Database riverred: Transaction Log Backup...
> Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> ** Execution Time: 0 hrs, 0 mins, 11 secs **
> [2] Database Riverred: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> SQL2000 with SP3a - in a w2k cluster environment
> Any ideas?
> thx in advance
>|||We tried to run it via t-SQL and it didnt work, The retention period is set
for 2 days. I have tried changing the times as well.
D
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Run the backup from T-SQL and see if it also occurs. Check your retention
> period of dumps in the maintenace plan.
> Regards
> Mike
> "David" wrote:
> > Hi All
> >
> > I have this weird problem, and i cant find out what is happening.
> >
> > I have a a database maintence plan that backsup the transaction log for one
> > of our databases. The job runs, i watch the trn file get created, then when
> > the job is complete, the trn file disappers, and the job fails becasue it
> > cannot open the log file to verify the backup.
> >
> > I have no idea where the trn files are going.
> >
> > here is the DBmaintance log file.
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> > [1] Database riverred: Transaction Log Backup...
> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
> >
> > [2] Database Riverred: Verifying Backup...
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> > See the SQL Server error log for more details.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> > terminating abnormally.
> > Deleting old text reports... 0 file(s) deleted.
> >
> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >
> > SQL2000 with SP3a - in a w2k cluster environment
> >
> > Any ideas?
> >
> > thx in advance
> >|||"David" wrote:
> Hi All
> I have this weird problem, and i cant find out what is happening.
> I have a a database maintence plan that backsup the transaction log for one
> of our databases. The job runs, i watch the trn file get created, then when
> the job is complete, the trn file disappers, and the job fails becasue it
> cannot open the log file to verify the backup.
> I have no idea where the trn files are going.
> here is the DBmaintance log file.
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> [1] Database riverred: Transaction Log Backup...
> Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> ** Execution Time: 0 hrs, 0 mins, 11 secs **
> [2] Database Riverred: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> SQL2000 with SP3a - in a w2k cluster environment
> Any ideas?
> thx in advance
>|||We just moved the log file backup to a different drive. and it works.
Now i am scared about why those trn files were disappearing on the other
drive?
any ideas?
"David" wrote:
> We tried to run it via t-SQL and it didnt work, The retention period is set
> for 2 days. I have tried changing the times as well.
> D
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > Run the backup from T-SQL and see if it also occurs. Check your retention
> > period of dumps in the maintenace plan.
> >
> > Regards
> > Mike
> >
> > "David" wrote:
> >
> > > Hi All
> > >
> > > I have this weird problem, and i cant find out what is happening.
> > >
> > > I have a a database maintence plan that backsup the transaction log for one
> > > of our databases. The job runs, i watch the trn file get created, then when
> > > the job is complete, the trn file disappers, and the job fails becasue it
> > > cannot open the log file to verify the backup.
> > >
> > > I have no idea where the trn files are going.
> > >
> > > here is the DBmaintance log file.
> > >
> > > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> > > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> > > [1] Database riverred: Transaction Log Backup...
> > > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> > >
> > > ** Execution Time: 0 hrs, 0 mins, 11 secs **
> > >
> > > [2] Database Riverred: Verifying Backup...
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> > > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> > > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> > > See the SQL Server error log for more details.
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> > > terminating abnormally.
> > > Deleting old text reports... 0 file(s) deleted.
> > >
> > > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> > > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> > >
> > > SQL2000 with SP3a - in a w2k cluster environment
> > >
> > > Any ideas?
> > >
> > > thx in advance
> > >|||What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages did the
backup command return?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
> We tried to run it via t-SQL and it didnt work, The retention period is set
> for 2 days. I have tried changing the times as well.
> D
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Run the backup from T-SQL and see if it also occurs. Check your retention
>> period of dumps in the maintenace plan.
>> Regards
>> Mike
>> "David" wrote:
>> > Hi All
>> >
>> > I have this weird problem, and i cant find out what is happening.
>> >
>> > I have a a database maintence plan that backsup the transaction log for one
>> > of our databases. The job runs, i watch the trn file get created, then when
>> > the job is complete, the trn file disappers, and the job fails becasue it
>> > cannot open the log file to verify the backup.
>> >
>> > I have no idea where the trn files are going.
>> >
>> > here is the DBmaintance log file.
>> >
>> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
>> > [1] Database riverred: Transaction Log Backup...
>> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> >
>> > [2] Database Riverred: Verifying Backup...
>> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
>> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
>> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
>> > See the SQL Server error log for more details.
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> > terminating abnormally.
>> > Deleting old text reports... 0 file(s) deleted.
>> >
>> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >
>> > SQL2000 with SP3a - in a w2k cluster environment
>> >
>> > Any ideas?
>> >
>> > thx in advance
>> >|||Hi
There might be something that is deleting the files. It might be a
housekeeping job that someone wrote, setup and forgot to stop.
Try giving the SQL Service account only read/write access to the directory,
and everyone else, read only. Run the backups and see if they survive.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David" <David@.discussions.microsoft.com> wrote in message
news:A31659E5-BFED-4516-B8EA-89C1D96B9691@.microsoft.com...
> We just moved the log file backup to a different drive. and it works.
> Now i am scared about why those trn files were disappearing on the other
> drive?
> any ideas?
> "David" wrote:
>> We tried to run it via t-SQL and it didnt work, The retention period is
>> set
>> for 2 days. I have tried changing the times as well.
>> D
>> "Mike Epprecht (SQL MVP)" wrote:
>> > Hi
>> >
>> > Run the backup from T-SQL and see if it also occurs. Check your
>> > retention
>> > period of dumps in the maintenace plan.
>> >
>> > Regards
>> > Mike
>> >
>> > "David" wrote:
>> >
>> > > Hi All
>> > >
>> > > I have this weird problem, and i cant find out what is happening.
>> > >
>> > > I have a a database maintence plan that backsup the transaction log
>> > > for one
>> > > of our databases. The job runs, i watch the trn file get created,
>> > > then when
>> > > the job is complete, the trn file disappers, and the job fails
>> > > becasue it
>> > > cannot open the log file to verify the backup.
>> > >
>> > > I have no idea where the trn files are going.
>> > >
>> > > here is the DBmaintance log file.
>> > >
>> > > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> > > Server
>> > > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> > > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03
>> > > AM
>> > > [1] Database riverred: Transaction Log Backup...
>> > > Destination: [G:\riverred\Log File
>> > > Backups\riverred_tlog_200504080727.TRN]
>> > >
>> > > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> > >
>> > > [2] Database Riverred: Verifying Backup...
>> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201:
>> > > [Microsoft][ODBC SQL
>> > > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log
>> > > File
>> > > Backups\reiverred_tlog_200504080727.TRN'. Device error or device
>> > > off-line.
>> > > See the SQL Server error log for more details.
>> > > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> > > terminating abnormally.
>> > > Deleting old text reports... 0 file(s) deleted.
>> > >
>> > > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14
>> > > AM
>> > > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> > >
>> > > SQL2000 with SP3a - in a w2k cluster environment
>> > >
>> > > Any ideas?
>> > >
>> > > thx in advance
>> > >|||That error doesn't exists in the sysmessages table. Can you post the error test? And the BACKUP LOG
command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:388D6FDC-AA7F-4244-8C83-0D1832D185E5@.microsoft.com...
> Sorry, didnt work meant, i recieved the error message, Error, 22029
>
> "Tibor Karaszi" wrote:
>> What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages did
>> the
>> backup command return?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
>> > We tried to run it via t-SQL and it didnt work, The retention period is set
>> > for 2 days. I have tried changing the times as well.
>> >
>> > D
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> Run the backup from T-SQL and see if it also occurs. Check your retention
>> >> period of dumps in the maintenace plan.
>> >>
>> >> Regards
>> >> Mike
>> >>
>> >> "David" wrote:
>> >>
>> >> > Hi All
>> >> >
>> >> > I have this weird problem, and i cant find out what is happening.
>> >> >
>> >> > I have a a database maintence plan that backsup the transaction log for one
>> >> > of our databases. The job runs, i watch the trn file get created, then when
>> >> > the job is complete, the trn file disappers, and the job fails becasue it
>> >> > cannot open the log file to verify the backup.
>> >> >
>> >> > I have no idea where the trn files are going.
>> >> >
>> >> > here is the DBmaintance log file.
>> >> >
>> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> >> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> >> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
>> >> > [1] Database riverred: Transaction Log Backup...
>> >> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> >> >
>> >> > [2] Database Riverred: Verifying Backup...
>> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
>> >> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
>> >> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
>> >> > See the SQL Server error log for more details.
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> >> > terminating abnormally.
>> >> > Deleting old text reports... 0 file(s) deleted.
>> >> >
>> >> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
>> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> >
>> >> > SQL2000 with SP3a - in a w2k cluster environment
>> >> >
>> >> > Any ideas?
>> >> >
>> >> > thx in advance
>> >> >
>>|||I moved the backup destination to another drive, then the maitanence plan
started to work, after 1 day of working properly i am exhibiting the same
problem.
I can see the trn file get created with 0kb then i see the size of the file
appears then the whole trn file disappears.
I tried making a new maitanence plan as well, and get the same problem.
error messages for the job
18204 :
BackupDiskFile::OpenMedia: Backup device
'H:\SQLServer\redriver_tlog_200504152131.TRN' failed to open. Operating
system error = 32(The process cannot access the file because it is being used
by another process.).
3041 :
BACKUP failed to complete the command BACKUP LOG [AbsolutePoker] TO DISK =N'H:\SQLServer\redriver_tlog_200504152131.TRN' WITH INIT , NOUNLOAD ,
NOSKIP , STATS = 10, NOFORMAT
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'redriver Log Backup'' (0x6A757F003EBDCD4DB810F28061D0AAE2) - Status: Failed
- Invoked on: 2005-04-15 21:31:50 - Message: The job failed. The Job was
invoked by User domain\administrator. The last step to run was step 1 (Step
1).
Maintanence plan history:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
Server Driver][SQL Server]Cannot open backup device
'H:\SQLServer\redriver_tlog_200504152131.TRN'. Device error or device
off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
"Tibor Karaszi" wrote:
> That error doesn't exists in the sysmessages table. Can you post the error test? And the BACKUP LOG
> command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:388D6FDC-AA7F-4244-8C83-0D1832D185E5@.microsoft.com...
> > Sorry, didnt work meant, i recieved the error message, Error, 22029
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages did
> >> the
> >> backup command return?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "David" <David@.discussions.microsoft.com> wrote in message
> >> news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
> >> > We tried to run it via t-SQL and it didnt work, The retention period is set
> >> > for 2 days. I have tried changing the times as well.
> >> >
> >> > D
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> >> Hi
> >> >>
> >> >> Run the backup from T-SQL and see if it also occurs. Check your retention
> >> >> period of dumps in the maintenace plan.
> >> >>
> >> >> Regards
> >> >> Mike
> >> >>
> >> >> "David" wrote:
> >> >>
> >> >> > Hi All
> >> >> >
> >> >> > I have this weird problem, and i cant find out what is happening.
> >> >> >
> >> >> > I have a a database maintence plan that backsup the transaction log for one
> >> >> > of our databases. The job runs, i watch the trn file get created, then when
> >> >> > the job is complete, the trn file disappers, and the job fails becasue it
> >> >> > cannot open the log file to verify the backup.
> >> >> >
> >> >> > I have no idea where the trn files are going.
> >> >> >
> >> >> > here is the DBmaintance log file.
> >> >> >
> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> >> >> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
> >> >> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
> >> >> > [1] Database riverred: Transaction Log Backup...
> >> >> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
> >> >> >
> >> >> > [2] Database Riverred: Verifying Backup...
> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> >> >> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
> >> >> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
> >> >> > See the SQL Server error log for more details.
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> >> >> > terminating abnormally.
> >> >> > Deleting old text reports... 0 file(s) deleted.
> >> >> >
> >> >> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> >> >
> >> >> > SQL2000 with SP3a - in a w2k cluster environment
> >> >> >
> >> >> > Any ideas?
> >> >> >
> >> >> > thx in advance
> >> >> >
> >>
> >>
> >>
>
>|||I have a feeling that this is an OS, possibly a permissions issue. I'd start by scheduling a regular
Agent job to do the same and see if it behaves the same. I'd also look in eventlog for all possibly
related errors. Could be permissions, quota and other stuff...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:F49567EC-9741-4A24-8BD1-CCE0EFD4DDF7@.microsoft.com...
>I moved the backup destination to another drive, then the maitanence plan
> started to work, after 1 day of working properly i am exhibiting the same
> problem.
> I can see the trn file get created with 0kb then i see the size of the file
> appears then the whole trn file disappears.
> I tried making a new maitanence plan as well, and get the same problem.
> error messages for the job
> 18204 :
> BackupDiskFile::OpenMedia: Backup device
> 'H:\SQLServer\redriver_tlog_200504152131.TRN' failed to open. Operating
> system error = 32(The process cannot access the file because it is being used
> by another process.).
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [AbsolutePoker] TO DISK => N'H:\SQLServer\redriver_tlog_200504152131.TRN' WITH INIT , NOUNLOAD ,
> NOSKIP , STATS = 10, NOFORMAT
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'redriver Log Backup'' (0x6A757F003EBDCD4DB810F28061D0AAE2) - Status: Failed
> - Invoked on: 2005-04-15 21:31:50 - Message: The job failed. The Job was
> invoked by User domain\administrator. The last step to run was step 1 (Step
> 1).
> Maintanence plan history:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Cannot open backup device
> 'H:\SQLServer\redriver_tlog_200504152131.TRN'. Device error or device
> off-line. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
>
>
>
>
> "Tibor Karaszi" wrote:
>> That error doesn't exists in the sysmessages table. Can you post the error test? And the BACKUP
>> LOG
>> command?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "David" <David@.discussions.microsoft.com> wrote in message
>> news:388D6FDC-AA7F-4244-8C83-0D1832D185E5@.microsoft.com...
>> > Sorry, didnt work meant, i recieved the error message, Error, 22029
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What does "didn't work" mean? Can you post the BACKUP LOG command? Also, what error messages
>> >> did
>> >> the
>> >> backup command return?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "David" <David@.discussions.microsoft.com> wrote in message
>> >> news:F142C1A1-26D7-4597-93A0-0635C61861B8@.microsoft.com...
>> >> > We tried to run it via t-SQL and it didnt work, The retention period is set
>> >> > for 2 days. I have tried changing the times as well.
>> >> >
>> >> > D
>> >> >
>> >> > "Mike Epprecht (SQL MVP)" wrote:
>> >> >
>> >> >> Hi
>> >> >>
>> >> >> Run the backup from T-SQL and see if it also occurs. Check your retention
>> >> >> period of dumps in the maintenace plan.
>> >> >>
>> >> >> Regards
>> >> >> Mike
>> >> >>
>> >> >> "David" wrote:
>> >> >>
>> >> >> > Hi All
>> >> >> >
>> >> >> > I have this weird problem, and i cant find out what is happening.
>> >> >> >
>> >> >> > I have a a database maintence plan that backsup the transaction log for one
>> >> >> > of our databases. The job runs, i watch the trn file get created, then when
>> >> >> > the job is complete, the trn file disappers, and the job fails becasue it
>> >> >> > cannot open the log file to verify the backup.
>> >> >> >
>> >> >> > I have no idea where the trn files are going.
>> >> >> >
>> >> >> > here is the DBmaintance log file.
>> >> >> >
>> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
>> >> >> > 'VIRTUAL1\SHAREDSQL' as '****\****' (trusted)
>> >> >> > Starting maintenance plan 'riverred Log Backup' on 04/08/2005 7:27:03 AM
>> >> >> > [1] Database riverred: Transaction Log Backup...
>> >> >> > Destination: [G:\riverred\Log File Backups\riverred_tlog_200504080727.TRN]
>> >> >> >
>> >> >> > ** Execution Time: 0 hrs, 0 mins, 11 secs **
>> >> >> >
>> >> >> > [2] Database Riverred: Verifying Backup...
>> >> >> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL
>> >> >> > Server Driver][SQL Server]Cannot open backup device 'G:\riverred\Log File
>> >> >> > Backups\reiverred_tlog_200504080727.TRN'. Device error or device off-line.
>> >> >> > See the SQL Server error log for more details.
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
>> >> >> > terminating abnormally.
>> >> >> > Deleting old text reports... 0 file(s) deleted.
>> >> >> >
>> >> >> > End of maintenance plan 'Riverred Log Backup' on 04/08/2005 7:27:14 AM
>> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> >> >
>> >> >> > SQL2000 with SP3a - in a w2k cluster environment
>> >> >> >
>> >> >> > Any ideas?
>> >> >> >
>> >> >> > thx in advance
>> >> >> >
>> >>
>> >>
>> >>
>>

Backup of transaction log disappears

Saturday, February 25, 2012

Backup Oddity

A client needed to put in there own backup (without using
the Database Maintence Plan) I won't go into the reasons
but it was correct.
Anyway they asked us to find a safe way of deleting the
internal records and the files, hence me.
I have found something pretty odd. The sp_dropdevice does
not delete the records in the msdb tables backupset or
backupmediafamily why is this the case ?
Thanks
JYou would have to ask the MS programmers that wrote sp_dropdevice why it
doesn't delete the backup history. It might have been a conscious decision,
it might have been that they just didn't do this when backup history tables
changed structure between 6.5 and 7.0. It is always difficult to answer
"why" question if you weren't present MS design. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:ed7d01c40c36$12470cb0$a401280a@.phx.gbl...
> A client needed to put in there own backup (without using
> the Database Maintence Plan) I won't go into the reasons
> but it was correct.
> Anyway they asked us to find a safe way of deleting the
> internal records and the files, hence me.
> I have found something pretty odd. The sp_dropdevice does
> not delete the records in the msdb tables backupset or
> backupmediafamily why is this the case ?
> Thanks
> J|||Thanks Tibor,
Now I don't need to trawl though BOL to see if I have
missed anything :-D
J

>--Original Message--
>You would have to ask the MS programmers that wrote
sp_dropdevice why it
>doesn't delete the backup history. It might have been a
conscious decision,
>it might have been that they just didn't do this when
backup history tables
>changed structure between 6.5 and 7.0. It is always
difficult to answer
>"why" question if you weren't present MS design. :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ed7d01c40c36$12470cb0$a401280a@.phx.gbl...
using
does
>
>.
>|||A tip, Julie:
Read the source code for the procedure in question. Reading the source code
for sp_dropdevice, I see that there is no code in there to remove backup
history, so there is no need to look any further. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:ecfd01c40c39$8ee0e8b0$a301280a@.phx.gbl...
> Thanks Tibor,
> Now I don't need to trawl though BOL to see if I have
> missed anything :-D
> J
>
>
> sp_dropdevice why it
> conscious decision,
> backup history tables
> difficult to answer
> message
> using
> does|||Again thanks Tibor,
J

>--Original Message--
>A tip, Julie:
>Read the source code for the procedure in question.
Reading the source code
>for sp_dropdevice, I see that there is no code in there
to remove backup
>history, so there is no need to look any further. :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ecfd01c40c39$8ee0e8b0$a301280a@.phx.gbl...
reasons
the
or
>
>.
>

Friday, February 24, 2012

Backup Maintence Plan Fails

I get the error every day. i've checked everything i know
of to make sure that nothing including the backup job is
accessing the database. This job has run for years and is
suddenly failing for the last few weeks. Any help would be
appreciated. Win2k Server SQL2000 with SP3.
Lamar
App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
3041 : BACKUP failed to complete the command BACKUP
DATABASE [DMD1] TO DISK = N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
18210 : BackupMedium::ReportIoError: write failure on
backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
Operating system error 33(The process cannot access the
file because another process has locked a portion of the
file.). "Seems like some other program is using and locking the backup file. You have to hunt that down.
Could be some anti-virus program, for instance. I thinks that www.sysinternals has tools for this.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lamar Mrris" <LamarMorris@.ti.com> wrote in message news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
> I get the error every day. i've checked everything i know
> of to make sure that nothing including the backup job is
> accessing the database. This job has run for years and is
> suddenly failing for the last few weeks. Any help would be
> appreciated. Win2k Server SQL2000 with SP3.
> Lamar
> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> 3041 : BACKUP failed to complete the command BACKUP
> DATABASE [DMD1] TO DISK => N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> 18210 : BackupMedium::ReportIoError: write failure on
> backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
> Operating system error 33(The process cannot access the
> file because another process has locked a portion of the
> file.). "
>
>|||... or maybe the backup file is being backed up -- to tape, for example.
Whatever the case, checking with the system admin should provide some help,
at least eliminate some candidate problem sources.
Quentin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u3gxJDWnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Seems like some other program is using and locking the backup file. You
have to hunt that down.
> Could be some anti-virus program, for instance. I thinks that
www.sysinternals has tools for this.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lamar Mrris" <LamarMorris@.ti.com> wrote in message
news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
> > I get the error every day. i've checked everything i know
> > of to make sure that nothing including the backup job is
> > accessing the database. This job has run for years and is
> > suddenly failing for the last few weeks. Any help would be
> > appreciated. Win2k Server SQL2000 with SP3.
> >
> > Lamar
> > App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> > 3041 : BACKUP failed to complete the command BACKUP
> > DATABASE [DMD1] TO DISK => > N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
> > NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
> > App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
> > 18210 : BackupMedium::ReportIoError: write failure on
> > backup device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
> > Operating system error 33(The process cannot access the
> > file because another process has locked a portion of the
> > file.). "
> >
> >
> >
>|||Thanks for the input, i've already excluded the whole directory from
Virus scanning or realtime protection.
Lamar
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||To add to what Tibor said, you can get a utility called
nthandle.exe from www.sysinternals.com.
To help you determine what process is holding that backup
file, you can modify your backup job to add a step before
the backup step. In this step, run:
path>nthandle.exe DMD1_db
and make sure that you specify the output file for this
step.
When the backup job fails, you can then review the output
file for the first step to identify the offending process
that was holding your backup file.
My guess would be: it's your tape backup process if you
are running a tape backup process to backup the files on
the disk.
Linchi
>--Original Message--
>Seems like some other program is using and locking the
backup file. You have to hunt that down.
>Could be some anti-virus program, for instance. I thinks
that www.sysinternals has tools for this.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Lamar Mrris" <LamarMorris@.ti.com> wrote in message
news:064701c39d5b$2d38fc80$a001280a@.phx.gbl...
>> I get the error every day. i've checked everything i
know
>> of to make sure that nothing including the backup job is
>> accessing the database. This job has run for years and
is
>> suddenly failing for the last few weeks. Any help would
be
>> appreciated. Win2k Server SQL2000 with SP3.
>> Lamar
>> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
>> 3041 : BACKUP failed to complete the command BACKUP
>> DATABASE [DMD1] TO DISK =>> N'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK' WITH INIT ,
>> NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT "
>> App: E 'Wed Oct 01 00:24:26 2003': MSSQLSERVER - "
>> 18210 : BackupMedium::ReportIoError: write failure on
>> backup
device 'd:\MSSQL\BACKUP\DMD1_db_200309302200.BAK'.
>> Operating system error 33(The process cannot access the
>> file because another process has locked a portion of the
>> file.). "
>>
>
>.
>

Wednesday, March 7, 2012

Backup of transaction log disappears

Backup of transaction log disappears

Backup of transaction log disappears

Saturday, February 25, 2012

Backup Oddity

Friday, February 24, 2012

Backup Maintence Plan Fails

sql backup files

Blog Archive

About Me