Using DBCC UPDATEUSAGE
-
Posted on September 16, 2011 by Derek Dieter
-
2
When DBCC UpdateUsage is run, page and row counts are updated. When this information is incorrect, it can give incorrect reports for how much space is used by tables and indexes. The most likely cause for incorrect usage stats is when a database is upgraded to a new compatibility level of SQL Server (i.e. 2000 to 2005). Therefore it’s important to run this command after the statistics have been updated.
It may not be a bad idea to run on a weekly or monthly schedule if your database undergoes frequent DDL modifications.
To execute you can run one of the following commands:
[cc lang=”sql”]
Use MyDB;
GO
DBCC UPDATEUSAGE(0); — Execute for the current database
GO
DBCC UPDATEUSAGE(MyDB); — Execute using the database name
GO
DBCC UPDATEUSAGE(MyDB); — Execute using the database ID
[/cc]
This may take some time, so make sure to run it in a non production environment or during off hours as it may cause blocking.
You can also run for a specific table or index. If you run for a table, then the usage stats are updated for all indexes on that table.
[cc lang=”sql”]
— Update for a table (and it’s indexes)
DBCC UPDATEUSAGE(AdventureWorks, ‘Sales.SalesOrderDetail’);
— Update usage for a single index
DBCC UPDATEUSAGE(AdventureWorks, ‘Sales.SalesOrderDetail’, ‘IX_SalesOrderDetail_ProductID’);
[/cc]
That’s basically it. Now you may be running these commands and are not seeing any output. Well, that’s because the usage stats are already correct. But if you want to spoof the stats in order to see the output, you can use these undocumented options:
[cc lang=”sql”]
Use Adventureworks;
GO
— Override existing page and rowcount values
— Do not do this in prod
UPDATE STATISTICS Sales.SalesOrderDetail WITH ROWCOUNT = 20000, pagecount = 10000;
— Now when you run the update usage stats, it will show an update
DBCC UPDATEUSAGE(AdventureWorks, ‘Sales.SalesOrderDetail’);
[/cc]
- Comments (RSS)
- Trackback
- Permalink
“DBCC UPDATEUSAGE(MyDB); — Execute using the database name
GO
DBCC UPDATEUSAGE(MyDB); — Execute using the database ID”
I was not aware you could have a database ID of “MyDB”
While I haven’t tested the notirwe, my gut feel is it and the backup to nul are both way, way worse that switching to simple recovery or (on SQL 2005) backup with truncate only.If, on SQL 2005, someone executes a backup log with truncate, all log backups after that fail with an error message (this unfortunately was not true on SQL 2000). Hence it’s pretty apparent that the log chain is broken.BACKUP DATABASE [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.bak’GOBACKUP LOG [Testing] WITH TRUNCATE_ONLYGOBACKUP LOG [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.trn’GOThe last one returns the errorMsg 4214, Level 16, State 1, Line 1BACKUP LOG cannot be performed because there is no current database backup.A log backup without writing or made to the nul device does not tell SQL that the backup chain is broken. Because it thinks that the log backup was written to disk, subsequent log backups succeed and create files.BACKUP DATABASE [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.bak’GOBACKUP LOG [Testing] TO DISK=’NUL:’GOBACKUP LOG [Testing] TO DISK = N’D:\Develop\Databases\Backups\Testing_2005.trn’GOThe last log backup succeeds.If one tries to restore a DB (with logs) where someone has done a backup to nul, no surprise, it failsRestore Database Testing From Disk = N’D:\Develop\Databases\Backups\Testing_2005.bak’ WITH NORECOVERY, REPLACERESTORE LOG Testing FROM Disk = N’D:\Develop\Databases\Backups\Testing_2005.trn’ WITH RECOVERYMsg 4305, Level 16, State 1, Line 1The log in this backup set begins at LSN 1611000000015500001, which is too recent to apply to the database. An earlier log backup that includes LSN 1611000000015000001 can be restored.I think it needs to be made crystal clear (anywhere that the trick’ of backing up to nul is mentioned) that it does not have the same effect as Backup Log with truncate only or a switch to simple recovery.Hence, if the log really does need truncating, please, please, please, rather switch to simple and back to full rather than playing tricks like the backup to nul and leaving a log chain that looks intact but is totally useless because of a file that was written nowhere.
