November 12, 2008 at 11:52 pm
Good day,
I am having trouble with transaction log space usage when I do a daily index rebuild.
From what I understand in (the minimally logged transactions section of) BOL, the ALTER INDEX... REBUILD is not supposed to grow the log-file significantly when in BULK-LOGGED recovery model. Furthermore ALTER INDEX... REORGANISE is fully logged, regardless of recovery model.
I've been seeing that this actually is not all that does not quite meet my expectations.
I put the DB in bulk-logged recovery model (from full), then do a rebuild, then set recovery mode back to full. All of this in one step of a scheduled sql job with go's inbetween. Also did this using a reorganise the previous evening. When playing around, not using this job but SSMS, the log file just grew for one auto-grow, which was acceptable.
On prod, I could not very much play around, but can only check the size of the scheduled log backups. The one using REBUILD only came down to about 70% the size of the one having used REORGANISE the previous evening.
Should that be the case? Should the log backup size not be just-about nothing, with this happening at a time where nothing else is running? Any other advise on this topic?
Thanks
November 13, 2008 at 4:22 am
Found the answer! So for those interested... I found it in a blog by Paul Randall (SQLSkills) - thanks Paul!
The short of it is that I was indeed looking at two different things, which caused the bewilderment. Whilst testing I looked at the physical log file size and on prod I looked at the size of the log backup done after the rebuild. Haven't done any log backups whilst testing to check out their sizes.
As Paul points out, the log file is indeed remaining small, but the log backup pulls together everything needed to replay the action, resulting in a log backup of similar size to one done in full recovery mode.
Now we all know... :ermm:
November 13, 2008 at 5:53 am
Has anybody having idea which space SQL Server is USING?
During Maintenance DB is in Bulk-Logged mode.
Here is scenario:
TOTAL DB SIZE: 24 GB
Space Available: 12 GB
Data File: 21 GB
Log File: 3 GB
Tempdb: 3 GB
Now as mention above log file size won't increase. But log backup size is increased i.e. similar size to one done in full recovery mode.
Correct!!! I have notice the same.
Log size: 3 GB
Tlog file size: 10 GB (Single one Immediately took after the maintenance)
Other Tlog file size is normal around 35 MB ONLY.
Now my question is has anybody is having idea which space SQL Server used to store 10 GB Tlog file?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
November 13, 2008 at 6:24 am
SQL Server uses the log file itself for the rebuild operations unless you specify the tempdb option.If you notice the logfile while the rebuild is in process...u would have seen the huge size .Since you made a log backup,the committed transactions will go into it leaving the Log file as it was before.
November 13, 2008 at 7:01 am
Reddy, Here I am using SQL 2000 and log size of database is max 3 GB that's all no expantion due to bulk-logged recovery mode.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply