Disk space, dbcc shrinkfile can be your friend

So I just had a friend who had a disk space problem on one of her sql server test environments. Basically the users were doing more work, training and using more disk space then expected. Not all databases are created with multiple files and spreading out of the data as a matter of fact more sql server database have been set up, configured as simple databases than not.

For temporary relief a new file was added to the database on an additional drive while they considered what they wanted to do. So the following was performed to allow the users to continue their work.

ALTER DATABASE [test] ADD FILE ( NAME = N'test_data1', FILENAME = N'N:\MSSQL10.MSSQLSERVER\MSSQL\DATA\test_data.ndf' , SIZE = 307200KB , FILEGROWTH = 102400KB ) TO FILEGROUP [PRIMARY]

This added a new file on her additional drive of 300 mb with autogrow set to 100 mb.

Did they want to add disk or limit what the user community was doing on the system. The option to add disk space was decided upon and additional space was added by the systems team to her other drive.

Now she wanted to get rid of the file that was added to her additional drive and get back to a very simple database file configuration. So what could she do?

DAT-TA-DA!

DBCC shrinkfile comes to the rescue. There is an option on the dbcc shrinkfile called emptyfile
emptyfile basically moves all of the data out of the current file and into other files in the same filegroup.

By performing the shrinkfile with the emptyfile option she would be able to remove the additional file that had been created.
So I recommended that she do the following:

DBCC SHRINKFILE (test_data1, EMPTYFILE);
GO

Then to drop the data file from the database

ALTER DATABASE test
REMOVE FILE test_data1;
GO

This moved the data back her original file and then allowed her to get rid of the additional file that was added. Now she can go back and consider how she really wants to handle her files and filegroup structure but that is a different conversation.

Happy dba'ing and remember:

Help I'm a DBA...
there really is a lot of resource help out there.