How to Change Database File Size? – Interview Question of the Week #296

Question: How do I change a database file’s size? To grow a file deliberately, use ALTER DATABASE MODIFY FILE with a larger SIZE. To reduce an allocated file, use DBCC SHRINKFILE when there is a justified reason and enough reclaimable space. Changing size does not always mean shrinking.

The original experiment reduced SQLAuthority’s data file from 250 MB to 200 MB. That direction is important: SSMS generated a shrink command because the new requested size was smaller. I still do not recommend making shrink a daily maintenance habit.

See the script before running the operation

In Database Properties, open Files, change the intended Size (MB) value, and select Script to inspect the command. The original picture shows the reduction to 200 MB, not a general recipe for every size change:

-- Original historical command, not a command for your current database.
-- USE [SQLAuthority];
-- DBCC SHRINKFILE (N'SQLAuthority', 200);

The argument is a logical filename and a target size in MB. It is not a path to the MDF file. Check the current database and logical file before choosing any operation.

-- Read-only inventory in the database you intend to inspect.
SELECT name, type_desc, size / 128.0 AS allocated_MB,
       FILEPROPERTY(name, 'SpaceUsed') / 128.0 AS used_pages_MB
FROM sys.database_files;

-- Operation templates only, NOT an instruction to change your current database.
-- Pre-size a reviewed data file upwards:
-- ALTER DATABASE [YourDatabase]
-- MODIFY FILE (NAME = N'YourLogicalDataFile', SIZE = 300MB);
-- For an exceptional approved reduction, in YourDatabase:
-- DBCC SHRINKFILE (N'YourLogicalDataFile', 200);

Do not pay to shrink space that you need again

Data-file shrink can move pages, fragment indexes and consume I/O. If normal workload will grow the file again, shrinking merely creates extra work. Keep an appropriate growth increment and enough headroom instead.

Log-file shrink has different constraints: reusable log space and inactive virtual log files at the end determine what can be released. It is not a substitute for log backups or resolving a transaction that prevents reuse. A requested target is not a guarantee of the final file size.

My earlier shrink and fragmentation, data-file shrink and stopping a shrink operation posts explain the practical concerns.

Choose the operation from the intended direction, rather than treating SHRINKFILE as the only answer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.