Transaction Log fails to backup

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Transaction Log fails to backup

Author

Topic

tsm
Starting Member

5 Posts

Posted - 2004-09-07 : 05:01:33

Hi

I have a maintenance plan that backs up transaction logs for 27 DBs every hour. However, a log fails to be created for one particular DB. I checked the maintenance plan log and found the following message:

"[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'G:\SQL_TRANS\I removed the DB name' failed, status = 112. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP LOG is terminating abnormally."

The DB I'm having trouble with is 5.65Gb, by far the largest one I have (the others range from 5Mb-2Gb in size).

Forgive me if I'm stating the obvious, but is the cause of this problem size related? If so, how do I go about resolving it?

My server is running Win2k sp4 with SQL 2K

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 05:05:16

How much free space does youyr G drive have available? It may that there just isn't room for the file that SQL is trying to create.

Raymond

tsm
Starting Member

5 Posts

Posted - 2004-09-07 : 05:35:52

I have 8.08Gb free. I also made a point of changing the location of my temp directory to a drive with more space. No joy sadly.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 05:39:52

Ok, so what entries are in the SQL log for the failure?

Raymond

tsm
Starting Member

5 Posts

Posted - 2004-09-07 : 06:04:55

Looked in the current SQL server log in the Management folder and found the following:

Message 1:
BACKUP failed to complete the command BACKUP LOG [DB name removed] TO DISK = N'G:\SQL_TRANS\DB name

removed_tlog_200409070909.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Message 2:
Internal I/O request 0x716E0CA0: Op: Write, pBuffer: 0x108D0000, Size: 983040, Position: 8684716544, UMS: Internal:

0x103, InternalHigh: 0x0, Offset: 0x5A64200, OffsetHigh: 0x2, m_buf: 0x108D0000, m_len: 983040, m_actualBytes: 0,

m_errcode: 112, BackupFile: G:\SQL_TRANS\DB name removed_tlog_200409070909.TRN

Message 3:
BackupMedium::ReportIoError: write failure on backup device 'G:\SQL_TRANS\DB name removed_tlog_200409070909.TRN'.

Operating system error 112(error not found).

I hope this is what you were driving at.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 06:08:51

It certainly is. Look like a dodgy OS file doesn't it? Delete the file and re-try the backup in QA using the BACKUP LOG command above. Check that the log isn't more than 8Gb first.

Raymond

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-07 : 06:26:39

Dos error 112 is not enough space.

-------
Moo. :)

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 06:30:52

That's what I was thinking. But if there's 8.08Gb free and the entire database is 5.65 it should fit, shouldn't it? Or does it need to have two copies of what it's backup up as it does it (one in tempdb for example)?

Raymond

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-07 : 06:32:18

'tis a mystery.

-------
Moo. :)

tsm
Starting Member

5 Posts

Posted - 2004-09-07 : 07:02:53

Sorry, I'm confused. No log file was created for the DB, and the name of the OS file isn't mentioned, so I'm not sure what I should be deleting.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-07 : 07:04:32

Ignore my delete post. It didn't create it. Seems like with the size of the database you have you can't backup the log as you haven't enough space.

Raymond

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-07 : 07:12:56

the 5.65 gb is database size or transaction log?

tsm
Starting Member

5 Posts

Posted - 2004-09-07 : 07:17:02

The 5.65Gb is the size of the database.