On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC UpdateUsage
(0) With Count_Rows to correct the number of rows returned by sp_spaceused,
Enterprise Manager or in table sysindexes.
For example, I can have 318000 instead of 318256, 3456 instead of 3458
There was not this problem on a server with W2000 / SQL2000 SP1.
Anyone has an idea ?
Thanks,
Sebastien
I think you must have had statistics set to auto-update on the 2000 server,
and no longer have this set on the 2003 server.
I've never relied on sp_spaceused unless I also update stats before I run
it.
http://www.aspfaq.com/
(Reverse address to reply.)
"Sebastien" <nospam@.nospam.com> wrote in message
news:uXpFj4YoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> Hi,
> On a server with WS2003 / SQL2000 SP3, I must run everyday DBCC
UpdateUsage
> (0) With Count_Rows to correct the number of rows returned by
sp_spaceused,
> Enterprise Manager or in table sysindexes.
> For example, I can have 318000 instead of 318256, 3456 instead of 3458
> There was not this problem on a server with W2000 / SQL2000 SP1.
> Anyone has an idea ?
> Thanks,
> Sebastien
>
>
|||Hi Sebastien,
Thanks for your post.
As MVP Aaron said, there are some known issues in sp_spaceused, however,
you'd better take the following steps to see whether it make more effect.
First of all, take the latest updates of SQL Server. Some known issues are
fixed. For example,
FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers
that Use 16 or More CPUs
http://support.microsoft.com/?id=293849
Secondly, run the command sp_spaceused like below
sp_spaceused @.updateusage = true
Note that when you include the "@.updateusage=true" parameter for
sp_spaceused, the sp will run DBCC UPDATEUSAGE, which will update the
allocation information in sysindexes to insure that it provides accurate
information. Running DBCC UPDATEUSAGE on the whole database is a
resource-intensive task and can have a significant impact on the
performance of a system. Note that sysindexes is also updated at
checkpoints (though not as thoroughly), so during peak load times you may
want to invesitgate omitting the @.updateusage parameter and running
"CHECKPOINT" before sp_spaceused instead.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||No, auto-update statistics option is ON on the 2 server for the database,
and auto-create is OFF.
sp_spaceused mytable,@.updateusage=true or update statistics don't correct
that.
Only DBCC UpdateUsage (0,mytable) With Count_Rows can correct !!!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> a crit dans le message de
news:u0KceEZoEHA.1644@.tk2msftngp13.phx.gbl...
> I think you must have had statistics set to auto-update on the 2000
server,
> and no longer have this set on the 2003 server.
> I've never relied on sp_spaceused unless I also update stats before I run
> it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Sebastien" <nospam@.nospam.com> wrote in message
> news:uXpFj4YoEHA.1272@.TK2MSFTNGP09.phx.gbl...
> UpdateUsage
> sp_spaceused,
>
|||Thanks for your reply.
The server run french version of SQL2000 with SP3a and only have 4 CPU Xeon
HT.
sp_spaceused @.updateusage = true or sp_spaceused mytable,@.updateusage =
true don't solve the problem
only the dbcc command with Count_Rows can correct that !!!
Sebastien
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> a crit dans le
message de news:NWreWReoEHA.3212@.cpmsftngxa06.phx.gbl...
> Hi Sebastien,
> Thanks for your post.
> As MVP Aaron said, there are some known issues in sp_spaceused, however,
> you'd better take the following steps to see whether it make more effect.
> First of all, take the latest updates of SQL Server. Some known issues are
> fixed. For example,
> FIX: Statistics Maintenance Creates Significant Bottleneck on SQL Servers
> that Use 16 or More CPUs
> http://support.microsoft.com/?id=293849
> Secondly, run the command sp_spaceused like below
> sp_spaceused @.updateusage = true
> Note that when you include the "@.updateusage=true" parameter for
> sp_spaceused, the sp will run DBCC UPDATEUSAGE, which will update the
> allocation information in sysindexes to insure that it provides accurate
> information. Running DBCC UPDATEUSAGE on the whole database is a
> resource-intensive task and can have a significant impact on the
> performance of a system. Note that sysindexes is also updated at
> checkpoints (though not as thoroughly), so during peak load times you may
> want to invesitgate omitting the @.updateusage parameter and running
> "CHECKPOINT" before sp_spaceused instead.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi Sebastien,
Thanks for your further explaination.
Executing sp_spaceused computes the amount of disk space used for data and
indexes, and the disk space used by a table in the current database. If no
table (objname) is given, sp_spaceused reports on the space used by the
entire current database.
After you drop an index, sp_spaceused reports inaccurate information. This
is a known limitation. However, sp_spaceused provides a quick, rough
estimate. If you notice incorrect values when you execute sp_spaceused, run
DBCC UPDATEUSAGE to correct the inaccuracies. Because this statement takes
some time to run on large tables or databases use it only when you suspect
incorrect values being returned or when other users are not active.
I am afraid that you will have to run that DBCC command if sp_spaceused
@.updateusage = true doesn't make sence
I am sorry for the inconvenience it may caught. Thank you for your patience
and corperation. If you have any questions or concerns, don't hesitate to
let me know.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
No comments:
Post a Comment