• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1172
  • Last Modified:

Index rebuilding increasing log size

Very rarely  do much SQL Server 2k maintenance and am feeling rather dense about this ...

Have set up a weekly job to rebuild the indices in several databases (OK indexes if you'd rather!), but the log size grows rapidly during this rebuild.  Questions are:
1. Does this matter (it's not growing to the extent that there's any concern over HDD space)?
2. If it matters can I just schedule a database shrink (or should that be a log shrink?) immediately after the rebuild?
0
looper8
Asked:
looper8
1 Solution
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
1. Does this matter (it's not growing to the extent that there's any concern over HDD space)?

Rebuild process to fill log file means  , you should concern about HDD space.

2. If it matters can I just schedule a database shrink (or should that be a log shrink?) immediately after the rebuild?

you should shrnik the log file because it will be the fast rather than shrinking the complete database.
0
 
rattu976Commented:
I would suggest using indexdefrag for it will do things online, or if you are doing maintenance out of work hours use dbreindex.
Backup your transaction log before the job start and truncate Log after each index rebuild.
This way you  will be having smallest transaction log possible used by your database. I wont suggest shinking the transaction log however, as it will grow again as and when sql server required it again.
Please let me know if you need any more assistance.

Regards,
Rehan
0
 
looper8Author Commented:
Thanks.  

I can see why shrinking the log isn't worth doing, but confused about truncation.  Looking at Books Online I come up with this about Log truncation:

"Important  The Truncate method allows reuse of the space allocated to the operating system file(s) maintaining a transaction log. Log truncation is part of normal transaction log backup. If log backup is part of a database backup strategy, the Truncate method should never be called."

Seeing as I've got log backups going every hour during office hours is it necessary to do this?  In other words do I just leave the log as it is after the index rebuild (Sunday afternoon) and it will then get sorted with a log backup Monday morning?

0
 
abhijit_kCommented:
If u dont wish to Generate Trans Log while Reindexing the Put the Database into Bulk_logged before doing Reindexing and change the status once completed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now