SqlServer-query

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

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

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

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

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

SQL Server will not allow you to drop table without dropping foreign key constraint, foreign key means column of one table refers primary key of another table. This relationship called a Foreign key relationship, here primary table will act as parent table and secondary table called as child table. If you try to drop primary table (Parent ...

As SQL DBA, we often deal with orphan user related issues while moving database from one server to another. We got an email from client saying they are unable to connect database server after database movement … Login Eror Message was “Login failed for user ‘idb_dev’.(Microsoft SQL Server, Error: 18456)”.  also Error 15023: User already exists in ...

Grant permission using sql server certificate to execute system stored procedure like sp_updatestats. Sql server certificate is helpful to provide access to different users to execute certain stored procedure which require exclusive privilege after valid authentication method. Example 1. Create a stored procedure to execute sp_updatestats USE [master] GO CREATE PROCEDURE [dbo].[sp_updatestats_user] AS BEGIN exec ...

To keep track on database transaction log growth, you can use DBCC SQLPERF command that monitors transaction log space used in the database. Log file size is depends on recovery model of database and backup job.  Frequently running transaction log backup can keep transaction log in good shape. Sql server has dbcc sqlperf command to track ...

Sp_msforeachtable SQL Server has sp_msforeachtable as undocumented stored procedure that can be used to run query against all table in single database. Lets say you want to get the record count from each table so in that case you can use below command to generate the list of tables as well as record count. Example ...