April 2, 2012 at 11:59 am
I'm missing something here- maybe someone can help me out:
I have a database who's size on disk is about 340GB. The .mdf is 337GB and the log is about 3GB. When I look at the properties of this database in managment studio, it show what I would expect- the db is about 340GB with little "space avaialble". SP_Space used agreees with this, showing about 337GB of "reserved space" total- 320GB is data and 17GB is indexes.
the problem is- I cant explain where all that space is coming from. Using two different, similar queries to total up the size of all objects in the database, I can account for only about 90GB of data. I can't figure out what I am missing.
The first method I am using is Bill Graziano's famous "find big tables script". Because I'm scratching my head so furiously, I also ran another, similar script and still can not account for 2/3 of the space in this database.
I have run DBCC checkdb (with Physical_only) and DBCC updateusage to no avail. Any idea about what I could be missing here?
Script I am using to get size of objects:
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)], FileGroup FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total space used (MB)], f.name As FileGroup
FROM sys.sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((1= 1 AND o.type IN ('U', 'S')) OR o.type = 'U') AND
((1 = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
INNER JOIN sys.filegroups f
ON i.groupid = f.data_space_id
WHERE indid IN (0, 1, 255)
AND i.groupid = f.data_space_id
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)), f.name
) as a
ORDER BY [Total space used (MB)] DESC
April 2, 2012 at 12:14 pm
Try the query here.
http://jasonbrimhall.info/2011/11/17/table-space-revised-again/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2012 at 12:20 pm
Thank you- sir. That did the trick.
April 2, 2012 at 12:33 pm
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply