SQL Server 2005: File Resizing

Question

Hi all,

I have some databases that have four files each; one for PRIMARY, IDX, IMAGE, LOG their initial sizes were set to 200MB each but they are consuming far less space, about 100MB total.

1) is it possible to resize the database files while they are in use down to more reasonable sizes, say "current consumption" + 10MB?

2) is it possible to merge these files into just a single .mdf and .ldf?

My preferred answer to this would be some T-SQL, but I will accept links to MSDN (or other sites) with a reference for what T-SQL to look for.

Answer 1

1) DBCC SHRINKFILE or SHRINKDATABASE. There is also a context click in the SQL Management Studio for all tasks.

If they are set to auto grow, then they will do just that, and undo your shrinking. Shrink ops are best for when there is a large data removal that has taken place in the db, or when the server is going to crash from low disk space.

2) Yes, but its ridiculously difficult. You will have drop all tables and create them on the target filegroup and then repopulate them. There may be a way to do this using the RESTORE options, but IMHO you are better off leaving them if there is less than 10 files.