View Available Space in SQL Server 2000 databases using

SQL Server 2005 Management Studio.

Introduction

In March I wrote an article entitled “SQL 2000 to SQL 2005: Where have all the

old features gone?”, that dealt with accessing SQL Server 2000 with SQL 2005

tools. Initial idea was that many DBAs are probably using such setup to get

accustomed to the new tools, although their employer has not made the move to

the SQL Server 2005 yet.

In March article I forgot to mention what I now consider to be an important addendum: how to quickly view Available Space in the files of any given database.

Taskpad functionality

Basically we want to be able to see the free space left in

database files, and if you are not connecting to SQL Server 2005 you are not

enjoying the latest/greatest colorful Reports in the Summary tab. Oh No, life

is worthless, you say…but wait, don’t jump off the cliff yet, because here

comes the solution. Simply run the following code in the context of your

database:

CREATE PROCEDURE dbo.usp_ShowAvailableSpace 
AS
SELECT name AS NameOfFile,
  size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
 FROM dbo.SYSFILES
GO

Then execute:

EXEC usp_ShowAvailableSpace

You should get something like this:

If you track what Taskpad does, it does precisely that, except

that it rounds it off to the hundredth of a megabyte. If you really what the

same functionality as Taskpad, just run the following code:

Summary

This is a quick little tool get back what Taskpad offers to

Enterprise Manager users. So, until you convert all your servers to SQL 2005

this may prove helpful, hopefully.