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/
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:
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.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.
You are lost on autogrow, you are lost on filegroups. For your own sake, I hope this is not a production DB.
Having said that - here is how the DB spacing works:
Execute this in SQL Server Management Studio
sp_helpdb <yourdbname>
This should give you 2 sets of output.The first has a column called db_size, which will give you the current DB size. The second will give you independent sizes of you data and log files. Check the column here called maxsize.
Now execute this:
USE <yourDB>
GO
EXECUTE sp_spaceused
GO
The output of this query will tell you the unallocated space, or free space available in your DB file. When this fills up, your DB will either grow or will stop, based on various settings and circumstances.
When you initially create a DB, you define what is the initial size, how does the DB grow and what is the maxsize. If you create a DB with 3 GB initial size, unlimited growth on a HDD with 100 GB free space, the DB will continue growing, after the initial 3 GB is consumed, till it fills up all the available free space in your disk. However, if you create a DB with initial size of 1 GB and set the maximum to 3 GB, then your DB will stop when it grows to 3 GB, even if the HDD has another zillion TB of free space.
Please do not change the recovery model to simple if this is production. You will lose the ability to recover to a point in time.
No matter what you do - Step 1 : BACKUP YOUR DB
