dbaSQL
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 ('?')"
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????
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]
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.
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.
ASKER
then i guess we're good. it's definitely doing the reindex.
thank you, angel
thank you, angel
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?
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?
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?
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?
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?
EXEC sp_MSforeachtable @command1="print ''?'' DBCC DBREINDEX (''?'')"
'