April 21, 2008 at 8:08 am
HI:
I'm doing some analysis trying to figure out what's making my database so big. I'm querying the sysindexes and I'm finding something very off.
There are rows in the table where id is null and there are rows where object_name(id) is returning null. There appears to be a lot of data in these.
Any idea what these object are?
Thanks
-Mike
April 21, 2008 at 9:00 am
Could it all be permissions related?
BOL (http://technet.microsoft.com/en-us/library/ms186301.aspx)...
"built-in functions such as OBJECT_NAME may return NULL if the user does not have any permission on the object"
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 9:40 am
That doesn't sound right, I'm logged as the Administrator.
April 21, 2008 at 9:52 am
OK, seems to be a side effect of using 'GROUP BY id with rollup' When I drop the rollup all the objects are found. odd???
SET NOCOUNT ON
select id, cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from dbo.sysindexes with (nolock) where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY sum(reserved)*8 desc
April 21, 2008 at 10:16 am
Ah! The 'null' records are just the totals! It's all fine... 🙂
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply