Welcome to this post on file growths and shrinking in SQL Server. In this post I would like to discuss the file growths, sizes,
shrinking and how they impact daily use.

First the elephant in the room

File Shrinking

Not much to say here but don't, just don't!
It's very well documented that shrinking files, whether data or log, is a bad practice and totally unnecessary.
Typically, what I have seen over the years in my career, is when an administrator or DBA (junior or cross platform) sees
free space in a file(s) and shrinks the file(s) as it's viewed as an unnecessary use of space.

We all know that for data files, it will reintroduce internal fragmentation in any indexes, which is not ideal especially
when your maintenance jobs spend a lot of time keeping them healthy. When it comes to log files, I've seen
scenarios where administrators have set up daily jobs to shrink the log ( it will only grow again! ).

If proper capacity and planning has been performed, you will naturally have a set amount of free space within your files
to prevent constant growth placing undue stress on the storage sub systems. The files should be right-sized and grown
out to your expected 3 year size to provide sufficient data storage for the incoming data.

For the transaction log, testing will provide you with see the sweet spot size that is required to prevent constant growth
and an unnecessary increase in log VLFs, if you know the space will be used why remove it?
This leads us nicely onto file growths and sizes.

File Sizes and Growths

Where large transaction logs are encountered, it's important to realise that the transaction log has grown to that size
as that is the space it requires at busy periods to handle the transaction rate between log backups. To help maintain a
more controlled log size consider increasing the log backup frequency during busy times.

For example you may have your log backups run every 20 mins throughout the day, but if you know that from
11pm to 1am you have a large daily import it would make sense to add a new schedule that takes log backups every
5 minutes or so during that time window.

The mileage will vary on this and you should test to find the sweet spot but the flexibility is there if required.
There is no need to continually shrink log files only for them to grow rapidly again when an import job or
index maintenance job launches. Furthermore, your storage admins will likely thank you for reducing the stress on the
storage subsystem and may even be impressed that you have taken steps to ensure that the storage system suffers
no severe consequences.

What's the way forward

A positive start can be achieved by right sizing the storage and the database files by understanding the possible sizes
required for each. When implementing any application deployment, engaging with the vendor to obtain expected database
size information is the best route available. Microsoft also provide a database sizing tool to help with size estimations.

The Microsoft sizing calculations can be found at the following link:

Microsoft Database Size Calculations

For in house developed applications, the sizing calculations are even more valuable and provide an opportunity to ratify
database design by spotting columns that may benefit from a reduced\different data type to that which is proposed.

As an example, if a column is spotted as AddressLine1 and it's data type is defined as varchar(MAX), there's a valid
reason to challenge this data type usage for something a little more appropriate.

Be mindful of the database file sizes and track usages over periods of time and where auto grow is used set realistic
file growths to prevent regular unwanted growths.

In this article I will be discussing the moving of database files within a SQL Server instance. We'll also work through a typical move scenario, looking at the scripts we should use and the meta data available to help us.

Let's first begin with what we do know.

Under SQL Server 2000, altering database paths was all but limited to the TempDB database. You had to detach the user database and then re-attach it, or you could use backup, then restore. Hmm, messy to say the least. 

Under normal operation, once SQL Server has a handle on a database you will not be able to manipulate the files at the OS level. Any attempt to copy the files, etc., will result in the dialog box below. 

To address the first point, thankfully in SQL Server 2005 onwards, this is no longer necessary, and in fact SP_ATTACH_DB has been deprecated and will be removed in a future version of SQL Server. You should now use:

CREATE DATABASE ... FOR ATTACH

To address the second point, in order to release the handle the database engine has on the user database files we merely need to Offline the database. We do not need to stop the SQL Server services.

Let's just re-cap that; we do not need to stop the SQL Server services.

You may issue the following command to Offline the database;

ALTER DATABASE [yourDB] SET OFFLINE

If you have active connections and wish to roll them back and take the database offline you may do so using;

ALTER DATABASE [yourDB] SET OFFLINE WITH ROLLBACK IMMEDIATE

Once the database is Offline you may move and\or rename your database files. Just remember that if you delete or rename the files, the database will not come back online again. When attempting to Online the database you will usually receive an error along the lines of: 

You must first amend the system catalogs to provide the new paths\filenames, this is done using the ALTER DATABASE command passing in the MODIFY FILE parameters as shown in the following query construct:

ALTER DATABASE [yourDB]
  MODIFY FILE ( name=logicalfilename, 
                filename=N'c:\folder1\folder2\adbfile.mdf'
              )

Important Note: When using the T-SQL command above, SQL Server will accept whatever you type and issue in the ALTER DATABASE statement so be careful and check your typing!

For example this would be valid

ALTER DATABASE [yourDB] MODIFY FILE(name=logicalfilename, 

filename=N'c:\MSSQL\DATA\gobbeldygook.dat')

If the path\filename does not exist when the database tries to start you will receive an error!!

Moving the Files

With the above in mind, let's look at how we would achieve the goal of moving a databases disk files to new locations.

Our Scenario

The server drives are filling up quickly and you have been asked by the manager to move the disk files to a new set of drives provided by the Windows administrator. The engineer has created your new file paths for you and retained all NTFS ACLs required for the SQL Server services. The drives\paths supplied are as follows (I am using my C drive but this could easily be G or F or some other drive letter);

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBA\MSSQL\Data

Before making any changes to the OS files and their locations first check the metadata available to you in the following system catalog master.sys.master_files. The important metadata to collect consists of the Logical Filenames and the Physical Names. You may obtain this information using the following query;

SELECT database_id,
       name, 
       physical_name 
 FROM sys.master_files 
 WHERE database_id = DB_ID('SampleServiceCompany2009')

For my database I have the following: 

I need to amend these paths from the "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory to match the new location provided to me. The first task is to construct a set of T-SQL move commands. The ALTER DATABASE ... MODIFY FILE command really only needs 2 parameters for the file move: the files logical name and the physical_name. Both of these are retrieved in the query shown above. The move commands are extremely simple and as shown earlier take the following form; 

Issuing these commands now will update the system catalogs, however, the new paths will not take effect until the database has been restarted (or taken Offline then brought back Online). The following message informs us of this: 

I'll now take the database Offline using the command highlighted below. 

With the database Offline, I may now move the files(and even rename them if I really wanted to).

A word of caution here. It is advisable to copy and paste the files to the new locations. Only when the database comes online successfully would I then remove the old files.

Once you have copied the files to the new locations you would then bring the database Online, this is done using:

ALTER DATABASE [yourDB] SET ONLINE

Shown below are the typical screenshots you will see if the database fails to start. From the information dialog below click the message column and you will see details of the issue. 

The error details show below provide an indication to the issue, the files probably do not exist (in fact that's generally exactly what an OS Error 2 means). 

If the database starts successfully you will see the following 

Help, My Database Won't Start

In every scenario I have encountered whereby a database file move has failed, the issue has been down to a mistyped path and\or filename, resulting in the DBA then getting into a vicious loop trying to correct themselves. Should your database fail to start, don't panic. Perform the following tasks;

• Check the script you used to modify the database file locations, have you got the paths and filenames correct?
• Have your admin check the permissions to the new path for the SQL Server Database Engine service account.
• Query the catalog master.sys.master_files, do the paths\filenames here match what you're expecting?
• If you are unable to complete the move successfully, revert back to the original file paths\names. As you left the files in the source directory simply issue the appropriate ALTER DATABASE ... MODIFY FILE statements and bring the database back online.

Querying master.sys.master_files to obtain the current database file paths 

This is a very easy task to complete providing you pay full attention to the task in hand. If you encounter an error, stop and review what you currently have in place both from the system catalog metadata and the physical OS locations\filenames.

As always, enjoy and if you're still stuck post back and I'll help all I can