Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Quickly Viewing Available Space

By Boris Baliner,

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.

Total article views: 9324 | Views in the last 30 days: 5
 
Related Articles
SCRIPT

QA-Taskpad

Script to show the Database information similar to what is shown in EM Taskpad

FORUM

Doesn't look like Taskpad is coming back

I don't see Summary of local taskpad

FORUM

Sql 2000, Database don't have available space

Sql 2000, Database don't have avialable space

FORUM

Taskpad shows 0 space in DB

Hi, The taskpad view of a db is reporting 0MB available even though there are 2 additional data_fil...

ARTICLE

Managing Disk Space

How often do you worry about your database size and free space? Steve Jones asks how you administer ...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones