How To Drop _WA Statistics On SQL2K5

It appears that SQL2K5 has removed the ability to drop _WA statistics. It
keeps giving me the error “Cannot drop the statistics
'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist or
you do not have permission.” When trying to run “drop statistics
[tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]”. In SQL2K this used to work
fine….
We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
then now to SQL2K5 and each table originally was not indexed well at all so
we have TONS of these _WA statistics pied up on every table. I want to be
able to remove all _WA stats and allow the SQL Server to regenerate these as
needed now that we are running the new SQL2K5 engine.
Anyone find a way of doing this now? Or will SQL2K5 remove these on its own
if it does not need them any longer? Does not appear it removes them by
itself based on the quantity of these hanging around.
Thx!
Ross Nornes
How are you getting the list of statistics and are you sure the account does
have the proper permissions to drop them?
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
> It appears that SQL2K5 has removed the ability to drop _WA statistics. It
> keeps giving me the error "Cannot drop the statistics
> 'tbl_TSDetail_2983._WA_Sys_00000002_0301381D', because it does not exist
> or
> you do not have permission." When trying to run "drop statistics
> [tbl_TSDetail_2983].[_WA_Sys_00000002_0301381D]". In SQL2K this used to
> work
> fine..
> We have a large legacy DB that we upgraded originally from SQL 7 to SQL2K,
> then now to SQL2K5 and each table originally was not indexed well at all
> so
> we have TONS of these _WA statistics pied up on every table. I want to be
> able to remove all _WA stats and allow the SQL Server to regenerate these
> as
> needed now that we are running the new SQL2K5 engine.
> Anyone find a way of doing this now? Or will SQL2K5 remove these on its
> own
> if it does not need them any longer? Does not appear it removes them by
> itself based on the quantity of these hanging around.
> Thx!
> Ross Nornes
>
|||We just use a simple query to generate the DROP scripts that we used to use
for SQL2K. I have included it below for review.
And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
not be an issue.
select
'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
from sysindexes i join
sysobjects o on i.id = o.id
where
i.name like '_wa%'
order by i.name
Thx!
Ross Nornes
"Andrew J. Kelly" wrote:

> How are you getting the list of statistics and are you sure the account does
> have the proper permissions to drop them?
> --
> Andrew J. Kelly SQL MVP
> "Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
> news:6353D60F-3201-492A-94F4-78309078D072@.microsoft.com...
>
>
|||Ross,
I have seen that when the drop scrip was run in the context of master and
not the user db. But in any case you may want to use the new sys.stats view
instead.
SELECT 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] +
']'
FROM sys.stats as i
WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE
'_WA%'
ORDER BY i.name
Andrew J. Kelly SQL MVP
"Ross Nornes" <RossNornes@.discussions.microsoft.com> wrote in message
news:8F4B7751-D825-47F6-BB7B-A0B0C35D6105@.microsoft.com...[vbcol=seagreen]
> We just use a simple query to generate the DROP scripts that we used to
> use
> for SQL2K. I have included it below for review.
> And yes, I'm the DBA so I'm logged in as SA on the box. Permissions should
> not be an issue.
> select
> 'drop statistics [' + object_name(i.id) + '].['+ i.name + ']'
> from sysindexes i join
> sysobjects o on i.id = o.id
> where
> i.name like '_wa%'
> order by i.name
> Thx!
> Ross Nornes
> "Andrew J. Kelly" wrote: