Quick post on how to check how much space is free in your sql data files.

SELECT
	a.FILEID,
	[FILE_SIZE_MB] = 
		CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)),
	[SPACE_USED_MB] =
		CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
		CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) ,
	NAME = LEFT(a.NAME,30),
	FILENAME = LEFT(a.FILENAME,60)
FROM
	dbo.sysfiles a

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.