Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rubs_65
Posting Yak Master
144 Posts |
 Posted - 2006-08-21 : 18:20:20
|
| Hi,We have 2 datafiles of about 100GB each and a particular table using about 45GB. Is there a way that we can check how much pages/space the table is using on each file.Thanks--Harvinder |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-08-21 : 18:25:29
|
| check out the sp_spaceused system stored procedure. -ec |
 |
|
|
rubs_65
Posting Yak Master
144 Posts |
Posted - 2006-08-21 : 18:48:20
|
| where sp_spaceused show for each file? |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2006-08-22 : 02:44:15
|
| No. I think it works on an object. Check BOL.------------------------I think, therefore I am - Rene Descartes |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-08-22 : 19:28:35
|
This should help:create table #a(file_id tinyint not null, page_id int not null, pg_alloc tinyint not null, ext_size tinyint not null, obj_id int not null, index_id tinyint not null, pfs_bytes binary(8) not null)insert into #a exec('dbcc extentinfo(''myDatabase'',''myTable'')')select file_id, count(*) extents, sum(pg_alloc) pagesused, sum(pg_alloc)*8192 bytesused from #a group by file_iddrop table #aThe byte count is approximate, and the page counts could be off too, but it will at least get you started. This will include non-clustered index pages too, but you can use:select file_id, count(*) extents, sum(pg_alloc) pagesused, sum(pg_alloc)*8192 bytesused from #a where index_id<2 group by file_idTo filter them out. Google for "dbcc extentinfo" for more information. Also try experimenting with a test table to validate the results. I'll leave it to you to add sysfiles data to get the actual filenames in the query results.  |
|
|
|
|
|
|
|
|
Space usage by table on each file