Problems from reindexing tables every day on a SQL Server

Tags:
Indexing (information management)
SQL Server
tables
We have a daily job running to reindex all the tables for all the databases on our SQL Server. If I reindex selective reporting tables everyday, take a transaction log backup right after reindexing and check if the transaction log is greater than 1GB, I think I should invoke a stored procedure to shrink the transaction logs. If I do this, and if the transaction log is forced to shrink everyday, how is it going to affect the server’s performance? Also, is it going to affect something else? Finally, do I even need to reindex tables every day?

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you are shrinking the log file daily all you are doing is making the SQL Server spend time shrinking the file and growing the file. You are placing unneeded load on your disk subsystem, as well as CPU when it is moving the data pages from the end of the file to the beginning.

Unless you are changing a large percentage of your data daily you shouldn’t need to reindex your tables daily. Weekly is usually good enough.

Use DBCC SHOWCONTIG to see if the indexes are fragmented enough to warrant reindexing.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,190 pointsBadges:
    report
  • Colin Smith
    I would agree with Mrdenny on this matter and suggest to check the fragmentation before re indexing everyday. Also if your Log file is of concern to you then I would ask about your backup strategy. If your log grows quickly it might be helpful to dump the log more often. This will keep the log size small and also give you better recover ability if needed.
    885 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following