Cannot shrink database because of StatBlob data on sysindexes

SQL Server 2000 SP4
Data pages from the image column StatBlob on sysIndexes exist right at the
end of my data file. They are stopping me truncating the end of my datafile.
I had other tables with text columns. These were moved by either copying the
table or bcp/truncate.
It there a way to move the data on sysIndexes without doing a "shrinkfile
move datapages"?
Regards
Paul Cahill
You can try dropping all the statistics and see if it frees up that extent.
Just make sure you know which stats you may want to put back. By the way
this is a good example of one reason why you should keep only the system
objects in the Primary filegroup and place the user objects in secondary
filegroups.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
> SQL Server 2000 SP4
> Data pages from the image column StatBlob on sysIndexes exist right at the
> end of my data file. They are stopping me truncating the end of my
> datafile.
> I had other tables with text columns. These were moved by either copying
> the table or bcp/truncate.
> It there a way to move the data on sysIndexes without doing a "shrinkfile
> move datapages"?
> Regards
> Paul Cahill
>
|||Hi Andrew
How do I drop statistics for indexed columns? The StatBlob values are for
indexes not for stats manually created with CREATE STATISTICS.
I could rebuild all stats on the database but I do not know if this will
move the pages where StatBlob is stored.
I tried doing a full sp_updatestats on a test database and the pages did not
seem to move for sysIndexes
(I use dbcc extentinfo)
Regards
Paul
PS No he tells me
PPS Where does it say in the manual "DO NOT USE PRIMARY"
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
> You can try dropping all the statistics and see if it frees up that
> extent. Just make sure you know which stats you may want to put back. By
> the way this is a good example of one reason why you should keep only the
> system objects in the Primary filegroup and place the user objects in
> secondary filegroups.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:ubv2hej3GHA.3492@.TK2MSFTNGP06.phx.gbl...
>
|||Ahh, well then you may have to drop the index itself. updating stats most
likely will not do anything since it just updates the row in sysindexes that
already exists. It has no need to move it. Where as is you had a lot of
stats or indexes that you drop there is a potential for the extent they are
on to be deallocated and thus allowing the shrink.
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@.anon.com> wrote in message
news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew
> How do I drop statistics for indexed columns? The StatBlob values are for
> indexes not for stats manually created with CREATE STATISTICS.
> I could rebuild all stats on the database but I do not know if this will
> move the pages where StatBlob is stored.
> I tried doing a full sp_updatestats on a test database and the pages did
> not seem to move for sysIndexes
> (I use dbcc extentinfo)
> Regards
> Paul
> PS No he tells me
> PPS Where does it say in the manual "DO NOT USE PRIMARY"
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJDoC%23k3GHA.4560@.TK2MSFTNGP05.phx.gbl...
>
|||Hi Andrew.
Given a values from dbcc ententinfo can this be linked back to the row in
sysIndexes?
I.e. there may be a single index I could recreate that would clear my block.
I had never thought about not using primary for user data.
With hindsight it makes sense. I have not seen any Microsoft documents that
mention this.
It would make more sense if SQL Server simply kept it's system tables on a
private filegroup.
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
> Ahh, well then you may have to drop the index itself. updating stats most
> likely will not do anything since it just updates the row in sysindexes
> that already exists. It has no need to move it. Where as is you had a lot
> of stats or indexes that you drop there is a potential for the extent they
> are on to be deallocated and thus allowing the shrink.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <anon@.anon.com> wrote in message
> news:uA2U2kl3GHA.696@.TK2MSFTNGP03.phx.gbl...
>
|||To be honest I don't know if it can be linked back or not but it would not
be a single index as that would only take up one row in sysindexes. You
would need to clear the entire extent so it can be deallocated. Going
forward in 2005 there is even more reason to separate the filegroups (user
from primary) due to how you deal with partial restores. I have to admit
this is the first time I have seen sysindexes be an issue for shrinking data
files.
Andrew J. Kelly SQL MVP
"Paul Cahill" <noname@.noname.com> wrote in message
news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
> Hi Andrew.
> Given a values from dbcc ententinfo can this be linked back to the row in
> sysIndexes?
> I.e. there may be a single index I could recreate that would clear my
> block.
> I had never thought about not using primary for user data.
> With hindsight it makes sense. I have not seen any Microsoft documents
> that mention this.
> It would make more sense if SQL Server simply kept it's system tables on a
> private filegroup.
> Paul
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1Qn9Uo3GHA.1256@.TK2MSFTNGP04.phx.gbl...
>
|||Too many text columns. Too many tables. Our system dates back to SQL 6.5.
I noticed SQL 2005 has a text cleanup.
I think the only way out of this for me is a long slow process of moving out
followed by shrinkfile with move data.
Disk space permitting.
It's a big database and it's being logshipped across a wan with litespeed.
Thanks
Paul
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23YJd2Vx3GHA.1588@.TK2MSFTNGP02.phx.gbl...
> To be honest I don't know if it can be linked back or not but it would not
> be a single index as that would only take up one row in sysindexes. You
> would need to clear the entire extent so it can be deallocated. Going
> forward in 2005 there is even more reason to separate the filegroups (user
> from primary) due to how you deal with partial restores. I have to admit
> this is the first time I have seen sysindexes be an issue for shrinking
> data files.
> --
> Andrew J. Kelly SQL MVP
> "Paul Cahill" <noname@.noname.com> wrote in message
> news:Oa86zOw3GHA.4972@.TK2MSFTNGP03.phx.gbl...
>