November 5, 2008 at 4:08 am
I just set up a nightly job to run DBCC UPDATEUSAGE across all dbs on my qa box. It shoukd run over 30 odd "client" dbs.
Problem is it keeps failing after database 13 .. so I thought it was db 13 and just to test got the job to skip it .. and it failed on the next one etc .. .. run it on the databases indvidually and it works just fine !
any words of potential wisdom ?
DECLARE @DBName VARCHAR (128),
@Stmt VARCHAR (2000),
@p CHAR (2)
SET @p = CHAR (13) + CHAR (10)
DECLARE curDBList INSENSITIVE CURSOR FOR
SELECT [name]
FROM master..sysdatabases
WHERE [name] LIKE 'QAclient%'
OPEN curDBList
OPEN curDBList
FETCH NEXT FROM curDBList INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Stmt = 'USE [' + @DBName + ']' + @p
+ 'DECLARE @TabName VARCHAR (128),' + @p
+ ' @IndexName VARCHAR (128),' + @p
+ ' @Stmt VARCHAR (512)' + @p
+ 'DECLARE curTables CURSOR FOR' + @p
+ 'SELECT sct.[client_table_name],' + @p
+ ' six.[name]' + @p
+ 'FROM sys_client_tables AS sct JOIN' + @p
+ ' sysindexes AS six' + @p
+ 'ON sct.[client_table_name] = OBJECT_NAME(six.[id])' + @p
+ 'AND six.[indid] = 1' + @p
+ 'OPEN curTables' + @p
+ 'FETCH NEXT FROM curTables INTO @TabName, @IndexName' + @p
+ 'WHILE @@FETCH_STATUS = 0' + @p
+ 'BEGIN' + @p
+ ' SET @Stmt = ''EXEC (''''DBCC UPDATEUSAGE (''''''''' + @DBName + ''''''''', '''''''''' + @TabName + '''''''''', '''''''''' + @IndexName + '''''''''') WITH NO_INFOMSGS'''')''' + @p
+ ' EXEC (@Stmt)' + @p
+ ' FETCH NEXT FROM curTables INTO @TabName, @IndexName' + @p
+ 'END' + @p
+ 'CLOSE curTables' + @p
+ 'DEALLOCATE curTables'
PRINT 'DBCC UPDATEUSAGE: ' + @DBName
EXEC (@Stmt)
FETCH NEXT FROM curDBList INTO @DBName
END
CLOSE curDBList
DEALLOCATE curDBList
p.s sys_client_tables is just a metadata table that is the subset of tables that are in use etc
November 5, 2008 at 8:08 am
maybe remove the NO_INFOMSGS so you can see the error/info message?
I wonder if this works in SQL 2000 (to replace your cursor and dynamic SQL)
sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') use ? DBCC UPDATEUSAGE(0)'
November 5, 2008 at 10:18 am
Why are you running update usage nightly?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2008 at 5:10 am
the result of inheriting some maint routines 🙂 Im setting up news ones but heavily based on the old
I believe the rationale was due to the highly volatile table sizes and hence the need for the frequency
But .. having read some about etc and from your comment I guess that is excessive ?
November 6, 2008 at 9:11 am
All update usage does is fix any incorrect space measurements in the metadata. 2000 does sometimes get that wrong (leading to fun things like databases with -10% free), but I doubt it's severe enough to warrant running daily.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply