February 1, 2005 at 12:15 pm
I found an old thread that looks like someone having the same problem, but there was never a solution posted. Old_Thread
Does anyone have a reason or solution for a transaction log that continually takes 10X the amount of space to backup than it is using. I have a rather large database 45GB with a 4.5GB Translog. The Maint plan backup of the log failed and when I tried to run one manually, the backup of just the transaction log took almost 50GB. What would cause this? There may have been some Index Defrags that helped cause the log to grow to 4.5GB. This is very similar to the problem faced in the thread I linked above, but it is from back in 2002.
This is not the first time I have run into this, last week I saw the same things 50.1GB for a 6GB Translog. Then the backups ran normally for a few days and now this again.
I am running SQL 2K SP3 on Win 2K platform.
Thanks
TJP8
February 1, 2005 at 3:23 pm
Are you shrinking the size of the log as part of your maintenance? This could cause a situation where the backup APPEARS to be larger than the file itself, but in fact the file was that large (or larger) then the backup occurs and the file is shrunk.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
February 2, 2005 at 6:44 am
A shrink only runs once a week. I looked at the size of the transaction log, through EM and the physical files on the File system, prior to backing it up and it was only 4.5GB. So I do not think that would be it, but thanks anyway.
TJP8
February 2, 2005 at 12:56 pm
Sounds like you are appending your backup to an existing backup file. Take a look at the file name of your backup and see if that name already exists on the server. Also look at your backup statement to see if it has a WITH INIT clause in it, or in your maintenance plan "overwrite" instead of "append". (I don't use maintenance plans so my terminology may differ somewhat).
Steve
February 2, 2005 at 1:44 pm
Steve,
Yes I choose append from the backup wizard, But I do not believe that I am appending to another backup file. This is how I go about running the manual backup
RC on the DB and select Backup DB to invoke Backup wizard => select Transaction Log Backu => leave the Database/Name/Description names the way they are => select append to media => remove current 'Backup to:' destination containing the last backup.TRN path => Add new destination and specify a new backup.TRN with a datetimestamp in the format of a sqlmaint backup name. => ok to run it. This takes a 4.5GB Tran Log and makes a 50GB backup. I have always left it as append and created a new filename destination as a precaution, but I always have been under the impression that it was creating a completely new file and only appending anything if it found that .TRN file already existed. Am I correct in my understanding of the append flag?
As for the Maint Plan, First the scheduled Tran log backup has not run in the past few days due to another error that I believe I have related to windows compression and large backup files, but it runs for about 1-1.5 hours before it errors out. When I have successfully run this backup it has been outside of compression, however I have manually recieved these errors when trying it inside compression and prior to it erroring out noticed the Trans log backup file was approaching 50GB. So I am assuming that the Maint plan would be having the same issue if it were finishing the backup, but I can not tell how large the file is prior to erroring out due to it dropping the file after error. I have changed the maint plan to write into another folder that isn't compressed starting tonight to see if that fixes the other problem, so If it does it should show me if the Maint plan is getting the same size problem.
Here is the statement created and schedule by the Maint Plan as a backup job
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 31D7DD19-F6F6-47E9-BA4A-8904F1C1182C -Rpt "e:\Program Files\Microsoft SQL Server\MSSQL\LOG\Full Bulk Lg DB Maint Plan6.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "E:\SQL_Backups\Transaction_Logs" -DelBkUps 2DAYS -CrBkSubDir -BkExt "TRN"'
In looking further into xp_sqlmaint utility in BOL there was not reference to an overwrite/append flag. It does mention that it automatically creates the file name in the format of dbname_db_yyyyMMddhhmm.BkExt.
Sorry this response got a bit lengthy but I wanted to make sure I put in any information that may be relevent.
Thanks
TJP8
February 2, 2005 at 2:51 pm
You're right. An append will create a new file if it isn't there, and will append if it is there. This just doesn't make sense. How are you determining that the transaction log is 4.5GB? If you're not already doing this, in EM, left click on the database to select it, right click on it and select View/ Taskpad. Let us know what it tells you about the data file size, space used, the transaction log size, and space used.
You might try running this from query analyzer and see what happens -
Be absolutely sure that the backupname.trn doesn't exist already
BACKUP LOG dbname
TO DISK = 'E:\SQL_Backups\Transaction_Logs\backupname.trn'
WITH INIT
-- Steve
February 2, 2005 at 3:24 pm
Steve,
Exactly it doesn't make any sense, and by the way congratulations on post #500.
Yes I was viewing through TaskPad in EM as well as mapping to the server and looking at the size of the physical file on the server. The trans log has grown a little more since this post started since I haven't backed it up in a few days. By attaching the timestamp I am 99.99% sure it is a completely new file and not trying to append. I started one yesterday and it was up to about 38GB at 50 minutes and we had some user complaints regarding some performance decline so I canceled the backup.
From EM - Taskpad
File: Data Space Total: 78184.06MB Used: 48111.44MB Free: 30072.62MB
HNAC2C_DATA.MDF File size on filesystem: 76.3GB
File: Trans log space Total: 7229.49MB Used: 7176.09MB Free: 53.4MB
HNAC2C_LOG.LDF File size on filesystem: 7.05GB
The filesystem size numbers were taken by mapping to the server, right clicking on the files and view properties.
I am not going to run a Query Analyzer backup this afternoon, since I changed the maint plan, but if it doesn't run tonight I will try that tomorrow. I will update tomorrow with any results.
Thanks
TJP8
February 3, 2005 at 6:47 am
The New Maintenance plan ran a successful Backup of the DB and Trans Log last night. This plan was run outside of Windows compression as mentioned in a post above. When I mapped out to the server to take a look at the files I found the following information.
Trans Log Backup - 51.8GB - 52 minutes to complete
Full DB Backup - 47.0GB - 31 minutes to complete
I don't know if this really helps any, but those are the results.
TJP8
February 3, 2005 at 9:19 am
Is your transaction log on a compressed drive? If so, I wonder if this could be causing your abnormalities? Perhaps SQL is reporting a 7.05GB transaction log, but uncompressed, it would be 51.8GB? If this is the case, you should know that Microsoft highly discourages using compressed drives with SQL Server!
Steve
February 3, 2005 at 9:44 am
February 3, 2005 at 10:11 am
I don't know, then. A transaction log backup should not be larger than the transaction log was before the backup started. The only ways I can think of for this to happen (ruling out compressed drives) is a) appending the backup to an existing backup file or b) optimizations occurring on the database (reindexes) before the log backup starts, or maybe c) something is preventing you from seeing the true size of the transaction log (ie, the log has multiple files, but EM hasn't refreshed to show the second file, its a long shot). In the case of b), the physical transaction log file should be at least the size of the backup after the backup finishes unless a shrink is done in the job.
You might try right clicking on the database, All Tasks, Generate SQL Script, Options tab, check Script database, General Tab, and click preview. See what it shows for the transaction log file(s) and size(s).
Beyond that, I'm pretty much out of ideas.
Steve
February 3, 2005 at 11:31 am
Thanks, Here is my rundown on your points A-C
a) Like I said before I can pretty much rule this out.
b) There may have been some DBCC INDEXDEFRAG's run between Trans log backups, but there definitely wouldn't have been any running during the backup. So the Log should be at its full size when I back it up.
c) if this is happening I have no idea what is causing it since the physical file is showing up at that size as well as all of the taskpad in EM
I ran the Generate Script preview and it showed the file sizes of the Data and Log files as the same as EM. It just doesn't make any since. I will continue to watch it and see if this trend continues, and if I have any new information or revelations I will posted them here. I really appreciate you taking the time to try and help me through this. If you happen to come up with anymore ideas please let me know.
Thanks,
TJP8
February 3, 2005 at 11:36 am
Here's a thought. Do you have a test server that you can restore your backup(s) to? It might be interesting to see what happens when you restore that 51.8GB log backup. It'd be a really good idea to test these backups anyway, since you know there are abnormalities!
Steve
February 8, 2005 at 7:36 am
That was a good idea on testing it. I was able to find a server with enough free disk space so that I could test the recovery. It worked but it took a very long time. 5.5Hrs on the 45GB Full DB restore and another 12HRs to restore the 50GB Trans log. This server is not as robust as the production server plus I was creating the DB from the restore, but that is quite a bit of time if I ever have to restore it. The good news is that it appeared to restore successfully.
The Bad news is that I was about to write off my problem as some sort of fluke occurrence, but it happened again last night. I got a 50GB Trans log backup last Wednesday, and then I had not seen any more since. The log backup had been running in 5-10min rather than 50-65min and it was around 1-2GB in size. It had done this Thu, Fri, & Sat, we do not run backups on Sun. Well last night it reverted back, I checked it this morning and the full DB backup was 52GB with a Trans log backup of 54GB that ran for 66min.
It seems to be a good backup, but I still do not understand why it is growing like this.
I just wanted to update the thread for anyone that had been monitoring it.
Thanks
TJP8
February 8, 2005 at 9:11 am
And again, the transaction log file stayed constant at 4.5GB? Have you seen a pattern as to when it is happening? Is there any indication in the logs (SQL error log and/or system/application/security logs through event viewer) as to what other processes may have been running during the backup time or even sometime before? I assume that you have a single job defined for your backups? What I'm getting at here is that if you have multiple jobs defined, is there consistency with the job that creates the big tlog backups? If you can predict when it will happen, it might be useful to set up some monitoring on the server, to see what is happening on the day that produces the big log dump. It might even be good to log on to the machine and look around to see if you see anything unusual. sp_who2 to see what processes are connected, and what they're doing. Check the current users to see what ids are logged into the server.
Still scratchin' my head on this one!
You know, I just went and looked at the old thread you referenced, and it does sound like the issue that you have described! I think maybe it might warrant a call to Microsoft.
Also, Sable is still around, last logged in July, 2004. You might send him/her a private message to see if he/she resolved the issue.
Steve
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply