July 11, 2011 at 11:42 am
I am trying to run the following script on SQL svr 2008. To re-index the Databases we use. EXEC sp_MSforeachtable @command1= "PRINT ‘?’ ALTER INDEX ALL ON ? REBUILD WITH (ONLINE = ON)"
When I run it on our test DB prior to moving to production I get the following error once I execute the query.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '‘'.
Any ideas?
July 11, 2011 at 11:46 am
You've got the quotation marks wrong.
Try this:
EXEC sp_MSforeachtable @command1= 'PRINT ''?''; ALTER INDEX ALL ON ? REBUILD WITH (ONLINE = ON)'
Note that the quotation marks around the command are single-quotes, and the quotation marks around the initial question mark are two single-quotes, not one double-quote.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2011 at 12:15 pm
That did it! Thank you.
July 11, 2011 at 12:54 pm
You might want to play it safe and put [] around the table name, in case you have some which are not valid identifiers or are reserved words.
EXEC sp_MSforeachtable @command1= 'PRINT ''?''; ALTER INDEX ALL ON [?] REBUILD WITH (ONLINE = ON)'
Do you really need to blanket rebuild every index every time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 3:05 pm
pmcalister (7/11/2011)
(ONLINE = ON)
Also, with Standard edition this will be ignored
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2011 at 3:31 pm
I have ran this on two test SQL servers. When it is executing it has ballooned one log file to 143GB. Then the server shut down because it used up all usable disk space.
This script is definitely not what I want to use. If I ran it in production it would take the whole business down. I have gone back to a this standard script for re-indexing.
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
GO
July 11, 2011 at 4:00 pm
switch to bulk logged recovery mode for the index rebuilds and back to full afterwards.
you should be using ALTER INDEX.. rather than DBCC DBREINDEX. Instead of blindly rebuilding indexes employ a routine to intelligently rebuild or reorganise based on fragmentation levels.
There are many scripts available, have a look around
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2011 at 4:30 pm
Thank you Perry. Will do.
July 11, 2011 at 4:48 pm
Intelligent indexing script: http://sqlfool.com
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply