Solved

Find database size in query analyzer

Posted on 2004-09-03
15
4,414 Views
Last Modified: 2007-11-27
For SQL Server 2000, I'd like to know the way of looking the database and log size with the Query Analizer. sp_spaceused gives only the current usage. I want to find the total size of the database.

Thanks,
Robinson.
0
Comment
Question by:son_robin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 11975201
Try sp_helpdb
0
 
LVL 4

Author Comment

by:son_robin
ID: 11975320
This gives the current size only. I want the Total Size of the DB and/or Space Available. If you click on the Database properties from Enterprise Manager these details are there. I want to find from query analyzer.
0
 
LVL 14

Expert Comment

by:Jan_Franek
ID: 11975461
And what about sp_helpdb with database name. It shows me total size of all segments and also free space on each of them. But I'm on Sybase and may be, MS SQL will show you something else.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:SNilsson
ID: 11975678

You can get this using T-Sql, I have scripts that will give you DB size, backup size and space left on disc X.

But I cant provide it until I'm back to work on monday.
0
 
LVL 4

Author Comment

by:son_robin
ID: 11975942
I will wait for monday Nilsson. Thanks a lot
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11976210
Here's what EM does (amongst a lot of other things)

select [Total size in KB]=sum(convert(float,size)) * (8192.0/1024.0) from dbo.sysfiles

exec sp_spaceused

DBCC SQLPERF(LOGSPACE)

0
 
LVL 4

Author Comment

by:son_robin
ID: 11976583
Again you are giving me ways to find Used space. I need the total size!!!
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11976712
What the heck do you think that first select statement gives you?
0
 
LVL 8

Expert Comment

by:SNilsson
ID: 11976820

SjoerdVerweij : no need to be rude.

son_robin: please elaborate on what you need

*Total size of log and data files for current database
*Total size of log and data files for all databases
*The above divided by Total size left on disc
*Other
0
 
LVL 4

Author Comment

by:son_robin
ID: 11976941
Yes.

If you can right click on the database from the enterprise manager (SQL SEerver 2000) and click on properties, it will give size and space available.

SjoerdVerweij, I know your query can return size (current size). But I want to get the space avilable or Total size. Got it??

Thanks SNilsson!

0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11976948
I thought it was a fairly mild response to an inaccurate and mildly accusatory statement. No need to be belligerent.

(Sorry, but I just had the worst lunch in the history of lunches and the indigestion is doing terrible things to my mood).
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11976996
Okay, what is the difference between current size and total size?
0
 
LVL 4

Author Comment

by:son_robin
ID: 11977323
Total size is is the size allocated when you create the database. Current size is the size occupied by the database at present. The space available shows how much size is left so that we can dump data into it.

I need to write a code in the front end to verify if the dtatabse is nearing its available total size and if it does then alert the system admin

Hope this clarification helps.

Thanks,
Robinson.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 11977451
I see. One more question though: why not set the database to grow automatically?
0
 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 11983101
Agree with SjoerdVerweij, you want to preallocate your database giving enough room to grow, but why not take advantage of automatic growth--some people think it's sloppy, but I think it's better than a suspect database.

Here is a copy of the script we use to get size--it's overkill for your situation, but it does breakdown to the filegroup level:


USE master
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_filegroup_info'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_filegroup_info
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_filegroup_info'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_filegroup_info >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_filegroup_info >>>'
END
go
CREATE PROCEDURE sp_filegroup_info
--WITH ENCRYPTION
AS

CREATE TABLE #FileDetails (
        FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
        "Name" nvarchar( 128 )  , "FileName" nvarchar( 500 ) ,
        TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
        UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
CREATE TABLE #LogDetails (
        DatabaseName nvarchar( 128 ) , FileSize float ,
        "%Usage" float , Status int
)

-- Get data file info:
INSERT INTO #FileDetails (
        FileId , FileGroupId , TotalExtents , UsedExtents , "Name" , "Filename"
)
EXECUTE( 'dbcc showfilestats with tableresults' )

-- Get log files info:
INSERT INTO #LogDetails (
        DatabaseName , FileSize ,  "%Usage" , Status
)
EXECUTE( 'dbcc sqlperf( logspace ) with tableresults' )

SELECT FILEGROUP_NAME( FileGroupID )  AS FileGroupName , FileId , "Name" , "FileName" ,
       STR( TotalSize , 28 , 2 ) + ' MB' AS FileSize ,
       STR( UsedSize , 28 , 2 ) + ' MB' AS CurrentSize ,
       STR( ( ( UsedExtents * 1. ) / TotalExtents ) * 100 , 5 , 2 ) AS "%Usage"
FROM #FileDetails
UNION all
SELECT '<All Data Filegroups>' , NULL , NULL , NULL ,
       STR( SUM( TotalSize ) , 28 , 2 ) + ' MB' AS FileSize ,
       STR( SUM( UsedSize ) , 28 , 2 ) + ' MB' AS CurrentSize ,
       STR( ( ( SUM( UsedExtents ) * 1. ) / SUM( TotalExtents ) ) * 100 , 5, 2 ) AS "%Usage"
FROM #FileDetails
UNION ALL
SELECT '<Transaction Log>' , NULL , NULL , NULL ,
       STR( FileSize , 28 , 2 ) + ' MB' AS FileSize ,
       NULL ,
       STR( "%Usage" , 5 , 2 ) AS "%Usage"
FROM #LogDetails
WHERE DatabaseName = DB_NAME()
ORDER BY FileGroupName , FileId
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_filegroup_info'), 'IsProcedure') = 1
BEGIN
     GRANT EXECUTE ON dbo.sp_filegroup_info To Public
     PRINT '<<< CREATED PROCEDURE dbo.sp_filegroup_info >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_filegroup_info >>>'
go
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

742 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question