Transaction Log and Recovery Model

Question

Post reply

Transaction Log and Recovery Model

Viewing 12 posts - 16 through 26 (of 26 total)

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

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 1

My bad....here is the output from the DBCC SQLPERF(logspace)

MEDICAL17.992198.3125270

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 2

Also I see this is the error logs

BACKUP failed to complete the command BACKUP LOG [MEDICAL] TO DISK = N'F:\MSSQL\BACKUP\MEDICAL\MEDICAL_tlog_200809240730.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You may want to change the schedule for the log backup so that they run right through the night (rather than stopping at 9pm).

The back ups jobs are sucesseding,

BACKUP failed to complete the command BACKUP LOG [MEDICAL] TO DISK

Not if they're giving that message they're not. Is there a reason associated with that error? There should be another line in the error log that gives more info.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 4

They are showing that they've suceeded in the jobs, but my error messages are quite different. Here is what the additional error log says, but it's not helpful because I alreayd know the space is gone.

What I'm seeing is that the database backs up fine, then we are getting this error

BackupMedium::ReportIoError: write failure on backup device 'F:\MSSQL\BACKUP\MEDICAL\MEDICAL_tlog_200809240630.TRN'. Operating system error 112(There is not enough space on the disk.).

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 5

There is also this error message

Internal I/O request 0x4F978B38: Op: Write, pBuffer: 0x08830000, Size: 983040, Position: 14789678592, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x71889600, OffsetHigh: 0x3, m_buf: 0x08830000, m_len: 983040, m_actualBytes: 0, m_errcode: 112, BackupFile: F:\MSSQL\BACKUP\MEDICAL\MEDICAL_tlog_200809240630.TRN

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

From what I can see from that, your log backups are failing due to insufficient space available on disk. How large is the log and how much space is available on the disk you're backing up to?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 7

The log file that just now backed up at 3:30 is 18kb and the drive is 30GB....

what I just noticed was that the .trn files are not located on the drive that keeps filing up, it's the .ldf files? But that drive has 80GB, and the last Database file from today at 3:30 is 23kb?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It's definitely the backup drive that the errors are complaining about.

write failure on backup device 'F:\MSSQL\BACKUP\MEDICAL\MEDICAL_tlog_200809240630.TRN'. Operating system error 112(There is not enough space on the disk.

Can you monitor the space on that drive and the size of the log say at half hour intervals for a couple of days?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 9

Yes, I can change it. Would it be a better solution for me to also have them back up all day instead of the 6-9 window? Not sure as to why this was set like this to begin with?

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 10

I have set the backups to every half hour for the rest of the night and I'm also profiling the DB....maybe I'll catch something there?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Molly Cary (9/24/2008)
Yes, I can change it. Would it be a better solution for me to also have them back up all day instead of the 6-9 window? Not sure as to why this was set like this to begin with?

It's probably better, especially if there's activity on the DB overnight.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Molly Weierbach SSCommitted Points: 1530 More actions · Answer 12

I had no problems last night and the log file didn't fill up and all the backups ran as normal. I'm going to continue to monitor this but I think this was the major issue was not having the transaction logs back up over night. Thanks for all the help