Trans Log backup much larger than Original Trans log
-
Well not right at 4.5GB, but it stayed in a range from 3.5-6GB during the time that it was running normally. I can not seem to make out a pattern, The last 2 times that it has occurred have been through the maintenance plan (last night and last wednesday night), but it has also run successfully with the same job generated by the Maint plan. I have also had it back up to 50GB with a Backup Wizard Backup as well. The event viewer & errorlogs look like a normal backup job has completed. I did talk to the app owner a little this morning about any differences with yesterday and last week, and he said they had to run the load job multiple times yesterday. I thought this may have been something, but in looking at the Size of the Translog file which has not been shrunk since over the weekend, it is only 6643.49MB with only 2472.19 currently being used. This leads me to believe that although it may should have been a slightly larger Trans log backup than usual it shouldn't have been 54GB.
I also PM'ed Sable and got a reply. They got around this by using litespeed as a backup, so they never really found a solution either. One of his/her Coworkers suggested that the Trans Log could have some corruption, and that maybe I should try this
"Try switching the recovery model of your database to simple mode, then
detach it. Delete the transaction log file, and reattach the database, allowing for a new transaction log file to be created. After this, you can switch the recovery model back to full. There could be possible corruption inside of the transaction log device, and if this is the case, this procedure will clear that up."
I had not really thought about that possibility, Have you ever seen anything like that? I would have to find an outage window before trying this.
Thanks,
TJP8
-
I have never seen anything like this in nine years. The only things that I've seen that could explain this is either the compressed volume thing (although you wouldn't see a ten-fold difference in size) and an "unseen" file shrink. Since you have pretty well eliminated both of those possibilites, I would strongly reccommend that you call MS.
P.S. you don't really need to switch the database to the Simple recovery model to detach and delete the log file. The key there is that it is detached cleanly. But I can't imagine how a corrupted log file would cause this to happen at all and I would think that you would see other problems if that were the case...
/*****************
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
*****************/
-
Also, before trying the detach reattach method, make ABSOLUTELY sure that you have a GOOD backup that you can recover from. The single file attach method is really for disaster recovery, and doesn't always work, in which case, if you are faced with recovering with one of those 50GB transaction log backups, you may be in real trouble! Have a look at sp_attach_single_file_db in Books Online. I somewhat agree with DCPeterson (and am thrilled to finally have some input on this from someone other than me!
). There is obviously a problem somewhere, and this is where I differ slightly from DCP, I wouldn't rule out a problem with the tlog. In any event, I think a call to Microsoft is warranted. If your problem proves to be a bug, its my understanding that they won't charge you.Steve
-
One other thing, I'm not sure that LiteSpeed is the answer. To me, unless its proven to be a problem with the native backup routines (which LiteSpeed uses behind the scenes), you're treating the symptom and not the disease. That could come back to bite you really, really hard down the road. (By the way, I like LiteSpeed. I'm evaluating it for our shop right now, and it seems to be a great product!)
Steve
-
I didn't really "rule out" a problem with the log file, but it seems doubtful since you would expect to see a host of other problems if that were the case.
Anyway, MS will make you give up a credit card number when you call, but if it is determined to be a bug, they will not charge you. The advice about a backup is critical and ALWAYS applies whenever you plan to modify a database in any way.
/*****************
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
*****************/
-
Thanks, DCPeterson, for straightening me out on that! Sorry if I came across beligerently! And I would agree that if there was a corruption in the tlog, there would most likely be other problems, not the least of which might be an inaccessable database, which by the way, would be where the sp_attach_single_file_db procedure might come in handy!
TJP8, please let us know what the outcome is if you call MS!
Steve
-
Steve and DC,
I agree that litespeed is really just treating the symptoms not fixing the problems, something in mine and Sable's case was not quite right. And you are both absolutely right on making sure I have a good backup before trying anything like a detach/reattach. My thought on the corruption of the Trans log was that if it were really corryupt why does it only happen sparingly. The Maint plan did run an integrity check this weekend, not sure if that would be able to detect corruption in the trans log though. I agree, Microsoft is probably where this question needs to go next, but before I do that I am going to have to sit down with my Manager and the App Owner and discuss it further since it is not currently a production down issue.
There is another option. I have only recently taken DBA responsibility for this server, and so I am still trying to get a full handle on the APP/DB relationship. In my discussions today around this problem with the App Owner, I have discovered that the data is almost never changed/updated directly. Almost all changes are Batch job Loads usually once a day that could be recreated for the previous few days if necessary. Then the data is used for analysis and reporting purposes. This makes me believe that this could be a solid candidate to put in Simple mode as long as I can make the App Owner aware that if a Recovery is needed he will be responsible for all changes since the restored backup. I mentioned this to him earlier today and he seemed like that would not be a problem.
This would again be more of a treatment of the symptoms than fixing the problem, but may be good enough for us in this situation.
Thanks
TJP8
-
Just one last thought... I will reiterate that if I were in your place, I'd be calling MS. At $250 per incident (worst case) it probably doesn't take much of your time to make that up, and in the several times I've called (only one of which I was actually charged for) I've found the support to be well worth the money.
I too would like to hear back from you if you call MS or get this figured out by some other means!
Steve, NP, I didn't think you were being beligerent, I just wanted to clarify my statement. Often in these fourms I find that I wasn't exactly clear, or left out some small (but possibly important) detail that I might take for granted.
/*****************
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
*****************/
-
I agree completely with DCPeterson here, if there is a problem with the database, regardless of what it is, or how the symptoms can be treated, I'd want to know what that problem is, and how to "cure the disease". If you "treat the symptom" by changing to simple recovery, the problem could actually get worse, and who knows what the symptoms will be then? crash the server?
Based on your description, I would also agree that the database is a good candidate for simple recovery. But, I'd do that AFTER the problem was taken care of!
By the way, Sable may be interested in the outcome as well, particularly if the problem has the capability of escalating.
Steve
-
A quick update, A decision was made not to get Microsoft involved at this time. Since this was a good candidate for Simple mode we decided to go that route, however, I have left it in Full for now to try and find a pattern. Since there were more issues around getting a regular backup when I started this thread I did not have a regular pattern. Since the new Maint plan was implemented, running outside of compression, it has been running successfully and the large Transaction log has appearred in a pattern. The Trans log backup has been 50+ GB the last 3 monday nights. We run fulls Mon-Fri nights and Trans logs Mon-Sat night with the maint plan doing so me system work over the weekends. I have included below select information from the Maint plan history since last Friday.
The only thing that could make me think of something like this happening is the Index rebuild and then the database shrink, which are scheduled off of one schedule in the Maint plan optimization tab. The Transaction log backup in red was 59GB. I am going to try and turning off the optimization piece this weekend and see if that corrects the problem. The shrink runs prior to the Transaction log backup, so that should not have an effect on it. I looked at the file on monday morning and it was only 2.5GB.
I am posting this mostly as a follow up for informatonal purposes, but if you see anything in there that hasn't been touched on in this thread already please speak up. Thanks again for all the effort.
TJP8
activity start_time end_time duration message Backup transaction log 2/18/05 10:00 PM 2/18/05 10:01 PM 107 Backup Destination: [e:\… Verify Backup 2/18/05 10:01 PM 2/18/05 10:02 PM 38 Delete old tlog backup files 2/18/05 10:02 PM 2/18/05 10:02 PM 0 1 file(s) deleted. Delete old text reports 2/18/05 10:02 PM 2/18/05 10:02 PM 0 1 file(s) deleted. Backup database 2/18/05 11:15 PM 2/18/05 11:47 PM 1964 Backup Destination: [e:\… Verify Backup 2/18/05 11:47 PM 2/18/05 11:58 PM 668 Delete old db backup files 2/18/05 11:58 PM 2/18/05 11:58 PM 0 1 file(s) deleted. Delete old text reports 2/18/05 11:58 PM 2/18/05 11:58 PM 0 1 file(s) deleted. Backup transaction log 2/19/05 10:00 PM 2/19/05 10:01 PM 106 Backup Destination: [e:\… Verify Backup 2/19/05 10:01 PM 2/19/05 10:02 PM 37 Delete old tlog backup files 2/19/05 10:02 PM 2/19/05 10:02 PM 0 1 file(s) deleted. Delete old text reports 2/19/05 10:02 PM 2/19/05 10:02 PM 0 1 file(s) deleted. Check Data and Index Linkage 2/20/05 12:00 AM 2/20/05 12:32 AM 1962 Rebuild Indexes 2/20/05 12:40 AM 2/20/05 4:29 AM 13789 Shrink Database 2/20/05 4:29 AM 2/20/05 4:29 AM 6 Backup transaction log 2/21/05 10:00 PM 2/21/05 10:37 PM 2257 Backup Destination: [e:\… Verify Backup 2/21/05 10:37 PM 2/21/05 10:58 PM 1273 Delete old text reports 2/21/05 10:58 PM 2/21/05 10:58 PM 0 1 file(s) deleted. Backup database 2/21/05 11:15 PM 2/21/05 11:36 PM 1309 SQL Error (due to disk space) Delete old text reports 2/21/05 11:36 PM 2/21/05 11:36 PM 0 1 file(s) deleted. -
Given the amount of time required to do the index rebuild, I'm not surprised to see a pretty large t-log backup right after. The only mystery is how it gets so small again. The log file cannot be shrunk until it has been backed up even if you don't actually back it up and just use the NO_LOG or TRUNCATE_ONLY option.
Look in the msdb database at the backupfile against the backupset table to check the relative size of the file and backup. Use the following query to do this and see what you get. The file_size and backup_size should be pretty close after an index rebuild operation, and in no case should the backup_size be larger.
SELECT f.logical_name
,f.file_size
,s.backup_size
,s.backup_start_date
,s.backup_finish_date
FROM msdb.dbo.backupfile f
JOIN msdb.dbo.backupset s
ON s.backup_set_id = f.backup_set_id
WHERE s.database_name = 'DBName'
/*****************
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
*****************/
-
Sorry for the slow reply to you DC.
First off, I ran your query above. I am not very familiar with looking at these tables, but from the looks of it, for every full backup there is a log and a Data Backup, and they share the same start time and backup_size. Then when there is a Trans log backup there is an additional log backup in these tables with a separate, unique backup size and sometimes it also has a data entry There were only 2 entries in the results from the night of 2/21/05 which is the last night that the file was 50GB+. The Trans log was scheduled for 10:00PM and the Full for 11:15PM. The Full failed on 2/21/05 due to not having enough disk space on the drive, since the Translog had taken 50GB+. I have included below the entries from 2 previous occurences 2/7 and 2/14 , the full also failed due to space issues on 2/14, and 2/19-3/1.
Logical_name file_size backup_size backup_start_date backup_finish_date
HNAC2C_Data 88399216640 58090149376 2005-02-07 22:00:01.000 2005-02-07 22:45:26.000
HNAC2C_Log 6966214656 58090149376 2005-02-07 22:00:01.000 2005-02-07 22:45:26.000
HNAC2C_Data 88399216640 53437390336 2005-02-07 23:15:00.000 2005-02-07 23:50:08.000
HNAC2C_Log 6966214656 53437390336 2005-02-07 23:15:00.000 2005-02-07 23:50:08.000
.....
HNAC2C_Data 94816501760 57727928832 2005-02-14 22:00:01.000 2005-02-14 22:41:38.000
HNAC2C_Log 6565658624 57727928832 2005-02-14 22:00:01.000 2005-02-14 22:41:38.000
......
HNAC2C_Log 6565658624 1745910272 2005-02-18 22:00:01.000 2005-02-18 22:01:47.000
HNAC2C_Data 94816501760 59570777600 2005-02-18 23:15:00.000 2005-02-18 23:47:44.000
HNAC2C_Log 6565658624 59570777600 2005-02-18 23:15:00.000 2005-02-18 23:47:44.000
HNAC2C_Log 6565658624 1600792064 2005-02-19 22:00:02.000 2005-02-19 22:01:37.000
HNAC2C_Data 99807723520 60675798528 2005-02-21 22:00:01.000 2005-02-21 22:37:38.000
HNAC2C_Log 2555904000 60675798528 2005-02-21 22:00:01.000 2005-02-21 22:37:38.000
HNAC2C_Log 2555904000 1698220544 2005-02-22 22:00:01.000 2005-02-22 22:01:36.000
HNAC2C_Data 99807723520 59939581440 2005-02-22 23:15:00.000 2005-02-22 23:50:31.000
HNAC2C_Log 2555904000 59939581440 2005-02-22 23:15:00.000 2005-02-22 23:50:31.000
HNAC2C_Log 2555904000 1992722944 2005-02-23 22:00:01.000 2005-02-23 22:01:58.000
HNAC2C_Data 99807723520 61039668736 2005-02-23 23:15:01.000 2005-02-23 23:48:13.000
HNAC2C_Log 2555904000 61039668736 2005-02-23 23:15:01.000 2005-02-23 23:48:13.000
HNAC2C_Log 2555904000 2131209728 2005-02-24 22:00:00.000 2005-02-24 22:01:55.000
HNAC2C_Data 99807723520 62490701312 2005-02-24 23:15:00.000 2005-02-24 23:52:01.000
HNAC2C_Log 2555904000 62490701312 2005-02-24 23:15:00.000 2005-02-24 23:52:01.000
HNAC2C_Log 4049076224 3350353408 2005-02-25 22:00:02.000 2005-02-25 22:03:00.000
HNAC2C_Data 99807723520 64577859072 2005-02-25 23:15:01.000 2005-02-25 23:51:53.000
HNAC2C_Log 4049076224 64577859072 2005-02-25 23:15:01.000 2005-02-25 23:51:53.000
HNAC2C_Data 99807723520 9406302720 2005-02-26 22:00:02.000 2005-02-26 22:06:59.000
HNAC2C_Log 4049076224 9406302720 2005-02-26 22:00:02.000 2005-02-26 22:06:59.000
HNAC2C_Data 99807723520 10561179136 2005-02-28 22:00:01.000 2005-02-28 22:08:56.000
HNAC2C_Log 10233053184 10561179136 2005-02-28 22:00:01.000 2005-02-28 22:08:56.000
HNAC2C_Data 99807723520 67901602304 2005-02-28 23:15:00.000 2005-02-28 23:50:49.000
HNAC2C_Log 10233053184 67901602304 2005-02-28 23:15:00.000 2005-02-28 23:50:49.000
HNAC2C_Data 99807723520 2232747520 2005-03-01 22:00:01.000 2005-03-01 22:02:14.000
HNAC2C_Log 10233053184 2232747520 2005-03-01 22:00:01.000 2005-03-01 22:02:14.000
HNAC2C_Data 99807723520 69668781568 2005-03-01 23:15:01.000 2005-03-01 23:50:16.000
HNAC2C_Log 10233053184 69668781568 2005-03-01 23:15:01.000 2005-03-01 23:50:16.000
Should a transaction log backup have an entry for both Data and Log? Why do some have both for Transaction logs and other have just log?
Also I turned off the reorg this past weekend and left the shrink in the optimization tab of the Maint Plan and the Monday night backup of the Transaction log was normal size. So it appears that it may have something to do with the Reorg job from the Maint Plan, but I still do not know why other DB's aren't experiencing similar issues. I don't know if any of this means anything at all
Thanks
TJP8
-
TJP8,
If you answered this in a previous post, please forgive me for not going back and reading the whole thing! Is your recovery model full or bulk logged? In the current issue of "SQL Server Magazine", Kalen Delaney states:
"The log size doesn't always reflect the log-backup size. If your database is using the bulk-logged recovery model, the log backups will include all data that the bulk operations affected, so the backups can be many times as large as the log file."
SQL Mag instantdoc id 45094
Steve
-
It is in bulk logging mode. So that may be what my real issue is? The reorg jobs aren't logging in the Trans log, but when I try to back up the trans log it has to backup up parts of the database as well as the log to try and make sure it is consistent?
TJP8
-
I thought you had placed it in Simple... Yes, if your db is in Bulk Logged recovery mode all data pages affected by a bulk operation will be included in your log backups. This is necessary unless you want your log backups to become useless as soon as you do a bulk operation, in which case you would have to run a full backup immediately after a bulk operation (if memory serves, this is how SQL 7 worked). That is the issue here.
/*****************
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
*****************/
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply