When moving databases around, often people will truncate the logs and/or run DBCC SHRINKDATABASE but those only reduce the actual used space. They don’t actually shrink the database files themselves. DBCC SHRINKFILE is a bit picky in that in you pass it the logical file names. If you have the normal setup where you create a database with MDF, LDF and NDF, then it can get cumbersome to look up those names. What I use is the following piece of SQL which will shrink all the files for the current database. Note that SHRINKFILE only works on the current database.
USE [YourDatabaseGoesHere] go IF DATABASEPROPERTYEX(N'{0}', N'Status') IS NOT NULL BEGIN DECLARE @fileName SYSNAME DECLARE @command NVARCHAR(1024) DECLARE curFiles CURSOR FOR SELECT [name] FROM sys.database_files OPEN curFiles FETCH NEXT FROM curFiles INTO @fileName WHILE @@FETCH_STATUS = 0 BEGIN SET @command = 'DBCC SHRINKFILE(' + @fileName + ', 0, TRUNCATEONLY);' PRINT @command EXEC(@command) FETCH NEXT FROM curFiles INTO @fileName END END;
Happy Coding!