how to get a list of objects in a file or filegroup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gigel - 30 Jan 2008 18:47 GMT

hi all,

To speed up the backup of a 23GB sql 2k db used for monthly archives I split
the original file
into 2 files in two separate file groups. I then moved all user tables and
indexes on the secondary
file group I defragged and shrunk the file but I am sill left with 4GB in
the primary.

How can I tell what else is left in there?

Thanks,

Gigel

Reply to this Message

Ben Nevarez - 30 Jan 2008 20:14 GMT

Hi,

Try this:

Find your groupid by running
select * from sysfilegroups

Then run

select object_name(id) from sysindexes
where groupid = your_groupid

Hope this helps,

Ben Nevarez

> hi all,
>
[quoted text clipped - 10 lines]
>
> Gigel

Reply to this Message

gigel - 30 Jan 2008 20:36 GMT

Thanks for your reply Ben.

I did help. I found a bunch of indexes, all id 255, all dpages = 0. I assume
they don't take up any space.

I tried dropping them but I got Cannot drop the index 'X.Y' because it is a
system index.

Gigel

> Hi,
>
[quoted text clipped - 28 lines]
>>
>> Gigel

Reply to this Message

Tibor Karaszi - 30 Jan 2008 21:02 GMT

255 are pages for text, ntext or image column datatypes.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks for your reply Ben.
>
[quoted text clipped - 38 lines]
>>>
>>> Gigel

Reply to this Message

gigel - 30 Jan 2008 21:24 GMT

That was it. Thanks a lot guys.

Gigel

> 255 are pages for text, ntext or image column datatypes.
>
[quoted text clipped - 41 lines]
>>>>
>>>> Gigel

Reply to this Message