isitdevops.com/databases

Screenshots

Instance Level

The following SQL job ensures the SQL Server Agent logs and SQL Server Error logs are recycled on a daily basis. This will help ensure that neither log grows too large in size, and can therefore easily be accessed for troubleshooting purposes.

Supporting screenshots have been added below this section, using the “Cycle error logs” plan as an example.

Start > Microsoft SQL Server > SQL Server Management Studio > SQL Server Agent > Jobs > New Job

Name: Cycle error logs Category: Database Maintenance Steps: ü Step 1 o Step Name: Cycle agent log o Type: T-SQL o Database: msdb o Command: exec sp_cycle_agent_errorlog o On success action: Go to the next step ü Step 2 o Step Name: Cycle error log o Type: T-SQL o Database: msdb o Command: exec sp_cycle_errorlog o On success action: Quit the job reporting success Schedule Name: Daily at midnight Schedule: Daily at 00:00

BizTalk Backup Job Configuration

If BizTalk Server is being installed as part of the environment build, a separate backup job, specifically for BizTalk will need to be configured after the BizTalk specialists have deployed the software.

Firstly, identify a suiteable Windows user account (usually a domain service account) for running the backup job. Craete a SQL Server login for the account (unless it already exists) and then assign it the BTS_BACKUP_USERS role either via the GUI or the following T-SQL:

— Assign backup role to nominated user

USE [BizTalkDTADb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘STORES\BizTalk_SVC_BKP’

USE [BizTalkMgmtDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘STORES\BizTalk_SVC_BKP’

USE [BizTalkMsgBoxDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘STORES\BizTalk_SVC_BKP’

USE [BizTalkRuleEngineDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘STORES\BizTalk_SVC_BKP’

USE [SSODB]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘STORES\BizTalk_SVC_BKP’

Once that’s done, navigate to “SQL Server Agent” in SSMS, expand the “Jobs” folder. Right click on the “Backup BizTalk Server (BizTalkMgmtDb)” job that’s been created, and click on “Properties”. Update the “Owner” of the job to the user chosen above.

Click on the “Steps” page from the menu on the left, highlight the “BackupFull” step and Edit.

Update the “Command” string so that the destination path reflects an appropriate directory for hosting the BizTalk backup files. It’s usually a good idea to use the same disk as the other database backups, but a different parent directory so that any existing maintenance plans with “Maintenance Cleanup Tasks” do not interfere with purging of the BizTalk backup files.

As an example, the following directory is used here: V:\MSSQL\BizTalk_Backups

Repeat the set above for the “MarkAndBackupLog” step of the job.

Update the “Clear Backup History” step with an appropriate retention period for the backup history. The default of 14 days is usually acceptable, but given that we usually only keep 2 days’ worth of backups online, this can be reflected here also.

NOTE: The above step does not purge the physical backup files. An additional step (below) will be added to take care of this.

For the same step, click on the “Advanced” option and update the “On success action” to “Go to the next step”. Then change the “On failure action” to “Quit the job reporting failure”.

From the “Job step list” page, click “New”.

Create a new step with the following properties, updating the path, server name, and hour’s retention as appropriate to your requirements:

Step Name: Delete Old Backups

Type: PowerShell

Run as: SQL Server Agent Service Account

Command:

$path = ‘V:\MSSQL\BizTalk_Backups‘

$filter = ‘SNAPSQL01*.bak’

$retention= (Get-Date).AddHours(-48)

Get-ChildItem (Join-Path $path $filter) -Recurse |? {($_.PSIsContainer -eq $false) -and ($_.LastWriteTime -lt $retention)} | Remove-Item

Click on the “Advanced” page. Update the “On success action” to “Quit the job reporting success”. Then change the “On failure action” to “Quit the job reporting failure”.

Finally, check the “Job History” after around 30 minutes to ensure the job is completing successfully, and every 15 minutes (default).

Further information around configuration of the BizTalk Backup job can be found here in the MSDN article here:

https://msdn.microsoft.com/en-us/library/aa546765.aspx

Large Index Rebuilds

The dbo.Index_Rebuilds Stored Procedure (SP) has been created internally to address large ad-hoc index rebuilds. Whilst the automated index management tool is sufficient in most cases, there are occasions when large indexes incur timeouts before completing the rebuild or reorganize operations. When this happens, not only does the operation fail, but it can take a long time to rollback the transaction, which can also significantly degrade performance.

Accepted Parameters

A list of accepted parameters are listed within the SP and also below for reference:

@MaxFrag Max level of acceptable fragmentation (default is 20.0).

@MaxDensity Max level of acceptable density (default is 80.0).

@DatabaseName Database to check. This determines the table whitelist used (@TableInList).

@RebuildLimit Limits how many indexes are rebuilt/reorganized (default is 1).

@ReorgMax Fragmentation level at which point rebuilds are initiated. Anything lower than this limit will execute a reorganize instead, 0 will disable reorgs (default is 30.0).

@RunMode Options are ‘REPORT’ for report only, with no REBUILD/REORG operations being performed, or ‘ACTIVE’ mode which executes the REBUILD/REORG operations (default is ‘CHECK’ mode).

Example Executions

EXEC Index_Rebuilds @MaxFrag=5.0, @MaxDensity=80.0, @DatabaseName=’DB1′, @RebuildLimit=1, @ReorgMax=0, @RunMode=’REPORT’

Job outcome from supplied parameters:

· Run in REPORT only mode against DB1

· Rebuild/reorganize limit of 1

· Indexes with fragmentation levels above 5% OR page density of less than 80% will be candidates

· Indexes will be rebuilt, nothing will be reorganized (@ReorgMax=0)

EXEC Index_Rebuilds @DatabaseName=’DB2′, @RebuildLimit=1, @ReorgMax=0, @RunMode=’ACTIVE’

Job outcome from supplied parameters:

· Run in ACTIVE (execute) mode against DB2

· Rebuild/reorganize limit of 1

· Indexes with fragmentation levels above 20% OR page density of less than 80% will be candidates (DEFAULTS)

· Indexes will be rebuilt, nothing will be reorganized (@ReorgMax=0)

EXEC Index_Rebuilds @DatabaseName=’DB2′, @RebuildLimit=2, @ReorgMax=30, @RunMode=’ACTIVE’

Job outcome from supplied parameters:

· Run in ACTIVE (execute) mode against DB2

· Rebuild/reorganize limit of 2

· Indexes with fragmentation levels above 20% OR page density of less than 80% will be candidates (DEFAULTS)

· Indexes with less than 30% fragmentation will be reorganized, anything equal to or above this will be rebuilt

Example SQL Server Scheduled Job

 

Name: DB2 Large Index Rebuilds Category: Database Maintenance Description: Weekly index rebuild job for the most fragmented indexes Steps: ü Step 1 o Step Name: Rebuild index o Type: Operating system (CmdExec) o Run as: SQL Server Agent Service Account o Command: o sqlcmd -S MSSQLSERVER01 -E -d DB2 -Q “EXEC Index_Rebuilds @MaxFrag=50.0, @MaxDensity=80.0, @DatabaseName=’DB2′, @RebuildLimit=1, @ReorgMax=0, @RunMode=’ACTIVE'” -o “Q:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\DB2_Large_Index_Rebuild.log” o On success action: Quit the job reporting success Schedule Name: Weekly index Schedule: Every Weds at 00:20

Example Output File

***********************

* May 27 2015 12:20AM *

***********************

Listing index candidates for maintenance. Only 1 index(es) will be worked on…

IndexName TableName DensityPercent FragPercent

—————————————- —————————— ————– ———–

PK__RETAIL_T__F05C7E984850AF91 RETAIL_TRANSACTION 79.87 97.52

IDX_RTL_TRN_NO RETAIL_TRANSACTION 74.47 35.11

IDX_END_DT RETAIL_TRANSACTION 78.3 31.56

Starting maintenance…

*** Index [PK__RETAIL_T__F05C7E984850AF91] is 97.52% fragmented ***

Executing statement: ALTER INDEX [PK__RETAIL_T__F05C7E984850AF91] ON [DB2].[dbo].[RETAIL_TRANSACTION] REBUILD

Started at 00:22:04:857

Running in ACTIVE mode. Index maintenance WILL be performed.

Finished at 00:32:54:510

Duration: 00:10:49:653

Procedure completed.