Size of a Database

Question

Post reply

Size of a Database

Ashwin M N

Hall of Fame

Points: 3668
More actions
January 15, 2008 at 6:31 am

#120108

Hi, I am not clear with the size of the database.
I can get the size of the database from three ways.
1> Right click on the database and properties we get the size.
2> In the query window execute the query select size from sysfiles.
3> Backup the database and we will get to know the size.
Which one is the correct size of the database since the value I got
differs from one another.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

(1) This gives the total size of the database files, including free space within those files

(2) This gives the sizes of the individual database files, including free space

(3) The size of the backup will usually be roughly the equivalent to the total space used within the database files, including the log file(s). I wouldn't rely on this to be 100% accurate, though.

John

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 2

Then which is the accurate way to find the size of a database?

Suppose I execute the DBCC SHRINKFILE (@Filename) will I be

able to remove the free space and get the exact size of the database?

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 3

you can use this sp_spaceused in query analyzer to know about the size of database. It will give you three columns: databasename, size and unallocated space

Ashwin M N Hall of Fame Points: 3668 More actions · Answer 4

When I use sp_spaceused I get the reserved,data,index_size,unused

size. Does this include the size of the database objects(procedures,tables,views etc) or only the data and index size stored in it?

rajankjohn SSCertifiable Points: 5061 More actions · Answer 5

dont think of shrinking the files unless you are sure the database will never grow again. The free space within the database is not a bad thing. So just think the size of the database as the total size (data + freespace)

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 6

you can use the object name with sp_spaceused to know about the size of object you are interested in. The exact syntax for it is:

sp_spaceused [[@objname =] 'objname']

[,[@updateusage =] 'updateusage']

Refer:

http://msdn2.microsoft.com/en-us/library/aa260286(SQL.80).aspx

Bhavnesh Kakkar Ten Centuries Points: 1023 More actions · Answer 7

sp_spaceused computes the amount of disk space used for data and indexes, and the disk space used by a table in the current database. If objname is not given, sp_spaceused reports on the space used by the entire current database. It includes stored proc also

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

I use the script below:

-- Author: Damon T. Wilson

-- Creation Date: 13-DEC-2006

--

-- Runs on SQL 2000.

-- Runs on SQL 2005.

SELECT name AS NameOfFile,

size/128.0 as TotalSizeInMB,

CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpacesUsedInMB,

size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB

FROM dbo.SYSFILES

go

Happy T-SQLing

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

Sandeep Saretha SSChasing Mays Points: 647 More actions · Answer 9

You can also use sp_helpdb and see the dbsize column of the result. It will give you total size of database including data and log files.

Sandeep

Tara-1044200 SSCoach Points: 15951 More actions · Answer 10

How can i find just the data size in a DB without Log size?

srikant maurya Hall of Fame Points: 3293 More actions · Answer 11

You can use the below query to find out the spcae od data file

select * from dbo.sysfiles

or sp_spaceused

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

And if you want a quick view of all your database sizes alphabetically:

IF OBJECT_ID('tempdb..#t') IS NOT NULL

DROP TABLE #dbsize

CREATE TABLE #dbsize

(DB_Name sysname

,size int)

--now load the temp table

INSERT INTO #dbsize

exec sp_MSforeachdb 'Select ''[?]'' as DB_Name, sum(size) from [?].dbo.SYSFILES'

-- and look at the data

SELECT DB_Name, size/128 as 'Size Mb' FROM #dbsize order by DB_Name

--clean up

DROP TABLE #dbsize