June 30, 2010 at 8:21 am
Hi
We have a scheduled process each night which drops and recreates index’s on one of our main Databases. This in turn causes the transaction logs file size to over time get bigger as the index's grow etc.
I was in the middle of doing some study for exam 70-433 for sql 2008 and noticed an Entry advising that its common practise to change the recovery model of a database to Bulk logged while certain operations are done which are not intended to be logged.
Just thought to ask your thoughts on this as I suppose it would be something like
a) Doing a full backup prior to Index recreation
b) flick the recovery model to Bulk Logged
c) Drop and recreate the Indexes
d) Switch back to full recovery Model
Any general thoughts/advise would be appreciated.
Kind Regards,
Matt
June 30, 2010 at 8:27 am
mattmc (6/30/2010)
HiWe have a scheduled process each night which drops and recreates index’s on one of our main Databases. This in turn causes the transaction logs file size to over time get bigger as the index's grow etc.
I was in the middle of doing some study for exam 70-433 for sql 2008 and noticed an Entry advising that its common practise to change the recovery model of a database to Bulk logged while certain operations are done which are not intended to be logged.
Just thought to ask your thoughts on this as I suppose it would be something like
a) Doing a full backup prior to Index recreation
b) flick the recovery model to Bulk Logged
c) Drop and recreate the Indexes
d) Switch back to full recovery Model
Any general thoughts/advise would be appreciated.
Kind Regards,
Matt
It isn't that some operations aren't logged, they are minimally logged. If you are going to switch to BULK LOGGED recovery model then execute a minimally logged operation, then switch back to FULL recovery model here is what I would do:
1. T-LOG backup
2. Change recovery model to BULK LOGGED
3. Drop and recreate the Indexes (or whatever minimally logged operation you are running)
4. Change recovery model to FULL
5. T-LOG backup
June 30, 2010 at 8:35 am
mattmc (6/30/2010)
We have a scheduled process each night which drops and recreates index’s on one of our main Databases.
Why drop and recreate? So that you can do some large data loads?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 5:03 am
Ok thanks for your feedback
July 1, 2010 at 5:06 am
"Why drop and recreate? So that you can do some large data loads? "
No large data load's , just purely from an index maint standpoint.
July 1, 2010 at 5:11 am
Then rebuild the index. (ALTER INDEX ... REBUILD)
Dropping and recreating the index has at least twice the log impact of just rebuilding the index (SQL has to log the drop, then it has to log the create). In the case of the clustered index (if you're dropping and recreating that) it has many times the impact as dropping the clustered index will rebuild every single nonclustered index and recreating the clustered index will again rebuild every single nonclustered index on the table. Rebuilding the cluster does not rebuild the nonclustered indexes at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 5:20 am
Ok that makes sense.
Thanks for your feedback and such a fast reply 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply