SQL Server – Move SQL Server 2000 System Databases

Move System Databases in sql server 2000 Moving all sql server system databases from new location to another location or different server, Here is step by step approach to move all system database at once 1. Update the startup parameters (i.e -d and -l ) for SQL Server with new location of mdf and ldf file. ...

SQL Server – MSDB System database

MSDB database SQL Server MSDB database part of System database. The MSDB database holds SQL Server Jobs details, alerts, Database mail and service broker. SQL Server stores all backup and restore history within tables in msdb. These backup and restore history is important in case to find out old full backup and file location. All ...

SQL SERVER – Find collation of table column using tsql

  There are many ways to find collation of table column and database in sql server, simplest way is using ssms (sql server management studio).   First connect to sql server instance using ssms  -> Select your database  -> Right click on it  -> Properties     Here you will see collation of database under ...

SQL SERVER – Create Database Role with simple example using tsql Script

Create Database Role   Many time SQL DBA gets request to assign permisison to database user and create database role for application developers. SQL server comes with inbuilt  database role when you create new database by default database role gets created example db_datareader which gives you permission to view table data.    In SQL Server, ...

SQL Server Error 14274 – Cannot add, update or delete a job (or its steps or schedules) that originated from an MSX server

Error 14274 occurs when you try to update a sql agent job after renaming server name. If sql server is running on the server then after renaming windows server, you need to update sql server name. Otherwise when you will try to add, update or delete a job on sql server, it will throw below ...

SQL Server Profiler – Collect Workload data for Database Engine Tuning Advisor

Many times as DBA, you must have used SQL Server profiler and Database Engine Tuning Advisor to drill down into performance issue. SQL Server profiler give you an option to collect the information related to SQL Server events which includes stored procedure execution, t-sql, triggers, connections and many more. As database administrator, you may get ...

SQL Server – sp_who2 – Troubleshoot blocking problem

Sp_who2 – System Stored procedure Many of you as dba must be knowing the use of sp_who2 in sql server, If not let me tell you it will help to troubleshoot performance issue of database also monitors the current activity on SQL Server. SP_WHO2 is system stored procedure which is installed by default while installation ...

SQL SERVER – How to find blocking in sql server – using system stored proc sp_who2

Mostly commonly asked question related to sql server blocking – how to find blocking and blocked session (spid) in sql server?  There are number of way to find blocking in sql server, you can use either system stored procedure (sp_who2), sql script, sql server activity monitor, sql server profiler and DMVs Find blocking sessions using sp_who2 ...

SQL Server – Msg 6112, Level 16, State 1- Distributed transaction with UOW

Some time ago, I was trying to kill negative spid in sql server but the kill statement was ineffective. I was getting error saying “Microsoft Distributed Transaction Coordinator can resolve this transaction. Kill command failed” SQL Server Error – Distributed transaction with UOW Msg 6112, Level 16, State 1, Line 1 Distributed transaction with UOW {BABC4276-DBA0-45EF-AC87-FFD587FF2650} is in ...

SQL Server – How to find and kill negative spid

To find out negative spid in sql server, you can use below query. This query will give the negative spid  and kill command. Copy this command and execute against master database to kill negative spid.  select  distinct convert (smallint, req_spid) As spid,  DB_NAME(rsc_dbid) As dbname,  ‘kill  ”’+ cast (req_transactionUOW as varchar(50)) +”” as KillCommand from  master.dbo.syslockinfo ...