Re: Transaction Log Failure

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 07:53:11 -0500

A maintenance plan can not create a log file, only back up an existing
file...
THe first thing to try to do, is to get the log file back in the same
location as it originally existed, then bring up sql server.
If that can not be done. Then try
   copy the mdf file to another location , or copy it as another filename
( with SQL Server Down)
   Then bring up SQL Server and try
   sp_attach_single_file_DB newdbname, mdf filename

This will try to attach the data file and create a new log file.
If this does not work, you may simply have to restore from your most recent
backup. 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe K." <JoeK@discussions.microsoft.com> wrote in message
news:6933F018-DE9A-4AE8-96C5-7C159994842A@microsoft.com...
>
> The system I have is a SQL Server 2000 Enterprise edition with Windows
2003
> server with an Active/Active Cluster.  The disks reside on a SAN
environment.
>  One of the drives was dropped from the SAN that had the SQL Server
Cluster
> Resources in it's dependency list of drives.  SQL Server failed because of
> this. After SQL Server was restarted the database transaction logs started
> failing at this point.  The server was rebooted and I created new SQL
Server
> maintenance plan that would create a transaction log file every hour.
After
> server reboot and a new SQL Server maintenance plan the transcation log
job
> still aborted. Also checked the security on the output directory for
problems.
>
> Please help me resolve this database tranasction log problem.
>
> Thanks,
>
> SQL Server Errorlog Transaction Log Failure
> 2004-12-14 21:00:43.48 spid94    BackupDiskFile::OpenMedia: Backup device
> 'T:\MTAR\MTAR_tlog_200412142100.TRN' failed to open. Operating system
error =
> 32(The process cannot access the file because it is being used by another
> process.).
> 2004-12-14 21:00:43.51 backup    BACKUP failed to complete the command
> BACKUP LOG [STARPRD] TO  DISK = N'T:\MTAR\MTAR_tlog_200412142100.TRN' WITH
> INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
> 2004-12-14 21:30:27.62 backup    BACKUP failed to complete the command
> BACKUP LOG [STARPRD] TO  DISK = N'T:\MTAR\MTAR_tlog_200412142130.TRN' WITH
> INIT ,  NOUNLOAD ,  NOSKIP ,  STATS = 10,  NOFORMAT
> 2004-12-14 22:30:06.05 spid141   The backup data in
> 'T:\MTAR\MTAR_tlog_200412142230.TRN' is incorrectly formatted. Backups
cannot
> be appended, but existing backup sets may still be usable.
>

Relevant Pages

  • Re: Event 208 Error
    ... Something did a non-logged operation. ... is and either fix it or adjust your backup procedures to accomodate it. ... I support the Professional Association for SQL Server ... Below is what the maintenance plan ...
    (microsoft.public.sqlserver.server)
  • Re: Unable to attach the database
    ... BUT NEVER DELETE A LOG FILE! ... If you have a clean backup of the database, ... good understanding of SQL Server and how to work from this situation. ...
    (comp.databases.ms-sqlserver)
  • Re: DB Backup problem
    ... I support the Professional Association for SQL Server ... Ditch the Maintenance Plan Optimizations. ... it is the optimization step that is likely ... > 6:00pm - Transaction Log Backup ...
    (microsoft.public.sqlserver.server)
  • Re: suitable plan for Log backup
    ... transactions since the last log backup... ... To reduce the risk backup the log ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... after backup the size of log file does not decrease. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server Transaction Log
    ... BACKUP LOG databasename WITH TRUNCATE_ONLY ... SQL Server MVP ... > truncate_only option but no change in the log file size. ...
    (microsoft.public.sqlserver.server)