Script to change collation of multiple columns in multiple table for all tables in database

Various time we come across situation where in lower environment the collation will be different and in production environment the collation will be different. This might hamper you work in many ways. Your development work may come to halt or slowdown making these changes.

So here is a small script to change collation of various columns for all table. If you want for some columns and some table the select statement in cursor needs to tweaked.

Declare 
@TableName varchar(300),
@ColumnName varchar(300),
@SQLText varchar(max),
@CharacterMaxLen varchar(max),
@CollationName varchar(max),
@IsNullable varchar(max),
@DataType varchar(max)

SET @CollationName = 'RequiredCollation'

Declare MyTableCursor cursor for
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR 
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' 
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position 
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + 
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + 
              ') COLLATE ' + @CollationName + ' ' + 
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
            PRINT @SQLText 

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor
--Print @SQLText
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Types of Backups and the background process of database Backup methodology.

Types of Backups and the background process of database Backup methodology.

• Full Backup: It’s just full backup of database, it backs up all data and objects that reside in the database but it will not backup objects like jobs and SQL Server logins which don’t reside in database. With the versions prior to the SQL Server 2005, if you execute a full database backup, you could not make any other backups (such as transactional log backup), until the full database backup is completed. But SQL Server 2005 allows you to do two concurrent backups at the same time. If you exceed two the third backup will fail. It’s advised that not to use more than one backup at a time as it will use high disk I/O. Full backup dose not truncate or backup transactional log. Full backup process:
1. When backup command is executed, SQL server locks the database, blocking all transactions and check points is issued on the database, which writes all deity pages to disk. This ensures that all committed transactions up to the point of backup command will be part of the full backup.
2. SQL Server makes a mark in the transactional log to point where the full back begins. This is important in the recovery process; SQL Server will use this mark to help validate what combination of files can or cannot be used to restore the database from the full backup, releases lock on the database.
3. The data is then read out of the database files to the backup files, which is plain text file that is create on disk or tape.
4. Lock the database, blocking all transactions, and issues checkpoint.
5. When full backup is completed SQL Server makes another mark in the transaction log.
6. Release database lock.
7. Extracts all transactions between the two log marks and append to the backup, by maintaining the consistency of backup for e.g., if someone modifies the data on the pages while backup process, and if he restores the backup he might lose the modified data.
• Differential database backup: A differential database backup contains only the changes made to the database since the last full backup. The main purpose of differential backup is to reduce the number of transactional log backups that needed to be restored at the time of recovery. A differential database backup is possible because SQL Server keeps track of the extents that have changed, in an internal bitmap contained within the data file. When the full backup is made bitmap is reset. One bit is used per extent, which is eight physical contiguous 8kb pages (i.e., extent is 8X8 = 64kb), so single 8kb page can map to about 4GB of data. SQL Server creates one of these mapping pages for every 8,192 extents.
• Transactional log backup: it is a backup of your current transactional log; you can take transactional log back up only if you have taken full backup of the database at least once in its life time. Log backup starts at the log sequence (LSN) number at which previous log backup completed or if it is 1st log backup, it will start at which the full backup was completed. SQL Server then backs up all the subsequent transactions until the backup encounters an open transaction. Once SQL Server encounters the open transaction the log backup completes. Any LSN’s that are backed up will be removed from the transactional log, which enables system to reuse the log space. From recovery standpoint no backup is critical than transactional log backup because it allows you to recover more granular point in time. To apply a transactional log, your database must be restored WITH NORECOVERY or WITH STANDBY. WITH NORECOVERY puts your database in pure loading state where it cannot be used for client connection, but WITH STANDBY puts your database in loading state where clients can access your database in read-only mode, but when a transaction is loaded, the database needs exclusive access to load the log, so it would be unavailable for clients use.
• File and File group backup: File and file group backups are based on the full and differential backups, if your database architecture uses multiple files and file groups, although you can do a standard full or differential backup, you now have the option of doing backups on files or file groups which will make recovery much easier. Although you can do file or file group backup from full or differential backup, the advantage of file or file group backup is when we are doing backup of multi terabyte databases.
• Partial backup: It possible that some of the file groups in SQL Server database as read only and some of them both (read/write). In previous versions of SQL Server, a full backup captures all extents even if the file group is marker as read only, which meant that there were no changes to the data. SQL Server 2005 introduces a new parameter to the backup command to handle this situation. The READ_WRITE_FILEGROUPS clause causes the backup engine to skip any file groups that are marked as read only, saving time and space in the backup by having the backup engine gather only the set of extents that could change. E.g., BACKUP DATABASE PUBS READ_WRITE_FILEGROUPS TO DISK='C:\DEMO\BACKUP\PUBS1.BAK'
• Mirror Backup: this is a new feature from SQL Server 2005 onwards, it is the ability to mirror your backup media.
• Media Retention: It an instance level option where we can set how long SQL Server can retain backup set. Retaining means within SQL Server you cannot delete it (but it will not prevent doing anything physically on your media) this option is media retention and is considered an advanced configuration option within SQL Server. It is set in days, and the values can be from 0 (the default) to 365.

TSQL Script to create Default Database Email

Recently we did a installation of some bulk SQL Server for various purpose. Now we needed database Email to be configured on each server and there had to be some standard. I worked out this my Making a generic script which can be used and run on any machine and which maintain symmetry

USE master
Go

sp_configure 'show advanced options', 1
GO

RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

USE msdb
GO

DECLARE
    @profile    VARCHAR(100),
    @display    VARCHAR(100),
    @emailaddr    VARCHAR(100),
    @accountname    Varchar(100)

SELECT
    @profile    = 'MyProfileName_DB_Mail_Profile_' + @@ServerName,
    @display    = 'MyProfileName_DB_Mail_Account_' + @@ServerName,
    @emailaddr    = 'MyRequiredEmailAddress' + @@servername + '@myDomain.com',
    @accountname    = 'MyAlertName_Alert_Account';

-- Create Database Mail account
EXECUTE dbo.sysmail_add_account_sp 
        @account_name = @accountname,
        @description = 'MyDatabaseAccountName Alert Database Account', 
        @email_address = @emailaddr,
        @display_name = @display,
        @mailserver_name = 'MyMailserver.com';        

-- Create a Database Mail profile
EXECUTE dbo.sysmail_add_profile_sp
        @profile_name = @profile,
        @description = 'MyDatabaseEmailProfileName Alert Database Profile';

-- Add multiple Database Mail accounts to Mail profile
EXECUTE dbo.sysmail_add_profileaccount_sp
        @profile_name = @profile,
        @account_name = @accountname,
        @sequence_number = 1;

-- Grant the mail profile access to msdb public role & make it a default
EXECUTE dbo.sysmail_add_principalprofile_sp  
        @profile_name = @profile,
        @principal_name = 'public',
        @is_default = 1;

EXEC master.dbo.xp_instance_regwrite 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
        N'DatabaseMailProfile', 
        REG_SZ, 
        @profile;

GO

Stored Procedure to Get Expensive Queries

As a DBA we often need to get information on various Queries, One of them is how expensive it is. A query can be judged as expensive on various criteria’s like Long Running, Memory Utilization, CPU utilization etc.
I came up with this Stored Procedure which gives you Expensive Queries based on criteria’s like Duration, Memory, CPU, Read etc.

USE [master]    
GO     
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[usp_GetExpensiveQueries]‘) AND type in (N’P', N’PC’))     
DROP PROCEDURE [dbo].[usp_GetExpensiveQueries]     
GO     
USE [master]     
GO     
SET ANSI_NULLS ON     
GO     
SET QUOTED_IDENTIFIER ON     
GO     
CREATE PROCEDURE [dbo].[usp_GetExpensiveQueries]     
@Limit AS INT,     
@Database_Name AS VARCHAR(255),     
@Expense_Counter INT     
AS     
/*Variables Used     
1. @Limit — No of Recoreds to be Retrieved in Int     
2. @Database_Name — Database Name for which the Expense queries needs to be Retrieved in Varchar     
3. @Expense_Counter — Criteria on Which the Query Expense needs to judged     
Refer Below for Expense Counter     
End Variables Used*/     
/*Expense Counter     
DurTimeAvgMin 0     
CPUTimeAvgMin 1     
TotalCPUTime 2     
TotalDurTime 3     
NoPhysicalReads 4     
AvgNoPhyscialReads 5     
NoLogicalReads 6     
AvgNoLogicalReads 7     
*/     
SELECT Database_name,QueryText,ProcBatTest,PlanGenerationNumber,ExecutionCount,DurTimeAvgMin,     
CPUTimeAvgMin,TotalCPUTime,TotalDurTime,NoPhysicalReads,AvgNoPhyscialReads,NoLogicalReads,AvgNoLogicalReads     
FROM     
(     
SELECT TOP (@Limit) DB_NAME(CONVERT (INT, epa.value)) AS [Database_Name],     
SUBSTRING(est.text, (eqs.statement_start_offset/2)+1,     
        ((CASE eqs.statement_end_offset     
         WHEN -1 THEN DATALENGTH(est.text)     
         ELSE eqs.statement_end_offset     
         END – eqs.statement_start_offset)/2) + 1) AS QueryText,     
est.text AS ProcBatTest,         
eqs.plan_generation_num AS PlanGenerationNumber,     
eqs.execution_count AS ExecutionCount,     
(eqs.total_worker_time/1000) AS TotalCPUTime,     
(((eqs.total_worker_time/1000)/eqs.execution_count)/3600) AS CPUTimeAvgMin,     
(eqs.total_elapsed_time/1000) AS TotalDurTime,     
(((eqs.total_elapsed_time/1000)/eqs.execution_count)/3600) AS DurTimeAvgMin,     
eqs.total_physical_reads AS NoPhysicalReads,     
(eqs.total_physical_reads/eqs.execution_count) AS AvgNoPhyscialReads,     
eqs.total_logical_reads AS NoLogicalReads,     
(eqs.total_logical_reads/eqs.execution_count) AS AvgNoLogicalReads,     
eqs.last_execution_time AS LastExecutionTime     
FROM SYS.DM_EXEC_QUERY_STATS eqs     
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sql_handle) est     
CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(plan_handle) eqp     
CROSS APPLY SYS.DM_EXEC_PLAN_ATTRIBUTES(eqs.plan_handle) epa     
WHERE attribute = ‘dbid’  
AND DB_NAME(CONVERT (INT, epa.value)) = @Database_Name) x     
–and qs.last_execution_time > ’2011-08-09 17:29:33.750′     
–If we want to get queries executed greater than some time     
–and (((qs.total_elapsed_time/1000)/qs.execution_count)/3600) >= 2     
ORDER BY     
–Seems to be Problem with Order By working on the same     
–Order By Fixed     
CASE     
WHEN @Expense_Counter = 0 THEN DurTimeAvgMin     
WHEN @Expense_Counter = 1 THEN CPUTimeAvgMin     
WHEN @Expense_Counter = 2 THEN TotalCPUTime     
WHEN @Expense_Counter = 3 THEN TotalDurTime     
WHEN @Expense_Counter = 4 THEN NoPhysicalReads     
WHEN @Expense_Counter = 5 THEN AvgNoPhyscialReads     
WHEN @Expense_Counter = 6 THEN NoLogicalReads     
WHEN @Expense_Counter = 7 THEN AvgNoLogicalReads     
END DESC     
GO