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.
