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 |
|
laserovic
Starting Member
11 Posts |
 Posted - 2011-09-21 : 18:01:19
|
| Hi all,My database has 220GB, but there is 75Gb free space (in mdf) - we dropped one big table and the space won't be used soon.How to do it without fragmenting indexes ? I have tried it with a copy of the db, but DBCC SHRINKFILE defragmented most of indexes , after reindex still a lot of them are much more fragmented then now.It is OLTP, daily usage from 6 a.m. to 6 p.m., so it is possible to do it after working hours.I have some ideas likea) move tables to another filegroup, shrink the file and move back orb) copy tables, then indexes and other structures to another database and rename after, but this is really a lot of work.I always avoided shrinking database, but now i need it at the moment and there must be a way how to do that. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
laserovic
Starting Member
11 Posts |
Posted - 2011-09-22 : 01:46:01
|
| It was just an attempt and I deleted the copy of the database afterwards. I don't have data now but I have a script which collects fragmentation but a lot of important indexes on largest tables had over 50% or sometimes over even >90% fragmentation (and some of them not). I can try it again. But this week, if possible, I wanted to try a different method. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
laserovic
Starting Member
11 Posts |
Posted - 2011-09-22 : 17:06:16
|
| Today I started to work on the option a) and for the moment results are very good, for the two largest tables, I had only an issue with moving of the tables having PK=clustered index, since the syntax is different. |
|
|
|
laserovic
Starting Member
11 Posts |
Posted - 2011-09-23 : 12:25:02
|
| I found the script which does part of the work - except clustered indexes, but it should not be hard to adapt it to move clustered ones also.After move there is almost no fragmentation.[url]http://stackoverflow.com/questions/4237238/moving-all-non-clustered-indexes-to-another-filegroup-in-sql-server[/url] |
|
|
|
laserovic
Starting Member
11 Posts |
Posted - 2011-09-25 : 17:19:38
|
| I changed the script from the link above, so it works also for clustered index (just remove the condition in dynamic SQL), the only remaining problem might be "Cannot create index enforcing primary key constraint 'xxxxxxx' using DROP_EXISTING option while table has an XML Index.". |
|
|
|
|
|
|
|
|
Decrease DB size after drop table