Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

How can I run this against ALL USER databases on a server without creating a separate job for each database?

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

EXEC sp_MSforeachdb 'use ?
EXEC sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')"
'
Avatar of dbaSQL

ASKER

yeah, i was trying that, angel, but i get this:

Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.

likely the result of an error of mine as i've been trying to put this together, but now i am unable to get beyond it????
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

excellent, angel, i think. i'm pretty sure it worked, however, the job log just has a bunch of these in there ?:

DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
This is normal. If you run DBCC, it will always put at least that line.
Avatar of dbaSQL

ASKER

yep, i am pretty familiar w/that, but are you saying i won't see anything regarding the dbreindex itself?
you will only see errors if any.
Avatar of dbaSQL

ASKER

then i guess we're good.  it's definitely doing the reindex.
thank you, angel
Avatar of dbaSQL

ASKER

angel, please let me know if you'd like me to open a new question.  I've just got one follow up on this one --- the weekend maintenance failed in the dbreindex step.  The error was :  Msg 1934, Sev 16: DBCC failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]

this had happened before, which is why I'd changed the dbreindex step to point only to one database and I used the sample script listed herein:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q301292

But, having changed it to the 'new' dbreindex code, it failed again, same error.  how do you think i should approach this?
>Msg 1934, Sev 16: DBCC failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]
to avoid this problem, you have to use the following line before running the DBCC line (as mentionned in the article):
SET ARITHABORT ON

Not sure why it should not work?
Avatar of dbaSQL

ASKER

well, angel, i'm guessing it's because of the one db that has the computed columns.  i just rescripted in test and ran it like this, it worked:

SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
EXEC db1..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'
EXEC db2..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'
EXEC db3..sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'
SET ARITHABORT OFF
SET QUOTED_IDENTIFIER OFF

but i'm  pretty worried about this as I'm not certain really why this is happening, and i've computed columns in several databases.  it fails only in this one?