Sql Custom Script to run in the job

Question

Post reply

Sql Custom Script to run in the job

ramana3327

SSCoach

Points: 19722
More actions
May 20, 2015 at 7:19 am

#398481

Hi,
Currently we are using sql server 2008r2 standard edition. We are doing reorganizing all Indexes everyday (using default maintenance plan).
I am searching for a custom scrip which do reorganize the indexes when it is greater than 5 and rebuild when fragmentation greater than 30 and page count is greater than 1000 for only one user database. This db is set up for transaction replication

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i personally use ola hallengren scripts, but have heard a lot of good things about minion reindex;

both have defaults that fit your request, and the ability to modify the paramters to somethign differnet/custom

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

http://www.midnightsql.com/Minion/minion-reindex/

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!

Almighty SSCrazy Points: 2001 More actions · Answer 2

Almighty

SSCrazy

Points: 2001

May 21, 2015 at 3:15 am

#1799577

OLA has the answer 🙂

ramana3327 SSCoach Points: 19722 More actions · Answer 3

When I tried to download Hallengren IndexOptimize.sql, it is showing that commandexecute.sql also required but I didn't feel that it is required.

When I am creating the SP IndexOptimize without CommandExecute it is telling that The module 'IndexOptimize' depends on the missing object 'dbo.CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists. But when I execute that it ran succesfully

Lowell SSC Guru Points: 323518 More actions · Answer 4

well, exec sp_depends IndexOptimize shows it as a dependancy, and if you review the code itself, the code blocks realted to 'INDEX_REORGANIZE' and 'UPDATE_STATISTICS' (~ lines 1180 or so)c learly have calls to that procedure.

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!

ramana3327 SSCoach Points: 19722 More actions · Answer 5

Yes. You are right in line 1187 it is executing.

I will try to modify that sp without calling that.

Thanks for posting this

Lowell SSC Guru Points: 323518 More actions · Answer 6

ok, the better question is why do you feel you need to modify the code to exclude the procedure?

Ola's scripts are tried and true, peer reviewed and used in big time production environments. i personally have 70 sql servers with it deployed on it; the code differences i did was to change add a couple of additional jobs, and enable the schedules on them.

spend some time reading his web site's expansive documentation and become familiar with the code; i think as you gain confidence, you'll see the procedure you want to exclude is not doing any harm.

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!

ramana3327 SSCoach Points: 19722 More actions · Answer 7

My environment is small. Required only one database maintenance around 200 GB database.For me that scripts looks so many parameters. I am feeling I might not need the core requirement verification and availability groups.. If I remove all those then my scripts looks small then everyone can understand what is inside.

I agree with you this script is good and works for any environment but looks bigger for me