locked
Maintenance activity through command line RRS feed

  • Question

  • Hi All,

    I'm trying to automate the "Maintenance plan". I was able to do it through GUI by logging into SQL studio.

    But, I'm supposed to automate by embedding the SQL command into jython.

    Would be greatfull if somebody can provide me complete steps for maintenance plan.

    Below is the Maintenance plan I'm expecting for:

    DBE – Full Backup
    1. Schedule the job to run every night at 5 AM.
    2. Configure the tasks in the following order: Back Up Database (Full),
    Maintenance Cleanup Task (3 days) and Clean Up History (older than 4
    weeks).

    DBE – Integrity Checks
    1. Schedule the job to run every Saturday night at 6 AM.
    2. Configure the tasks in the following order: Check Database Integrity and
    Update Statistics.

    DBE – Optimization
    1. Schedule the job to run every Saturday night at 10 PM.
    2. Configure the tasks in the following order: Rebuild Index.

    Tuesday, March 31, 2015 7:12 AM

Answers

  • Once DB is created, we perform post-installation activity, which includes creating maintenance plan. After which we release the server into production. That's when "SQL DBA's" come into picture.

    I have done some research and finally got something, please help me in refining the below:

    USE master;
    GO
    EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'E:\Backup',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @Compress = 'Y',
    @CheckSum = 'Y'

    GO
    EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'USER_DATABASES',
    @CheckCommands = 'CHECKDB',
    @PhysicalOnly = 'Y'

    GO
    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,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'

    GO
    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

    the last two steps are redundant.. you do not both of them..just have one of them..



    Hope it Helps!!

    • Marked as answer by Michelle Li Wednesday, April 8, 2015 7:36 AM
    Friday, April 3, 2015 10:36 PM
    Answerer

All replies

  • Hi, I suggest the Ola Hallengren solution

    https://ola.hallengren.com/

    This solution gives you all the steps you need

    hope will help


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    Tuesday, March 31, 2015 7:29 AM
  • Yeah, I had gone through this link. 

    I'm not an SQL DBA, I'm unable to understand the command given there.

    Can you please provide me a sample command and steps to execute them and where to execute. Lets take the above example (Integrity check should happen in following order : 1st should be Integrity check and 2nd should be Update statics) 

    I hope I'm clear now!

    Thanks!

    Tuesday, March 31, 2015 7:36 AM
  • I'm trying to automate the "Maintenance plan". I was able to do it through GUI by logging into SQL studio.

    Hello,

    Why don't you use SQL Server Agent Jobs to schedule the maintenance Jobs? You can define the schedule within the maintenance plan using SSMS GUI.

    See Create a Maintenance Plan (Maintenance Plan Design Surface) => "Subplan"


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 31, 2015 8:59 AM
  • I want to automate the task and there shouldn't be any manual intervention. I have automated the creation of database.

    Once database is created, I have to run a script which will create a maintenance plan.

    Once we confirm the completion, SQL admins will login.

    Tuesday, March 31, 2015 9:42 AM
  • Then script out the jobs and learn the schema so you can migrate the output script into something that can be automated.  Which means, for those tables that use keys and identifiers that are generated at creation time, you need to retrieve them and use them in any following statements.  This isn't rocket science.
    • Proposed as answer by Saeid Hasani Wednesday, April 1, 2015 12:23 AM
    Tuesday, March 31, 2015 12:38 PM
    Answerer
  • SQL admins will login.

    So what exactly do these "admins" actually do? Perhaps the admins want to perform maintenance activities on a realistic schedule and not according to your "automation".  There is a reason you employ administrators.

    Tuesday, March 31, 2015 12:40 PM
    Answerer
  • Once DB is created, we perform post-installation activity, which includes creating maintenance plan. After which we release the server into production. That's when "SQL DBA's" come into picture.

    I have done some research and finally got something, please help me in refining the below:

    USE master;
    GO
    EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'E:\Backup',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @Compress = 'Y',
    @CheckSum = 'Y'

    GO
    EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'USER_DATABASES',
    @CheckCommands = 'CHECKDB',
    @PhysicalOnly = 'Y'

    GO
    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,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'

    GO
    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

    Wednesday, April 1, 2015 3:52 AM
  • Once DB is created, we perform post-installation activity, which includes creating maintenance plan. After which we release the server into production. That's when "SQL DBA's" come into picture.

    I have done some research and finally got something, please help me in refining the below:

    USE master;
    GO
    EXEC dbo.DatabaseBackup
    @Databases = 'USER_DATABASES',
    @Directory = 'E:\Backup',
    @BackupType = 'FULL',
    @Verify = 'Y',
    @Compress = 'Y',
    @CheckSum = 'Y'

    GO
    EXECUTE dbo.DatabaseIntegrityCheck
    @Databases = 'USER_DATABASES',
    @CheckCommands = 'CHECKDB',
    @PhysicalOnly = 'Y'

    GO
    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,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y'

    GO
    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

    the last two steps are redundant.. you do not both of them..just have one of them..



    Hope it Helps!!

    • Marked as answer by Michelle Li Wednesday, April 8, 2015 7:36 AM
    Friday, April 3, 2015 10:36 PM
    Answerer