?
Solved

sp_spaceused

Posted on 2006-05-11
12
Medium Priority
?
2,095 Views
Last Modified: 2008-04-25
I noticed our data size grew 3 gigs last night when i di
exec Sp_SpaceUsed
i noticed negative numbers so i did
dbcc updateusage ('testcp') WITH COUNT_ROWS
and it was ok....fixed the values.

So should i do a dts job each night to do this
exec sp_spaceused @updateusage = 'TRUE'
dbcc updateusage ('testcp') WITH COUNT_ROWS

for all my tables.....

does any one else run these overnight.
Cheers.
0
Comment
Question by:TRACEYMARY
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 400 total points
ID: 16659924
TRACEYMARY,

Invoking exec sp_spaceused @updateusage = 'TRUE'  causes DBCC UPDATEUSAGE to run so you don't need to specify it again.  Of course, doing this on your entire database will be pretty resource intensive so make sure you schedule it to run during non-peak hours.

Some people have reported that running sp_spaceused @updateusage = 'TRUE'  doesn't always correct the problem and many run DBCC UPDATEUSAGE separately without doing sp_spaceused.

Do you have the Auto Update Statistics option enabled?

I believe UPDATE STATISTICS will also correct your problem but is as resource intensive as DBCC UPDATEUSAGE.

I do UPDATE STATISTICS on my larger systems several times a week.  

I have 103 SQL Servers with databases ranging from 20 mb to 750 gigs.
0
 

Assisted Solution

by:menter
menter earned 800 total points
ID: 16660380
In SQL Server 2005, DBCC CHECKDB has been enhanced to detect when page or row counts become negative. When detected, the DBCC CHECKDB output contains a warning and a recommendation to run DBCC UPDATEUSAGE to address the issue. Although it may appear as if upgrading the database to SQL Server 2005 caused this issue, the invalid counts existed before the upgrade procedure.

0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16661156
where on 2000
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Expert Comment

by:menter
ID: 16661390
TRACEYMARY

Check out the link below to understand autostats and how to determine which tables require updating.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;195565

Regards,
menter
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16661510
Thats the thread i am following and thats why i did the profiler to see what is happening.
The questions is ....it is performing auto stats every so many seconds......
0
 

Assisted Solution

by:menter
menter earned 800 total points
ID: 16661657
This is not a fixed interval and it is not configurable by the DBA. It is tied to the query optimizer, which as you know has to pick from several possible query plans in a reasonable amount of time.

The statistics auto update is triggered by query optimization and involves only a subset of columns referred to in the query. The rowmodctr column in SYSINDEXES value shows the number of changes on the table since the last time the statistics were updated or created.
0
 
LVL 3

Assisted Solution

by:napel25
napel25 earned 800 total points
ID: 16665334
I run DBCC UPDATEUSAGE ('databasename') every morning on all the databases I manage. After that I query the storage values and store them in another database to be able to predict the future growth of the databases.
Most are small databases (<250MB) so this statement finishes quite soon. But on the bigger databases it's a problem. I just made this job a little smarter to be able to manage the bigger databases (>20GB). With smarter I meen split it up and use a smart order.
I think the UPDATE STATISTICS will not solve the problem. This command will only update the index statistics and not update any storage value of tables.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16666806
i think i set up a new job and do
dbcc updateusage

that saves me doing
sp_spaceused @updateusage = 'TRUE'  

every night.

Is there a way to do it for all databases .....and output to a table the results?

Cheers
0
 
LVL 3

Accepted Solution

by:
napel25 earned 800 total points
ID: 16666825
Next batch will do all databases on a server. But it does not write the output to a table.

DECLARE @DB_name VARCHAR(50)
DECLARE @Readonly INT
DECLARE @cmd VARCHAR(255)
SET NOCOUNT ON
CREATE TABLE #DB([name] nvarchar(24), [db_size] nvarchar(13), [owner] nvarchar(24), [dbid] smallint, [created] char(11), [status] varchar(340), [compatibility_level] tinyint)

INSERT INTO #DB
EXEC sp_helpdb

DECLARE DB_Cursor CURSOR
FOR SELECT t.[Name]
         , CASE WHEN sd.status & power(2, 10) = 0 THEN 0 ELSE 1 END AS [Readonly]
    FROM master..sysdatabases sd
       , #DB t
    WHERE t.dbid = sd.dbid
    ORDER BY [db_size] asc

OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DB_name, @Readonly
WHILE(@@FETCH_STATUS <> -1)
BEGIN
  print @DB_name
  IF (@Readonly = 0)
  BEGIN
    SELECT @cmd = 'DBCC UPDATEUSAGE([' + @DB_name + ']) WITH NO_INFOMSGS'
    EXEC (@cmd)
  END
  FETCH NEXT FROM DB_Cursor INTO @DB_Name, @Readonly
END
CLOSE DB_Cursor
DEALLOCATE DB_Cursor
SET NOCOUNT OFF
DROP TABLE #DB
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16666889
Cheers...i get that put on our servers to run every night.

Any problems with space issues or anything gained except updating system tables?
i.e can i report any ....thing to boss saying oh by running this ...i get the following:
0
 
LVL 3

Expert Comment

by:napel25
ID: 16667100
You only should run this batch when you query after this batch the database/table/index sizes. Otherwise you better can not run this batch. This batch locks the resources it checks.
Best is to save the storage data to use it with statistics to predict the growth of the database or the objects witin it.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16667139
Ok thanks for the heads up.
If i see the table index sizes going negative for example then run this command.

Cheers
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question