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 |
|
imughal
Posting Yak Master
192 Posts |
 Posted - 2012-01-03 : 06:29:47
|
| Hi,On my sql server so number of database are created. on my one database i have check avg_fragmentation_in_percent which is greater than 30% now i want to Rebuild Index on all tables within a specific database as given in following linkhttp://blogs.msdn.com/b/jorgepc/archive/2007/12/09/how-to-check-fragmentation-on-sql-server-2005.aspxplease tell me script to rebuild index of all table within given database.what is the recommended method or index rebuild table level or complete database. Please suggest with respect to sharepoint 2010. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-03 : 07:47:20
|
| Don't rebuild everything. Waste of time and waste of resources. Rebuild what needs rebuilding and leave the rest alone. Scripts for that include at http://www.sqlfool.com and at http://ola.hallengren.com--Gail ShawSQL Server MVP |
 |
|
|
biswajitdas
Starting Member
44 Posts |
Posted - 2012-01-12 : 16:13:58
|
| If your database is not huge please ignore my answer. Just one thing adding here as per my exp in my past production env when I used script to build the index ,I faced below issue and then I modified according to that.1)If your database is VLDB ,let say approx 300 to 400 gb then when you go for rebuild index by picking all the table in one shot then you must consider you spce in the logfile.The script will failed because of log file is full if you dont control over it.2)Having said that you can modify the script by choose only those table which were highly fragmented into a #temp table and pick up table one by one and re-build the index.3)Before taking another table to rebuild index you can check that is your log file is full or more then 80% , if it is then you can relese or shrink the log file so that the next table which is ready for to rebuild the index will not failed due to the log file space issue.Sr Sql server DBA/Artitech |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-12 : 17:49:01
|
quote:
if it is then you can relese or shrink the log file so that the next table which is ready for to rebuild the index will not failed due to the log file space issue.
Why would you want to shrink the log right before an operation that takes lots of log space. Highly counter-intuitive to remove the free space from the log (and hence more likely to hit log full errors) right before doing something that requires lots of free space in the log file.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-12 : 18:47:21
|
| "choose only those table which were highly fragmented "I think you mean Indexes, rather than Tables ?If you have issues with Index Rebuild causing excessive Log use then consider Reorganisation index of Reindex (on large indexes).We backup our Log file every 2 minutes during index rebuild / reorganise. |
|
|
|
|
|
|
SQL script to rebuild all indexes in DB