SQL Server General DBA

Finding Connections for A Single Database

During trouble-shooting you may want to find all the connections relevant to one database. You can use sp_who2 but the problem with that is it can return a lot of results for all connections and you need to scroll through entire result set. The following script will return connections relevant to one database with other […]

Using Stored A Procedure with Temp Tables for SSIS Packages

Using stored procedure with temp tables for SSIS packages. Some stored procedures are complex and so require temporary tables or table variables. Let us say we have the following procedure to extract data. CREATE PROC ProcWithTableVariable AS DECLARE @Table TABLE (id Int) INSERT INTO @Table VALUES (1),(2) SELECT * FROM @Table And SSIS OLE DB […]

Verify Whether a SQL Server Agent Job is Running

When I posted an faq about Finding Running SQL Server Agent Jobs I had several few emails and queries on how verify whether a job is running by using only a TSQL script. The below script will verify if the job exists and whether or not it is running. DECLARE @jobname sysname =’Running Job’ — […]

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 […]
Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |