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 |
|
flummer
Starting Member
2 Posts |
 Posted - 2009-06-09 : 05:59:42
|
| I have a problem with a database that has grown VERY large. It is aproximetly 30gb and i only have a few GB (about 6GB) of free space on my drive. Properties on the DB (.dat) says the db has 0mb free space. However i think that the database has very large indexes that might be possible to shrink. I cant run a reindex since i dont have the space so i think index defrag is my only option.I found a script on msdb that is used in sql 2005 and 2008 to run indexdefrag on all indexes and it works well but when i try to run the same script i get syntax errors.Does anyone have a script i can run for sql 2000 to do a indexdrefrag on all indexes (that doesnt need alot of free space on the server).Is there any way i can get a list of all index and maybe see witch are the biggest ones?Any help will be greately aprichiated. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-09 : 06:45:17
|
Your best bet may be to drop all indexes, and recreate them in storage size order starting with the largest.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-09 : 07:28:44
|
| I wrote this a while ago to rebuild all the indices on a 2000 server. There's probably a better way to do all of this (Tara's got a very complete solution) but this is probably as simple as it can be:It uses DBCC rebuild so will cause locking, probably not a good idea to run except at a nice downtime window.NB -- Code removed. I'm not sure that it would have been a good idea!prob better to go with Peso's adviceSorry,Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
flummer
Starting Member
2 Posts |
Posted - 2009-06-09 : 09:48:01
|
| How would i go about droping and recreating the indexes? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-09 : 14:44:20
|
First script out all your indexes as CREATE INDEX.Then script out all your indexes as DROP INDEX. Now drop all you indexes. And then create your indexes with the largest index in terms of storage space first.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
Shrink big database index