Find the open transaction details in Sql Server

The below query can be used to get the details of OPEN TRANSACTION:

SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0

How to get the size of the database

Today i am posting the methods to find the size of database through the sql statement.
Method 1: Using sp_helpdb
The below statement gives the information about all the databases:
EXEC sp_helpdb

whereas the below gives the information about a specified database (in this case about master database):
EXEC sp_helpdb N'master'

Method 2: Using sys.master_files
It is a system view which contains a row per file of a database as stored in the master database:
SELECT DB_NAME(database_id), SUM(size)*8/1024 as size_in_MB FROM sys.master_files GROUP BY database_id

How to find the unused indexes in Sql Server

SELECT OBJECT_NAME(si.OBJECT_ID) as TableName, si.NAME as IndexName, si.INDEX_ID as IndexID
FROM
sys.indexes si INNER JOIN sys.objects so ON si.object_id = so.object_id
WHERE OBJECTPROPERTY(si.object_id,'IsUserTable') = 1 AND index_id NOT IN
(
SELECT index_id FROM sys.dm_db_index_usage_stats sdmv
WHERE sdmv.object_id = si.object_id AND sdmv.index_id = si.index_id
)

XQuery Language

While looking into XQuery, I found it very interesting and decided to share this with you by posting it into the blog.
XQuery language that is used for querying the xml data type. XQuery is a language that can query structured or semi-structured XML data. With the xml data type support provided in the Database Engine, documents can be stored in a database and then queried by using XQuery.

Example:

Lock escalation

Lock escalation is the process of converting many row/page locks into table locks. Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, Microsoft SQL Server dynamically determines when to perform lock escalation. When making this decision, SQL Server takes into account the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that is being used for locks in the system as a whole.

Transaction Isolation Levels

SQL Server's isolation models each attempt to conquer a subset of these problems, providing database administrators with a way to balance transaction isolation and business requirements. The five SQL Server isolation models are:

* The Read Committed Isolation Model is SQL Server’s default behavior. In this model, the database does not allow transactions to read data written to a table by an uncommitted transaction. This model protects against dirty reads, but provides no protection against phantom reads or non-repeatable reads.
* The Read Uncommitted Isolation Model offers essentially no isolation between transactions. Any transaction can read data written by an uncommitted transaction. This leaves the transactions vulnerable to dirty reads, phantom reads and non-repeatable reads.
* The Repeatable Read Isolation Model goes a step further than the Read Committed model by preventing transactions from writing data that was read by another transaction until the reading transaction completes. This isolation model protect against both dirty reads and non-repeatable reads.
* The Serializable Isolation Model uses range locks to prevent transactions from inserting or deleting rows in a range being read by another transaction. The Serializable model protects against all three concurrency problems.
* The Snapshot Isolation Model also protects against all three concurrency problems, but does so in a different manner. It provides each transaction with a "snapshot" of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.

If you need to change the isolation model in use by SQL Server, simply issue the command:

SET TRANSACTION ISOLATION LEVEL

where is replaced with any of the following keywords:

* READ COMMITTED
* READ UNCOMMITTED
* REPEATABLE READ
* SERIALIZABLE
* SNAPSHOT