Post reply

Replace xp_sqlMaint (RebldIdx) and (RmUnusedSpace) for SQL 2008 R2

  • September 17, 2013 at 12:40 pm

    #395503

    Hi,

    I have to update a SQL script that was running on SQL Server 2000.

    This is the command line:

    EXECUTE master.dbo.xp_sqlmaint '-D MYDATABASENAME -RebldIdx 10 -RmUnusedSpace 50 10'

    This line does not work in SQL Server 2008 R2. I receive this error:

    The SQLDMO 'Application' object failed to initialize (specific error: One of the library files needed to run this application cannot be found.)

    This is a common error. Tons of post on Google, but no real alternatives.

    I do not want to install any backward compatibility stuff. I need to find new SQL Server 2008 commands to replace it.

    ----

    To replace the RmUnusedSpace parameter, I could maybe use :

    --> DBCC SHRINKFILE (LOG, 50) Is 50 the value that would replicate the old behaviour ?

    But which SQL command would replicate the (-RebIdIdx 10) parameter?

    Thank you for helping.

    Dom.

  • September 18, 2013 at 6:33 am

    #1651088

    The script you inherited from SQL 2000 is from a SQL Server maintenance plan. From SQL 2005 onward SQL maintenance plans use SSIS packages instead of xp_sqlMaint.

    So instead of fixing the code, you better create a new maintenance plan or use your own custom maintenance scripts.

    http://technet.microsoft.com/en-us/library/ms189953(v=sql.105).aspx

    [font="Verdana"]Markus Bohse[/font]

  • September 18, 2013 at 6:38 am

    #1651094

    Oh, yes one more comment:

    DON'T use DBCC SHRINKFILE in a maintenance plan. It's a very bad practice.

    [font="Verdana"]Markus Bohse[/font]

  • September 18, 2013 at 6:46 am

    #1651100

    I do not want to create a maintenance plan.

    I am not even sure it exists under SQL Server 2008 R2 Express anyways. But that is not important, I do not want a maintenance plan.

    This command line is ran as a one-shot right after a one-time initialization huge data import.

    I need to execute an SQL command that would do the same trick.

Viewing 4 posts - 1 through 3 (of 3 total)

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