Deadlock Query

SELECT  L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction, as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
–WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

SQL Server High Avaibility solutions Overview

High Level Differences between SQL High availability solutions


Description Log-Shipping Replication Database Mirroring
Latency >1min(Based on the transactional log backup size) Potentially as low as a few seconds Potentially < 1 min
Causes schema alterations to be made at the publisher No Plain Transactional – no; Updating subscribers – yes No
Causes schema alterations to be made at the subscriber No Possibly (see text) No
Requires schema properties No Primary keys are  required for transactional table articles(Only tables which have primary keys can be used for replication) No
Selection of individual objects possible(For ex. Tables, stored procedures, views) No Yes No
Subscriber database “protected”*Subscriber = DR database Yes No Yes
System data transferred Mostly No Yes
Can the subscriber server be used as reporting server? Can be used Yes *PossiblyIt can be achieved through creating a snapshot of the DR database
Supports automatic failover No No Yes
Ease of implementation Simple Moderate Moderate

To fetch size of all Databases in SQL Server

For 2000:

ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sysdatabases d ON d.dbid = mf.dbid
WHERE d.dbid > 4 — Skip system databases

For 2005 & above:

ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sysaltfiles mf
INNER JOIN sys.databases d ON d.database_id= mf.database_id
WHERE d.database_id > 4 — Skip system databases

How to Rename SQL Server Cluster Virtual Server Name(Network name)

Before you begin the renaming process, review the items below.

  • SQL Server does not support renaming servers involved in replication, except in the case of using log shipping with replication. The secondary server in log shipping can be renamed if the primary server is permanently lost.
  • When renaming a virtual server that is configured to use database mirroring, you must turn off database mirroring before the renaming operation, and then re-establish database mirroring with the new virtual server name. Metadata for database mirroring will not be updated automatically to reflect the new virtual server name.

SQL Server 2005, 2008, 2008 R2

  1. Using Cluster Administrator, change the SQL Network Name to the new name.
  2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.
  3. Bring the SQL Server resource back online.

After a virtual server has been renamed, any connections that used the old name must now connect using the new name.

To verify that the renaming operation has completed, select information from either @@servername or sys.servers. The @@servername function will return the new virtual server name, and the sys.servers table will show the new virtual server name. To verify that the failover process is working correctly with the new name, the user should also try to fail the SQL Server resource over to the other nodes.

To minimize network propagation delay of a virtual server renaming operation, use the following steps:

  • Issue the following commands from a command prompt on the server node:
    ipconfig /flushdns
    ipconfig /registerdns
    nbtstat –RR

For SQL 2012, Additionally registry settings also should be changed.