How to Manage SQL Server Database Files

Posted on May 18, 2009 by Sean McCown Microsoft SQL MVP in SQL Server with 0 Comments

Managing database files in SQL Server 2008 is fairly easy and most of it can be done in SSMS.  However, when you start getting into large numbers of files it can take a lot of time to manage them this way and it’s an error prone process to do everything manually so we’re going to show you how to manage your database files with T-SQL instead.  Trust us, while this seems like a beating compared to doing it in the GUI, we’re really doing you a favor because this method is far more flexible. Also, if you come across a database that has a lot of files you won’t have to learn a completely different skill to manage them effectively.  So let’s get into the different things you may need to do with your database files.

Log Files

We’re starting with log files because typically they’re the ones that will give you the most trouble in production.  One of the first things you’ll need to do is find out how big your log file is on disk and how full it is.  This can tell you right away if you’re dealing with an issue caused by the log filling up.

Sponsored

To query these simple log file statistics you open a query window in SSMS and type the following command:

DBCC SQLPERF('logspace')

This will give you the log space usage for every DB on the server.  So depending on what you find (we’ll pretend here that you found a log that’s full and needs your attention), you need to decide whether you want to grow your log file or back it up.  There are 3 reasons why a log will not grow.

  1. The log isn’t set to autogrow.
  2. The log is set to autogrow, but it has a maxsize limit set and it’s already reached the limit.
  3. The log has has autogrow with no maxsize limit, but it has filled up the current disk.

You can handle all of these situations the same way, but the first 2 have extra options.  First let’s talk about the common method:  creating a new log file.

As you may remember from our article on planning your data files, log files don’t use equal fill.  So you’ll need to create a new log file.  The easiest way to do this is to just add a new log file to the DB.  To do this, just open a query window in SSMS and type the following command:

ALTER DATABASE DBNameADD LOG FILE(NAME = LogicalName,FILENAME = 'filepath',SIZE = size,MAXSIZE = maxsize,FILEGROWTH = filegrowth)

Here’s a brief explanation of the options:

NAME – The logical file name.  This is the friendly name given to the files by SQL Server so you can work with them without having to know their location.

FILENAME – This is the full path to the file on disk.  This is the physical file path you’re mapping the logical file name to.

SIZE – The initial size you want your file to be.

MAXSIZE – The limit you don’t want your file to grow past.  This is useful if you need to ensure you save space on the disk for other things.

FILEGROWTH – This is how much you want the file to grow by when it autogrows.  It’s better to use a hardcoded value than a percentage.

Now here’s what the command may look like with all the values filled in.  You should run this from the context of the DB you want to add the file to.

ALTER DATABASE MyDBADD LOG FILE(NAME = MyDBLog2,FILENAME = 'F:\Logs\MyDBLog2.ldf',SIZE = 500MB,MAXSIZE = 5000MB,FILEGROWTH = 500MB)

Expanding to a new log file will certainly get you going again, but there are other options depending on what your situation is.  If you need to set the log to autogrow (#1 above), simply modify the DB again and add the option like this:

ALTER DATABASE MyDBFILE(NAME = MyDBLog2,AUTOGROW = 500MB)

MODIFY

And if you need to remove the MAXSIZE limit so the file can grow as much as the disk will hold:

ALTER DATABASE MyDBFILE(NAME = MyDBLog2,MAXSIZE = UNLIMITED)

MODIFY

And of course if the log is getting too big for the drive and is causing other stuff to run out of room then you can prevent it from autogrowing like this:

ALTER DATABASE MyDBFILE(NAME = MyDBLog2,AUTOGROW = 0)

MODIFY

Sponsored

Data Files

Now we’re going to deal with data files, but the good news is that a file is basically a file and working with them is essentially the same.  The only difference really is that you’ll have some different parameters in each one that pertain only to that file type, but the commands work the exact same.

Since we’ve already explained the bulk of the commands above we’re only going to talk about one of the new options that’s interesting most of the time.  We’re going to talk about the ‘To FileGroup’ option.

Sponsored

Let’s go ahead and assume that you’ve separated your DB into filegroups and your index filegroup is full and you need to expand it.  Note that there’s nothing special about an index filegroup except that you’re using it for indexes.  You could use it for any type of data you want.  So here’s how you would add a data file to your index filegroup.

ALTER DATABASE MyDBADD FILE(NAME = MyDBIndex2,FILENAME = 'F:\Logs\MyDBIndex2.ndf',SIZE = 500MB,MAXSIZE = 5000MB,FILEGROWTH = 500MB) TO FILEGROUP PrimaryIndexes

And if you don’t specify the filegroup at the end the new file will be added to the default filegroup and will not expand the PrimaryIndexes filegroup.

You may also have noticed that there’s not a ‘TO FILEGROUP’ option for the log files.  Well, remember I said that logs are written to serially?  Because of this there would be no reason to put a log on a filegroup so the option doesn’t exist.

We hope you see how easy it is to manage your database files using T-SQL.  There are more options you can choose from, but it’s easy to look those up and plug them into what you already know.  And by taking the time to learn how to do it in code, you’re setting yourself up for success when you have to manage a lot of files.  And remember, it’s really best to grow files by a specific value than by a percentage.  As the files get bigger, so does the percentage and therefore your files grow by a greater amount each time.  And it’s much harder to predict what size the file will be in 6 months.

Sponsored