Calculating table size

I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size.

I would really apreciate any help on this.

regards

? Since you already have the tables, with rows of real data, I think you can do better than an estimation tool. Take the current table size: EXEC sp_spaceused 'yourTable' ... then get the number of rows: SELECT COUNT(*) FROM yourTable ... and divide. This will give you a much more realistic estimate than any tool can. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Jorge González@.discussions.microsoft.com> wrote in message news:acc81bad-c554-4d2b-8289-fb1482786568@.discussions.microsoft.com... I have a BIG database with 8 tables growing rapidly. I would like to estimate the size of a row in disk space so I can predict how big my bd will grow in time. I know the math to calculate this but I'm look for a tool to automate this task. A tool that takes into accout calculating index size aside from table size. I would really apreciate any help on this. regards|||

The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc.

I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007.

http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx

-Paul

|||? Hi Paul, I'll check out your webcast, but I'd just like to give a bit of feedback on the reports themselves. Although I really love the look and feel and depth of content of these reports, I find that they're too heavy to run on a busy production server -- especially one with a lot of databases and/or a lot of tables. Would it be possible to modify the reports for a future release such that the drill-down data is "lazy loaded" on demand? This would make the reports a lot more useable for enterprise scenarios. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Paul Mestemaker - MSFT@.discussions.microsoft..com> wrote in message news:7fb4862e-7192-496e-b484-e1547a88728d@.discussions.microsoft.com... The Disk Usage report in SQL Server Management Studio shows you the current size of your database breaking down the statistics by table, row size, index, partitions, etc. I'm putting together a blog series on Management Studio reports. In the first post, I put together a 2 minute demo of how to view the reports and export them to Microsoft Excel 2007. http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx -Paul|||

I apologize for the delay in response, I have not setup the alerts properly.

We are somewhat limited in how we can load data into the ReportViewer control in Management Studio. There are two ways the control supports an interactive experience.

Drill-in
This is what we do today. We load in all of the data we need to render the report and show/hide information giving a simulated interactive experience.
|||

Hi Jorge,

You can try the following script on SQL 2000 / 2005

CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)

DECLARE @.Counter int
DECLARE @.Max int
DECLARE @.Table sysname

SELECT name, IDENTITY(int,1,1) ROWID
INTO #TableCollection
FROM sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)

SET @.Counter = 1
SET @.Max = (SELECT Max(ROWID) FROM #TableCollection)

WHILE (@.Counter <= @.Max)
BEGIN
SET @.Table = (SELECT name FROM #TableCollection WHERE ROWID = @.Counter)
INSERT INTO #SpaceUsed
EXECUTE sp_spaceused @.Table
SET @.Counter = @.Counter + 1
END

SELECT * FROM #SpaceUsed

DROP TABLE #TableCollection
DROP TABLE #SpaceUsed

Regards