April 8, 2010 at 4:02 pm
Hello, I was hoping somebody can shed some light on why my log backup size is so big.
SQl 2008 Std 64-bit
I have a 80GB database in Full Recovery Model on a server.
This is made up of about 60GB data and 20GB free space plus a 5 - 6 GB log file.
I need this database on another server that is being set up.
I do a one off backup and restore the DB to the second server.
I change the Recovery model to SIMPLE then FULL and shrink the log back to 5GB.
So the log is now empty (i am not worried about retaining any existing log data at this point).
I backup the database on the second server so I can do transaction backups later.
I start a full reindex job on this database as part of my testing.
As mentioned the data is about 60Gb with 2 very large tables.
I change the database to Bulk_Logged, Do a Full Reindex then change back to Full in the one process to drastically reduce the log data generated.
The log size is still 5GB with only a small amount of data in it. this is all OK and good.
The thing that I do not understand is when I do a transaction backup the .trn file is over 40GB!!
I can understand this if I am reindexing and the DB is in fully logged mode but the Log file hardly has any data in it.
I don't really want a 40GB log file every time I reindex.
Can somebody shed some light on why this is happening if i have made myself clear.
Thanks
April 8, 2010 at 4:11 pm
In bulk logged mode just a record of the extents moved as part of the reindex is recorded in the log, this could be a lot of extents, but as Only info on the extents rather than actual data is recorded log use in minimised.
HOWEVER, when you backup that portion of the log, SQL needs to be able to replay the data moves should it be restored, so the actual contents of the extents is backed up, as you have found out that can be a lot of data.
---------------------------------------------------------------------
April 8, 2010 at 4:21 pm
Thank you, so is there any I can do to prevent such a big log file?
Would a re-organise create a lot less data and a full index once a week? The reindex would take longer though for a re-organise I think,
I could change to simple model after reindex but then I would have to do a full backup straight away.
The original server uses SQL 2000 and DBCC Reindex and only produces 4GB file while entirely in FULL model.
But if I am correct SQL 2008 uses different logic to reindex?
April 8, 2010 at 4:32 pm
If I understand correctly you are already switching to simple mode to shrink the log, so why don't you switch to simple, reindex, shrink log,switch to full, backup.
whether the reorganise runs longer or not depends on how much fragmentation there is. As the database is dominated by two large tables reorganise may be your best option.
Not sure why the discrepancy in log backup size from SQL2000. Is this restore to another server incorporating an upgrade as well? Maybe its related.
If you do reorganise and this is an upgrade update your stats as well.
---------------------------------------------------------------------
April 8, 2010 at 10:07 pm
george sibbald (4/8/2010)
If I understand correctly you are already switching to simple mode to shrink the log, so why don't you switch to simple, reindex, shrink log,switch to full, backup.whether the reorganise runs longer or not depends on how much fragmentation there is. As the database is dominated by two large tables reorganise may be your best option.
Not sure why the discrepancy in log backup size from SQL2000. Is this restore to another server incorporating an upgrade as well? Maybe its related.
If you do reorganise and this is an upgrade update your stats as well.
You should realize that a reorganize is fully logged - always. So the transaction log itself will grow, and the log backup will also be large.
Are you sure you need to re-index fully all the time? You should be looking at only rebuilding indexes that are heavily fragmented. Google SQL Fool - Michelle has a very good script for doing this available.
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
April 8, 2010 at 10:58 pm
What I am doing is upgrading from SQL 2000 to SQL2008. The databases will be going to a completely new server so I have the luxery of setting up the database, admin jobs etc before hand and get them all working correctly and then just restore and upgrade the databases when the time comes.
On hind sight I was being lazy and just looping through each table and full indexing it. The database is getting a bit big this.
I will follow other scripts and get the fragmentation level and then only re-index those that are greater than the threshold.
So to clarify please. reorganise will create larger logs than rebuild? or is it the other way around?
April 8, 2010 at 11:07 pm
Reorganize is fully logged, rebuild can be minimally logged when the recovery model is simple or bulk-logged. Either way, the transaction log backup after that operation will be very large.
Since this is part of an upgrade - I would go ahead and rebuild everything in simple recovery (one time, after upgrade), then modify to full and perform a full backup.
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
April 8, 2010 at 11:28 pm
What I still do not understand is why the data in the log file was very small (< 1GB) yet the transaction log backup files was ~40GB.
I thought what was backed up as part of a log back up was what was in the log file.
I guess this obviously shows that is not the case and in the case of indexing other data is written to the log backup file from the data file
April 9, 2010 at 4:46 am
mark avis-234606 (4/8/2010)
What I still do not understand is why the data in the log file was very small (< 1GB) yet the transaction log backup files was ~40GB.I thought what was backed up as part of a log back up was what was in the log file.
I guess this obviously shows that is not the case and in the case of indexing other data is written to the log backup file from the data file
reason for that as I described in my first post.
If your database is mostly two large tables scope for reducing impact of defragmenting is reduced. Which is why I suggest reorganise and simple mode. Reorganise is fully logged but it works in small transactions to minimise contention so data it logs will be truncated when database in simple mode and log size will be controlled.
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply