September 24, 2008 at 6:33 am
My bad....here is the output from the DBCC SQLPERF(logspace)
MEDICAL 17.99219 8.312527 0
September 24, 2008 at 6:37 am
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
September 24, 2008 at 7:13 am
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
September 24, 2008 at 7:52 am
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.).
September 24, 2008 at 7:53 am
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
September 24, 2008 at 1:10 pm
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
September 24, 2008 at 1:34 pm
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?
September 24, 2008 at 1:42 pm
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
September 24, 2008 at 1:51 pm
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?
September 24, 2008 at 3:50 pm
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?
September 25, 2008 at 9:55 am
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
September 25, 2008 at 10:31 am
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
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply