June 29, 2006 at 4:53 am
Hi All,
I currently have a problem here, its to do with managing space on one of my servers. This server holds a very large database, to be precise 800GB size. We drop tables at some point in time and also shrink afterwards to free up space.
In trying to shrink the database, its taking over 8 hours and the CPU capacity is hitting 100 %, so I had to stop it.
The actual space used on the server after running sp_spaceUSED is as follows:
Database size: 805679.38 MB
Unallocated space: 143749.79 MB
Reserved: 472913752 KB
Data: 388724378 KB
Index Size: 186791342 KB
UnUsed: 896523 KB
Number of Filegroups = 1
I have recommended some solutions here, they are as follows:
We have multiple filegroups for different kinds of data
Indexes be put on a separate filegroup
Reducing the size of the datafile first so that we can start implementing filegrouping.
Defragmenting the indexes
In the meantime, I need to reduce the size of this 800GB database, before I can start to implement the use of filegroups.
How can I go about doing this and also what recommendations would you all make ?
While attempting to shrink, what is the best command to use in my situation ?
June 29, 2006 at 6:43 am
John
Multiple filegroups are good for a VLDB, since you can back up individual filegroups rather than the whole database, making your backup strategy more flexible. You will only see a siginificant performance gain if the filegroups are on separate physical disks. In that case, yes, put indexes on their own filegroup, and separate out tables that are frequently joined with each other, if you have enough disks. Putting tempdb on its own disk makes sense if your application uses temp tables.
Reindexing is also a good idea - do it once a week for a heavily-used OLTP database, if you have a large enough maintenance window. We have a process that runs DBCC SHOWCONTIG on each table, and reindexes if the table is below 80%.
I'm not sure why your shrink operation is taking so long. Are all the users off when you're doing it? It's not a requirement that they be off, but I imagine it may make it quicker if they are.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply