Contents
Can a shrinkfile be used to shrink a database?
Shrinks the current database’s specified data or log file size. You can use it to move data from one file to other files in the same filegroup, which empties the file and allows for its database removal. You can shrink a file to less than its size at creation, resetting the minimum file size to the new value.
How does emptyfile move data from one file to another?
In other words, EMPTYFILE migrates data from a specified file to other files in the same filegroup. EMPTYFILE assures you that no new data gets added to the file, despite this file not being read-only. You can use the ALTER DATABASE statement to remove a file.
What does DBCC shrinkfile ( Transact-SQL ) do?
DBCC SHRINKFILE (Transact-SQL) Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.
How can I move database files in SQL Server?
In SQL Server, you can move system and user databases by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement. Data, log, and full-text catalog files can be moved in this way.
Why are tempdb data files not shrinking well?
TempDB data files don’t shrink well on SQL 2008. So ideally you want to pre-size your TempDB data and log files appropriately so that this isn’t a concern, but sometimes a rogue developer runs a crazy huge query on a production server during work hours, causing the TempDB data files to blow up huge.
What to do if file size does not change after shrink?
If the file size doesn’t change after an error-less shrink operation, try the following to verify that the file has adequate free space: Run the following query. Run the DBCC SQLPERF command to return the space used in the transaction log. The shrink operation can’t reduce the file size any further if there’s insufficient free space available.