SQL Server General DBA

Create a Deadlock and Set the Deadlock Victim

To create a deadlock for testing purposes you can execute the below TSQL script: — 1) Create Objects for Deadlock Example USE TEMPDB CREATE TABLE dbo.foo (col1 INT) INSERT dbo.foo SELECT 1 CREATE TABLE dbo.bar (col1 INT) INSERT dbo.bar SELECT 1 — 2) Run in first connection and in my server this connection was SPID […]

Pareto Charts in SSRS

The purpose of a Pareto chart is to highlight the most important amongst a set of factors. For example, in quality control for a manufacturer, a Pareto chart can highlight the most common sources of defects and the highest occurring type of defect. The Pareto principle is also known as 80-20 rule, so for quality […]

Design A Hybrid Report in SSRS

Hybrid reports are the reports which have two types of graphs using different axes within the same graph . For example. In the above report, Product cost is on the right axis while Sales Amount Percentage is shown on the left axis, so that viewers can easily compare Product Cost vs Sales Amount Percentage. Below […]

Drop Failed for Login ‘User xxx’

How To Drop Users Who Are Owners or Databases and Jobs When you want to drop users you need to en sure that those users are not owners databases SQL Server agent jobs, otherwise you will be confronted with the below error: To avoid this you will need to change both the database and SQL […]

Finding Running SQL Server Agent Jobs

Monitoring SQL Server agent jobs is a critical task for DBAs. There are several ways of achieving this. The most obvious method is to use the Job Activity monitor. For the above list, note that the currently running jobs will have a green arrow icon next to them. However, if you have large number of […]

How to Differentiate System and User Databases In SQL Server

This question is frequently asked in many forums and the most popular answer is to use the DB_ID() function as shown below. So if you want to get only the system databases you can use DB_ID(databasename) < 5 in your where clause. This work around is used since there is no dedicated column in sys.databases […]

Drop a Database by Closing Existing Connections using SSMS or T-SQL

To drop a SQL Server database, you will need exclusive access to the database ensure there are no other current users of the database or you will encounter the error: Drop failed for Database ‘dbName’ …. Cannot drop database because it is currently in use Ensuring there are no other current users can be very […]

Configuring Email in SQL Server 2000

A couple of days back I was asked to configured database email for a DB Server which was hosted on a SQL Server 2000 environment. Configuring a database email in SQL Server 2000 is somewhat different than the SQL Server 2005 environment as there is no GUI available in SQL Server 2000. The following steps […]

Altering a Column Which has a Default Constraint

Setting up a default column is a common task for  developers.  But, are we naming those default constraints explicitly? In the below  table creation, for the column, sys_DateTime the default value Getdate() will be allocated. CREATE TABLE SampleTable (ID int identity(1,1), Sys_DateTime Datetime DEFAULT getdate() ) We can check the relevant information from the system […]

Troubleshooting High-CPU Utilization for SQL Server

The objective of this FAQ is to outline the basic steps in troubleshooting high CPU utilization on  a server hosting a SQL Server instance. The first and the most common step if you suspect high CPU utilization (or are alerted for it) is to login to the physical server and check the Windows Task Manager. […]