January 26, 2005 at 2:02 pm
We have a SQL 2K DB running on Win 2K that is about 45GB the DB is in Bulk-Logging mode. We have started running some jobs that cause the Transaction log to grow quite large 20-30GB. We have started seeing some problems where the Transaction log backup fails. This happens during a nightly Maint plan backup and run through EM. So far this week the Full DB backup and the Trans log backup have failed through the maint Plan with the same errors that I saw last week for the Trans log. I have attached the complete errors below the body. 'Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).' The O/S error looks like the file is locked, but I do not know of anything running that would be locking the file. I looked around here and some other sites and I could not find anything that seemed to help me so I want to post here to see if anyone can help out.
There are a few more twists to this that may help out.
1. We are using Windows folder compression to save space. I saw a few things around concerns with this but nothing that specifically looked like my problem
2. I had odd reaction from the Transaction log today. Yesterday it was about 35GB when I left. Today it was only 6.5GB. The backup failed last night and there was no evidence in the Error log of a backup truncate only. I do not know why the log shrunkSo now that the log was only 6GB I decided to try and back it up. It ran for over 2 hours and according to the status bar was probalby 80%+ finished before it kicked out the same error. What was odd was the last time I checked the size of the backup file it was 41.8GB. that is almost 7 times the size of the current trans log. How can that be? Was the space that seemed to disappear from the Trans log still out there somewhere? To run it I selected RC'ed the database=>Backup=>transaction log=> removed the destination(was for a BAK file) => added a destination with a new name and TRN extension => set append to media => run. I was under the impression that if yu append to a new destination, you are not really appending to anything but a blank file. I checked the size of the trans log datafile on the server and it showed up as 6.5GB. Does anyone have any idea what is going on here? This has me stumped so any suggestions are appreciated.
Thanks in advance
TJP8
---------------Transaction Log Backup errors
2005-01-25 23:24:56.85 spid57 BackupMedium::ReportIoError: write failure on backup device 'E:\SQL_Backups\Transaction_Logs\HNAC2C\HNAC2C_tlog_200501252219.TRN'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).
2005-01-25 23:24:56.85 spid57 Internal I/O request 0x7F36F3F8: Op: Write, pBuffer: 0x07EC0000, Size: 983040, Position: 44529105408, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x5E243A00, OffsetHigh: 0xA, m_buf: 0x07EC0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile: E:\SQL_Backups\Transaction_Logs\HNAC2C\HNAC2C_tlog_200501252219.TRN
2005-01-25 23:24:56.85 backup BACKUP failed to complete the command BACKUP LOG [HNAC2C] TO DISK = N'E:\SQL_Backups\Transaction_Logs\HNAC2C\HNAC2C_tlog_200501252219.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
----------------Full Backup errors
2005-01-26 00:23:26.53 spid58 BackupMedium::ReportIoError: write failure on backup device 'E:\SQL_Backups\HNAC2C\HNAC2C_db_200501252325.BAK'. Operating system error 33(The process cannot access the file because another process has locked a portion of the file.).
2005-01-26 00:23:26.53 spid58 Internal I/O request 0x7F2ED138: Op: Write, pBuffer: 0x07EC0000, Size: 983040, Position: 40178358784, UMS: Internal: 0x103, InternalHigh: 0x0, Offset: 0x5AD11A00, OffsetHigh: 0x9, m_buf: 0x07EC0000, m_len: 983040, m_actualBytes: 0, m_errcode: 33, BackupFile: E:\SQL_Backups\HNAC2C\HNAC2C_db_200501252325.BAK
2005-01-26 00:23:26.53 backup BACKUP failed to complete the command BACKUP DATABASE [HNAC2C] TO DISK = N'E:\SQL_Backups\HNAC2C\HNAC2C_db_200501252325.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
January 27, 2005 at 2:50 am
Hi,
you can try with adding startup parametar -T3111 on your SQL server service (you must restart SQL server after adding this)
January 27, 2005 at 6:27 am
what will this do in this situation? I have never used this flag, and doing a quick search about all I found on this was http://support.microsoft.com/kb/297104/EN-US/
The error mentioned in this support note is different from what I am getting, plus we are already at SP3 so this bug should be irrelevant. If you have further information or experience with this flag I would appreciate you sharing it.
Thanks
TJP8
January 27, 2005 at 9:52 am
There may be 2 issues here. First I'd go back to BOL and check out everything I could on transaction logging. If recoverabilty is not an issue then 'Simple' is probably best. Second, if there is disk space I'd uncompress the disk folder for your backups. Maybe it's the OS fightingh SQL for the file. By this I mean your creating it and it's compressing it (or trying to) at the same time.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 27, 2005 at 2:46 pm
To Give those of you reading this a followup. I backed up the Trans log outside of Windows compression and it succeeded. I then backed up the database inside compression and it also succeeded, so it appears that there may be an issue with the large Trans logs and windows compression. I have never seen the full backup have a problem when the Trans log backup is working. Thanks slavicajis and Rudy for your replies.
Even though I seem to have found my past this problem, I would appreciate it if anyone offer up an explanation on these 2 issues
1. The log file was 8GB yet it took 50GB to back it up. Why?
2. Why would compression cause problems on larger files and not the rest? Any thoughts or experience with backing up SQL Server into compressed folders would be appreciated as well.
Thanks
TJP8
January 27, 2005 at 3:45 pm
Standard compression utilities typically have problems with files larger than one or two gigs. Simply try to run a huge file through winzip and not only will it take forever but it will absolutely crush your CPU. This is the reason why it is not recommended to backup to compressed folders. It not only opens the door for errors like you have noted but it crushes performance.
If compression is important I would recommend looking at a utility like LiteSpeed from Imceda Software (www.imceda.com). This utility peforms SQL Server backups but does its compression on the fly with no need for temporary storage. Their compression engine does not have problems handling huge backup files and their backups are substantially faster than native backups. They have a free 14 day evaluation downloadable from their website.
December 20, 2005 at 1:31 pm
I am having the same problem. But in my case it only happens when the backup file exceeds 34 gig. I have used compressed dirctories for my backup for years without any problem. Does anyone know whether Microsoft has addressed this issue.
December 20, 2005 at 2:01 pm
I never heard whether MS addressed this problem or not, but after I took my Backup out of the Compressed Folder, I never saw this message again. Although I have to have a lot more Disk Space dedicated to this backup. If you do find anything out about this that doesn't come from this forum, please be sure to share it with us. Good Luck.
TJP8
July 30, 2007 at 6:23 am
Got exactly the same error after compressing backup folder for 60GB database. As a temporary(I hope) solution, I've split backup into multiple files. This seems to do the trick - it's fine now. The only downside is the duration of backup on compressed folder - it went up from original 1:50 to 4:40.
Regards.
July 31, 2007 at 3:59 am
We had a filesharing issue for large files in win2k3. Fixed with applying SP2, adding memory & flashing firware. Alas another feature crept up: commandshells don't always start (issue with unix-interoperability)
January 17, 2008 at 4:21 pm
Have any of you had this issue with log shipping?
I have a 50Gb database with 10Gb in Tran Log that keeps sending me this error
Suddenly it began to happen 3 days ago- no changes to the SQL Server -
SQL 2000 + SP4-
January 18, 2008 at 2:41 am
Windows compressed folders do not compress files over 2GB. What may be happening is that as you write the log backup, Windows is compressing the data. When the file grows above the 2GB level, Windows throws an error because it cannot compress a file of this size.
The idea of compressing your backups is good, but your method is not. You need to get a vendor backup tool, such as RedGate SQL Backup, Quest Litespeed, etc (Google knows all of the vendors if you ask the right question.)
If you have Bulk Logged recovery mode, the log file will hold only the space map pages for any bulk-logged operation. However, the log backup will go to the database and get all the pages that were inserted and include them in the backup. The log backup of a Bulk Logged database can therefoer be far larger than the log file. This behaviour is documented in BOL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 10, 2008 at 9:47 pm
I am also facing the same problem,
My DB size is 38 GB
I am using SQL Server 2000 on Win 2003 SP2
My backup folder was set to compress the files
When I take complete backup after 90% backup is getting fail.
Rajesh Kasturi
March 19, 2009 at 1:50 pm
Thanks very much for providing this information. From October last year my backups have been failing and I couldn't figure out why. My interim solution was to change the backup folder to another drive. The problem with that was that the only other drive was my data drive and you guessed it, I ended up running out of space and my database crashed. Now I know my problem was very simple. thanks!
March 20, 2009 at 3:16 pm
Please don't tell us you haven't had a backup in nearly 6 months???? And is so, what took so long to find a resolution?
-- You can't be late until you show up.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply