MSSQL Administration Course Notes – Day 4

###Yesterdays Review

In SQL 2012 you can now be a user on a database without a SQL login. This requires containment level to be set to partial at the database level.

Everyone is part of the Public role (server or database level).

Sysadmin at the server level maps to dbo in databases. Sysadmins have the abilities to impersonate all users be default.

If running proc which executes as a user, the user running the proc must have impersonate access to the user the proc is running under.

If you write audit events to a file, you can only truly read it from within SQL using the function sys.fn_get_audit_file.

CDC - This records data changes and what the changes were.

###AM Session - Automating Server Management With SQL Server Agent

Setup Database Mail
Server > Management > Database Mail > Right Click - Configure Database Mail If you grant a user the ‘DatabaseMailUserRole’ on a database level you can create them a private database mail profile.

Configure SQL Agent To Use Database Mail
Server > Right Click - SQL Server Agent > Properties > Alert System Also configure failsafe operator. This is useful if used pager email operators and no one is on duty. Use pager email addresses so you can schedule peoples hours.

Keep on top of house keeping for mail log size


SQL Agent operators are humans / email addresses that will receive emails.

Alerts in a SQL Agent Job are like a trigger / monitor, not an notification.

Three type of alerts: Standard Error Messages Stat Counters WMI Event

Standard Error Codes - 1033 is english and severity 19 or above is classed as fatal errors.

SELECT * FROM sys.messages
WHERE language_id=1033

message ID 50000 > Is a custom message Can add a custom message


For a TSQL type Agent Job, you need to set run as under advance menu/page, not general. Below will walk through creating a new credential.

Map an credential to an underlying account. You map a Windows account to a credential within SQL Server. Note passwords are manually managed / stored within SQL Server. Server > Credentials

You then have to bind the credential to what it will be used for and is given a name a proxy Server > SQL Agent > Proxies > New

SSMS can only connect to one instance of SSIS on a machine so you have to mess around with the below ini then restart SSIS Service for SSMS to connect. In the XML you’re looking for <ServerName></ServerName> C:Program FilesMicrosoft SQL Server110DTSBinnMsDtsSrvr.ini.xml

House keeping for agent error logs - This happens on restart of the service or you can run the below command to manually cycle the logs.

exec sys.sp_cycle_errorlog

For an alert to send out an email if configured, then it must enabled in sys.messages by settings is_event_logged column to 1:

SELECT * FROM sys.messages

-- Returns list of events being logged
SELECT * FROM sys.messages
WHERE is_event_logged &lt;&gt; 0

###PM Session - Ongoing DB maintenance

Checks logical and physical integrity in the database Capture64-ddbc_checkdb_options

-- DBCC CHECKDB  For Errors Only

-- Repair without dataloss -
-- Note: Single user mode is required

-- Repair with DATALOSS!! This just deletes the issue.
-- Note: Need to be in single user mode on the database for this to prevent issues when running

A DBCC CHECK with TABLOCK will run much quicker as it isn’t using database snapshots though will lock the table while running. This therefore may cause user impact for large tables if running during office hours.

824 error indicates a detected logicalic consistency IO based issue. Always check reading certain records if done a SELECT * to get a 824. You can see page errors in the below table. In the enterprise edition of SQL Server, you are able to do page restores.

SELECT * FROM MSDB.dbo.suspect_pages

Tip - Defragment within SQL not on the OS level as it’s not SQL aware.

###PM Session - Indexes

Clustered Index - Only one per table - Creating a PK by default creates a clustered index in the background. Note - If a clustered index is created on a heap with several existing non-clustered indexes, all the non-clustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Similarly, if a clustered index is dropped on a table that has several non-clustered indexes, the non-clustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables.

Non-clustered Index - You can have up to 999 indexes. Non-clustered indexes contain the clustering key value instead of the row identifier.

non-left level - index pages left level - is the data


MS say less than 30% use REORGANIZE above use REBUILD though might be better to wait till 50% for REBUILD. Each system and requirement are unique.

Physical - Locks for the duration. Transaction log intensive


Use fillfactor on the index rebuild to leave free space on each page so that if new items are added it gives the option for the index to have space to insert in the correct place to short term help with preventing fragmentation. This example will leave 80% free on each page.


Logical - Can access data while running. Will skip page if locked, by user for example. Can get index_name from sp_helpindex [TABLENAME]


###PM Session - Maintenance Plans

Server > Management > Maintenance Plans Pretty straight forward. Not really much notes needed as maintenance plans reference other system functionality.

Misc Links

Good script as health check for when taking over a new server

Posted in Databases, Databases with : Training