?
Solved

DBCC UPDATEUSAGE for multiple databases

Posted on 2009-04-22
12
Medium Priority
?
2,461 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:thaapavuori
  • 5
  • 4
  • 3
12 Comments
 
LVL 12

Accepted Solution

by:
Chris M earned 2000 total points
ID: 24206585
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
 
LVL 12

Expert Comment

by:Chris M
ID: 24206652
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
 
LVL 4

Expert Comment

by:ThorSG1
ID: 24211780
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:thaapavuori
ID: 24212168
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
 
LVL 4

Expert Comment

by:ThorSG1
ID: 24214268
It should be something like this.

DECLARE @SQLString varchar(1000)
SELECT @SQLString = 'USE ? DBCC UPDATEUSAGE WITH NO_INFOMSGS'
EXEC sp_msforeachdb @SQLString
0
 

Author Comment

by:thaapavuori
ID: 24214375
This would do something for all DBs? And what this command excatly do?

Timo
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 24215511
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
 
LVL 12

Expert Comment

by:Chris M
ID: 24223021
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
 

Author Comment

by:thaapavuori
ID: 24236155
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
 
LVL 12

Expert Comment

by:Chris M
ID: 24236718
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
 
LVL 12

Expert Comment

by:Chris M
ID: 24236751
In case your database is case sensitive by configuration then change the last line to this:
Exec sp_MSforeachdb @cmd
0
 

Author Comment

by:thaapavuori
ID: 24247813
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 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