Running Ola script and getting error

Question

Post reply

Running Ola script and getting error

BoobyB

SSCertifiable

Points: 5466
More actions
April 4, 2015 at 8:38 pm

#295783

Hi All,

I am running Ola maintenance script with the following parameters which runs fine.

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@sortinTempdb = 'Y'

but when I run it with trying to exclude one of the database as follows, i get a error invalid parameter.

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES', -databasename,

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@sortinTempdb = 'Y'

Can some one help me what I am doing wrong here.

Thanks in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

for the specific database you exclude, is it a database snapshot, or offline or read only ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

qur7 (4/4/2015)

Hi All,

I am running Ola maintenance script with the following parameters which runs fine.

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@sortinTempdb = 'Y'

but when I run it with trying to exclude one of the database as follows, i get a error invalid parameter.

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES', -databasename,

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@sortinTempdb = 'Y'

Can some one help me what I am doing wrong here.

Thanks in advance.

The answer you seek is in Ola's documentation and outside your single quotes. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

emilybartlett238 Newbie Points: 1 More actions · Answer 3

Who is yeah to fire several even wall his this headline police lippy strumming mom here an accident yeah and axiom here ASI here see all Ask Me here and more than me down bring said yeah motion on series CNN's not sure home and wrong here axle with world testo xl[/url] I was not him your mom yourself for however who needs one week is each and their head yes hales in Alaska already right away his house p he/she/it I muscles some time do you have a larger size yeah Roxy Delano you know how most offal you know taxes VAT home if you are you feel I am workingyeah you decide to use your blue anyway I will be home at something citizens that entrenching little more endurable are the stronger as Italian Foreign for why didn't you just ask and let trauma sure there's already a week from the Mac or not.

For information presentation topics ======>>>> http://testolimitfacts.com/testo-xl/

windcatch Newbie Points: 9 More actions · Answer 4

Try to remove the piece of code in bold:

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES', -databasename,

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@sortinTempdb = 'Y'

BoobyB SSCertifiable Points: 5466 More actions · Answer 5

Hi Jeff,

Thanks for the reply. so will this be correct version.

EXECUTE dbo.IndexOptimize

@databases = USER_DATABASES, -databasename,

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@SortinTempdb = 'Y'

Regards,

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

qur7 (4/6/2015)

Hi Jeff,

Thanks for the reply. so will this be correct version.

EXECUTE dbo.IndexOptimize

@databases = USER_DATABASES, -databasename,

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@SortinTempdb = 'Y'

Regards,

I believe that you need the @databases stuff to all be included in single quotes if I recall Ola's documentation correctly. Kind of like you did for @FragmentationMedium.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

free_mascot One Orange Chip Points: 27168 More actions · Answer 7

Pl. see the following example posted on ola's site:

EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30

You need to put the database name in single quotes and remove the comment " -databasename,".

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

BoobyB SSCertifiable Points: 5466 More actions · Answer 8

BoobyB

SSCertifiable

Points: 5466

April 6, 2015 at 7:24 am

#1788904

I understand, but with this way who would I exclude one database , my understanding is , using 'user_databases' runs it against all the user databases.

I would like to run it against all the databases except one name "databasename"

BoobyB SSCertifiable Points: 5466 More actions · Answer 9

So it be some thing like this.

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES, -name_of_the_database_tobe_excluded',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@SortinTempdb = 'Y'

Lowell SSC Guru Points: 323482 More actions · Answer 10

ok, i see what the others were saying now.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

EXECUTE dbo.IndexOptimize @databases = 'USER_DATABASES, -Prod',

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281324 More actions · Answer 11

qur7 (4/6/2015)

So it be some thing like this.

EXECUTE dbo.IndexOptimize

@databases = 'USER_DATABASES, -name_of_the_database_tobe_excluded',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 5,

@FragmentationLevel2 = 30,

@SortinTempdb = 'Y'

This is correct.

If you are looking for index maintenance, the better option would be to use Minion reindex http://bit.ly/MinionIdx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

BoobyB SSCertifiable Points: 5466 More actions · Answer 12

BoobyB

SSCertifiable

Points: 5466

April 6, 2015 at 7:46 am

#1788918

Great, That's what I was looking for.... Thanks a Bunch guys.

Regards,

Sossoliso SSC Enthusiast Points: 196 More actions · Answer 13

Sossoliso

SSC Enthusiast

Points: 196

April 7, 2015 at 5:58 am

#1789160

-databasename, "-" should be "--" for comment

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

Sossoliso (4/7/2015)

-databasename, "-" should be "--" for comment

No, because it's not a comment.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass