Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2008-04-24 : 14:21:12
|
| Hello All,I have a ~100GB database which gets reindexed every Sunday. There is a maintenance plan created to do this job. After this task run, the database becomes full and I have to always shrink it.Can any one tell me better solution to do the reindexing on the tables? Thanks,-S |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-24 : 14:23:17
|
| When you reindex via a maintenance plan, it reindexes everything regardless if it needs it. So you need to come up with a custom solution that checks what is fragmented and then reindex only those.I've got a custom script for this in SQL Server 2005 but not one for 2000. And stop shrinking it. You are causing a performance problem as it obviously needs it again every Sunday.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-04-24 : 14:34:19
|
| check this out for Sql 2000:http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p2.aspx |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2008-04-29 : 16:24:22
|
| Thanks Tara Kizer,Can you please post the script for re-indexing of tables in SQL server 2005Thanks,-S |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2008-04-29 : 16:28:52
|
| Thanks Tara Kizer,Can you please post the script for re-indexing of tables on SQL server 2005 version? You already replied to my earlier post saying you have the custom script for the same.Thanks in advance,-S |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2008-04-29 : 16:48:50
|
| Thank you Tara |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2008-04-29 : 16:54:13
|
| Hello Tara,I have to run defrag on the tables only if the scan density is under the specific threshold. How would I modify your script to implement the same?Thanks,-S |
|
|
|
|
|
|
Database reindexing