resize db files

Question

resize db files

devereauxj

Hall of Fame

Points: 3263
More actions
August 2, 2004 at 7:05 am

#83514

I took over a database that has 2 datafiles that are 4.7 and 3.8 gig each. I want to reconfigure the data to more managable file size (2048) which is also our standard. I know how to do this in Oracle, export the data, recofingure the files in tables spaces and import the data again.
What is the best way to do this in SQL Server 2000? This is a 7 day a week system, but I have from 1:00am to 6:00am to do the work. I do have a test server to play with.

Thanks,
Joseph

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff W SSCertifiable Points: 5207 More actions · Answer 1

Been there with this one. I have a question before I start to ramble...How many tables and indexes are you dealing with?

"Keep Your Stick On the Ice" ..Red Green

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Curious why you htink 2048 is more manageable?

To do this, if you have free space, you can execute a dbcc shrinkfile to lower the size of the existing databases. I'd do that first. Then if you need more space, create 1 or 2 more data files, make them close to the size or 2048 that you need. Don't have them autogrowing since that is a relatively expensive process.

Now you need to move data over there. If you move a clustered index to a datafile, the data moves, so in EM (the easy way) you can specify a new data file for your clustered index and the data will move. Once you have enough stuff moved (migth need to experiment), you can shrink the first files.

Jeff W SSCertifiable Points: 5207 More actions · Answer 3

Steve...I think they have a standard of 2048. I think it kind of takes off of the Oracle Extent concept (and OS block size). I used to have my SQL Server files set to some factor 8 because of the page sizes..After doing some math I realized it was kind of irrelevant.

Enterprise Manager would be great for a few tables, but not thousands.

"Keep Your Stick On the Ice" ..Red Green

devereauxj Hall of Fame Points: 3263 More actions · Answer 4

2048 is our standard. In some cases we end up zipping down the files and ftp them from one site to another. This is basically a convience and standards issue. I don't have a techincal issue with the size of the files.

There are 720 tables and 1398 indexes. We don't have a cluster. Straigh 0+1 raid.

thanks,

Joseph

Jeff W SSCertifiable Points: 5207 More actions · Answer 5

Do you have clustered indexes?

"Keep Your Stick On the Ice" ..Red Green

devereauxj Hall of Fame Points: 3263 More actions · Answer 6

Yes. We use clusterd primary keys. Where the primary key is not clustered, we generaly have a compound index clustered on fields that are more commonly used in the "where" clauses.

Joseph