Create a Cursor with providing user_name(username)as the input parameter)

DECLARE @name_holder VARCHAR(100)
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','%snap%') 
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO @name_holder
EXEC sp_addrolemember 'db_datareader', 'user_name'
FETCH NEXT FROM UserCursor INTO @name_holder
CLOSE UserCursor

There are hard times, when we need to script all the SQL Server Jobs which are running in one server and when we need to move it to some other server. Its been a very challenging task for a DBA that to deploy same set of jobs in multiple servers. Here, is a way to script all the SQL Jobs from SQL Server Management studio and port it to some other servers in one shot.

So, How to do this?

Step 1: Login to the SQL Server instance through SQL Server Management Studio.
Step 2: Expand SQL Server Agent >> Jobs
Step 3: Hit F7 (or) Click on menu ->View >> Summary. This will list out all the options under SQL Server Agent. Click on Jobs on the Summary Page view.
Step 4: Now ALL jobs would be listed out there.
Step 5: Press Control-‘A’ key and choose all the Jobs which needs to be scripted (OR) Hold Control Key to select specific/Set of Jobs which you want to script out.
Step 6: Right click >> “Script Job as” >> “Create To” >> File (provide the file name and save it on the desired script)

Please refer to the screenshots for reference:

Image 1:



Image 2:All_Jobs_scripts_2


What is a Stretch Database?

It is a brand new feature available with SQL Server 2016 which allows to extend on-premise(local) SQL Server databases to Microsoft Azure(Cloud based database). In other words, Microsoft cloud (Azure) can be used as an additional storage for your infrastructure. It allows us to archive historical data (on our local in-premises database) transparently and securely on SQL Azure. After enabling Stretch Database, it silently migrates your historical data to an Azure SQL Database.

  • We don’t have to change existing queries and client apps.  We can continue to have seamless access to both local and remote data.
  • Our local queries and database operations against current data typically run faster.
  • We can enjoy reduced cost and complexity.

Note: Our archival data would be on the Microsoft Azure (public cloud)

Stretch Database is for whom?

Stretch Database may help to meet your requirements and solve your problems as follows:
If you have to:

  • keep transactional data for a long time.
  • Rarely, query the historical data.
  • Applications, including outdated Applications, which cannot be updated to new versions/don’t want to update.
  • save on storage expenditures.

For DBAs?

  • The size of some tables is getting out of control.
  • Need to access historical data, but they only rarely use it.
  • Abnormal/unplanned storage growth which cannot be predicted.
  • Backup SLA policy breach due to large Database file size.

Stretch Database  are very useful for transactional databases with large amounts of historical data, typically stored in a  individual tables. These tables may contain more than a billion rows.

SQL Server 2016 provides a very good noted performance for mission critical applications/databases and deeper insights on data across on-premises(Local Databases) and cloud Databases(Azure).

Some Key/major Improvements, Options, Capabilities available with SQL Server 2016 are:

  • Always Encrypted
  • Stretch Database
  • Real-time Operational Analytics & In-Memory OLTP

Add On capabilities available with SQL Server 2016:

  • PolyBase – More easily manage relational and non-relational data with the simplicity of T-SQL.
  • AlwaysOn Enhancements – Achieve even higher availability and performance of your secondaries, with up to 3 synchronous replicas, DTC support and round-robin load balancing of the secondaries.
  • Row Level Security– Enables customers to control access to data based on the characteristics of the user. Security is implemented inside the database, requiring no modifications to the application.
  • Dynamic Data Masking – Supports real-time obfuscation of data so data requester do not get access to unauthorized data.  Helps protect sensitive data even when it is not encrypted.
  • Native JSON support – It allows easy parsing and storing of JSON and exporting relational data to JSON.
  • Temporal Database support – Tracks historical data changes with temporal database support.
  • Query Data Store – Acts as a flight data recorder for a database, giving full history of query execution so DBAs can pinpoint expensive/regressed queries and tune query performance.
  • MDS enhancements – Offer enhanced server management capabilities for Master Data Services.
  • Enhanced hybrid backup to Azure – Enables faster backups to Microsoft Azure and faster restores to SQL Server in Azure Virtual Machines.  Also, you can stage backups on-premises prior to uploading to Azure.

Resource Governor:

As the name implies, Resource governor is a very useful and powerful feature which is used to allocate and control available resources on the Server of your current workload and server capacity.
Beware, before you implement this feature in your environment. It needs lots of baselines, analysis.
Most of us, might have/definitely lead to a situation where there are either resource unavailability for the current work load on our SQL DB boxes.
From SQL Server 2008 onwards, new feature has been introduced named as RESOURCE GOVERNOR.
Using, this feature as we DBAs control /allow/manage available resources for a spefic time/type of workload.
For Ex:
Normally, we cannot instruct our SQL Server to use only specific amount of memory/CPU for a particular task right. So what is the solution to make our SQL Server to allow us to customize available resources based on our workload at various points?
When our CEO, runs a sales report which almost tweaks to 100% of CPU/Memory some time due to some other junk queries ran by a developer at a back end and report was not generated. It would cause lots and lots of issues for business right.
Using Resource Governor, You can specify SQL Server to use specific amount  of CPU/Memory. When there is a request from CEO User id which allocates 75% of resources to CEO request and restrict available resources to 25% for all other requests comes to this SQL Server which in turn process CEOs request on high Priority.


1. Resource Governor is only to control/manage resources efficiently for DB Engine and it cannot be used to manage any other SQL services.

2. It is not advisable to use Resource Governor if you have more than one SQL instance installed on that machine. Since it will allocate resources based on the availability of the resources to that particular SQL instance.

For Ex: In a Server, physical memory RAM is 10GB and we have two SQL instances named as INST1, INST2 installed on it. Each instance have been allocated 4 GB of RAM each. So if you allocate Memory 50% of memory for a workload, through Resource Governor for INST 1. Then allocated memory would be 2GB. Since it will take the max available memory from the instance level.

50% memory allocated by Resource Governor  =  50% memory / Max available memory

50% of 4 GB Memory = 2 GB

So, We need to be very careful. When using this resource governor in our environment.

Deadlock Query

Posted: June 26, 2014 in General

SELECT  L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction, as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
–WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

High Level Differences between SQL High availability solutions


Description Log-Shipping Replication Database Mirroring
Latency >1min(Based on the transactional log backup size) Potentially as low as a few seconds Potentially < 1 min
Causes schema alterations to be made at the publisher No Plain Transactional – no; Updating subscribers – yes No
Causes schema alterations to be made at the subscriber No Possibly (see text) No
Requires schema properties No Primary keys are  required for transactional table articles(Only tables which have primary keys can be used for replication) No
Selection of individual objects possible(For ex. Tables, stored procedures, views) No Yes No
Subscriber database “protected”*Subscriber = DR database Yes No Yes
System data transferred Mostly No Yes
Can the subscriber server be used as reporting server? Can be used Yes *PossiblyIt can be achieved through creating a snapshot of the DR database
Supports automatic failover No No Yes
Ease of implementation Simple Moderate Moderate

Query to check the existence of the user on a current login domain.


EXEC xp_cmdshell ‘net user /domain username’

For 2000:

ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sysdatabases d ON d.dbid = mf.dbid
WHERE d.dbid > 4 — Skip system databases

For 2005 & above:

ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sys.databases d ON d.database_id= mf.database_id
WHERE d.database_id > 4 — Skip system databases

SQL Server stores history of all the database backup history forever. History of all the backup is stored in msdb database system tables. Following Stored Procedure can be executed with parameter which takes days of history to keep.

USE msdb;
EXEC sp_delete_backuphistory @oldest_date = ’01/01/2014′;