SQL Server General DBA

Data Mining Add-ins Error

When using Microsoft SQL Server 2012 Data Mining Add-ins you may encounter the below error: Error (Data mining): Session mining objects (including special data source views used to process data mining dimensions) cannot be created on this instance The reason this occurs is that the Data Mining tool needs to create session mining models in […]

Overview of Template Explorer Feature in SQL Server Management Studio

This article will give you an Overview of Template Explorer Feature in SQL Server Management Studio which was initially introduced in SQL Server 2005. SSMS has a variety of inbuilt templates which contains SQL Scripts that can help you create objects within SQL Server Database. Let’s work through an example of using the Template Explorer […]

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 […]
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 |