Database running out of space

My database has 16MB of space left.

I used to just truncate as I was taught but I found these links that advise against truncating:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/

Is there anything else I can do on my database to reduce the size other than deleting table records? I am new to the DBA forum and I probably should have looked around for other questions before posting but I am desperate as I am worried about my database going down.

If your database has 16 MB of free space, and the drive it's on has plenty of free space, then don't worry about it. As long as there is sufficient space and you haven't disabled the database's ability to autogrow, then the database will grow the data file when it needs to. Of course you should set your data file's autogrow to some realistic increment, balancing duration it takes to grow with the frequency it will have to grow. The defaults of 1MB or 10% are not realistic at all, and should have been changed ages ago. If you have instant file initialization enabled you can lean towards larger, but I have no idea what a good balance for you might be.

You can check autogrow settings here:

SELECT name, type_desc, size, growth, is_percent_growth
  FROM [database_name].sys.database_files;

Ideally you will create the database with sufficient size to avoid autogrowth at all, since this operation can be expensive (especially if you don't have instant file initialization) and can block all other activity for the duration of the growth event. You may want to manually expand the file now or during a maintenance window to avoid this happening during peak activity. So proactively you can do this (e.g. to change the auto growth setting to 100 MB):

ALTER DATABASE [database_name] 
  MODIFY FILE (NAME = N'database_name_data', FILEGROWTH = 102400KB);

If your drive has 16 MB of free space, then you need to free up some space somehow, or move the database to a drive that has more space. It's possible that the drive has been taken up by an overgrown log file, probably because you're in full recovery model and have never taken a log backup. In this case you can either:

• Switch to simple recovery mode, run CHECKPOINT, and then DBCC SHRINKFILE to make the log a reasonable size. Resist the temptation to shrink the file to 1 MB; you don't want to get into a tug-of-war with your disk space.

• Stay in full, then BACKUP LOG, and then DBCC SHRINKFILE with the same caveats as (1).

Now, it could also be that you're in simple mode and the log has ballooned to support some atypically large transaction. So you may still be able to do (1) without any worry, but if you're going to be running these large transactions again, or not doing (1) or (2) regularly, then you're only "solving" the problem temporarily. Get a bigger disk or use full recovery model and manage your log much more effectively.

That all said, this is just a guess; maybe your drive is actually being taken up by a large (and useless) page file, or your MP3 collection, or videos from your last vacation.

StackExchange Database Administrators Q#42539, answer score: 10