Rebuild index task/job fails every time...

  • Rhox (7/4/2011)


    I was searching for a good index optimalisation script.

    That reorganised or rebuild the indexes (online) for the database.

    I'm trying following script: Example Script to Rebuild / Reorganize Fragmented Indexes

    I've modified only the rebuild command line with the extra option 'ONLINE = ON'.

    But I'm always getting an error: Incorrect syntax near '('. on Line 38

    LINE38 > FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS

    I noticed the compatibility mode of our db = 80 (SQL 2000)

    Already changed DB_ID() into 'A105_P' (the db name) but then I get this output (scripted error):

    DATE : Jul 4 2011 1:01PM - Retrieving indexes with high fragmentation from A105_P database.

    DATE : Jul 4 2011 1:01PM - There is some run time exception.

    ERROR CODE : 245

    ERROR MESSAGE : Conversion failed when converting the varchar value 'A105_P' to data type smallint.

    Ok gonna try that script, thanks!

  • muthukkumaran Kaliyamoorthy (7/4/2011)


    Incorrect syntax near '('. on Line 38

    LINE38 > FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS

    I noticed the compatibility mode of our db = 80 (SQL 2000)

    Already changed DB_ID() into 'A105_P' (the db name) but then I get this output (scripted error):

    DATE : Jul 4 2011 1:01PM - Retrieving indexes with high fragmentation from A105_P database.

    DATE : Jul 4 2011 1:01PM - There is some run time exception.

    ERROR CODE : 245

    ERROR MESSAGE : Conversion failed when converting the varchar value 'A105_P' to data type smallint.

    You can't pass the db name directly 'A105_P' to the db_id . use like db_id('A105_P')

    DMV will not work against the compatibility 80.

    Yes they will. They just need some help getting there...

    use master

    go

    select dbo.compatview (@Dbid_level80...)

    Maybe it works with db_id('level 80 db name') but I'm not 100% sure on that one.

  • muthukkumaran Kaliyamoorthy (7/4/2011)


    DMV will not work against the compatibility 80.

    It can and will, see Paul Randall's article at the following link

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (7/4/2011)


    muthukkumaran Kaliyamoorthy (7/4/2011)


    DMV will not work against the compatibility 80.

    It can and will, see Paul Randall's article at the following link

    Thanks perry. Thats great info from paul.

    Thanks to you.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • You're welcome

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hi,

    I am going to deploly at production database for Mr. Michal rebuild index script,

    http://sqlfool.com/2011/06/index-defrag-script-v4-1/

    I have doubt as below parameter

    @forceRescan 1 - forace scan and 0 = use existing scan,

    should I kept first time value 1 and then next time will kept it 0.?

    Standard Edition will not be support as below parameter, pl. confirm

    @onlineRebuild =0

    @sortInTempDB =0

    In standard edition let it be value 0 these two parametes.

    thanks'

    ananda

Viewing 6 posts - 16 through 20 (of 20 total)

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