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 ...

SQL Srever 2012 – Limiting the Size of SQL Server Error Logs

Limiting the size of sql server error log is one of the features in SQL server 2012 which we could consider using. We could limit the log file size from SQL 2012 which would ensure that disk space is not eaten up by huge sql server error logs. Refer below article and blog http://support.microsoft.com/kb/2199578 http://www.sqlskills.com/blogs/paul/limiting-error-log-file-size-in-sql-server-2012/ ...

SQL Server Error – Index was outside the bounds of the array-Fix

While connecting sql server 2012 using sql server 2008 SSMS, you will receive error  Index was outside the bounds of the array. Below article deals with the issue and we would have to be in certain service pack levels to fix the same.  In case you have SQL Server 2008 R2 management studio in your ...