Synchronous Database Mirroring Misconceptions

This article is about the Synchronous Database Mirroring misconceptions. Synchronous is one of the operating modes of SQL Database Mirroring also known as High Safety mode; High Safety of transactions in case of failover.

Let’s see what Microsoft TechNet says:

When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.
After synchronization finishes, every transaction committed on the principal database is also committed on the mirror server, guaranteeing protection of the data. This is achieved by waiting to commit a transaction on the principal database, until the principal server receives a message from the mirror server stating that it has hardened the transaction’s log to disk. Note the wait for this message increases the latency of the transaction.

The common misconceptions/confusions are as follows:
  1. Principal server first commits the transaction and then sends log records to the mirror and wait until mirror server writes the same to the database.
  2. Principal sends its log to the mirror and once it commits in mirror then only it commits in principal.

If you know what happens during commit, it is a bit easier. Commit does not mean the transaction is written to the database (data file). When you commit a transaction SQL Server writes it to the log file on the disk, in other words it hardens the log. This confirms the durability of the transaction.

Let’s take an example of the following transaction:

Begin Tran

Statement 1

Statement 2

Commit;

SQL Server writes the log records in the log file of principal database as per normal process of logging. Mirror server follow the same.

When the application fires a commit statement, Synchronous Database Mirroring mechanism has to confirm, mirror server has written all the log records related to the transaction before sending confirmation to the application.

So, from above example transaction, It is possible that SQL Server has already written the Begin Tran and Statement 1  to the log in principal and mirror server.

The following diagram depicts the same:

Synchronous Database Mirroring

Now lets see what happens when application fires a Commit Tran:
    1. Principal server receives the commit transaction from client.
    2. The principal server writes the log records for the transaction to the log file and sends the log records to the mirror server at the same time as shown below.Synchronous Database MirrorringNote: Principal Server has written the log records to the log file. So one of the misconception is cleared here. Principal Server does not wait for acknowledgement from mirror to commit the transaction.
    3. Principal server completes the I/O  but it cannot send the confirmation to the client at this point.
    4. The mirror server hardens the log in disk.
    5. I/O on the mirror server completes.
    6. Returns an acknowledgement to the principal server.
    7. On receiving the acknowledgement from the mirror server, the principal server sends a confirmation/Acknowledgement to the client.

The following diagram shows the complete cycle:

Synchronous Database Mirroring

Note: The misconception, Principal Server waits until Mirror Server writes the transactions to the mirror database is not true. Synchronous database mirroring confirms that the Mirror writes the log records  to the log file and not data file, before sending acknowledgement, hence even if the principal crashes after that, all the log records of the committed transactions are durable and mirror can take over the role of principal. And that is why it is also know as High Safety Mode.

The mirror server continuously writes data to mirror database from redo queue. In Asynchronous mirroring the Principal Server does not wait for the Acknowledgment from the mirror.

I hope this clears few misconceptions or confusions of Synchronous database mirroring. Please let me know if you have any questions.

You may like the following articles as well:

Myth around Remote Query Timeout Option
Remove or Delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)
SSRS Encryption Key Backup Information
SQL Database Restore using NetBackup

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/synchronous_mirroring/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Issues with Uninstall OLE DB Provider for DB2

After reading this article you’ll be able to fix if standard method of uninstall OLE DB Provider for DB2 does not work. The requirement was to upgrade OLE DB provider for DB2 from version 3.0 to version 5.0. As per Microsoft Document you cannot upgrade OLE DB Provider for DB2 without uninstall of prior versions.

The following instructions are listed in Microsoft Document named “Microsoft OLE DB Provider for DB2 Version 5.0

Upgrade from Previous Version

Microsoft OLE DB Provider for DB2 V 5.0 does not upgrade previous releases. If you have the following previous versions installed, then you must remove them prior to installing the Microsoft OLE DB Provider for DB2 V 5.0.”

To uninstall the product

You can use Windows Programs and Features to remove the product.

  1. Click Control Panel, click Programs, and then click Programs and Features. The Uninstall or change a program dialog appears.
  2. In the Name list, double click Microsoft OLE DB Provider for DB2 Version 5.0. The Data Provider Installation Wizard appears.
  3. Click Next to get started.
  4. On the Program Maintenance dialog, click Remove.
  5. On the Remove the Program dialog, click Remove.
  6. When prompted by Windows User Account Control, click Yes.
  7. On the Completion page, click Finish

This works in ideal condition. Uninstall of OLE DB Provider for DB2 does not always go well and throw weird error messages. The Uninstall may fail due to different reasons like existing DB2 version was not properly installed, someone deleted some of the MSI files or the registry corruption etc.

One of the error message looks like as follows:

MSI (s) (78:64) [21:44:40:923]: Note: 1: 1725
MSI (s) (78:64) [21:44:40:923]: Product: Microsoft OLE DB Provider for DB2 -- Removal failed.
MSI (s) (78:64) [21:44:40:923]: Windows Installer removed the product; Product Name: Microsoft OLE DB Provider for DB2 Product Version: 8.0.4294.0. Product Language: 1033. Manufacturer:Microsoft.Removal success or error status: 1603

As I mentioned earlier the error messages are strange and generic and googling did not help to resolve this particular issue. After this article it may help though 😉
In our case what worked was voiding the existing version of DB2 by changing the registry settings.
Please be careful and make sure to take a backup of your registry settings before following the below mentioned steps.

Steps to Rename The Registry Settings For Existing Version of DB2:
  • Go to the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Sna Server and rename “Sna Server” to “_Sna Server” as shown in the following screen shot:

Uninstall OLE DB Provider for DB2

Similarly make the following changes:

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\_Host Integration Server
  • HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Sna Server
  • HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\ _Host Integration Server
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\_00FAC227E6ED834428ED6E468B6B22B2
  • HKEY_CLASSES_ROOT\Installer\Products\ _00FAC227E6ED834428ED6E468B6B22B2
  • Than go to the Program Files Directory and for the Microsoft OLEDB Driver for DB2 and just put an (Underscore) in front of the directory name.

You may be thinking why only (Underscore), you can put anything meaningful like “DB2V3_Old”. It’ll work, but if you put symbol it’ll appear at the top and easy to identify what you have changed or for future reference.

I am leaving that decision up to you. The below screen shot shows how it looks in the registry after renaming:

Uninstall OLE DB Provider for DB2

Once you are done with all the changes your new install should work.

Programs and features looks like both the versions stays happily:

Uninstall OLE DB provider for DB2

You also like to know How to Download OLE DB Provider for DB2

SQL Database Restore using NetBackup
SSRS Encryption Key Backup Information
Remove or Delete Tempdb data file without restarting SQL Server
How to Fix if All logins get deleted due to Trigger Execution
Myth around Remote Query Timeout Option

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/uninstall_db2_provider/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

How to Download OLE DB Provider for DB2

This article is about how to download OLE DB Provider for DB2. This is a Microsoft Data Provider which offers tools and technologies using which SQL Server can access DB2 Databases.

The requirement was just to install DB2 driver and so change time was kept minimal. But I had to spend more than an hour just to find the required file in Microsoft Site. So this five minutes read can save your lot of time.

You may give it a try to search it yourself before reading this article and will get you know why simple things are not always easy.

If you are in the middle of change like me and your search engine lands you here then you can directly go to the end of this article where I have provided direct links to download. Later you can come back and continue to read.

The following instructions are listed in Microsoft Document. This example shows the download of OLE DB Provider for DB2 Version 5.

To Install the Product:
  1. Go to the Microsoft Download Center.
  2. Download either the x86 (32-bit) or the x64 (64-bit) version of DB2OLEDB5_x64.msi installation program.
  3. Double-click the .msi file to start the Installation Wizard
  4. Click Next to get started.
  5. License Agreement page, review the license terms, click the I accept the terms in the license agreement option, and then click Next.
  6. Registration Information page, enter your Name and Company, and then click Next.
  7. Feature Selection page, optionally click Browse to change the Folder name in which to install the product, optionally click Disk Cost to space required to install the product, and then click Next.
  8. Ready to Install the Program page, click Install.
  9. When prompted by Windows User Account Control, click Yes.
  10. On the Installing page, view the status of the installation process.
  11. On the Completion page, click Finish.

Install looks like a cakewalk until you pass the very 1st step.

Once you go to Microsoft Download Center:

Download OLE DB Provider for DB2

Wow! Now what? Which option to choose or what should you search for?

Right here I wasted a lot of time. What you should search for is Feature Packs of SQL Server Version for which you want to install the OLE DB provider for DB2.

Download OLEDB Provider For DB2 Microsoft Download Center

Choose the link showing Feature Pack as shown below:

OLEDB Provider for DB2 Microsoft Download Center

You will get the download option as shown:

OLEDB Provider for DB2 Microsoft Download Center

Click on Download and you get the options of DB2OLEDB5_x64.msi file to choose from:

Download OLE DB Provider for DB2

Select the checkbox for DB2 and click on Next and the download process should start.

For SQL Server 2012 and prior versions you need to expand Install Instructions:

Download OLE DB Provider for DB2

Then search for DB2 keyword and you will get the option to download:

Download OLE DB2 Provider for DB2

Hope this is useful and please let me know by your valuable comments.

Direct links  to Download OLE DB provider for DB2:

SQL Server 2016 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=52676

SQL Server 2014 Feature Pack:

https://www.microsoft.com/en-us/download/details.aspx?id=42295

SQL Server 2012 Feature Pack:

https://www.microsoft.com/en-us/download/confirmation.aspx?id=29065

SQL Server 2008 R2 Feature Pack

https://www.microsoft.com/en-us/download/confirmation.aspx?id=16978

You may also like below articles:
SQL Database Restore Using NetBackup
SSRS Encryption Key Backup Information
Myth around Remote Query Timeout Option
User Defined Profiler Template Location
Fact about Dedicated Administrator Connection (DAC)

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/oledb-db2/
Twitter
PINTEREST
LinkedIn

{ 2 Comments }

SQL Database Restore using NetBackup

In this article I am going to explain how to perform Database restore using NetBackup tool. I was fortunate enough to learn from experienced people, otherwise it was difficult to follow the materials provided. In this post I have tried to explain the database restore process in detail.

Launching NetBackup Tool and connecting to SQL Instance:
  • Click on Start > All Programs > Symantec NetBackup > NetBackup Agents > Right Click on NetBackup MS SQL Client > Run as Administrator as shown in the following example:

Note: Run as Administrator is a very important step, otherwise the tool won’t connect to SQL Instance and throws ambiguous error messages.

Image showing lunching NetBackup using Run As Admin option

  • In the User Account Control Window click on Yes.

Image of User Account Control Wizard

  • In the NetBackup Database Extension Window click on Ok to select the connection properties.

Image of NetBackup Database Extension Wizard to choose the connection properties

Now let’s see the ways to connect default and named SQL instances through NetBackup tool.

To Connect Default Instance:
  • In the SQL Server Connection properties window put the host name.
  • For Instance field, leave it as default click on Apply and then Close.

Image showing connection properties for default instance

Note: You do not have to put any credentials for UserId field. Leave it as default. SQL Server version, Security and Host Type will be populated once it is connected as shown in the above screen shot.

To Connect Named SQL Instance:
  • If the SQL Instance name is HostName\SQLInstance, you need to put the host and Instance name as shown in the following screen shot.
  • Click on Apply and then close.

Image about NetBackup connection to Named Instance

Now you are connected to the target Instance. You can always verify if you are connected to the correct instance from File > Set SQL Server Connection properties.

Database Restore Steps using NetBackup:

Go to File > Restore SQL Server Objects

Image showing NetBackup Restore step

Provide the SQL Server Instance name from which backup has to be restored, i.e. Source SQL Server name.

Image showing NetBackup history

You can use time filter to view the backups in particular date range.

Note: Unlike native backup of SQL Server, NetBackup tool does not create any .bak files which can be copied from source to destination. The backup files/images are scattered on storage. This is the reason you have to provide Source and Destination Server name during the restore process.

Click on Ok to go the next wizard “Restore Microsoft SQL Server Objects”.

In the below screen shot I have shown full and differential backup images. I am going to show you the steps for restore using full backup image.

For Scripting choose “Create a move template”. This will create a restore template (.bch file) and the Save radio button will get selected by itself.

NetBackup object wizard

Click on Restore.
Provide a meaningful filename for the .bch file.

NetBackup restore shwoing .bch file

Click on Save.
You’ll get a pop up message asking “Would you like to open it in notepad?”
Click on Yes.

NetBackup restore showing pop up message

In the below screen shot I have explained what all places you have to make the changes. This is very important and you have to pay attention:

Template/script showing different places to modify:
NetBacup restore showing places to change file names

Note: If you choose differential backup image to restore, the tool will automatically choose the base full database backup image. So in that case you have to make the same changes for all the data and log file locations.

And same goes with log file restore as well. Once you are done with all the changes save the file.
Now go to File > Manage Script Files:

NetBackup Restore showing Mange Script Files option

Select the created template. e.g. “DatabaseName_11072017” as shown in the below screen shot.
Click on Start.

It will ask for confirmation. Click on Yes.

NetBackup Restore confirmation message

Another self-explanatory message will pop up.
Clock on Ok.
NetBackup Restore step

It will take you back to the “Manage Script” wizard.
This is a bit confusing or rather a bug. Just click on Cancel.
NetBackup Restore tool bug

Your restore process should start and can be viewed the progress in File > View Status.

NetBackup Restore view status

Hope this is helpful.
You may like the below articles as well:

Myth around Remote Query Timeout Option
SSRS Encryption key backup information
Fact about Dedicated Administrator Connection (DAC)

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/netbackuprestore/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

List of Users with db_owner Role in All Databases

Just after returning from Christmas holiday, I got a requirement to send a report having a list of users with db_owner role in all databases in SQL Server Instance. There was an internal audit going on and the auditor wanted that report.
I knew my dear friend “Internet” will help me get that report in lieu of few clicks. I really didn’t want to spend time on writing a whole new script as I was still in holiday mood.

Wait a sec! I think I already had something which I could modify a bit to get the work done. What I had was List all permissions for a user in all or selective databases

But the output is something which I cannot send a report since it shows blank rows for the databases on which the user does not have access to as shown below:
Image of query output showing some blank rows

So, I used a temporary table to get the result I wanted. In fact I have added the same in the above article as well upon realization of the problem.

There are two ways you can pull the same report.

  • Using Cursor to scan through all the databases.
  • Using the undocumented stored procedure sp_MSforeachdb.
Using Cursor to retrieve a List of users with db_owner role in all databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 
SELECT @statement = 'use '+@dbname +';'+ 'SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission

Sample output:

Image showing output of all users with db_owner role in all databases using cursor

Using sp_MSforeachdb to retrieve the same information:
CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_msforeachdb '

use [?]

BEGIN

  SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
  pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
  FROM sys.database_role_members roles
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
  where pp.name=''db_owner'' and p.name<>''dbo'' 

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission
Sample output is same as above:
Image showing the sample out of sp_MSforeachdb
Query to Retrieve List of all users having db_owner role in Selective databases:
DECLARE @dbname VARCHAR(50)
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #UserPermission
(
ServerName SYSNAME,
DbName SYSNAME,
UserName SYSNAME,
TypeOfLogIn VARCHAR(50),
PermissionLevel VARCHAR(50),
TypeOfRole VARCHAR(50)
)

DECLARE db_cursor CURSOR
LOCAL FAST_FORWARD
FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('master','ReportServerSSRS','ReportServerSSRSTempDB')
AND state_desc='online'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @statement = 'use '+@dbname +';'+ 'SELECT
ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
pp.name as PermissionLevel, pp.type_desc as TypeOfRole
FROM sys.database_role_members roles
JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where pp.name=''db_owner'' and p.name<>''dbo'''

INSERT INTO #UserPermission
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #UserPermission

DROP TABLE #UserPermission
Sample output of selective databases:
Sample Output for selective databases
Using sp_MSforeachdb to list users with db_owner role only in user databases:
CREATE TABLE #UserPermission
(
   ServerName SYSNAME,
   DbName SYSNAME,
   UserName SYSNAME,
   TypeOfLogIn VARCHAR(50),
   PermissionLevel VARCHAR(50),
   TypeOfRole VARCHAR(50)
)

INSERT #UserPermission
EXEC sp_msforeachdb '

use [?]

IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN

  SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
  pp.name as PermissionLevel, pp.type_desc as TypeOfRole 
  FROM sys.database_role_members roles
  JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id
  JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
  where pp.name=''db_owner'' and p.name<>''dbo''   

END '

SELECT * FROM  #UserPermission

DROP TABLE #UserPermission
Sample result set:

Image showing output of sp_MSforeach db for user databases

Hope this helps. If you have any suggestion please feel free to put in comments section.

You may also find the following scripts useful for your day to day life:
Script to find the SQL Agent Job Name in Execution
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/users-db_owner-access-databases/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

SSRS Encryption Key Backup Information

Today I am going to discuss about an uncommon requirement and that is to find out SSRS Encryption Key backup information. In other words if there is any backup taken for the key and if yes then what is the location and backup date.

Now the question is very legitimate as they wanted to know if the reports can be restored in case situation demands. Not being a BI expert started my research from scratch.

Tried to query the “Keys” table in ReportServer database:

Image showing the keys table outputAs you can see no information about location of keys or at least last backup information.

A wild guess to check “backup_devices” view but no luck as expected:

Image showing output of backup_device view
Could not find any information in registry keys as well.
As per discussion with my colleague I installed SSRS on my personal laptop and then started a Process Monitor trace right before backing up the encryption key. During the process, a RSA machine key is created in C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\.

This was like the light at the end of the tunnel and decided to dig further to discover something which would make me feel like a champion 😉

The below screen shot shows the generated machine key and path:

Image showing the machine key path

Initiated Encryption Key backup and checked in the process monitor:

Image showing the key backup path in process monitorI could see that the path showing the key backup as shown in the above screen shot.

Next tried to search if any information regarding the machine key has been logged:

That is searching for the path C:\Users\ReportServer\AppData\Roaming\Microsoft\Crypto\RSA\
Image of process monitor which does not have the Machine key creation information This “Cannot find string” message shattered my hope 🙁

To understand how the machine key got generated, I uninstalled SSRS and checked the same path and the key was still lying there. Did a reboot of the system but it was still there.
I manually deleted the key, reinstalled and configured SSRS and the key got created. So the machine key gets generated while configuring SSRS.
So my assumption:

Machine key gets generated while taking SSRS Encryption Key Backup was wrong.

Conclusion:

I have checked with many experts in different forums and the most fruitful one was https://www.sqlservercentral.com.
What we can conclude from all these is as of now SQL Server does not record the information of SSRS encryption key backup path. It make sense because the .SNK file could be moved to another location manually.
But I expect Microsoft to at least record the information about the successful backup just the way they record database backup information.

Work Around:

The only work around is to have a company policy of some sort on where to backup these keys. Like you may want to suggest to have a policy/documentation to do the backup every x months on x path.
Or use default location for the backup of the instance there are some things you could do using the registry.
And then use PowerShell to find the LastWriteTime of the .SNK file.

Here I have explained every way I tried and failed to find the required information. Sometimes it is important to know what all ways does not work so that you can think in other direction.
Well if you find the solution please let me know and that is why I request you to share this with your friends/network using the below social share icons.

You may also like to know about:
Myth around Remote Query Timeout Option
Delete Tempdb data file without restarting SQL Server
Fact about Dedicated Administrator Connection (DAC)

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/ssrs-encryption-key-backup-information/
Twitter
PINTEREST
LinkedIn

{ 2 Comments }

Script to Find the SQL Agent Job Name in Execution

In initial days I had to scratch my head for a long time to find out the SQL Agent Job Name in Execution just to provide details to customer or to find out the job causing issues.
The below simple query will be helpful in the following situations:

  • Several agent jobs showing in “sp_who2 active” results set and you want to identify them.
  • You have identified the root blocker as a SQL Agent job and need the problem job name to notify Customer.
  • Customer requested to find out the status of a job in progress etc.
sp_who2 active output does not show the job name:

Image of Sp_who2 active output which does not show job name

The below simple query will help you find the information:
SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(<jobid> AS UNIQUEIDENTIFIER)

Considering the above example:

SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(0xD81B43320D3F2843B9A6A40CA120F14B AS UNIQUEIDENTIFIER)

Image of the query result showing job name

Let me know if it helps and any suggestions are always welcome.
You may also find the below scripts useful:
List all permissions for a user in all or selective databases
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/script-to-find-the-sql-agent-job-name-in-execution/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

List all permissions for a user in all or selective databases

Today I am gonna talk about the requirement of finding out all permissions for a user in all or selective databases.

The query here will help you in following situations:

  1. Customer has provided you with a user id and couple of database names and they wanted to find out all the permissions the user has on those databases.
  2. You have a task to provide access for a user id to some of the databases. You have provided the access and now want to pull the report to send to customer.
  3. You have been given a user id and asked to find out all permissions in all user databases.

This may not be exactly what you are looking for but certainly will give you some idea to make it your way.

Retrieve all permissions for a user in selective databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.sys.databases
WHERE name IN ('database1','database2') --Obviously you’ll put your database names 
AND state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'SELECT

ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name =''Test_User'''  --The user you want to find permissions for

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

In this example I have listed the permissions for “Test_User” on databases “database1” and “database2″as shown the below screen shot:

Retrieve all permissions for a user in all user databases:
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.sys.databases
WHERE name NOT IN ('master','msdb','model','tempdb') 
AND state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

SELECT @statement = 'use '+@dbname +';'+ 'SELECT

 ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin, pp.name as PermissionLevel, pp.type_desc as TypeOfRole 

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
where p.name=''Test_User'''

EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor 

Here is an example output:

Please feel free to let me know if there is any better way you can think of to retrieve the same information.

Check out the below scripts as well:
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name prior to Failover
Script to List All Users with db_owner role in SQL 2000

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/list-all-permissions-for-a-user-in-all-or-selective-databases/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Script to retrieve database backup information

Backup script is something which is a very common need of a DBA in day to day life.This article will provide you with a base query to retrieve database backup information.

I am not able to give credit to anybody as I have seen lot of similar queries in the internet and so could not find the original writer.

Hmm! then what is the need of having another one in this blog? Well, I have experienced that sometimes a simple query suffice your need rather than a fancy one.

The below queries will help answering  the following questions:
  • Do you have recent database backups?
  • What type of backup is being taken for particular databases?
  • What is the backup size?
  • Who has taken the backup or which application login id is being used to take backup?
  • What is the Recovery model of the databases being backup up?
  • How long it took to backup?
Backup details of all the databases of all backup types:
SELECT server_name, database_name, 
CASE 
   WHEN TYPE='D' THEN 'Full Backup' 
   WHEN TYPE='L' THEN 'Log Backup'
   WHEN TYPE='I' THEN 'Diff Backup' 
END AS BackupType, 
backup_start_date, backup_finish_date, DATEDIFF(SS,  backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', USER_NAME AS UserName  
FROM msdb..backupset 
ORDER BY backup_finish_date DESC 
The following screen shot is to show you an example output:

You can use this as a base and tweak it here and there as per your requirements. Few examples are given below.

Retrieve the same information for particular database(s) and for a particular type:
SELECT server_name, database_name, 
CASE 
   WHEN TYPE='D' THEN 'Full Backup' 
   WHEN TYPE='L' THEN 'Log Backup'
   WHEN TYPE='I' THEN 'Diff Backup' 
END AS BackupType, 
backup_start_date, backup_finish_date, DATEDIFF(SS,  backup_start_date, backup_finish_date)/60 AS [Duration(Minutes)],
CAST(backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', USER_NAME AS UserName  FROM msdb..backupset
WHERE database_name IN ('master','model','msdb')
AND TYPE='D' 
ORDER BY backup_finish_date DESIC 
Include recovery model of the databases in the same output list:
SELECT b.server_name, b.database_name, 
CASE 
   WHEN TYPE='D' THEN 'Full Backup' 
   WHEN TYPE='L' THEN 'Log Backup'
   WHEN TYPE='I' THEN 'Diff Backup' 
END AS BackupType, 
d.recovery_model_desc AS RecoveryModel,
b.backup_start_date, b.backup_finish_date, DATEDIFF(SS,  b.backup_start_date, b.backup_finish_date)/60 AS [Duration(Minutes)],
CAST(b.backup_size/1024/1024 AS NUMERIC(10,2)) AS 'BackupSize(MB)', b.USER_NAME AS UserName  FROM msdb..backupset b
JOIN sys.databases d
ON b.database_name = d.name
WHERE database_name IN ('master','model','msdb')
AND TYPE='D' 
ORDER BY backup_finish_date DESC

Please let me know if it helps by leaving your comments below.

Further more you can go through the below link for some addition scripts:

https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

You might also want to look at the below scripts:
Script to get Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/script-to-retrieve-database-backup-information-including-login-details/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

User Defined Profiler Template Location

First of all let me explain you why you need to know the user defined profiler template location.

In situations you may need to define a template and share it with other DBAs to run or vice versa. You have saved the profiler and request the other person to run by providing the name of the profiler. The other person called you (when you are not in shift) since he/she is not able to find the profiler you created, but that is hard for you to believe as you are quite sure you did it right.

Once you go through this article you will get to know:

  1. How to create a user defined template?
  2. Why others are not able to see the template?
  3. How to resolve this?

You can watch the following video demo which shows the stuffs I am going to explain here:

1. How to create a user defined template?

Launch SQL Server Management Studio (SSMS) and open SQL Server profiler:

Open a new Template:

Provide a template name and choose a base template from the drop down menu. In the below example I have chosen Standard (default) template. Then click on Events Selection tab.

In the above example I have not selected the check box for “Use as a default template for selected server type”. You can choose this option if you want your template to be default template.

Choose the Events as per your requirement. Click on “Show all events” and “Show all columns” to view all the events and columns to choose from. Then click on “Column Filters” to put filter for the trace.

In the below example I have put a filter for database id since I wanted to capture the transactions only for one database. The filter may differ for your requirement.

Once you are done with the filter selection click on OK and then Save the template.
Now if you go back and check the drop down list of templates you will find your custom template.
Go to File > New Trace and then connect to any SQL Instance

The following screen shot shows the newly created template “Demo Template (user)” in the drop down list.

2. Why others can’t see this template under the same drop down?

Here is the physical location of the user defined template file:

C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110

The main point to note here is the template gets created under the user profile folder of the login id, used to login to the server and that is why it is not visible to others.
AppData is a hidden folder and 11.0 and 110 will change depending upon the SQL Server version.

3. How to resolve this?

Let’s see the location of SQL Server Provided profiler templates.

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110

You guessed it right! You just need to copy the user defined template from
C:\Users\UserName\AppData\Roaming\Microsoft\SQL Profiler\11.0\Templates\Microsoft SQL Server\110
To
C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Profiler\Templates\Microsoft SQL Server\110

Hope you enjoyed this article and feel free to share if you think it is helpful. Please let me know if you have any questions on this.

You might also want to check Myth around Remote Query Timeout Option

Please spread the word:
RSS
Follow by Email
Facebook
Google+
http://sqlgeekspro.com/user-defined-profiler-template-location/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }