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’, ‘Domain\BizTalk_SVC_BKP’

USE [BizTalkMgmtDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [BizTalkMsgBoxDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [BizTalkRuleEngineDb]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\BizTalk_SVC_BKP’

USE [SSODB]

EXEC sp_addrolemember ‘BTS_BACKUP_USERS’, ‘Domain\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: \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 = ‘\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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

A guide to installing SQL Server 2008 R2 for a single instance. In addition, post installation steps have been added where appropriate, to ensure a consistent approach is taken to the configuration of the MSSQL instance.

Included are some basic requirements for SQL Server Reporting Services (SSRS) and BizTalk, which are optional components and not always applicable to the installation.

The installation of Service Pack 3 for SQL Server 2008 R2 has also been summarised, which is the latest Service Pack available at the time of writing this document.

Pre-Installation Requirements

Windows Page File

Ensure that the Windows server has been built with a dedicated disk for the Windows Page File (swap space). Generally, this should be sized at 1.5 times the size of the amount of physical RAM installed on the machine, with a “system managed page file size” configuration. For servers with large amounts of physical RAM, the 1.5 times rule may not be appropriate and should be considered on an individual build basis.

Disk Configuration

Whilst not always possible, the recommended disk configuration to use for a dedicated SQL Server database server is as follows:

Disk Letter	Contains	Typical Path
C:\	Operating System	\
D:\	MSSQL software, System DB data/logs	\Program Files\Microsoft SQL Server
E:\	User DB data	\MSSQL\DATA
F:\	User DB logs	\MSSQL\LOGS
G:\	Temp DB data	\MSSQL\Temp_DATA
H:\	Temp DB logs	\MSSQL\Temp_LOGS
I:\	DB backups	\MSSQL\Backups

Using the default Allocation Unit Size for the backup disk provides potential for the backup files (<20GB) to be later compressed to preserve disk space.

The “Bytes Per Cluster” value can be verified per disk, by the running the following command line (with “Administrator” rights):

fsutil fsinfo ntfsinfo L:|find “Bytes Per Cluster”

Bytes Per Cluster : 65536

Further information around disk partition alignment recommendations can be found here:

https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx

Service Account and Security Rights

A domain level SQL Server service account should be created in readiness for the installation, as this will be used to run the various SQL Server services.

Add the SQL Server service account to the server’s local “Administrators” group. In addition, add the same service account to the following “Local Security Policies”:

· Replace a process level token

· Lock Pages in Memory (LPIM) (may not be necessary in Windows Server 2008+ builds)

· Perform Volume Maintenance Task

 

If it is not possible to run the services as a domain account, and not a Production environment build, then you may consider using “NT AUTHORITY\Network Service”. This is a low privilege account, but one that retains the computer’s network credentials.

Installation Instructions

From the MSSQL installation kit, run setup.exe and select “Installation” from the side menu. Select “New installation or add features to an existing installation.”

Ensure that the “Setup Support Rules” pass without any issue and continue.

If the product key has been pre-populated, leave as is. Otherwise obtain a valid and appropriate product key and enter it here.

Accept the license terms and continue.

Proceed with the install of the “Setup Support Files” if prompted.

Further “Setup Support Rules” are checked at this stage. The “Windows Firewall” rule will typically flag a warning, which can be ignored. All other checks should pass OK.

Opt for a “SQL Server Feature Installation” and continue.

Feature Selection

Select the features appropriate to the requirements of the installation. Typically the following features would be installed:

· Database Engine Services (no sub-components)

· Reporting Services (optional)

· SQL Server Books Online

· Management Tools – Basic

· Management Tools – Complete

 

DeDetailed descriptions for each of the features can be found Microsoft documentation here:

https://msdn.microsoft.com/en-us/library/ms143786(v=sql.105).aspx

 

Additional “Installation Rules” will perform further pre-requisite checks. Ensure these pass OK and continue.

Instance Configuration

Update the “Instance root directory” path to an appropriate disk (C:\ should not be avoided). If a named instance is required, this should be provided here with an appropriate naming convention.

Disk space requirements will be checked next, and highlight any issues.

Change the “Startup Type” for the “SQL Server Agent” service to Automatic.

An appropriate domain service account should be used if at all possible to run each of the services listed.

If this is not possible, and not a Production environment build, then you may consider using “NT AUTHORITY\Network Service”.

This is a low privilege account, but one that retains the computer’s network credentials.

Make sure the collation is set to “Latin1_General_CI_AS” and continue.

On the “Account Provisioning” tab, ensure that the “Authentication Mode” is set to “Mixed Mode”. Additionally, add in accounts for other DBA logins/groups. These will be granted “sysadmin” rights and therefore have unrestricted access to the SQL Server instance.

 

You will also need to supply a password for the “SA” account (“System Administrator”) which has the same “sysadmin” rights as the listed users, but uses SQL Server authentication. Make sure the password conforms to the current password complexity standard, and is recorded securely.

Update the “Data root directory” to match the location used for the “Instance root directory” path earlier on. In addition, update the remaining Data, Log and Backup directory paths as appropriate.

The drive letters may differ slightly per installation, but if possible, the following directory structures should be used:

Directory	Path
MSSQL software	D:\Program Files\Microsoft SQL Server
User DB data	E:\MSSQL\DATA
User DB logs	F:\MSSQL\LOGS
Temp DB data	G:\MSSQL\Temp_DATA
Temp DB logs	H:\MSSQL\Temp_LOGS
DB backups	I:\MSSQL\Backups

 

 

Leave the FILESTREAM feature disabled, and continue.

If “Reporting Services” was selected from the “Feature Selection”, you’ll see the following screen. Continue using the “Install the native mode default configuration” option.

Ensure that the “Send Windows and SQL Server Error Reports” option is unticked and continue.

Ensure that the “Installation Configuration Rules” completed OK and continue.

Finally, check the installation summary, specifically the directory paths, features, and edition of SQL Server (usually Standard) to make sure everything is as expected, then click Install.

Once installed, check the log file for any issues, and click Close.

Service Pack 3 Installation

Accept the license terms and continue.

Leave all of the features select, so that all components are patched to the same level.

Wait for the “Check Files In Use” check to complete. Everything should be stopped that needs to be at this point, but you may need to explicitly stop applications/services depending on the output here, before the patching can continue.

Double check the correct features are listed for patching, then click Update. The “Patch Level” listed below indicates the current version installed. In this example, it is 10.50.1600.1 which is SQL Server 2008 R2 RTM (base release).

Once the update has completed, click Close.

To verify the Service Pack has been applied, you can log into SQL Server Management Studio (SSMS) and the version number will be displayed to the right of the instance name. Alternatively, you can run the following T-SQL:

SELECT CAST(SUBSTRING(@@version ,0, CHARINDEX(CHAR(10), @@version )) AS CHAR(70)) AS [Version]

Version

———————————————————————

Microsoft SQL Server 2008 R2 (SP3) – 10.50.6000.34 (X64)

Further information around SQL Server version information can be found here in the Microsoft KB:

http://support.microsoft.com/en-us/kb/321185

A similar process to the above is followed when applying Cumulative Updates.

NOTE: Some patches may require server restarts before/after update process.

Post Installation Steps

Max Server Memory Settings

By default this is effectively set to unlimited, which can lead to the Operating System being starved of physical memory at times, or other SQL Server instance, and so should always be adjusted.

The guide below assumes an x64 bit installation is running with either SQL Server 2005/2008/2008 R2/2012 versions:

Physical RAM Installed		SQL Max Server Memory Setting	Physical RAM Remaining
Total GB	Total MB		Free MB	Free GB	Remaining %
2	2,048	1,450	598	0.6	29.2
4	4,096	3,000	1,096	1.1	26.8
6	6,144	4,800	1,344	1.3	21.9
8	8,192	6,400	1,792	1.8	21.9
12	12,288	10,000	2,288	2.2	18.6
16	16,384	13,500	2,884	2.8	17.6
24	24,576	21,500	3,076	3	12.5
32	32,768	29,000	3,768	3.7	11.5
48	49,152	44,000	5,152	5	10.5
64	65,536	60,000	5,536	5.4	8.4
72	73,728	68,000	5,728	5.6	7.8
96	98,304	92,000	6,304	6.2	6.4
128	131,072	124,000	7,072	6.9	5.4
256	262,144	250,000	12,144	11.9	4.6
1024	1,048,576	1,029,000	19,576	19.1	1.9

If more than one SQL Server instance (MSSQL) is running on the server, split the allocated SQL Max Server Memory across the instances, appropriate to demands of each environment.

For example, a server with 16GB of physical memory installed running MSSQL and SSRS would typically result in the MSSQL instance being configured to use 12,500MB (minus 1,000MB for SSRS).

This update can either be performed using the GUI or via T-SQL.

From SSMS, right click on the instance and open up the “Properties”. Click on the “Memory” page on the left hand menu, and adjust the “Maximum server memory (in MB)” value as required. Leave the “Minimum server memory (in MB)” value as zero, and accept the changes (no restart is required).

T-SQL:

— Adjust Max Server Memory

EXEC sys.sp_configure ‘show advanced options’, ‘1’ RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘max server memory (MB)’, ‘16500’

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘show advanced options’, ‘0’ RECONFIGURE WITH OVERRIDE

Database Settings

From SSMS, right click on the instance and open up the “Properties”. Click on the “Database Settings” page on the left hand menu, and tick the “Compress backup” option. Also check that “Database default locations” paths for the data and log files are also correct, although they should already be set from the install.

T-SQL:

— Enable compression for backups

EXEC sys.sp_configure ‘backup compression default’, ‘1’

RECONFIGURE WITH OVERRIDE

Advanced Optimization Settings

From the same instance properties window, click on the “Advanced” page on the left hand menu, and change the “Set Optimize for Ad hoc Workloads” option to “True”. This helps optimize ad-hoc queries by reducing plan cache bloat, and reducing memory pressure.

T-SQL:

— Set Optimize for Ad hoc Workloads to True

EXEC sys.sp_configure ‘show advanced options’, ‘1’ RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘optimize for ad hoc workloads’, ‘1’

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure ‘show advanced options’, ‘0’ RECONFIGURE WITH OVERRIDE

Database Mail Configuration

There is no current requirement to configure database mail, but this URL explains how to do so:

http://www.snapdba.com/2013/04/enabling-and-configuring-database-mail-in-sql-server-using-t-sql/

Error Logs Retention

From SSMS, navigate to “Management” and then right click on “SQL Server Logs” then “Configure”.

Tick the “Limit the number of error log files before they are recycled” box and change the “Maximum number of error log files” to 62, allowing for 2 months’ worth of logs.

T-SQL:

–Configure SQL Error Log retention for 62 days (2 months)

EXEC xp_instance_regwrite ‘HKEY_LOCAL_MACHINE’, ‘Software\Microsoft\MSSQLServer\MSSQLServer’,

‘NumErrorLogs’, REG_DWORD, 62

SQL Server Agent Job History

From SSMS, right click on “SQL Server Agent” then “Properties”. Click on the “History” page on the left hand menu and make sure the “Limit size of job history log” box is ticked. Change the “Maximum job history log size (in rows)” to 10000 and the “Maximum job history rows per job” to 100.

T-SQL:

–Change the SQL agent history retention

EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000,

@jobhistory_max_rows_per_job=100

TempDB configuration

The default TempDB sizing and configuration is usually sufficient for most installations. However, some environments may benefit from splitting the data file into multiple files of equal size, especially if latch contention issues are experienced and TempDB usage is quite high.

Creating multiple TempDB files enables MSSQL to split the I/O transactions across each of the data files, optimizing performance when multiple CPUs perform the SQL operations simultaneously. This can also prevent or alleviate blocking activity.

The advice from Microsoft is such that:

“As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.”

However, previous experience has shown that using 1 data file per logical core, can sometimes lead to further contention and performance issues. From experience, the best approach to take is to create equally sized TempDB data files based on ½ the number of logical processor cores, with a maximum of 8 files.

Further details can be found in the Microsoft KB here:

http://support.microsoft.com/en-us/kb/2154845

Additional information about working with TempDB can be found here:

https://technet.microsoft.com/en-us/library/cc966545.aspx

 

Normal 0 false false false false EN-GB JA X-NONE

Get the old file locations first:

SELECT name AS LogicalName, physical_name AS PhysicalPath, CASE type_desc

WHEN ‘LOG’ THEN ‘Log’

WHEN ‘ROWS’ THEN ‘Data’ ELSE type_desc END AS FileType,

state_desc AS Status

FROM sys.master_files

WHERE database_id

–WHERE database_id >= 5 — User DBs

–WHERE database_id = DB_ID(‘model’) — Specific DB

 

For 2008 on there is no longer a requirement to use the 3608 trace flag, or startup the instance in single-user mode.

ALTER DATABASE MODIFY FILE commands can be used for the model and msdb moves, just like tempdb.

Normal 0 false false false EN-GB JA X-NONE

 

Model, msdb, and tempdb

Issue the following ALTER DATABASE T-SQL commands, updating the new file paths as appropriate:

— e.g. Move model

USE master

GO

ALTER DATABASE model MODIFY FILE (NAME = modeldev,FILENAME = ‘C:\SQLData\model.mdf’);

GO

ALTER DATABASE model MODIFY FILE (NAME = Modellog,FILENAME = ‘C:\SQLLogs\modelLog.ldf’);

GO

etc

then

— Move msdb

— Move tempdb

 

Stop SQL Server (from a Command Prompt):

net stop SQLSERVERAGENT

net stop MSSQLSERVER

Move the model.mdf and MSDBData.mdf files from D:\SQLData\ to C:\SQLData\ (do not move tempdb.mdf).

Move the modelLog.ldf and MSDBLog.ldf files from D:\SQLLogs\ to C:\SQLLogs\ (do not move tempLog.ldf).

Delete the tempdb.mdf and tempLog.ldf files from D:\SQLData\ and D:\SQLLogs\; These will be recreated at startup of the SQL instance so do not need to be moved.

Start SQL Server (from a Command Prompt) leaving the SQL Server Agent for now:

net start MSSQLSERVER

 

Normal 0 false false false EN-GB JA X-NONE

Master

There is no longer a requirement to place the SQL instance in master recovery mode first.

Start “SQL Server Configuration Manager”, click on the “SQL Server Services” option, right-click on the SQL Server instance and click on “Properties”. Click on the “Advanced” tab.

Edit the “Startup Parameters” by changing the existing master MDF and LDF entries:

-dC:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lC:\SQLLogs\mastlog.ldf

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

-d is the fully qualified path for the master database data file.

-e is the fully qualified path for the error log file.

-l is the fully qualified path for the master database log file.

Apply the changes, then stop SQL Server (from a Command Prompt):

net stop MSSQLSERVER

Move the master.mdf files from D:\SQLData\ to C:\SQLData\ and the mastlog.ldf file from D:\SQLLogs\ to C:\SQLLogs \.

Restart SQL Server along with the SQL Server Agent:

net stop MSSQLSERVER

net start MSSQLSERVER

 

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

You can check the locations of the System database files after the move(s) by running:

SELECT name AS LogicalName, physical_name AS PhysicalPath,CASE type_desc

WHEN ‘LOG’ THEN ‘Log’

WHEN ‘ROWS’ THEN ‘Data’ ELSE type_desc END AS FileType,

state_desc AS Status

FROM sys.master_files WHERE database_id

 

 

DECLARE @dbname sysname

/* Work with current database if a database name is not specified */

SET @dbname = ‘DB1’

SELECT   
CONVERT(char, backup_start_date, 111) AS [Date],
CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name],
[filegroup_name] AS [Filegroup Name],
logical_name AS [Logical Filename],
physical_name AS [Physical Filename],
CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT    b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name,
a.[filegroup_name], a.physical_name,
(
SELECT    CONVERT(numeric(15,2),((a.file_size * 100.00)/i1.file_size)-100)
FROM    msdb.dbo.backupfile i1
WHERE     i1.backup_set_id =
(
SELECT    MAX(i2.backup_set_id)
FROM    msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON      i2.backup_set_id = i3.backup_set_id
WHERE    i2.backup_set_id i2.file_type=’D’ AND
i3.database_name = @dbname AND
i2.logical_name = a.logical_name AND
i2.logical_name = i1.logical_name AND
i3.type = ‘D’
) AND
i1.file_type = ‘D’
) AS Growth
FROM    msdb.dbo.backupfile a JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHERE    b.database_name = @dbname AND
a.file_type = ‘D’ AND
b.type = ‘D’

) as Derived
WHERE (Growth 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]

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.

 

 

 

doesn’t work – but could.

SELECT TOP 10

(total_logical_reads/execution_count) AS avg_reads,

(total_logical_writes/execution_count) AS avg_writes,

(total_physical_reads/execution_count) AS avg_physical_reads,

execution_count,

statement_start_offset as stmt_start_offset,

(SELECT SUBSTRING(text, statement_start_offset/2 + 1,

(CASE WHEN statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(MAX),text)) * 2

ELSE statement_end_offset

END – statement_start_offset)/2)

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

(SELECT query_plan from sys.dm_exec_query_plan(plan_handle)) as query_plan

FROM sys.dm_exec_query_stats

ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC

 

 

SELECT OBJECT_NAME(id), name, STATS_DATE(id, indid), rowmodctr FROM sys.sysindexes

WHERE STATS_DATE(id, indid)

AND rowmodctr>0 AND id IN (SELECT object_id FROM sys.tables) order by rowmodctr desc;

 

SELECT ‘UPDATE statistics dbo.’ + OBJECT_NAME(id) FROM sys.sysindexes

WHERE STATS_DATE(id, indid)

AND rowmodctr>0 AND id IN (SELECT object_id FROM sys.tables)and name not like ‘_WA_%’;

 

 

 

EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

 

SQL Server 2005

SELECT dbschemas.[name] as ‘Schema’, dbtables.[name] as ‘Table’, dbindexes.[name] as ‘Index’, indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc;

 

ALTER INDEX ON REBUILD WITH (MAXDOP = 1);

dbcc show_statistics(CommandLog,PK_CommandLog);

select * from sys.dm_db_index_physical_stats(dbid, objid, indexid, partition_no, mode);

use nulls to get values for all databases etc

 

select DB_ID(‘Maintenance’)

select OBJECT_ID(‘CommandLog’)

select * from sys.dm_db_index_physical_stats(13, 2105058535, -1, 0,NULL)

list traces — SELECT * FROM :: fn_trace_getinfo(default)

delete trace — oracle sp_trace_setstatus @traceid=?, @status =2

SELECT name FROM sys.databases

 

 

 

Find database growth stats by looking at backup sizes.

SELECT

CONVERT(char, backup_start_date, 111) AS [Date], –yyyy/mm/dd format

CONVERT(char, backup_start_date, 108) AS [Time],

‘dbname’ AS [Database Name],

[filegroup_name] AS [Filegroup Name],

logical_name AS [Logical Filename], physical_name AS [Physical Filename],

CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],

Growth AS [Growth Percentage (%)]

FROM

(SELECT

b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name,

a.[filegroup_name], a.physical_name,

(SELECT CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)

FROM msdb.dbo.backupfile i1

WHERE i1.backup_set_id =

(SELECT MAX(i2.backup_set_id)

FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3

ON i2.backup_set_id = i3.backup_set_id

WHERE i2.backup_set_id a.backup_set_id AND i2.file_type=‘D’

AND i3.database_name = ‘dbname’

AND i2.logical_name = a.logical_name

AND i2.logical_name = i1.logical_name

AND i3.type = ‘D’

)

AND i1.file_type = ‘D’

) AS Growth

FROM msdb.dbo.backupfile a

JOIN msdb.dbo.backupset b ON a.backup_set_id = b.backup_set_id

WHERE b.database_name = ‘dbname’

AND a.file_type = ‘D’

AND b.type = ‘D’

) as Derived

WHERE

(Growth 0.0) OR (Growth IS NULL) ORDER BY logical_name, [Date]