Database Files location with Comma separated

Enclosed script will provide the details of MDF and LDF locations with a comma separated. This will help us to collect the inventory during the intial requirement gathering.

 https://gallery.technet.microsoft.com/Database-Files-location-08d507df

 

Usefull DMV's

Dynamic Management Views and Functions (Transact-SQL) SQL Server 2014 Other Versions 5 out of 5 rated this helpful - Rate this topic Dynamic management views and functions return server state information that can be used to monitor the health of a server.

https://gallery.technet.microsoft.com/Usefull-DMVs-14b6bb92

SQL Server 2012 Instalation

https://gallery.technet.microsoft.com/SQL-Server-2012-Instalation-fcf5fb9e

DataCompression_Scripts and Steps

Database Compression document enclosed. This will do only page level compression if we run the scripts as it is mentioned in the document.If we need to do the ROW level compression please change the script, Just remove the commets where i have commented for the ROW.

https://gallery.technet.microsoft.com/DataCompressionScripts-and-40780587

Backup Details Scrpit _Power shell

Download script will provide the backup details in a csv format.

https://gallery.technet.microsoft.com/Backup-Details-Scrpit-6bb5de67

To Find Lead Blocking - Use Below Query

This will list out the lead blocker spid, based on this we can ask our application teams take decision to kill or not. This will be used in day to day basis when ever application team reports hampering performance or complaining the time taken to process is longer than the usual.

https://gallery.technet.microsoft.com/Lead-Blocking-Query-df3f0e21

Register a SPN for SQL Server Authentication with Kerberos

When it comes to configuring your SQL Servers to use Kerberos authentication there are a couple of prerequisites that must be met. First, the clients and servers must be joined to a domain. If they are joined, but they are in different domains then a two-way trust must be setup between these domains. Secondly an SPN must be successfully registered for the SQL Server service so that it can be identified on the network.

Please download the enclosed document which found blog, explained very neatly and easily.

https://gallery.technet.microsoft.com/Register-a-SPN-for-SQL-570f50fe

Always On HA Overview

1.    Introduction

High Availability of an application or service provided to the end user and reducing the downtime of the outages occurred if any (Disaster Recovery Management) plays a major role in the lifecycle of any application. System outages are either anticipated (planned) or result of failures (unplanned) which may lead to data loss directly/indirectly impacts the end user requirements. In these situations, the primary objective of the software engineers/developers is to bring back the system online as soon as possible with minimal the data loss. AlwaysOn is a feature in the SQL server 2012 which provides flexible and cost efficient high availability and disaster recovery solution. AlwaysOn provides automatic recovery from failures avoiding the downtime which increases the high availability of critical applications online thereby reducing the data loss. AlwaysOn can be configured at both database level and instance level.

1.1.       Availability Groups (AG) & AG Listener

Availability group is a collection of databases in which AlwaysOn is configured at database level. Availability group enhances capabilities of database mirroring and ensures the availability of databases. Client application connects to the Availability group’s databases through a virtual network name called Availability group listener (AG Listener).Client need not required to find the active physical instance of the SQL server instead Client connects to the AG Listener which determines the available primary SQL server. A SQL server instance can have multiple availability groups.

1.2.       Availability Replicas and Roles

Availability Replicas are the user databases residing in the Availability Group (AG). Each database in the availability group can have a maximum of four availability replicas but only one replica in the AG can act as primary replica which is enabled for read-write operations and remaining replicas act as secondary read only replicas. The secondary replicas are considered as the backup.

Please Download :

https://gallery.technet.microsoft.com/Always-On-HA-Overview-33efee38

Get size of all tables in database

https://gallery.technet.microsoft.com/Get-size-of-all-tables-in-8d05c25c

Kill Sessions For The Database

The enclosed script is used to kill the sessions of a perticular datbase. This can be used while restore of the database or any sessions needs to be killed from the application.

https://gallery.technet.microsoft.com/Kill-Sessions-For-The-ada2f686

Query to find All Columns in ALL databases on a Instance

https://gallery.technet.microsoft.com/Query-to-find-All-Columns-f70dfa36

How to configure SSRS with constraint delegation

Please Ref:
https://gallery.technet.microsoft.com/How-to-configure-SSRS-with-2d4a300a

SQL Server Collation Change Step By Step

Please Ref : https://gallery.technet.microsoft.com/SQL-Server-Collation-cc5cc63c

TEMP DB FULL

Here in the scripts:

https://gallery.technet.microsoft.com/TEMPDB-FULL-81137dd8

The process could not execute 'sp_repldone/sp_replcounters

Error messages:

·          The process could not execute 'sp_repldone/sp_replcounters' on 'SCKNLSSI '. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011

·          Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure. (Source: MSSQLServer, Error number: 18752)
Get help: http://help/18752

·          The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017

·          The process could not execute 'sp_repldone/sp_replcounters' on 'SCKNLSSI'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037

SOLUTION:-

To clear the pending transactions, run the following against the published database, 

1. Stop the logreader  Agent 

2. Stop the distribution agent:

exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

Turn on both agents.

DB Mail Configuration and Testing Mail Profiles

Below Information collected from peer blogs, and very usefull.

===============================================

To Enable Database Mail execute the following block of code:

use master

go

exec sp_configure 'show advanced options', 1

reconfigure

exec sp_configure 'Database Mail XPs', 1

reconfigure

Deleting the Old Profiles :

 IF EXISTS(

SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id

      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE       p.name = @ProfileName AND

      a.name = @AccountName)

BEGIN

      PRINT 'Deleting Profile Account'

      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName

END

 IF EXISTS(

SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)

BEGIN

      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName

END

 IF EXISTS(

SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)

BEGIN

      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName

END

Creating Accounts & Profiles for DB Mail:

--// Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'Test Mail Account',

    @description = 'Mail account for administrative e-mail.',

    @email_address = 'abc@xyz.com',

    @replyto_address = 'abc@xyz.com',

    @display_name = 'TEST Display',

    @mailserver_name = 'smtp.xxxx.net',

    @port = 587,

    @username = 'xyz',

    @password = 'xxyyzz',

    @enable_ssl = 1

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp     @profile_name = 'Test Mail Profile',     @description = 'Profile used for administrative mail.'

 -- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp     @profile_name = 'Test Mail Profile',     @account_name = 'Test Mail Account',

    @sequence_number =1

 -- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp     @profile_name = 'Test Mail Profile',

    @principal_name = 'public',     @is_default = 1

Sending Mail and Check its status:

--Send mail

EXEC msdb.dbo.sp_send_dbmail     @recipients=N'abba@gmail.com',

    @body= 'Test Email Body',     @subject = 'Test Email Subject',     @profile_name = 'Test Mail Profile'

--Send mail with attachment

EXEC msdb.dbo.sp_send_dbmail      @profile_name = 'DBMail'

    ,@recipients = 'GroupSQLDBA@MyCo.com'

    ,@from_address = 'DBMail@MyCo.com'

    ,@query = 'SELECT resource_type, resource_database_id,

                    request_mode, request_session_id

               FROM sys.dm_tran_locks

              WHERE request_mode IN (''IX'', ''X'')'

    ,@subject = 'Exclusive Locks'

    ,@attach_query_result_as_file = 1 ;

Important Tables used in configuring Database mail and check their status:

--Profiles

SELECT * FROM msdb.dbo.sysmail_profile

--Accounts

SELECT * FROM msdb.dbo.sysmail_account

--Profile Accounts

select * from msdb.dbo.sysmail_profileaccount

--Principal Profile

select * from msdb.dbo.sysmail_principalprofile

--Mail Server

SELECT * FROM msdb.dbo.sysmail_server

SELECT * FROM msdb.dbo.sysmail_servertype

SELECT * FROM msdb.dbo.sysmail_configuration

--Email Sent Status

SELECT * FROM msdb.dbo.sysmail_allitems

SELECT * FROM msdb.dbo.sysmail_sentitems

SELECT * FROM msdb.dbo.sysmail_unsentitems

SELECT * FROM msdb.dbo.sysmail_faileditems

--Email Status

SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',       fail.mailitem_id,       LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG

join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'

 --Mail Queues

EXEC msdb.dbo.sysmail_help_queue_sp

--DB Mail Status

EXEC msdb.dbo.sysmail_help_status_sp

=====================================================================================

http://msdn.microsoft.com/en-us/library/ms190307.aspx

Examples

A. Sending an e-mail message

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Automated Success Message. The body of the message contains the sentence 'The stored procedure finished successfully'.

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Adventure Works Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @body = 'The stored procedure finished successfully.',

    @subject = 'Automated Success Message' ;

B. Sending an e-mail message with the results of a query

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order Count, and executes a

query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Adventure Works Administrator',

    @recipients = 'danw@Adventure-Works.com',

    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder

                  WHERE DueDate > ''2004-04-30''

                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,

    @subject = 'Work Order Count',

    @attach_query_result_as_file = 1 ;

C. Sending an HTML e-mail message

This example sends an e-mail message to Dan Wilson using the e-mail address danw@Adventure-Works.com. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail sends the message in HTML format.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =

    N'

Work Order Report

' +

    N'' +

    N' Work Order IDProduct ID' +

    N'NameOrder QtyDue Date' +

    N'Expected Revenue

' +

    CAST ( ( SELECT td = wo.WorkOrderID,       '',

                    td = p.ProductID, '',

                    td = p.Name, '',

                    td = wo.OrderQty, '',

                    td = wo.DueDate, '',

                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty

              FROM AdventureWorks.Production.WorkOrder as wo

              JOIN AdventureWorks.Production.Product AS p

              ON wo.ProductID = p.ProductID

              WHERE DueDate > '2004-04-30'

                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2

              ORDER BY DueDate ASC,

                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC

              FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'

' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',

    @subject = 'Work Order List',

    @body = @tableHTML,

    @body_format = 'HTML' ;

              Below Code We can use to send test email from all existing DB PROFILES

USE msdb

 GO

DECLARE  @Total int

 ,@cnt int  ,@ProfileName varchar(50)

 ,@Server varchar(50)  ,@Sub varchar(400)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)

 SET @cnt = 1

SET @Server = @@SERVERNAME

WHILE(@cnt <= @Total)

 BEGIN

 SET @ProfileName = (select name from sysmail_profile where profile_id = @cnt)

SET @Sub = 'Testing Email From ' + @Server + ' from profile : ' + @ProfileName

 IF (@ProfileName is not null)

 BEGIN

PRINT @ProfileName

 PRINT @Sub

EXEC msdb.dbo.sp_send_dbmail

   @recipients = 'Test@gmail.com'

  , @subject = @Sub

  , @body = @Sub

  , @profile_name = @ProfileName

 , @body_format = 'HTML' ;

 END

SET @cnt = @cnt + 1

 END