March 26, 2007 at 8:00 am
I am looking to do the following:-
a) Calculating the size(disk usage) of databases in a given Sql
server 2005.
b) Calculating the size(disk usage) of tables in a database.
c) Calculating the size(disk usage) all the related data for a key
value, for example get size of the all the related data associated
with FKSomeForeignKey with value (1234) which is probably a
foreign key in all the related tables.
As of now I have the following corresponding solutions :-
a) exec sp_spaceused on all the databases on a given server
b) exec sp_spaceused <tablename> on a particular database
c) Get all the data for a all the columns related for the given
value,in this example,get values of all the related columns from all
the tables where I find a particluar value of FKSomeForeignKey
,put that in a temporary table
and doing exec sp_spaceused <TempTableName>.
Quesions:-
a) Is this the correct way of accurately calculating the sizes?
b) If not,what is the correct/better approach?
c) Is there a tool(free/commercial) available to do exactly this?
Thanks in advance,
Ashish
March 27, 2007 at 3:59 am
Following on from the above I would like to know of a way to find the size and available space of different database files.
I tend to use these scripts for space used ata database level but it isn't granular enough:
sp_MSforeachdb
"SELECT '?' as 'Database Name',(SUM(size)*8.0)/1024.0 as 'Database Size' FROM ?..sysfiles WHERE status & 0x40 <> 0x40"
go
sp_MSforeachdb
"SELECT '?' +' data space used (MB)',(SUM(reserved)*8.0)/1024.0 FROM ?..sysindexes WHERE indid IN(0,1,255)"
go
March 27, 2007 at 7:00 am
Since you're working in 2005, there are reports and dynamic management views for getting this information. For example, the report Disk Usage by Table returned this data from AdventureWorks:
| Table Name | # Records | Reserved (KB) | Data (KB) | Indexes (KB) | Unused (KB) | |
| dbo.AWBuildVersion | 1 | 16 | 8 | 8 | 0 | |
| dbo.DatabaseLog | 389 | 1,504 | 1,408 | 24 | 72 | |
| dbo.ErrorLog | 10 | 16 | 8 | 8 | 0 | |
But, you can go into the dm_db_* for database information. dm_db_file_space_usage covered the database. I'd explore the others. In 2005 most of the system information is pretty much on display most of the time. dm_db_partition_stats shows an incredible amount of information on the various distribution across indexes including row count, pages used, pages reserved, lob pages used & reserved, overflow pages used & reserved... That's where I'd go for the information you want.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 27, 2007 at 7:06 am
Any thoughts for SQL 2000 on data files and their space available?
March 27, 2007 at 9:44 am
No, unfortunately, you're probably after the right stuff hitting the system tables. There just isn't as much information available in 2000 as is presented in 2005. You should post in the 2000 forum to see what information you can get there.
Sorry I wasn't helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply