Decreasing the size of a database file through the Management Studio GUI?

Somebody told me today that he was using SQL Server Management Studio Database Properties (Files Page) to try to set the Initial Size property of a database file to a value lower than the one currently set for that file on the server. And even though the UI didn't complain about it, it wasn't actually changing anything. After changing the value, and clicking OK, returning to the same dialog revealed the value hadn't been changed.

Following is a list of screenshots which shows the steps followed to reproduce the behavior:

First, a new database is created using the Initial Size file property values inherited from model.

Later, we open the Database Properties for the newly created database, and changed the Initial Size value from 3 to 2:

Then we hit OK and saw nothing, therefore assumed it had been changed. But then we revisited the Database Properties dialog, and noticed the change wasn't effective. It was still 3 MB, the value used originally at DB creation time:

But if you go back to the dialog and try to set the property to a lower value and Script it, you get the following informational message:

The reason why no change occurs is because we don't set the Initial Size property when you set its value to something lower than what is actually set on the server is because the expression in red below doesn't evaluate to TRUE. And this is not a bug, this is by design. I'll explain further down why it is by design.

In the code chunk below, if num2 (which is the new value you have tried to set for Initial Size property of the file) is smaller than the actual size of the file, currently set on the server, then the Size property is not set.

Substracting a given value (num2 == new value you're trying to set) from a higher value (actual size of the file on the server) always gives a negative value which isn't greater than a positive value (1E-06), and therefore we don't set the Size property for the DataFile.

Following is partial code from Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabaseFilePrototype (as implemented in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlManagerUi.dll)

private void CreateOrAlterDataFile(Database db)
{
string str = this.IsPrimaryFile ? ".mdf" : ".ndf";
DataFile dataFile = null;
FileGroup fileGroup = db.FileGroups[this.FileGroup.Name];

.
.
.

    double initialSize = this.originalState.initialSize;
double num2 = this.currentState.initialSize;
if (!this.Exists || ((1E-06 < Math.Abs((double) (num2 - initialSize))) && (1E-06 < (num2 - dataFile.Size)) ))
{
dataFile.Size = num2;
}

.
.
.

}

Following is the managed stack of the thread when it is setting the size property of the DataFile.

0:018> !clrstack
OS Thread Id: 0x1534 (18)
ESP EIP
0cb1ec74 2e68e380 Microsoft.SqlServer.Management.Smo.DataFile.set_Size(Double)
0cb1ec80 30149b40 Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabaseFilePrototype.CreateOrAlterDataFile(Microsoft.SqlServer.Management.Smo.Database)
0cb1ecd4 301497b1 Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabaseFilePrototype.ApplyChanges(Microsoft.SqlServer.Management.Smo.Database)
0cb1ece0 301452be Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.ApplyChanges(System.Windows.Forms.Control)
0cb1ed34 301760f0 Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet.DoPreProcessExecution(Microsoft.SqlServer.Management.SqlMgmt.RunType, Microsoft.SqlServer.Management.SqlMgmt.ExecutionMode ByRef)
0cb1ed70 30eedb8c Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(Microsoft.SqlServer.Management.SqlMgmt.RunType)
0cb1ed80 30eede84 Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(Microsoft.SqlServer.Management.SqlMgmt.PreProcessExecutionInfo, Microsoft.SqlServer.Management.SqlMgmt.ExecutionMode ByRef)
0cb1edc0 30eece48 Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(Microsoft.SqlServer.Management.SqlMgmt.PreProcessExecutionInfo, Microsoft.SqlServer.Management.SqlMgmt.ExecutionMode ByRef)
0cb1edd4 30ef94f4 Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(Microsoft.SqlServer.Management.SqlMgmt.RunType, System.Object)
0cb1ee08 30f158ed Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.WorkerEntryPoint(System.Object)
0cb1ee40 793d87af System.Threading._ThreadPoolWaitCallback.WaitCallback_Context(System.Object)
0cb1ee48 793608fd System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)
0cb1ee60 793d8898 System.Threading._ThreadPoolWaitCallback.PerformWaitCallback(System.Object)
0cb1eff0 79e7be1b [GCFrame: 0cb1eff0]

And now I'll explain why it is by design. The topic Database Properties (Files Page) from the product documentation mentions the following, regarding the Initial Size property:

Initial Size
Enter or modify the initial size for the file in megabytes. This defaults to the value of the model database.

It's true that it doesn't mention anything about min or max accepted values, or how it behaves when you try to set an existing file's size to a lower size, but like the ALTER DATABASE topic explains you can only MODIFY FILE size if size is larger than current file size:

 

SIZE size

Specifies the file size.

• size
  Is the size of the file.

  When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.

That's it for today.

Comments

• Anonymous
  December 16, 2007
  Some weeks ago a colleague from another company called me for help in fixing a database performance problem.