Calculate Size of SQL Server 2008 DB

Hi Experts,
How to calculate total size of SQL Server 2008 DB with breakdown for tables , indexes etc

thank a lot
juliakirAsked:
Who is Participating?
 
Brad HoweConnect With a Mentor DevOps ManagerCommented:
Simpe TSQL to get this info for you.

http://gallery.technet.microsoft.com/Determine-size-of-database-6c5373cd


USE BizTalkDTADb --You can change this to desires database
GO

SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
-- DB size.
EXEC sp_spaceused
-- Table row counts and sizes.
CREATE TABLE #t
(
[name] NVARCHAR(128)
, [rows] CHAR(11)
, reserved VARCHAR(18)
, data VARCHAR(18)
, index_size VARCHAR(18)
, unused VARCHAR(18)
)
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT [name] as 'Table name'
, [rows] as 'Rows'
, cast(replace(reserved, ' KB', '') as bigint) as 'reserved (KB)'
, cast(replace(data, ' KB', '') as bigint) as 'data (KB)'
, cast(replace(index_size, ' KB', '') as bigint) as 'index_size (KB)'
, cast(replace(unused, ' KB', '') as bigint) as 'unused (KB)'
FROM #t
-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM #t
DROP TABLE #t
GO


Cheers,
Hades666
0
 
David ToddSenior DBACommented:
Hi,

From SSMS, in Object Explorer, Right click on the database, select Reports, select Standard Reports, Select Disk Usage by Table.

Enjoy.

Regards
  David
0
 
juliakirAuthor Commented:
David ,
that is nice feature ! What kind of privelegies developer should have in order to be able to do that ?

thank you
0
 
David ToddConnect With a Mentor Senior DBACommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.