Commvault Simpana – Automating DBMaintenance utility execution with qlogin and Powershell

Running the dbmaintenance utility regularly is a great way to get the Commcell database in ‘primo shape.

Over time, data is aged, clients are reconfigured/deleted and this can all add up to indexes getting out of date, oversized tables etc.

It’s recommended to do a Full DBMAINTENANCE operation every 6 months, as per CommVault latest guidance :
https://ma.commvault.com/KB/Details/30208

A ‘FULL’ run of the utility checks the DB for inconsistencies, re-indexes all tables and shrinks the database. The ‘RECOMMENDED’ run does the same, without running the shrink db operation.

Note that with V10, the database services do not need to be stopped to run this process, but jobs should be suspended as well as the scheduler.

Check the books-online reference here;

http://documentation.commvault.com/commvault/v10/article?p=features/tools/db_maintenance.htm

To assist with automating this process, and to have it run ‘set & forget’ I devised a Powershell script to assist.

This script does the following;

Connects to the Commcell, suspends any running jobs & suspends the job scheduler.
Runs the DBMAINTENANCE process in FULL mode
Re-enables the Job Scheduler
Resumes and previously suspended jobs
Logs out of the Commcell
Emails the DBMAINTENANCE execution output/log to a designated email address(s).

Script is below; Change parameters/variables as required

# Runs FULL dbmaintenance on Commserve database. Save into C:\Program Files\CommVault\Simpana\Base or define path.
$date = (get-date -f dd_MM_yyyy_hh_mm_tt)
# Log directory will need to be created or change to an existing location
$logdir = 'C:\cv\logs\dbm'
$smtpsrv = 'emailserver.domain.com'
$smtpfrom = 'commserve@domain.com'
$smtpto = 'recipient@domain.com'
 
Function RunMaint {
cd "\Program Files\Commvault\Simpana\Base"
# Establish login to Commserve. Named user `required unless running as logged in user who has SSO acccess.
.\qlogin -u "username"  -ps "#encrypted password#"  -cs "your commserve hostname"
# Suspend any running jobs
.\qoperation jobcontrol -all -o suspend
# Stop job scheduler to prevent any scheduled jobs starting
.\qoperation execscript -sn SetCommcellActivity.sql -si SCHEDULE -si n
# run dbmaintenance in "FULL" mode. No need to wait/check for status/exit code.
.\dbmaintenance -full | out-file $logdir\dbmain_$date.log
# re-enable job scheduler
.\qoperation execscript -sn SetCommcellActivity.sql -si SCHEDULE -si y
# resume any suspended jobs
.\qoperation jobcontrol -all -o resume
# logout of CS session
.\qlogout -cs "your commservehostname" }
 
Function EmailResults {
cd "$logdir"
$MsgBody = (gc $logdir\dbmain_$date.log | fl Name, LastWriteTime |out-string)
$attachment = (gci -Path $logdir -File | Sort CreationTime | Select -Last 1)
send-mailmessage -from $smtpfrom -to $smtpto -attachment $attachment -subject "Commvault DBMAINTENANCE complete" -body "$MsgBody" -SmtpServer $smtpsrv }
 
RunMaint
EmailResults

# Runs FULL dbmaintenance on Commserve database. Save into C:\Program Files\CommVault\Simpana\Base or define path.

$date = (get-date -f dd_MM_yyyy_hh_mm_tt)

# Log directory will need to be created or change to an existing location

$logdir = 'C:\cv\logs\dbm'

$smtpsrv = 'emailserver.domain.com'

$smtpfrom = 'commserve@domain.com'

$smtpto = 'recipient@domain.com'

Function RunMaint {

cd "\Program Files\Commvault\Simpana\Base"

# Establish login to Commserve. Named user `required unless running as logged in user who has SSO acccess.

.\qlogin -u "username" -ps "#encrypted password#" -cs "your commserve hostname"

# Suspend any running jobs

.\qoperation jobcontrol -all -o suspend

# Stop job scheduler to prevent any scheduled jobs starting

.\qoperation execscript -sn SetCommcellActivity.sql -si SCHEDULE -si n

# run dbmaintenance in "FULL" mode. No need to wait/check for status/exit code.

.\dbmaintenance -full | out-file $logdir\dbmain_$date.log

# re-enable job scheduler

.\qoperation execscript -sn SetCommcellActivity.sql -si SCHEDULE -si y

# resume any suspended jobs

.\qoperation jobcontrol -all -o resume

# logout of CS session

.\qlogout -cs "your commservehostname" }

Function EmailResults {

cd "$logdir"

$MsgBody = (gc $logdir\dbmain_$date.log | fl Name, LastWriteTime |out-string)

$attachment = (gci -Path $logdir -File | Sort CreationTime | Select -Last 1)

send-mailmessage -from $smtpfrom -to $smtpto -attachment $attachment -subject "Commvault DBMAINTENANCE complete" -body "$MsgBody" -SmtpServer $smtpsrv }

RunMaint

EmailResults

Let’s see it in action;

Execution in progress inside PS session; Not all command return status/execution codes but you can see the gist;

From the commcell GUI; Jobs being suspended and resumed;

Email with execution log attached; and some nice stats.

and to verify it all did actually happen ! DBMaintenance.log and qcommand.log;

So go ahead, download it, configure it, schedule it and forget about it 🙂

4 thoughts on “Commvault Simpana – Automating DBMaintenance utility execution with qlogin and Powershell”

Filip Hasa says:

April 9, 2016 at 7:24 am

hi, nice script. Why dont do it CV workflow? I am looking forward on your next post. Thank you

1. Brett Sinclair says:
  
  May 10, 2016 at 5:12 pm
  
  Hi thanks. No specific reason I didn’t use a workflow. Maybe I’ll do that in a future post 🙂
  
boffhead says:

October 6, 2016 at 2:25 pm

Just wanted to note that you probably don’t want to run the dbmaint -full any more often than 6 months. If you run it too frequently it’ll cause DB fragmentation and actually decrease your performance.

https://ma.commvault.com/KB/Details/30208
Shrinking the database increases fragmentation. This is because shrinking works by moving most used data pages to the beginning of the data file and truncating the unused part of the data file. Since relations between data pages (as in the ones that belong to the same table) is not considered, shrinking results in a lot of logical fragmentation.

Run these instead to cover all bases between 6 monthly full dbmaints:
dbmaintenance.exe –recommended
or
dbmaintenance.exe –checkdb
dbmaintenance.exe –reindexall

1. Brett Sinclair says:
  
  October 15, 2016 at 1:20 pm
  
  Thanks Brendan, looks like the official guidance may have been updated. I’ve updated the post to reflect.
  
  –Brett

M	T	W	T	F	S	S
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

Commvault Simpana – Automating DBMaintenance utility execution with qlogin and Powershell

Connects to the Commcell, suspends any running jobs & suspends the job scheduler.

Runs the DBMAINTENANCE process in FULL mode

Re-enables the Job Scheduler

Resumes and previously suspended jobs

Logs out of the Commcell

Emails the DBMAINTENANCE execution output/log to a designated email address(s).

Like this:

4 thoughts on “Commvault Simpana – Automating DBMaintenance utility execution with qlogin and Powershell”

Leave a Reply Cancel reply

Connects to the Commcell, suspends any running jobs & suspends the job scheduler.

Runs the DBMAINTENANCE process in FULL mode

Re-enables the Job Scheduler

Resumes and previously suspended jobs

Logs out of the Commcell

Emails the DBMAINTENANCE execution output/log to a designated email address(s).

Like this:

Related Post

4 thoughts on “Commvault Simpana – Automating DBMaintenance utility execution with qlogin and Powershell”

Leave a Reply Cancel reply