DBCC UPDATEUSAGE for multiple databases

Hello,

We have problem with our SQL databases. We got advice from our software developer company to run this command (look below). The problem is that this same problem occur with hundreds databases. So with this command I need to run it hundreds of times. How I can run this same command for all databases in one time? Or is it possible for all databases which starts TK*? Most of databases are TK* and all them have this same problem. There is also couple other kind of databases and with them is no problems.

USE TK0010;
GO
DBCC UPDATEUSAGE (TK0010) WITH NO_INFOMSGS;
GO

Then other question. These all databases are transfered from SQL2000 to SQL2005. At the moment these databases are in SQL2000 mode. There is too much databases to change them mode manually. How I could script this for all databases?
thaapavuoriAsked:
Who is Participating?
 
Chris MConnect With a Mentor Consulting - Technology ServicesCommented:
It's worth notting that DBCC UPDATEUSAGE is always used on a single database at a time but there's a work around in case you want to run through all your user databases.
Use the code i have attached as you wish.

-- To update stats on the current DB
DBCC UPDATEUSAGE(0);
 
-- To go to a different database
DBCC UPDATEUSAGE ('db name');
 
-- To browse all databases (using a simple cursor)
-- Cursor updates usage for all non system databases
declare @name sysname, @str varchar(500)
declare cur_dbs cursor 
for select [name] from master.sys.databases 
	where [name] not in ('master', 'tempdb', 'model', 'msdb')	-- Ignore system databases
open cur_dbs
fetch next from cur_dbs into @name
while @@fetch_status = 0
begin
	select @str = 'DBCC UPDATEUSAGE('''+@name+''');'
	execute(@str)
	fetch next from cur_dbs into @name
end
close cur_dbs
deallocate cur_dbs

Open in new window

0
 
Chris MConsulting - Technology ServicesCommented:
To reduce the manual work, simply create a maintenance plan and add "updating of statistics" for every production database on the instance.
This will reduce your time so much since it will be run by the SQl agent.
Regards,
Chris Musasizi
0
 
ThorSG1Commented:
It would be easier to use sp_msforeachdb.  This is an undocumented stored procedure.  If you need additional help with it let me know.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
thaapavuoriAuthor Commented:
Ok, im not very used to use these sql scripts. So yes, if you can help with this sp_msforeachdb and also explain what it is that would be awesome.

Timo
0
 
ThorSG1Commented:
It should be something like this.

DECLARE @SQLString varchar(1000)
SELECT @SQLString = 'USE ? DBCC UPDATEUSAGE WITH NO_INFOMSGS'
EXEC sp_msforeachdb @SQLString
0
 
thaapavuoriAuthor Commented:
This would do something for all DBs? And what this command excatly do?

Timo
0
 
ThorSG1Commented:
I appologize I did not check my systex very will.  The first post will not work.  If you do not want it to run against all databases (which includes the system databases) you can also use the first post by pastorchris.  I did not check the systex but at a glance it looked fine.
 




--Defining a variable to save a string too.
DECLARE @SQLString varchar(1000)
--Setting the value of the string to run against all databases.
SET @SQLString = 'USE ? DBCC UPDATEUSAGE (''?'') WITH NO_INFOMSGS'
--Execute stored procedure sp_msforeachdb which will execute a string against every database including the system databases.
EXEC sp_msforeachdb @SQLString

Open in new window

0
 
Chris MConsulting - Technology ServicesCommented:
Hi ThorSG1,
As we help pros out there, I think it's important as experts to text our scripts first before we post them here, unless we state that the solution is something like  ... ABCD.
The script I sent in the first post is a tested script on both SQL 2005 and SQL 2000 instances, I thank you for testing your second post.
Secondly, Timo wants something for databases that he's moved from his SQL 2000 instance to an SQL 2005 instance - this definitely does not include systems databases because SQL 2000 and 2005 system databases are different in structure so it's not his area of target, if i understand his query.
In other words, Timo wants to update usage on only his "user databases" that's why i excluded system databases in my script.
 
Otherwise thanks for the contribution, God bless.
Chris Musasizi.
0
 
thaapavuoriAuthor Commented:
Hi. I have been away but now i could run this script. How I should modify this line:
SET @SQLString = 'USE ? DBCC UPDATEUSAGE (''?'') WITH NO_INFOMSGS'
0
 
Chris MConsulting - Technology ServicesCommented:
Hi,
I have tested the above script and it will not work on databases characters like hyphens, space, e.t.c characters in the database names.
I have therefore catered for all these conditions so please use it.
I have also tested my new script on both SQL 2000 and SQl 2005 instances.
Do not modify anything, just run the lines of code in the code snippet I have attached.
Enjoy!!
This is the second working script I a mposting. Let me know how you found the first script.

DECLARE @cmd varchar(1000)
SET @cmd = 'use [?]; dbcc updateusage([?])with no_infomsgs'
EXEC sp_msforeachdb @cmd

Open in new window

0
 
Chris MConsulting - Technology ServicesCommented:
In case your database is case sensitive by configuration then change the last line to this:
Exec sp_MSforeachdb @cmd
0
 
thaapavuoriAuthor Commented:
Hi, thanks for both of you. Finally I run pastorchris first script and it worked without any problem. It was a bit long and I was scare to use it because i didnt understand every part of it but it worked fine.  I changed this line: DBCC UPDATEUSAGE ('db name'); to 'db ´name' i put one of the batabases real name.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.