SQL 2012 Add Volumes to Cluster and Maintenance plans

compdigit44 used Ask the Experts™
I am in the process of adding new volumes to my SQL 2012 cluster and need to create a new maintenace plan.

1) I have already presented the new volumes to both nodes and configured the volume on the primary node. I see the volumes listed add offline and not initilized on the secondary node should I leave this as is? I want to make sure this part is setup correctly first before I add the volume to SQL.

2) When I create a maintenance plan it creates a job called name.subPlan_1 Is there anyway to rename these jobs?

3) I want to do a full backup on all DB's and have select the option to create a sub folder for all DB's I have also selected the option to backup the transaction log and created a sub folder with I now have two subfolder per DB?

3)My SQL server only host SharePoint 2010 DB's and according the MS Sharepoint maintance Plan recommendations document from 5/12/11 I should be doing a Index Rebuild over a ReOrg. Won't a rebuild take my DB's offline????

4) the past admin created a IndexOptimixe job via command line but I am not sure if it is doing a rebuild or reorg...

"sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @LogToTable = 'Y'" -b"

5) Why is it so hard to delect a schedule subplan.1 after you created a maintenance. So far the only way I have found to delete a job that isn't even scheduled is to delete the whole plan..

Please help!!!!!!!!!!!!!!!!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

The volume has to be added as a resource within the sql group.
When you are using the maintenance plan GUI, you can name/rename the sub-plan which will correspond to the name of the job

Within the maintenance plan, you can disable the plan, delete the schedule (hit the X on the right)
You can within the maintenance plan delete the subplan.

Look at the indexoptimize sP under the master DB and see what the code does.


Thanks for the response...

IN SQL 2012 does an index rebuild require the databases to bee offline? If not can this be run and still have the DB accessible?
That stored procedure is part of Ola Hallegren maintenance solution, which is one of the best. The way it works by default is this:

Fragmentation%, action

if <10 do nothing
between 1 and 30 do reorganize
>30 do rebuild

You can change that and force rebuild if you use parameters as pr documentation here:


The parameter that says
@LogToTable = 'Y'

means that there is a table in master database, or whatever database that procedure was created that will show you what action has been applied. To see that you have to query that table:

  FROM [zb_dba_maint].[dbo].[CommandLog]
	[CommandType] like 'ALTER%'
	and starttime >= dateadd(dd,-7, getdate())

Open in new window

Also you can check the jobs output file which you can find if you check the job, which is probably called IndexOptimize - USER_DATABASES, right click > Properties > Steps > Select the step and click Edit > Advance and you will see where the output file is created.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

In regards to your question about rebuild online the answer is yes. You will actually see in that query result that the rebuild uses the option ONLINE=ON if the server is 2008 and up.

I recommend you to use the stored procedure from that maintenance solution instead of creating a new plan. You can execute the stored procedure directly in a query window or just build a one time job as T-SQL command with the call for that procedure. Change the parameters according to your needs following the documentation:
EXECUTE [dbo].[IndexOptimize] 
	@Databases = 'USER_DATABASES', 
	@LogToTable = 'Y',

Open in new window


How can I check for fragmentation?


BTW, thank you for all of your help...

By any chance do you have thoughts on my first question....

1) I have already presented the new volumes to both nodes and configured the volume on the primary node. I see the volumes listed add offline and not initilized on the secondary node should I leave this as is? I want to make sure this part is setup correctly first before I add the volume to SQL.
Just disable it.

You can check the fragmentation with this:
	drop table #idx_frg
	DB_NAME(DB_ID()) AS DatabaseName,
    schemas.[name] AS SchemaName,
    objects.[name] AS ObjectName,
    indexes.[name] AS IndexName,
    objects.type_desc AS ObjectType,
    indexes.type_desc AS IndexType,
    dm_db_index_physical_stats.partition_number AS PartitionNumber,
    dm_db_index_physical_stats.page_count AS [PageCount],
    right('____'+cast(round(cast(dm_db_index_physical_stats.avg_fragmentation_in_percent as decimal(8,2)),2) as varchar),6) AS AvgFagmentationInPercent
into #idx_frg
	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
	INNER JOIN sys.indexes indexes 
		ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] 
		AND dm_db_index_physical_stats.index_id = indexes.index_id
	INNER JOIN sys.objects objects 
		ON indexes.[object_id] = objects.[object_id]
	INNER JOIN sys.schemas schemas 
		ON objects.[schema_id] = schemas.[schema_id]
	objects.[type] IN('U','V')
	AND objects.is_ms_shipped = 0
	AND indexes.[type] IN(1,2,3,4)
	AND indexes.is_disabled = 0
	AND indexes.is_hypothetical = 0
	AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
	AND dm_db_index_physical_stats.index_level = 0
	AND dm_db_index_physical_stats.page_count >= 1000
order by 
		when replace(AvgFagmentationInPercent,'_','') between 10.00 and 29.99 then 'Reindex' 
		else 'Rebuild' end as 'Action' 
	#idx_frg where replace(AvgFagmentationInPercent,'_','')>10.00 
order by 
	AvgFagmentationInPercent desc

select * from #idx_frg

Open in new window

It is the same the stored procedure does it. I insert the results into a temp table which I select from at the bottom. I do this because the query can take some time to run, 10-15 min even depending of how big your indexes are.

You don't need to check if you want to force the rebuild. Just look in the link I sent you and change the parameter like this:
EXECUTE dbo.IndexOptimize
	@Databases = 'USER_DATABASES',
	@UpdateStatistics = 'ALL',
	@LogToTable = 'Y'

Open in new window

I used INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE because in certain situations you can't rebuild indexes online(spatial and/or xml indexes) in which case the OFFLINE will be used.


Thanks I once I closed and reopened Management Studio I was able to delete the maintenance plan.

I am running the query you posted now...

I am sorry to both you again but... do you have any thought on my first question...

1) I have already presented the new volumes to both nodes and configured the volume on the primary node. I see the volumes listed add offline and not initilized on the secondary node should I leave this as is? I want to make sure this part is setup correctly first before I add the volume to SQL.


I am reading hallengren' website now and I need to edit the follow syntax to allow for backup compression.. Is my follow syntax correct?????

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\backup', @BackupType = 'FULL', @Verify = 'Y',  @CleanupTime = 24, @Compress = Y, @CheckSum = 'Y', @LogToTable = 'Y'" -b
Yes. The only part you need to edit is between "".

Also you should schedule diff and transaction log backus. You could use something like this:

1. FULL once a week
2. DIFF every week day except for the day when the FULL is executed
3. Transaction log backup every 15 minutes. The interval could be different if you want.

If you run the general script the jobs are created automatically for you and you only need to modify them according to your needs.


Thanks I did run the script and need to edit it...

I am do not understand what you are referring to with the "".

Also, in Windows 2008 cluster do I only have to initialize /format the volumes on one node??
The job is executed as a windows command using sqlcmd command line for some good reasons. However what that does is actually to execute the stored procedure against the current server and that is wrapped in double quotes, "". That part in your case is:
EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'D:\backup', @BackupType = 'FULL', @Verify = 'Y',  @CleanupTime = 24, @Compress = Y, @CheckSum = 'Y', @LogToTable = 'Y'

Open in new window

That part you can edit if you want but everything else you keep untouched.


OK so every value needs quots..

Do you have any thought on my cluster question please
One more thing. If you want to force the index rebuild, using the example posted before, you should do it only once. After that just use the procedure with the default parameters because it will automatically apply whatever action is needed depending on fragmentation level.

With cluster issue I have no enough experience to be able to help.
Distinguished Expert 2017

Is the new volume been added as a storage resource within the sql group?

The following link deals with the storage resource addition to an existing cluster.



My question is outside of SQL.

1) I have present the volume to both nodes and it is visible in My Computer..

At this point do I add the volume to SQL or do I have to bring the volume online on the other node?


Thank for the help everyone. Late yesterday I added new volumes to my SQL cluster, moved my DB, log and TEMPDB files to new volumes and was able to bring everything back online. I was a little unnerving but I learned a lot thanks to everyone.

I am not a DBA both more of Active Directory / Vmware person so if anyone needs help in the future make sure you post your question and I will do the best that I can to help!!!!
Distinguished Expert 2017

A few experts assisted you with the 5 questions on different topics that you had.

In the future, it would be best if you have a single topic per question. It would be fairer to all participants.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial