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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would be easier to use sp_msforeachdb. This is an undocumented stored procedure. If you need additional help with it let me know.
ASKER
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
Timo
It should be something like this.
DECLARE @SQLString varchar(1000)
SELECT @SQLString = 'USE ? DBCC UPDATEUSAGE WITH NO_INFOMSGS'
EXEC sp_msforeachdb @SQLString
DECLARE @SQLString varchar(1000)
SELECT @SQLString = 'USE ? DBCC UPDATEUSAGE WITH NO_INFOMSGS'
EXEC sp_msforeachdb @SQLString
ASKER
This would do something for all DBs? And what this command excatly do?
Timo
Timo
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
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.
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.
ASKER
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'
SET @SQLString = 'USE ? DBCC UPDATEUSAGE (''?'') WITH NO_INFOMSGS'
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.
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
In case your database is case sensitive by configuration then change the last line to this:
Exec sp_MSforeachdb @cmd
Exec sp_MSforeachdb @cmd
ASKER
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.
This will reduce your time so much since it will be run by the SQl agent.
Regards,
Chris Musasizi