CODEWITHSUNDEEP
sql-server-2005
Caveatrob
Caveatrob

Reputation: 13267

Shrink data files below initial size SQL Server 2005

Inherited a database with min size for one data file (primary) at 10 gigs and the min size for a secondary file to 6 gigs.

  1. Why would someone have created a secondary data file (NDF)?
  2. How do I shrink these below the initial sizes set for the fiels.

Upvotes: 1

Views: 5982

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

1. Why would someone have created a secondary data file (NDF)?

This is a way to get create two files on the operating system. Some advantages:

  • 2 file handles
  • possibility of splitting across different disks or storage subsystems (fast/slow) even if you don't do it right away
  • prepare for partitioning
  • controlling per-file operations, like shrinkfile below
  • allows a large db on a prod system to be restored onto a test/dev system that does not have a single large enough disk for single-file. Thus 2x 400GB files on a prod system (same folder) is restored onto 2 different 500GB disks on the dev system.

2. How do I shrink these below the initial sizes set for the files

Use DBCC SHRINKFILE instead of DBCC SHRINKDATABASE. It can be set to any desired size, even smaller than initial, as long as it is greater than the size of data currently stored. To reach smaller sizes, set the target

DBCC SHRINKFILE (1, 0);  -- file id 1, to 0% free space

To find out the file ids, you can use (while in the db)

select * from sysfiles

Upvotes: 2

bobs
bobs

Reputation: 22184

  1. You would create secondary data files to distribute your database over multiple disk drives.

  2. You can't shrink the original file below the initial size. You can shrink the NDF file so that it's empty (see DBCC SHRINKFILE, EMPTYFILE option), then drop the file and recreate it (if you want) at the size you want. The shrink will move the data to other files in the file group.

Upvotes: 0

Related Questions