Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
@swasheck this is a first for me. When you say any other storage, like servers or something and you completely lost me at filegroups – user1270384 May 20 at 0:20
2  
Your database has 16 MB of space left, or the drive it's on has 16 MB of space left? – Aaron Bertrand♦ May 20 at 0:32
No the database server has a 117GB disk but whoever intially created the database only created it at a size of 3GB and in my properties section of the database I can see size as: 3293.06MB and space available: 3.15MB(as at now) shouldn't I be worried? – user1270384 May 20 at 2:10
1  
No, see my answer (which I started before your comment came in). The database will grow as long as there is room on the drive and you haven't turned off autogrow. – Aaron Bertrand♦ May 20 at 2:23
ok thank I saw your answer, where do I check for auto grow? – user1270384 May 20 at 2:40

2 Answers

up vote 7 down vote accepted

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:

  1. 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.
  2. 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.

share|improve this answer
Thank you @Aaron Betrand, but you lost me at Auto Grow. I've heard about auto shrink. Where can I check whether its been set to auto grow like you say? – user1270384 May 20 at 2:39
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.- in actuality I found that the database has records going as far back as 2000 and I've begaun removing them. I also saw this article on index maintenance sqlserverpedia.com/wiki/Index_Maintenance which I ran a check on my db and found one particular table has a page count of 94707 so I was hoping to run this script would that be wise? – user1270384 May 20 at 2:45
up vote 4 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.