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 |
|
christhetechie
Starting Member
3 Posts |
 Posted - 2007-09-10 : 06:53:16
|
| I've inherited a SQL 2005 Server with a major problem. It's been badly admin'd for ages.It's got a 250gb disk with 3% space free. The disk is 93% fragmented. There is a sql data file of 231gb on that volume that i'm trying to shrink as it has 92gb of free space on it apparently. It won't shrink because the fragmentation is too bad - it just hangs for 2 days then times out. I've tried shrinking the files and database to no avail. I can't defrag as it just says it's finished after 2 minutes and doesn't do anything.Any suggestions or am I screwed? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-10 : 07:36:05
|
Is the file itself fragmented, or is it the content?You can run the CONTIG tool from former SYSINTERNALS to tell.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-10 : 10:36:47
|
| You can shrink the file with a "target size", which you could set to, say, 10GB less than the current size. (Maybe even start 1GB less than current size!!) If that works OK then you can try 10GB less ... and so on until you have cut it back to something more reasonable.Perhaps first you want to "reorganise" the indexes. You need to consider using in-place-defrag type method rather than t he "create a new index and copy everything across" method. I've done very little SQL 2005 so I can;t remember the syntax, sorry. See ALTER INDEX in BoL, or the DBA routines in Tara's Blog (here at SQL Team).Kristen |
|
|
|
christhetechie
Starting Member
3 Posts |
Posted - 2007-09-11 : 08:16:38
|
| None of the aforementioned methods are working unfortunately.The SPID dies after about 1 hour or fails completely.I've used red gate backup to runa backup and then restore it again but it's still fragmented (!). Argh! |
|
|
|
christhetechie
Starting Member
3 Posts |
Posted - 2007-09-11 : 08:17:14
|
| Sorry just to add - contig doesn't work either because there isn't enough free space. |
|
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
Catch-22 with fragmentation and shrinking