Behaviour of SQLServer if the drive is full 1

[RiyaKumar]

Hi,

The datafiles of our SQLServer databases reside in 'D' drive and the databases seem to grow rapidly. Iam just wondering what happens if the D drive becomes full. Whether SQLServer will throw some error?

Thanks in advance for the replies!

 

[SQLBill]

It will error out AND will 'freeze up'. The error will be something to the effect that there is no more room on the drive, truncate the log to free up space. Or something similar.

It's probably your TRANSACTION LOG that is growing rapidly and not the data files. Are you doing backups? (You should have at least done a full backup when you first created the database). What recovery mode is your database using? (In Enterprise Manager, right click on the database, select Properties, go to the Options tab. Find the recovery mode). If it is FULL, you should be doing Transaction Log backups regularly. If it is in SIMPLE mode, you might consider truncating the Transaction Log regularly.

Refer to the BOL for more information on the Transaction Log and Recovery Modes.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[RiyaKumar]

Thanks a lot for the quick response.

We use a 'Simple' recovery mode. Moreover I found that the data file is the one which is growing as
the usage is more. When I checked the properties of the database it said there is '0' space to grow but still continues without throwing any error message. I couldn't guess what is happening in the background. Does SQL Server has some mechanism to handle this(like shrinking) till the situation becomes worse?

Thanks,
RiyaKumar

 

[SQLBill]

In Enterprise Manager, right click on the database, select Properties and go to the Data file tab. Is it set for Autogrow (checked)? If so, then it just might be 0 room at that time, but in the process of autogrowing. Also check to see if you have Autoshrink checked.

What might be happening is that the database autogrows as needed and then shrinks the database (gets rid of the free space). That would explain the 0 room to grow because it hasn't expanded yet.

-SQLBill

 

[RiyaKumar]

Thanks again.

In the Data file tab of the database properties, the database is set to autogrow by 10% allowing unrestricted growth. Whereas,in the General tab, the 'space available' still seems to show 0.0 MB. And yes, AutoShrinking is enabled. Moreover,the database growth was observed many times in recent past. This confuses me little bit.

-RiyaKumar

 

[SQLBill]

Here's what happens.....your database needs more space. It autogrows by 10%. But the database does not need that much space. So after the transactions are completed there is space left. The database then autoshrinks. No more freespace (space available) is left.

So SPACE AVAILABLE = 0 does not mean anything except the assigned database space is full and will have to grow if there is more data to be added.

If you are autogrowing, you can really ignore that. It's mainly for when you don't allow autogrow. This would tell you that you need to expand the database space prior to adding more data.

Does that 'unconfuse' you?

-SQLBill