When using SQL Server Management Studio (SSMS) version 2008, beware that when you change a database file's autogrow or max size properties, SSMS will throw in a DBCC SHRINKFILE command for each file whose properties you are changing. Depending on the size of your files, this may result in a very long time to finish processing.
There's no reason that SHRINKFILE should be done at this time. I believe this is an anomaly. I've only seen this in SSMS version 2008. I have not seen it in 2005 nor in 2008R2. It is dependent on the version of the graphical user interface (GUI), and not the version of the underlying database engine.
To avoid this problem, either change your database files' properties using T-SQL instead of the GUI, or if you do use the GUI, click the SCRIPT button, then click on Cancel instead of OK. A new code window will then appear with code similar to what is shown below. You can now manually remove the DBCC SHRINKFILE commands.Below is a sample of the code that SSMS will generate. Note that only the commands containing MODIFY FILE are needed.
USE [PaulTest1]GO
DBCC SHRINKFILE (N'PaulTest1' , 2)GO
USE [PaulTest1]GO
DBCC SHRINKFILE (N'PaulTest1_log' , 1)GO
USE [master]GO
ALTER DATABASE [PaulTest1] MODIFY FILE ( NAME = N'PaulTest1', MAXSIZE = 351232KB , FILEGROWTH = 234%)GO
ALTER DATABASE [PaulTest1] MODIFY FILE ( NAME = N'PaulTest1_log', MAXSIZE = 2000000KB , FILEGROWTH = 300000KB )GO
Last Tuesday Steve Jones of SQL Server Central gave an informative presentation at the Boulder SQL Server Users' Group called Common SQL Server Mistakes. He mentioned that DBCC SHRINKFILE and SHRINKDATABASE are used far too often.
