Menu
In order to hide the output of DBCC SHRINKFILE I tried:
CREATE TABLE #Swallow(DbId int, Fileld int, CurrentSize int, MininumSize int, UsedPages int, EstimatedPages int)
INSERT INTO #Swallow EXEC('DBCC SHRINKFILE(''filename'', 0, TRUNCATEONLY)')
but it returns the following error:
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.
How can this be done?
837
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. You can shrink a file to a size that is less than the size specified when it was created.
If you shrink a database data file with NOTRUNCATE which causes massive index fragmentation and then a SHRINKFILE operation followed by an index rebuild this will push the database back to the original size. I am not an advocate of shrinking SQL Server database data files unless it is absolutely necessary.
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.
The file size hadn’t changed at all! You see, SHRINKFILE doesn’t truncate the file until the desired size is reached or there is no more free space to squeeze out. Checkpoints?
It seems there's a WITH NO_INFOMSGS option:
DBCC SHRINKFILE('filename', 0, TRUNCATEONLY) WITH NO_INFOMSGS
Ref: http://ss64.com/sql/dbcc_shrinkfile.html
134
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
