GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server

GRANT VIEW DEFINITION permission to all Stored Procedures in SQL Server

PermissionI have restricted all user from viewing script/text of stored procedure, views except DBA this is the part of our security policy for live environment. Generally DBA account should have permission to see the code of SP, View, Trigger etc. in live environment but I have requirement to let one person see script of all SPs. I have simply execute “GRANT VIEW DEFINITION” command if I had to let him view only few SPs but there are 100s of SPs in our database and I have to give him permission for all stored procedures. It is hectic to give permission for each SP manually and that is why I have quickly created one small TSQL script which give permission for “VIEW DEFINITION” on all stored procedures to given user.

TSQL script I am providing here is basically for “VIEW DEFINITION” permission for all “Stored Procedure” but you can twist this TSQL code quickly for your need to give any different permission to any other objects.

Let us first create One sample login and user for AdventureWorks2012 database. If you don’t have AdventureWorks2012 database, you can have it in your test or beta database.

[sourcecode language=”sql”]
USE [master]
GO

CREATE LOGIN [TestUser] WITH PASSWORD=N’testing’,
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [AdventureWorks2012]
GO

CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
[/sourcecode]

Now, Here is the TSQL script which will give permission to the “TestUser” user in Adventureworks2012 database to view all stored procedures.

[sourcecode language=”sql”]
DECLARE @tblSPList TABLE (
SPID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,
SPName SYSNAME
)

DECLARE @SPName SYSNAME,
@RowCounter INT,
@RecordCounter INT,
@UserName VARCHAR(100),
@ExecuteSQL VARCHAR(1000)

SET @UserName=’TestUser’

INSERT INTO @tblSPList (SPName)
SELECT ‘[‘+ROUTINE_SCHEMA+’].[‘+ROUTINE_NAME+’]’ FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ‘PROCEDURE’

SET @RecordCounter = (SELECT count(*) FROM @tblSPList)
SET @RowCounter = 1

WHILE (@RowCounter < @RecordCounter + 1)
BEGIN
SELECT @SPName = SPName
FROM @tblSPList
WHERE SPID = @RowCounter
SET @ExecuteSQL = N’Grant VIEW Definition on ‘ + rtrim(cast(@SPName AS VARCHAR(128))) + ‘ to [‘ + @UserName +’]’

–commenting following EXEC statement so that
–one can verify before execute
–EXEC(@ExecuteSQL)

–Print Execute Statement
PRINT @ExecuteSQL

SET @RowCounter += 1
END
GO
[/sourcecode]

You can modify this script to given different permission for different object.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automatic script backup of mail profile in SQL Server by SSIS

Automatic script backup of mail profile in SQL Server by SSIS

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and “Automatic script backup of email operator in SQL Server by SSIS and SMO”, today I come up with article which generates the script for all mail profile in SQL Server instance by SSIS.

Mail Profile is one of the important configuration for DBA because of this mail profile I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same mail profile(s) and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package to generate the script of all mail profile of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Data Flow Task” in your package and double click on that.

1DataFlowTask

Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.

[sourcecode language=”sql”]

SELECT ‘–Create Account’ AS CMD

UNION ALL

SELECT ‘EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = ”’ + m.name + ”’,

@description = ”’ + m.description + ”’,

@email_address = ”’ + m.email_address + ”’,

@replyto_address = ”’ + m.replyto_address + ”’,

@display_name = ”’ + m.display_name + ”’,

@mailserver_name = ”’ + s.servername + ”’,

@mailserver_type = ”’ + s.servertype + ”’,

@port = ”’ + cast(s.port as nvarchar) + ”’,

@username = ”’ + isnull(c.credential_identity,0) + ”’,

@password = ”x”,

@use_default_credentials = 0,

@enable_ssl = 0′ AS CMD

FROM msdb.dbo.sysmail_account m

LEFT OUTER JOIN msdb.dbo.sysmail_server s

ON m.account_id = s.account_id

LEFT OUTER JOIN master.sys.credentials c

ON s.credential_id = c.credential_id

UNION ALL

SELECT ‘–Create Profile’ AS CMD

UNION ALL

SELECT ‘

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = ”’ + name + ”’,

@description = ”’ + isnull(description,’NULL’)+ ”’

‘ AS CMD

FROM msdb.dbo.sysmail_profile

UNION ALL

SELECT ‘–Create Link For Profile to Account’ AS CMD

UNION ALL

SELECT ‘

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = ”’+ a.name +”’,

@account_name = ”’ + b.name + ”’,

@sequence_number = 1 ‘ as CMD

FROM msdb.dbo.sysmail_profile as a

JOIN msdb.dbo.sysmail_account as b on a.name = b.name

GO

[/sourcecode]

Here is the screen capture of “OLE DB Source” property.

2OLEDBSource

Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.

Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “MailProfile.sql” which I already had. You can keep blank SQL file in your destination.

3FlatFileProperty

Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “CMD” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.

4SuccessfullyRanPackage

We have many different server/instance and need script for all mail profiles from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Automatic script backup of email operator in SQL Server by SSIS and SMO

Automatic script backup of email operator in SQL Server by SSIS and SMO

DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.

I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and today I come up with article which generates the script for all email operator in SQL Server instance by SSIS and SMO.

Email Operator is one of the important configuration for DBA because of this operator I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same operator and hence I used to script it with me so that I can use the latest script in an emergency situation.

Let us now create one SSIS package which uses SMO script to generate the script of all email operator of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.

Let us now create new SSIS project and start creating package.

Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).

1Package

You have to add reference for following name spaces.

Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Sdk.Sfc

Microsoft.SqlServer.Smo

Apart from that, have following extra namespaces in “NameSpaces” region in your script window.

[sourcecode language=”C”]

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using System.IO;

using System.Collections.Specialized;

[/sourcecode]

Now here is the code you have to place in your “Main” method of script window.

[sourcecode language=”C”]

// TODO: Add your code here

StringCollection sc = new StringCollection();

ScriptingOptions so = new ScriptingOptions();

so.IncludeDatabaseContext = true;

string ServerName;
string UserName;
string Password;
string FolderDate;

ServerName = "ServerName";
UserName = "Login";
Password = "Password";
FolderDate = DateTime.Now.ToShortDateString().Replace(‘\\’,’_’).Replace(‘/’,’_’);

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = UserName;
conn.Password = Password;
conn.ServerInstance = ServerName;

Server srv = new Server(conn);
System.IO.Directory.CreateDirectory("D:\\MyServerName\\Operators\\" + FolderDate.ToString());
try
{
string script = "";
string OperatorName;

//Loop over all the jobs
foreach (Operator O in srv.JobServer.Operators)
{
//Output name in the console
Console.WriteLine(O.Name.ToString());

OperatorName = O.Name.ToString();
sc = O.Script(so);

//Get all the text for the job
foreach (string s in sc)
{
script += s;
}

//Generate the file
TextWriter tw = new StreamWriter("D:\\MyServerName\\Operators\\" + FolderDate.ToString() + "\\" + OperatorName.Replace(‘:’, ‘_’).ToString() + ".sql");
tw.Write(script);
tw.Close();

//Make the string blank again
script ="";
}
}
catch
{
//MessageBox.Show(eh.ToString());
}

Dts.TaskResult = (int)ScriptResults.Success;
[/sourcecode]

Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.

We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.

One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

UNPIVOT in SQL Server to convert column to row

UNPIVOT in SQL Server to convert column to row

Recently I got one new stored procedure to deploy in our live server. Before I deploy it to live, I have checked it and found heavy read and CPU in that SP. While investigating I found that CURSOR has been used in that SP and I can easily eliminate that CURSOR by using UNPIVOT in TSQL.

PIVOT and UNPIVOT both are wonderful enhancement in TSQL from SQL Server 2005. I really appreciate it, I have written many articles for PIVOT so far but unfortunately I haven’t written anything related to UNPIVOT in all these years so I just wanted to share this UNPIVOT feature with my blog reader so that SQL / .NET developer can use it whenever it is needed.

Pivot can easily converts your row data into column by aggregating values. Refer following links to learn more about PIVOT.

PIVOT –Fixed Column Cross Tab Query in Microsoft SQL Server 2005 (Click Here)
Dynamic PIVOT with month number to month name as header in SQL Server 2008 (Click Here)
PIVOT task in Data Flow Transformation in SSIS 2008 (Click Here)
Dynamic PIVOT with WHERE condition in SQL Server 2005 (Click Here)
Generic stored procedure for PIVOT in SQL Server (Click Here)

UNPIVOT is completely opposite to PIVOT. UNPIVOT converts your column to row. Let us understand this by an example:

Let us create one table with sample data:

[sourcecode language=”sql”]

IF OBJECT_ID(‘tempdb..#UnPivotSampleTable’) IS NOT NULL
DROP TABLE #UnPivotSampleTable

CREATE TABLE #UnPivotSampleTable
(
Customer VARCHAR(15)
,January INT
,February INT
)
GO

INSERT INTO #UnPivotSampleTable
SELECT ‘Ritesh’,1,6 UNION ALL
SELECT ‘Rajan’,NULL,8 UNION ALL
SELECT ‘Teerth’,3,NULL
GO

SELECT * FROM #UnPivotSampleTable
GO
[/sourcecode]

Here is the output of SELECT query. It shows customer name and quantity of order they have had in month of January and February.

1SELECTQuery

Now I just wanted to see which customer has given order in which month. I am not at all concern about the quantity; one of the easy ways to do this is UNPIVOT. Here is the TSQL for the same.

[sourcecode language=”sql”]

&amp;nbsp;

SELECT *

FROM

(

SELECT Customer,Orders

FROM #UnPivotSampleTable

UNPIVOT

(

VALUE

FOR Orders in (January,February)

) UnPivo

) tab

GO

[/sourcecode]

Here is the output of UNPIVOT which will show the name of month along with customer name. Ritesh has order in JAN as well as in FEB so there will be two rows for him and rest of the customer will have one row in result set.

2Unpivot

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

List of Table in Filegroup with table size in SQL Server

List of Table in Filegroup with table size in SQL Server

Size

Sometime back I have provided the script to calculate the size of each schema and table in SQL Server and move index efficiently to different filegroup in different drive but today I come up with little different script. I had requirement to know list of table under each file group along with size of that table and name of clustered index if any.

 

I have used sys.Allocation_Units, sys.Partitions and sys.indexes system table to find the detail I wanted to know.

 

As per MSDN:

 

“sys.Partitions” contains a row for each partition of all the tables and most types of indexes in the database. Special index types like Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.

 

“sys.Allocation_Units” contains a row for each allocation unit in database.

 

“sys.indexes” contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.

 

So, if we have to get list of filegroup and table name with size, we can utilize sys.partitions and sys.allocation_units. We have had JOIN with sys.indexes as we wanted to know name of clustered index on the table, if available.

 

Here is the small script I am using to cater my need:

 

[sourcecode language=”sql”]

SELECT

FILEGROUP_NAME(AU.data_space_id) AS FileGroupName,

OBJECT_NAME(Parti.object_id) AS TableName,

ind.name AS ClusteredIndexName,

AU.total_pages/128 AS TotalTableSizeInMB,

AU.used_pages/128 AS UsedSizeInMB,

AU.data_pages/128 AS DataSizeInMB

FROM sys.allocation_units AS AU

INNER JOIN sys.partitions AS Parti ON AU.container_id = CASE WHEN AU.type in(1,3) THEN Parti.hobt_id ELSE Parti.partition_id END

LEFT JOIN sys.indexes AS ind ON ind.object_id = Parti.object_id AND ind.index_id = Parti.index_id

ORDER BY TotalTableSizeInMB DESC

[/sourcecode]

 

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Move clustered index to different filegroup in SQL Server

Move clustered index to different filegroup in SQL Server

Disk IO system always play vital role in performance of SQL Server. No matter how powerful processor you have and how much RAM you have, if your disk doesn’t support transaction as per your need, you won’t get benefit of processor and RAM.

Recently, we have attached very fast SAN to our production server to replace RAID-5 array which had four hard disk of 10,000RPM. I wanted to transfer all clustered index to our new SAN system for few of our highly transactional table in the first phase.

I want to share how we can efficiently move index to a different filegroup in different drive.

Let us create one sample database for demonstration and create one table with sample data and index in newly created database.

[sourcecode language=”sql”]

CREATE DATABASE ExtremeAdvice
GO

USE ExtremeAdvice
GO

IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
 DROP TABLE orders
END
GO

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

–inserting 100000 fack rows into table
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
 DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
 ABS(a.object_id % 10),
 CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

–creating clustered index
CREATE CLUSTERED INDEX idx_Orders_refno ON orders(refno)
GO
[/sourcecode]

When you create database, by default there would be one default filegroup which is PRIMARY and default data file comes under PRIMARY filegroup.

Let us see where our index is located.

[sourcecode language=”sql”]
SELECT
obj.name AS TableName
, obj.type
, ind.name AS IndexName
, ind.index_id AS Index_ID
, files.name AS FileGroupName
FROM sys.indexes ind
INNER JOIN sys.filegroups files
ON ind.data_space_id = files.data_space_id
INNER JOIN sys.all_objects obj
ON ind.object_id = obj.object_id
WHERE ind.data_space_id = files.data_space_id
AND obj.type = ‘U’ — User Created Tables
GO
[/sourcecode]

Here is the screen capture for my database:

1FileGroup

Now, we shall create one more filegroup for our database and shall create one new datafile (.ndf) in new filegroup.

[sourcecode language=”sql”]
–add new filegroup
USE [master]
GO

ALTER DATABASE [ExtremeAdvice] ADD FILEGROUP [ExtremeAdviceIndex]
GO

–add new secondary data file to filegroup
USE [master]
GO

ALTER DATABASE [ExtremeAdvice]
ADD FILE (
NAME = N’ExtremeAdvice_Index’,
FILENAME = N’D:\DATA\ExtremeAdvice_Index.ndf’ ,
SIZE = 6144KB ,
FILEGROWTH = 1024KB )

TO FILEGROUP [ExtremeAdviceIndex]
GO
[/sourcecode]

Once you are done with new filegroup and data file. We shall CREATE INDEX with DROP_EXISTING = ON and we shall assign new filegroup while re-creating an index. Once you transfer clustered to different filegroup, all data will be transferred to new location with clustered index.

[sourcecode language=”sql”]
USE [ExtremeAdvice]
GO

CREATE CLUSTERED INDEX [idx_Orders_refno] ON [Orders]
(
[refno] ASC
)
WITH (
PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON,
ONLINE = ON,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON
) ON [ExtremeAdviceIndex]
GO
[/sourcecode]

Once, you are done with creating an index, you can confirm whether your index is moved to new location or not with following query.

[sourcecode language=”sql”]
SELECT
obj.name AS TableName
, obj.type
, ind.name AS IndexName
, ind.index_id AS Index_ID
, files.name AS FileGroupName
FROM sys.indexes ind
INNER JOIN sys.filegroups files
ON ind.data_space_id = files.data_space_id
INNER JOIN sys.all_objects obj
ON ind.object_id = obj.object_id
WHERE ind.data_space_id = files.data_space_id
AND obj.type = ‘U’ — User Created Tables
GO
[/sourcecode]

Here is the screen capture for my database.

2Filegroup

 

You can see that “idx_Orders_Refno” is  on “ExtremeAdviceIndex” FileGroup.

I have written some more articles about Index, if you want to refer, follow the links given:

  • Some basics about Index in SQL Server (Click Here)
  • Clustered Index in SQL Server (Click Here)
  • Nonclustered Index in SQL Server (Click Here)
  • Included Column Index with non clustered index in SQL Server 2005/2008/Denali (Click Here)
  • Filtered Index in SQL Server 2008/Denali (Click Here)
  • Cannot create index on view Msg 1940, Level 16, State 1, Line 1 (Click Here)
  • Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005 (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • Do you know Index Statistics in SQL Server (Click Here)
  • Index Rebuild or Reorganize in SQL Server (Click Here)
  • Index Fillfactor in SQL Server (Click Here)
  • Find missing Index with DMVs in SQL Server 2005/2008/Denali (Click Here)
  • Find unused index in SQL Server 2005/2008/Denali (Click Here)
  • sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Script all SQL Server Job automatically by SSIS and SMO

Script all SQL Server Job automatically by SSIS and SMO

As per MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

I have one of my production server in SQL Server 2012 and I wanted to take backup of SQL Server Job script automatically once in a day.

We may have replication / mirroring / log shipping for database but there are lot more things out side the database too. In time of failover the server, I need all the job in other server too and that is the reason I planned to backup all SQL Server Agent jobs automatically.

Let us see, how it goes!!!

Create one SSIS project and take one “Script Task”. Double click on “Script Task” to open its editor. From the editor, click on “Edit Script” button.

1ScriptTaskEditor

Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:

[sourcecode language=”c”]
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;
[/sourcecode]

Here is the code I have written in “Main()” method of script editor.

[sourcecode language=”c”]
// TODO: Add your code here
StringCollection strCol = new StringCollection();
ScriptingOptions scriptOpt = new ScriptingOptions();
scriptOpt.IncludeDatabaseContext = true;

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "sa";
conn.Password = "sa";
conn.ServerInstance = "serverName";
Server srv = new Server(conn);

try
{
string script = "";
string JobName;
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
{
script ="";
JobName = J.Name.ToString();
strCol = J.Script(scriptOpt);

//concate the text of job
foreach (string s in strCol)
{
script += s;
}
//save the job file
TextWriter tw = new StreamWriter("D:\\RiteshShah\\ServerName\\JobBackup\\" + JobName.Replace(‘:’, ‘_’).ToString() + ".sql");
tw.Write(script);
tw.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
[/sourcecode]

Here is the screen capture of my script editor.

2ScriptEditor

Now, save the script, close the script editor, click “ok” button for “Script Task” property and run your package. If the path for the script you have provided, is exists and the credential is proper, you will be able generate script of all SQL Server Agent Job.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Run SP_WhoIsActive in SSIS package to collect data from different SQL Server Instances

Sometime back I have already written about world famous stored procedure “SP_WhoIsActive” created by SQL Server GURU Mr. Adam Machanic (Blog). I used to have this SP in all of my important production server to keep an eye on the query performance. This SP returns huge amount of data based on the workload on the SQL Server so I always wish to maintain separate database in separate server for DBA related activity to keep production database free from DBA related activity as much as possible.

Note:

I have provided link of new version of “SP_WhoIsActive” in the first paragraph of this article but I am still using little older SP as I have already set it up in many of my servers.

I am not able to find that old SP from the blog of Mr. Adam Machanic and hence I am providing it here for the reference of my blog reader. Please download it from here as I have demonstrated this article based on the SP I am using.

I have created one SSIS package which is hosted on my DBA server and executes “SP_WhoIsActive” in each instance of production server in every few seconds or minutes, collects the data from “ SP_WhoIsActive” and stores those data in DBA database. My DBA database is named “DBADB”.

I assume that you already have “SP_WhoIsActive” in one of your production database in one SQL Server Instance and creating package for that. I also assume that you have “DBADB” database which collects and stores data.

Let us create package then:

Create one SSIS project and take “Execute SQL Task” in package.

Have some variables declaration for your package.

1VariableInSSISPackage

Se the connection of “Execute SQL Task” with database of your production server where you have created “SP_WhoIsActive” stored procedure. Once connection is made, have the following TSQL in “Execute SQL Task”.

[sourcecode language=”sql”]
IF OBJECT_ID(‘tempdb..#ActiveProcesses’) IS NOT NULL
DROP TABLE #ActiveProcesses

CREATE TABLE #ActiveProcesses(
[dd hh:mm:ss.mss] [varchar](15) NULL,
[dd hh:mm:ss.mss (avg)] [varchar](15) NULL,
[session_id] [smallint] NOT NULL,
[sql_text] [xml] NULL,
[sql_command] [xml] NULL,
[login_name] [nvarchar](128) NOT NULL,
[wait_info] [nvarchar](4000) NULL,
[CPU] [varchar](30) NULL,
[tempdb_allocations] [varchar](30) NULL,
[tempdb_current] [varchar](30) NULL,
[blocking_session_id] [smallint] NULL,
[blocked_session_count] [varchar](30) NULL,
[reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[physical_reads] [varchar](30) NULL,
[query_plan] [xml] NULL,
[locks] [xml] NULL,
[used_memory] [varchar](30) NULL,
[status] [varchar](30) NOT NULL,
[open_tran_count] [varchar](30) NULL,
[percent_complete] [varchar](30) NULL,
[host_name] [nvarchar](128) NULL,
[database_name] [nvarchar](128) NULL,
[program_name] [nvarchar](128) NULL,
[additional_info] [xml] NULL,
[start_time] [datetime] NOT NULL,
[request_id] [int] NULL,
[collection_time] [datetime] NOT NULL,
[seq] [int] IDENTITY(1,1) NOT NULL
)

EXEC sp_WhoIsActive @destination_table=’#ActiveProcesses’

SELECT
[dd hh:mm:ss.mss],
[dd hh:mm:ss.mss (avg)],
[session_id],
CONVERT(nvarchar(max),[sql_text]) AS [sql_text],
CONVERT(nvarchar(max),[sql_command]) AS [sql_command],
[login_name],
[wait_info],
[CPU],
[tempdb_allocations],
[tempdb_current],
[blocking_session_id],
[blocked_session_count],
[reads],
[writes],
[physical_reads],
CONVERT(nvarchar(max),[query_plan]) AS [query_plan],
CONVERT(nvarchar(max),[locks]) AS [locks],
[used_memory],
[status],
[open_tran_count],
[percent_complete],
[host_name],
[database_name],
[program_name],
CONVERT(nvarchar(max),[additional_info]) AS [additional_info],
[start_time],
[request_id],
[collection_time],
[seq]
FROM #ActiveProcesses
[/sourcecode]

Here you can find the screen capture of

2ExecuteSQLTaskGeneralProperty

Go to result set property of “Execute SQL Task” and take the output of SELECT query into one dataset variable we have created.

3Dataset

Now, take one Foreach loop task and iterate it for “ADO” with the dataset we have created.

4ForEachADOIterate

Now we have to map the variable to get the value from dataset in Foreach Loop task.

5VariableMapping

Have one more “Execute SQL Task”, inside the “foreach loop taks”, which insert row from dataset to “DBADB” database. Here is the TSQL which we used to use in “Execute SQL Task”.

[sourcecode language=”sql”]
insert into dba.ActiveProcesses
(
[dd hh:mm:ss.mss],
[dd hh:mm:ss.mss (avg)],
[session_id],
[sql_text],
[sql_command],
[login_name],
[wait_info],
[CPU],
[tempdb_allocations],
[tempdb_current],
[blocking_session_id],
[blocked_session_count],
[reads],
[writes],
[physical_reads],
[query_plan],
[locks],
[used_memory],
[status],
[open_tran_count],
[percent_complete],
[host_name],
[database_name],
[program_name],
[additional_info],
[start_time],
[request_id],
[collection_time]
)
select ?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?,?,?,
?
[/sourcecode]

6ExecuteSQLforDBADB

Once you set SQLStatement in “Execute SQL Task”, go to “Parameter Mapping” and set variables there.

7ParameterMappingForDBADB

Now, you are absolutely ready to schedule your SSIS package in JOB or in Schedule task which get executed at every few seconds and collect the data for you in DBADB database which you can analyze at your convenience.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep eye on the health of your SQL Server database

HealthKeep eye on the health of your SQL Server database

SP_WhoIsActive” stored procedure is one of my favorite SP which I used to create in my almost all production database from the day one since last few years. A very BIG THANKS goes to Mr. Adam Machanic (Blog) who is very well-known celebrity in the field of SQL Server since long. He is one of my favorite author and I used to follow his blog very closely.

Today I have created “SP_WhoIsActive” in one more database and thought to salute original creator of this wonderful SP.

I can not keep watch on all my databases on 24*7 basis but “SP_WhoIsActive” do it for me. Whenever I get any complain about slow running query/SP I ask for the approx date and time. When I have SP name, Date & Time, I used to query the table which is being populated by “SP_WhoIsActive” for that particular SP along with approx date & time so that I can get following crucial information about SP which it has faced during that time.

–>Session_ID
–>TSQL of the SP
–> Parameter of SP for which it ran
–> Login_Name
–> Wait_Info if there was any
–> CPU
–> TempDB allocation if it had
–>blocking session_id and count if there was any|
–>reads
–>writes
–>physical_reads
–>execution plan of SP
–>database name
–>execution start time and many more important information

Once I have all these information, I can start my debugging. Without having all these information I feel helpless as I can’t watch all databases personally in each instance.

Any DBA can understand the importance of all these information which “SP_WhoIsActive” provides. This is the reason “SP_WhoIsActive” is my one of the favorite SP which I used to have in each of my SQL Server instances.

Once again, I want to give all the credit to Mr. Adam Machanic. Hats off to you Adam as you have spent hundreds of hours for “SP_WhoIsActive” to help SQL Server community.

I have provided link of new version of “SP_WhoIsActive” in the first paragraph of this article but I am still using little older SP as I have already set it up in many of my server.

I am not able to find that old SP from the blog of Mr. Adam Machanic and hence I am providing it here for the reference of my blog reader. Please download it from here.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Mirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

noWaitMirroring SYNCHRONIZING status and DBMIRROR_DBM_EVENT wait type

Few days back one of the DBA in our company setup mirroring for one of our production database hosted in SQL Server 2008 R2. After setting mirroring up, our peak business hour started and we have started facing heavy blocking issues. Thanks to customized monitoring application we have developed which shows me that mirroring was in “Synchronizing” status not “Synchronized” and there were so many wait type “DBMIRROR_DBM_EVENT” which was blocking many sessions. Apart from that I have found that mirroring was having “High Safety” mode.

In highly transactional database, I prefer to have asynchronous mirroring “High Performance”. As soon as I changed the mirror safety mode and kill some heavy blocking process everything was up and running fine.

As per MSDN, High-performance operates asynchronously. Asynchronous operation supports only one operating mode—high-performance mode. This mode enhances performance at the expense of high availability. High-performance mode uses just the principal server and the mirror server. Problems on the mirror server never impact the principal server. On the loss of the principal server, the mirror database is marked DISCONNECTED but is available as a warm standby.

So, High-performance comes at the cost. If it would be “High Safety” it wouldn’t release until data successfully reached to mirror but it take lot of resources so I used to keep “High Performance” for one of our highly transaction database only.

BTW, for checking mirroring safety mode and status, I have already written one script earlier in my blog post which is give here again:

[sourcecode language=”sql”]
SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
mirroring_state_desc,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_role_desc,
mirroring_partner_instance AS MirrorServer
FROM sys.database_mirroring
GO
[/sourcecode]

For wait_type, you can execute following DMV, which I have already explained in my blog post “CXPACKET wait stats in SQL Server”.

[sourcecode language=”sql”]
select * from sys.dm_os_wait_stats where wait_type=’DBMIRROR_DBM_EVENT’
GO
[/sourcecode]

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.