CODEWITHSUNDEEP
sql-serverwindows-server-2008spacedrive
Konstantinos Papakonstantinou
Konstantinos Papakonstantinou

Reputation: 1007

Hard drive space vs Database space

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?

Upvotes: 0

Views: 2706

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7692

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.

Upvotes: 1

Related Questions