0

Our database server has 2 disks; disk C has 40GB free space according to file explorer and disk D is full. The database is comprised of partitions stored in both drives. When I run the following SQL command to see the available space left on the database 

select name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a

I see that space left on the mdf file is at 3Gb and decreasing every day. When there is no space left will the database simply crush? Or will it auto-grow and gain some space that is available on C drive? How can I set it to automatically request more hard drive space?

Konstantinos Papakonstantinou
  • 977
  • 3
  • 15
  • 33

1 Answers1

1

Normally, databases aren't comprised of partitions; they are comprised of filegroups, which in turn may have one or more files. Without knowing any details, I can only describe a general behaviour.

If you have several files in the filegroup being actively written, and these files are distributed across both drives, SQL Server will grow (and perform writes into) files on disks with free space available. Any other cases will result in a database becoming mostly read-only (it might still allow some modifications of existing data - depends on the amount of free space left on each particular page). Deletions, however, might still be possible - never tested such a scenario myself, though.

A special case is when you run out of space for your transaction log. When this happens and SQL Server is unable to grow it, the database becomes completely read-only.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • When I said partitions I meant files. Our database is not well structured and not following best practices, currently there are 16 files each belonging to a different filegroup and placed in 2 drives. There are 4Gb left on our transaction log, so that's fine I guess, no danger there. But daily I see that the main mdf file is being used for write operations, hence decreasing in free space. My question is if it's possible to add more space to this mdf file, since the hard drive seems to have more than 40GB left. – Konstantinos Papakonstantinou Oct 08 '14 at 07:04
  • @KonstantinosPapakonstantinou, you may add another file into the most troubling fliegroup and place it onto this drive. SQL Server should start using it almost exclusively, until available free space will become more even. – Roger Wolf Oct 08 '14 at 08:27