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+
https://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:Permissions for a user

Click here to copy the code

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:

Permission for user

Click here to copy the code
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+
https://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+
https://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+
https://sqlgeekspro.com/user-defined-profiler-template-location/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Script to get SQL Cluster Failover Time and Node Name

A DBA’s life becomes easier if there is a script to get the required information. This article is about a script to get SQL Cluster Failover time and Node Name prior to failover and little more.

You can go through the following link to know what and how to check the same information in SQL Server Error log. I have also explained what all other related information can be found. The output of the below query will make more sense if you read the article first.

SQL Cluster Instance Failover Time and Node Name prior to Failover

After writing the above article I thought of working on the script to scan through all the error log files. This way I can find not only the latest SQL Cluster failover time and Node name but also history of failover information. This may help in situations where you are facing frequent failover issues or you need a failover history report.

CREATE TABLE #ErrorLog(
   LogDate DATETIME,
   ErrorSource NVARCHAR(MAX),
   ErrorMessage NVARCHAR(MAX)
)

CREATE TABLE #NumberOfLogs(
   ID INT PRIMARY KEY NOT NULL,
   LogDate DATETIME NOT NULL,
   LogFileSize bigint
)

INSERT INTO #NumberOfLogs(ID,LogDate,LogFileSize)
EXEC master.dbo.xp_enumerrorlogs

DECLARE @ErrorLogID INT

DECLARE cNumberOfLogs CURSOR FOR
   SELECT ID
   FROM #NumberOfLogs

OPEN cNumberOfLogs
FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
   WHILE @@FETCH_STATUS = 0
   
   BEGIN
       INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC sp_readerrorlog @ErrorLogID, 1, 'NETBIOS'
        
       INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
       EXEC sp_readerrorlog @ErrorLogID, 1, 'SQL Server is terminating'
       FETCH NEXT FROM cNumberOfLogs INTO @ErrorLogID
   END 
   
CLOSE cNumberOfLogs
DEALLOCATE cNumberOfLogs

SELECT LogDate, ErrorMessage FROM #ErrorLog

DROP TABLE #ErrorLog
DROP TABLE #NumberOfLogs

The below screen shot shows a sample result set:

If this is helpful, please let me know by leaving your comments below. I would love to hear from you if you have any better way to get the same information.

You may also like to check the  Script to List All Users with db_owner role in SQL 2000.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/script-get-sql-cluster-failover-time-node-name/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

SQL Cluster Instance Failover Time and Node Name prior to Failover

You may have come across situations where SQL Server cluster instance has failed over from one node to other and you were asked to find the SQL Cluster Instance failover time and node name prior to failover.

Who can ask this question? Your approver, manager or colleague anyone can ask you for different reasons, to know since when the instance is running on wrong/passive node, what alert system you have and it worked properly or not etc.

Whatever may be the reason, you want to find it quickly. In this article I am going to explain how to find the required information from SQL error log.

You need to search for the messages containing the text NETBIOS in the error log files.

Launch SSMS (SQL Server Management Studio) and start with the first error log to view as shown below:

Click on Filter and type in “netbios” against the field “Message Contains Text”. Mark the check box for Apply filter and click on OK as shown below. The message you are searching for, can be typed in Capital/Lower case.

Start checking each error logs until you find the required information as shown in the following screen shot.

The Message is like:

“The NETBIOS name of the local node that is running the server is ‘NodeName’. This is an informational message only. No user action is required.”

The message provides the information about the current Node, hosting the SQL Server Instance and the time it came up on the Node. Hence you need to keep checking the prior error logs which will show a different Node name and time in case it failed over from other node.

In the above example it shows the particular SQL Instance was running on Node “N02” prior to the failover.

The following information you can find using the NETBIOS filter:
  1. What is the current node? (N01)
  2. Is failover happened? Yes, as it shows a different node (N02) in prior error log file.
  3. Which node it was running prior to failover?(N02)
  4. What time failover started (Between 9/9/2017 11:28:10 PM the instance was on N02 and 9/9/2017 11:47:07 PM the instance started on No1. Check the error log around the time removing the filter and you will be able to find the exact time as shown in the below screen shot.)


You can also find what time SQL Instance came online/ what is the last SQL restart time. Of course you can find it from tempdb creation time? (Date 9/9/2017 11:47:07 PM the instance started on No1).

Hope this helps and I would like to hear from you if you have any other ways to find the same information quickly.

You may also want to check the following articles:
Myth around Remote Query Timeout Option
All Logins got disabled due to trigger execution

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/sql-cluster-instance-failover-time-node-name-prior-failover/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Script to List All Users with db_owner role in SQL 2000

The Requirement was to provide a report to list all users with db_owner role. No big deal right? But the fun part was there were many servers running SQL 2000 version. Of course first thing first, search in the internet. Got few but not quite which I was looking for.

So ultimately I wrote the below query to provide the required details in SQL 2000. I have kept the option open to list not only for db_owner but also for any other database role.
You can tweak it as per your requirements.

SELECT p.name AS UserName,
      CASE WHEN p.isntgroup =1 AND p.isntuser=0 THEN 'Windows Group'
           WHEN p.isntgroup =0 AND p.isntuser=1 THEN 'Windows Login'
           WHEN p.issqlrole=1 THEN 'Database Role'
      ELSE 'SQL Login' END AS 'Login Type',
      USER_NAME(roles.groupuid) AS 'AssociatedRole' 
      FROM sysmembers roles
           JOIN sysusers p ON roles.memberuid = p.uid
           JOIN sysusers pp ON roles.groupuid = pp.uid AND  p.uid NOT IN (0,1,2,3,4) AND
           USER_NAME(roles.groupuid) IN ('db_owner', 'db_datareader')

The following screen shot shows the output:

I hope this helps. This post can only be treated as complete once you put your thoughts in the comment section.
You might also want to check the link which includes video to know the Myth around Remote Query Timeout Option

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/script-list-users-db_owner-role-sql-2000/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Logon Failed Due to Trigger Execution: Video Demo

I got a call in the middle of the night about all the logins in SQL Server has been disabled. Logon failed  due to trigger execution. The situation is quite scary, isn’t it? Well if you know what to do, then it is not except from the fact that you have to login in odd hours.

At first try to connect to the SQL Instance to find out the error:

The error message says “Logon failed for Login ‘sa’ due to trigger execution”. The second and third line of the error is little misleading.

It indicates that there must be a server level trigger someone has created due to which all the logins got disabled. The solution would be, find out the problem trigger and disable it. But you need to find out a way to get in to SQL Instance first.

You guessed it right!  You need to use DAC (Dedicated Admin Connection) to connect in this situation. If you have not read the article where I have explained different ways to enable and connect using DAC click the following link.

Dedicated Administrator Connection (DAC)

Here I am going to show using command prompt. In the video link given at the end of the article, I have demonstrated using SSMS.

Open command prompt and use DAC to connect to the instance. In the below screen shot I am connecting to default instance using windows authentication and then run the below query:

SELECT name, create_date FROM MASTER.sys.server_triggers ORDER BY create_date DESC


As shown in the above example the trigger name is “TestLogin” and if you have observed I have used order by create_date desc, this is because after the latest trigger creation, nobody is able to login.

Now you can disable the trigger using the below query:

disable TRIGGER [TestLogin] ON ALL server


Once the trigger is disabled you will be able to connect to your SQL Instance. You can script out the trigger to check and rectify it.

If you can play sound, go ahead and watch the demo to fix the issue using SSMS:

I hope this helps. I would love to hear from you about any other similar issue or if you have any questions.

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/sql-logins-got-disabled/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Facts about Dedicated Administrator Connection (DAC)

Have you ever thought how Dedicated Administrator Connection (DAC) allow you to connect to SQL Server even when it cannot accept standard connection requests?
DAC is a special type of diagnostic connection, introduced by Microsoft as a new feature in SQL Server 2005.
SQL Server reserves a minimum amount of memory and a private scheduler using which it allows you to connect in situations when you need it.
You need to use a login which is a member of the SQL Server sysadmin role to use DAC and for obvious reasons only one such session is allowed at any point of time.

By default DAC is enabled locally except in SQL Express edition. That means if you are able to RDP to the server and want to use DAC to connect to the local SQL Instance, you will be able to do so even when DAC is not enabled explicitly.
Go ahead and watch this video showing what I am talking about:

In SQL Express you need to add “-T7806” to the startup parameters and restart SQL Services. Also SQL Browser Service should be running.

Then why Microsoft has provided the option to enable DAC? In situations OS can be completely exhausted of resources to handle any further user connections which prevents using DAC locally.
That is why you have the configuration settings “remote admin connections” to allow DAC from a remote machine.

There are two ways to enable DAC:
  1. Using Query
  2. GUI (Management Studio)

1. Using Query to set the configuration  settings:

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

The message says “Run the RECONFIGURE statement to install”. This is little misleading as we have run that already. No need to run it again.

You can verify by executing the following query:

EXEC sp_configure 'remote admin connections'

As you can see in the above screen shot, both config_value and run_value has been set to 1. Otherwise run_value would have been ‘0’.

2. Using Management Studio:

Right Click on SQL Server Instance > Facets as shown below.

Choose Surface Area Configuration and make “RemoteDacEnabled” to True.

Now your SQL Instance is ready to allow DAC from remote machine.

There are two ways you can connect to SQL Instance using DAC:
    1. Command Prompt
    2. GUI (SQL Server Management Studio)

1. Command prompt:

Commands to connect using DAC:

a. To use SQL Authentication
sqlcmd -S [ServerName] -U [ltloginname] -P [Password] –A

b. To use Windows Authentication which will use the login id you are connected with:
sqlcmd –S [ServerName] -A

Examples:

To connect to default SQL instance using windows authentication:
sqlcmd -S (local) –A

Or you can just use a (.) dot:

To connect named instance using SQL Authentication:

Note: In the above example the last letter is in lower case (-p), to prompt for password. If you want to mention the password in the command line itself then if is upper case (-P).

2. GUI (SQL Server Management Studio):

Go to File > New > Database Engine Query as shown below

Alternatively you can click on the icon as shown below:

This is important to use Database Engine Query, otherwise you will get error as explained later in this article.

Type Admin:SQLInstanceName for the Server Name field name as shown below:

You can use either SQL Authentication or Windows Authentication.

If you do not pay attention to use “Database Engine Query” you will get the below error message:

In this article you have learned what DAC is and how it allows the special connection, different ways to enable DAC and different ways to connect.

I would love to hear from you if you have any questions and yes I hope you have gone through the topic of Myth around Remote Query Timeout Option

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/facts-dedicated-administrator-connection-dac/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }

Remove or Delete Tempdb data file without restarting SQL Server

You may want to delete that extra Tempdb data file which someone created by mistake or to accommodate a query. Whatever may be the reason, today I am going to show you how to do it and what issues you may face.

You can run the below query to remove the data file:

ALTER DATABASE tempdb
REMOVE FILE LogicalFileName;
GO
 

Or you can use GUI:

SQL Server throws the following error:

The error message says that it cannot remove the file because it is not empty.

If you just add a file and there is no ongoing activity in the file then it would allow to remove the file this way.

For each file you want to remove, you need to run the following command to empty the file and then run the above query to remove the file:

USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

But what if it throws the below error:

The error message is about a work table page which can not be removed. Work table is related to cached information, which indicates that you need to clear the cache.

Execute the below queries one by one and after clearing each, try to empty the file again:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

The successful run shows as below:

Please keep in mind that whenever you are clearing cache, it can cause performance issues. I am leaving the decision up to you to handle the risk factor according to the environment.

If your environment allows you to restart SQL Service you can just restart which will empty the tempdb files and you can run the remove command:

ALTER DATABASE tempdb
REMOVE FILE LogincalName;
GO

I hope you have already debunked the Myth around Remote Query Timeout option.

You can go ahead and watch this video:

Please spread the word:
RSS
Follow by Email
Facebook
Google+
https://sqlgeekspro.com/remove-delete-tempdb-data-file-without-restarting-sql-server/
Twitter
PINTEREST
LinkedIn

{ Add a Comment }