About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Tuesday, October 26, 2010

Watch out for hidden DBCC SHRINKFILE code in SSMS 2008

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. 

No comments:

Post a Comment