Increase data file size

In this recipe, we will increase the size of an existing secondary data file.

Getting ready

We will use the datafile1 data file in the TestDB database, which was created in the recipe Adding secondary data files to a filegroup. We will increase the size to 1 GB. Feel free to substitute this with a secondary data file from a database that exists in your environment.

This recipe will accomplish this T-SQL equivalent:

ALTER DATABASE [TestDB]
MODIFY FILE (
NAME = N'datafile1',
SIZE = 2048KB)
GO

How to do it...

To adjust the size of a secondary data files to an existing filegroup, follow these steps:

1. Open PowerShell ISE as administrator.

2. Import the SQLPS module and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "localhost"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

   Add the following script and run:

   $databasename = "TestDB...