May 21, 2010 at 12:33 pm
SQL SERVER: SQL Server 2005 Enterprise 64 Bit
OS: Windows Server 2003 Enterprise x64 Edition
DRIVES:
C (OS): Local Disk 33.8GB Total / 12.4GB Free
D (Data): Local Disk 342GB / 165GB Free
L (Log Volume): Local Disk 67.8GB / 55.4GB Free
DB SPECS:
RECOVERY MODEL: BULK LOGGED
FILES: 2;
1 MDF @ 173GB on drive D
1 LDF @ 4.5GB on Drive L
PROBLEM:
For many days now (since April 24th to be exact) our DPM Backup Job for this DB has been failing on each attempt to perform a log file backup. The EXPRESS FULL backups work fine, it is only the Log file backups that fail. I changed the time setting on the log backup from every 15min to once every hour because the time it would take for one Log Backup try to fail, the next scheduled one would have started; it was taking about 20min for a Log Backup attempt to fail. I had hoped that maybe an increased duration between these would solve the issue and it did NOT.
Next I tried to manually run the same BACKUP LOG TO DISK command for the same DB and to the same location on the disk as DPM was trying and I did this as the SA from SSMS. The end result was the same failure/error at about the 21min mark.
The drive the log backup is being done to clearly has more then enough space for the log backup so the error msg I'm getting does not make sense. This same DB has been getting backed up via DPM for many months so far and without issue. There have been no changes to the DB or to the server or to the system between the last time this worked and when it started to fail. The only item of any significance that may be related to when this started to fail back on April 24th is a power failure to our building which according to our IT guys did occur about the same time (definitely the same day) as when the first Log backup failure occurred.
I'm tinkering with the idea of taking the DB off line (during non-business hours) and dropping the ‘LDF’ file and re-creating it anew in the event that the log file itself has somehow become corrupt. Other then this I'm out of ideas.
Thoughts? Suggestions?
Kindest Regards,
Just say No to Facebook!May 21, 2010 at 12:39 pm
I wouldn't drop an ldf because log backups are failing, that way heartache lies. Run a dbcc checkdb on the database.
Is the log growing if the log backups are failing? what does dbcc sqlperf(logspace) say?
Does a native SQL log backup work? I would say the error is with DPM (whatever that is) especially as it just happens to coincide with a power failure.
---------------------------------------------------------------------
May 21, 2010 at 12:47 pm
george sibbald (5/21/2010)
I wouldn't drop an ldf because log backups are failing, that way heartache lies. Run a dbcc checkdb on the database.Is the log growing if the log backups are failing? what does dbcc sqlperf(logspace) say?
Does a native SQL log backup work? I would say the error is with DPM (whatever that is) especially as it just happens to coincide with a power failure.
Thanks for replying.
Its definately not DPM. As I menitoned in my post I have tried to run the Log Backup manually from SSMS as the sa and got the same failure/error.
As for risk with dropping the log, at this point we aren't able to use it for any kind of restore because our log backups are failing so dropping it and starting it anew won't take away anything we have now except possability the problem itself if it should prove to be soimethings pecific with the log file.
I haven't used DBCC CHECKDB since the full DB backup works fine and it is just the Log file backups that are the problem.
Kindest Regards,
Just say No to Facebook!May 21, 2010 at 1:00 pm
oops, sorry missed the part about the native backup.
Still run a DBCC, it will highlight any problems in the log file.
so the log must be full and growing?
---------------------------------------------------------------------
May 21, 2010 at 5:25 pm
Don't drop the log file! There could be transactions that are not saved to the database. Try to query these DMVs to see if there are any active transactions: sys.dm_tran_locks, sys.dm_tran_session_transactions, sys.dm_tran_database_transactions.
Also, try to backup to the different location. Something else to check - check if antivirus is disabled for database files/log files and backups locations.
May 21, 2010 at 5:34 pm
Do you backup to the local disk or network location? Does SQL Server service account has access to the backup location?
May 21, 2010 at 6:07 pm
I have read through this multiple times now, and I don't see where you state what the actual error is. What error are you getting?
As others have said - don't try and delete the log file. That could put you in a much more problematic situation where you wouldn't be able to bring the database online.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 24, 2010 at 11:14 am
Jeffrey Williams-493691 (5/21/2010)
I have read through this multiple times now, and I don't see where you state what the actual error is. What error are you getting?As others have said - don't try and delete the log file. That could put you in a much more problematic situation where you wouldn't be able to bring the database online.
Jefferey - I did negelct to list the actual error messages before but I was trying to minimize the amount of text so as to not get too verbose. I have listed the 2 messages below as they are listed in the EVENT LOG. Know that I have somewhat resolved this since my post on Friday of last week and I will provide details on that next, after providing the below which I should have included orginally.
EVENT1
//////////////////////////////////////////////////////////////////////////////////////
Date 5/24/2010 8:16:12 AM
Log SQL Server (Current - 5/24/2010 11:59:00 AM)
Source spid112
Message
BackupMedium::ReportIoError: write failure on backup device 'L:\logs\DPM_SQL_PROTECT\MYDATABASE\MyLogFile.ldf\Backup\Current.log'. Operating system error 112(There is not enough space on the disk.).
EVENT 2
//////////////////////////////////////////////////////////////////////////////////////
Date 5/24/2010 8:16:12 AM
Log SQL Server (Current - 5/24/2010 11:59:00 AM)
Source spid91
Message
Write on "L:\logs\DPM_SQL_PROTECT\MYDATABASE\MyLogFile.ldf\Backup\Current.log" failed: 112(There is not enough space on the disk.)
Kindest Regards,
Just say No to Facebook!May 24, 2010 at 11:32 am
UPDATE - RESOLVED (somewhat)
I managed to correct this issue by switching our RECOVERY MODEL to use FULL (instead of BULK-LOGGED).
With some additional searching I found a post at the SQLMOnster website detailing why this can happeen, why one can get a backup of their TRANS LOG that is many times larger then the actual ldf file itself when you have your RECOVERY MODEL set to BULK-LOGGED.
The actual post text is:
If you use the bulk-logged recovery model and do anything that is minimally logged, what SQL Server does is just log the extents that are changed (that is, just which extents, NOT the contents of each extent). This allows the llogging to be very fast, and not use much space in the ldf file. However, when you do a log backup, the backup needs the contents of the extent(s) that were updated, not just which extent(s) they were (otherwise if you ever had to restore the log backup there would be no way to recover those extents). So as part of the log backup, SQL also copies the contents of each of those extents. Which can make the .trn file much larger than the .ldf file.
Over the weekend, when we have a small window we can use for downtime, after getting a full backup of the DB I switched the DB to SIMPLE, shrunk the file and reset its size and then changed the DB to use FULL instead of BULK-LOGGED. Since doing this the backups are now all working. The EXPRESS FULL backups that DPM does are larger then before but the Tran Log backups done by DPM are relatively small (i.e. 20-50MB so far) and because these are kept only between EXPRESS BACKUPS in DPM the end result is we use about the same amount of drive space as before and we aren't having any errors.
I have not determined why this started in the first place, what caused the backup of the Trans Log by DPM to jump to 72GB from the 1MB it had been averaging but that is next on the agenda now that the Backup fire has been put out.
The DB in question is the source for our primary application where I work (our acounting system) and based on past experiences it wouldn't suprise me if some change in that application (via some update or fix done about the same time) is what caused the sudden change in the size of the LDF file backup. I cehcked the history of the Trans Log backup in DPM and it was averaging 1MB at 15 minute backjup increments and on 4/24/2010 at around 830AM something changed or happened that caused the trans log backups to jump to 1-5MB and then a few hours later to between 10MB & 50MB.
If it truns out that this is not due to this application but something SQL Server realted I'll try to rememebr to follow-up in this post with the details so that in the future others will not have the same headaches I've had to go thru when this happens.
Thanks to everyone for replying and chipping in.
Kindest Regards,
Just say No to Facebook!May 24, 2010 at 4:05 pm
reindexing operations can cause this behaviour. Be warned if you switch to full recovery mode whatever the process was will no longer be minimally logged, so you will end up with a big log backup AND a big log file.
---------------------------------------------------------------------
May 24, 2010 at 4:31 pm
Also, think about getting a backup tool like sql backup from RedGate or Litespeed from Quest. They compresses the backups very well and have proven blessings whenever I ran into space issues with backup file sizes in native backups.
Neepa
May 25, 2010 at 11:05 am
george sibbald (5/24/2010)
reindexing operations can cause this behaviour. Be warned if you switch to full recovery mode whatever the process was will no longer be minimally logged, so you will end up with a big log backup AND a big log file.
George,
We have a majopr INDEX REBUILD that runs at 1205 AM on Sundays that is for the largest tables (i.e. 10+GB in size) and it has run for over a year and the DPM Backups we do have run the same way for almost as long. Up until the Saturday night when the power went out we never had this space issue come up and everything including the amount of drive space the Trans Log backup drive uses has remained constant. DPM appends to the backup of the Trans Log until the EXPRESS FULL whcih is done daily, at which point the next Trasn Log backup overwirtes the last one and then begins appending until the next EXPRESS backup.
Based on the above can you think of any connection with the power outage that would cause something odfd to happen like this massive jump in space needed to do a Trans Log backup? I did theorize that maybe the extra space came about because at least one express backup and many tarns log backups did not occur for the few hours the power was out Saturday night. However once then ext full backup was done the next day this should have reset itself and the trans log backups, I would think, would go back to being like they were before.
Thoughts?
BTW - I/we knew the switch to FULL from BULK-LOGGED would reuire more space but luckiy its not been so much more that we had to allot any additonal space to the drive where teh backups are maintained. This may be due to how DPM does backups. I'm no DPM expert but I believe it does not do backups (at least the FULL backups) like SQL Server does natively and so the extra drive space you;d get in a backup of a FULL db in SQL Server natively may not be true when using DPM.
Thanks for replying.
Kindest Regards,
Just say No to Facebook!May 25, 2010 at 2:42 pm
nothing comes to mind. Did checkdb come up clean?
---------------------------------------------------------------------
May 31, 2010 at 6:34 pm
Neepa (5/24/2010)
Also, think about getting a backup tool like sql backup from RedGate or Litespeed from Quest. They compresses the backups very well and have proven blessings whenever I ran into space issues with backup file sizes in native backups.Neepa
Thanks for replyingbut going with an alternate backup app is not an option. DPM may have its faults but it is great at handling every backup need we have from Email to Data and so I can't justifyto even mysefl, switching to some other product just because of this one unusual setback that may not be in anyway the fault of the backup application.
Thanks
Kindest Regards,
Just say No to Facebook!June 2, 2010 at 10:20 am
george sibbald (5/25/2010)
nothing comes to mind. Did checkdb come up clean?
George
I have not yet done this. Our DB is almost 200GB and we have only a few hours of official down time duriong the weekends to do this and so the only realistic way to do this is top restore a copy to a dev server which I can do but is time conusming. The dev/test server also is not near as powerful so it would take longer then normal as well.
I however have not closed this POst Notice yet and plan to followup once I have tried this. I can tell you that the probelm has not gone away yet because when the RECOVERY MODEL was changed back to BULK_LOGGED this past weekend as a test run, we found the Backups once again required an excessively large amount of space relative to the actuall size of teh Transaction Log file.
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply