SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query

People are judged from their questions and not their answers. I received wonderful question the other day.

How sqlcmd can be used along with currently running query script posted on your blog?

Please read following two posts before continuing this article as they cover background of this article.

SQL SERVER – Interesting Observation – Using sqlcmd From SSMS Query Editor

SQL SERVER – Find Currently Running Query – T-SQL

If due to a long running query or any resource hogging query SQL Server is not responding sqlcmd can be used to connect to the server from another computer and kill the offending query. SQL Server provides a special diagnostic connection which allows administrators to access SQL Server when standard connection is not possible. Except very extreme situations dedicated administrator connection (DAC) is always successful.

Let us see how we can use DAC using sqlcmd.

Connect SQL Server using sqlcmd and -A option which will establish DAC.

C:\>sqlcmd -S localhost -U sa -P dev -d master -A

For our test purpose run following query which overload server and probably make it unresponsive. If your configure is not strong I strongly suggest not to run following query.

Following query is just for test purpose and not part of solution.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
CROSS JOIN Person.Address a1
CROSS JOIN Person.Address a2
CROSS JOIN Person.Address a3
CROSS JOIN Person.Address a4
CROSS JOIN Person.Address a5
CROSS JOIN Person.Address a6
CROSS JOIN Person.Address a7
CROSS JOIN Person.Address a8
CROSS JOIN Person.Address a9
GO

Once successfully connected it will provide prompt 1> enter following T-SQL query which will give SessionID of currently running query along with its elapsed time.

SELECT
req.session_id,
req.status,
req.total_elapsed_time
FROM sys.dm_exec_requests req
WHERE status = 'running'
AND req.total_elapsed_time > 1

Our previously running query gave session id 52 in on my server. The session id may be different for each SQL Server. Once the session id is figured out it can be killed using KILL [SessionID] command. Always make sure to type command GO after each complete query.

KILL 52

Once above query has run it will kill our example long running query and give following error.

Msg 233, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

sqlcmd is really useful utility of SQL Server and it comes in handy when server is not responsive. I strongly suggest to bookmark this article as it can come to rescue when nothing works and SQL Server is unresponsive.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Currently Running Query – T-SQL

This is the script which I always had in my archive. Following script find out which are the queries running currently on your server.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

While running above query if you find any query which is running for long time it can be killed using following command.

KILL [session_id]

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Change Color of Status Bar of SSMS Query Editor

This is one very interesting issue which I have started to follow recently. Just like any other organization my company has many servers. Some are production and some are development. It is very much necessary that query which are written for developer environment does not run for production environment accidentally.

In SQL Server 2008 there is special feature which can change the color of the task bar. This will alert developer to run query on server.

Let us see quick tutorial with images which explains how the color of the status bar in SQL Server management studio can be changed.

Another example of different color status bar.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Msg 15151, Level 16, State 1, Line 3 Cannot drop the login ‘test’, because it does not exist or you do not have permission

I got following error when I was trying to delete user ‘test’ with ‘SA’ login. I was little surprised but then I tried to delete with the windows authenticated systemadmin account. Once again I got the same error.

Msg 15151, Level 16, State 1, Line 3
Cannot drop the login ‘test’, because it does not exist or you do not have permission.

The reason I was surprised that I was systemadmin and I should be allowed to delete the login. I am including the script which I used to delete the account here.

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Test')
DROP LOGIN [Test]

Fix/Workaround/Solution:

I finally found out that error was misleading. I had another session open in SQL Server Management Studio with username ‘test’ and it was preventing me to drop the user. Once I closed that open session I was successfully able to delete the user using above script.

In Normal Case:
Following error usually show up when user is logged in, which I did not get it.
Msg 15434, Level 16, State 1, Line 3
Could not drop login ‘test’ as the user is currently logged in.

However, when I tried to re-create scenario I got above error. I want to share this experience with users and want to know if they have ever faced this scenario.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Add Any User to SysAdmin Role – Add Users to System Roles

The reason I like blogging is follow up questions. I have wrote following two articles earlier this week. I just received question based on both of them. Before I go on questions, I recommend to read both of the article first. Both of them are very small article so they are quick to read.

SQL SERVER – DISABLE and ENABLE user SA

SQL SERVER – Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ’sa’, because it does not exist or you do not have permission

The question I had received is following: “How to add any user to systemadmin role?

This is very simple process.

Method 1: T-SQL

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin'
GO

Method 2:  SQL Server Management Studio

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Fix : Error : Msg 15151, Level 16, State 1, Line 2 Cannot alter the login ‘sa’, because it does not exist or you do not have permission

Few days ago, I have wrote about SQL SERVER – DISABLE and ENABLE user SA I received following email from one of the user who received following error.

Msg 15151, Level 16, State 1, Line 2
Cannot alter the login ‘sa’, because it does not exist or you do not have permission.

Fix/Workaround/Solution:
This error had occurred because of insufficient rights. Please read my previous post here before reading further article.

SA is system admin user and it is the highest level of user in system. If any user have to modify the permissions of SA that user needs to have higher or equivalent rights as SA user. Users member of systemadmin group are can only change the rights of SA user.

First adding any user to systemadmin role and then using the same user to modify system admin’s tool will fix the issue.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DISABLE and ENABLE user SA

Just a day ago, I received question from blog reader Mike McDonald.

“How can I modify permissions for SA user? I tried to modify dbo users permission but now I am having problems.”

First of all, there may be no relation between dbo user and SA user. They are different and should be left separate.

Modifying the permission of SA user is not possible. However, SA can be disable or enabled using following script. Make sure that you are logged in using windows authentication account.

/* Disable SA Login */
ALTER LOGIN [sa] DISABLE
GO
/* Enable SA Login */
ALTER LOGIN [sa] ENABLE
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Change Collation of Database Column – T-SQL Script

Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.

Let us see following example.

USE AdventureWorks
GO
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROP TABLE TestTable
GO

When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.

Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.

Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.

Reference : Pinal Dave (http://www.SQLAuthority.com)

SQLAuthority News – Download RML Utilities for SQL Server

Note:   Download RML Utilities for SQL Server by Microsoft

The RML utilities allow you to process SQL Server trace files and view reports showing how SQL Server is performing. For example, you can quickly see:

  • Which application, database or login is using the most resources, and which queries are responsible for that
  • Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed
  • What queries are running slower in today’s data compared to a previous set of data

You can also test how the system will behave with some change (different service pack or hotfix build, changing a stored procedure or function, modifying or adding indexes, and so forth) by using the provided tools to replay the trace files against another instance of SQL Server. If you capture trace during this replay you can use the tools to directly compare to the original baseline capture.

Supports SQL Server versions 2000, 2005 and 2008.

Download RML Utilities for SQL Server

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQLAuthority News – SQL Server Security Whitepapers

Microsoft has published following three security related white papers. I suggest to all my readers to read them. Read the summary know what is covered in those  white papers.

  • Engine Separation of Duties for the Application Developer – Separation of duties is an important consideration for databases and database applications. By properly defining schemas and roles, you can create a distinction between users who can manipulate data from those that administer the database. This paper discusses the topics of which application developers should be aware and provides a heuristic example to guide you in achieving separation of duties.
  • Database Encryption in SQL Server 2008 Enterprise Edition – With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by using TDE, or the file-level encryption options provided by Windows. TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level, which is similar to two Windows features: the Encrypting File System (EFS) and BitLocker Drive Encryption, the new volume-level encryption introduced in Windows Vista, both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption, EFS, or BitLocker. This white paper compares TDE with these other encryption methods for application developers and database administrators. While this is not a technical, in-depth review of TDE, technical implementations are explored and a familiarity with concepts such as virtual log files and the buffer pool are assumed. The user is assumed to be familiar with cell-level encryption and cryptography in general. Implementing database encryption is covered, but not the rationale for encrypting a database.
  • Cryptography in SQL Server – Although cryptography provides SQL Server with powerful tools for encryption and verification, these are often not well understood. This can lead to poor or incomplete implementations. This white paper presents an overview of cryptographic functionality and discusses how this applies to authentication, signed procedures, permissions, and encryption. Because the target audience is the database professional and not necessarily security experts, the focus is on practical ways to use cryptography in SQL Server.

Abstract courtesy : Microsoft

Reference : Pinal Dave (http://www.SQLAuthority.com)