Find Blocking Information in Sql Server

--To Get wait stats information
SELECT * 
FROM sys.dm_os_wait_stats
GO
/*
runnable
running
suspended

sp_who2
to get the sql text 
dbcc inputbuffer(58)

 1 2 3 4
 */
 --blocking finding  query

SELECT  blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM    sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
                              blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
                              blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
-- out put from blocking information query                   
--ridlock fileid=1 pageid=171 dbid=14 id=lock80126980 mode=X associatedObjectId=72057594039042048
-- to get the database name and object name 
-- find the datbase name using dbid
select * from  sys.databases
---ridlock fileid=1 pageid=171 dbid=14 id=lock82366580 mode=X associatedObjectId=72057594039042048

-- find the object id that causing blocking
use hari2
go
SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id) 
FROM sys.partitions 
WHERE hobt_id IN (72057594039042048);

-- Getting blocking session id with username
SELECT  blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text,
sysp.loginame AS blockingLoginname,
sysp1.loginame as BlockedLoigname
FROM    sys.dm_exec_connections AS blocking
inner join master..sysprocesses sysp
on blocking.session_id = sysp.spid
INNER JOIN sys.dm_exec_requests blocked
inner join master..sysprocesses sysp1
on blocked.session_id = sysp1.spid
ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
                              blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
                              blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id

-- Root blocking information(Root id)
select distinct blocked as [Blocking Process] from sys.sysprocesses
where  blocked != 0 and blocked not in (select spid from sys.sysprocesses where blocked != 0)

Steve Jones' 10 Tips for Efficient Disaster Recovery

Steve Jones' 10 Tips for Efficient Disaster Recovery

Renaming the database

in this example I am renaming the test2 database to test3 database

alter database [test2]
set single_user

GO
alter database [test2]
modify name = [test3]

alter database [test3]
set multi_user

How to rebuild or Restore corrupted master database?

There are some times master database needs to rebuild due to corrupt or some other issues. In real world every day your backup jobs will be running and do backups for your application databases along with system databases..

Here are the steps you can follow how you can rebuild the master database.

1) find setup  C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release. and run the following command

setup /ACTION=REBUILDDATABASE /QUIET /INSTANCENAME=<instance name> /SQLSYSADMINACCOUNTS=<accounts>

once the command is executed and message will display databases are rebuild.

2) Now restore good backups of master,model,msdb databases in order to run properly according to your sql server original configurations.

Blocking Locks and Deadlocks

Blocking Locks

A blocking lock occurs when one lock causes another process to wait in a holding queue until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable.

Deadlocks

Consider the situation where one partially finished transaction must wait for another transaction to complete. At the same time, the other partially finished transaction must also wait for the original transaction to complete. This is called a deadlock: when each transaction is waiting for resources used by the other. When such a deadlock occurs, the database typically cancels one of the transactions.

Lock-Avoiding Design Strategies

There are a few design strategies that can reduce the occurrence of blocking locks and deadlocks:

• Use clustered indexes on high-usage tables.
• Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
• Break long transactions up into many shorter transactions. With SQL Server, you can use "bound connections" to control the execution sequence of the shorter transactions.
• Make sure that UPDATE and DELETE statements use an existing index.
• If you use nested transactions, be sure there are no commit or rollback conflicts.

How do you attach a mdf data file without a log file?

Make sure copy the .mdf data file into a folder where you will attach database. in my example I have copied under C:\data folder and run the following script.

CREATE DATABASE [AdventureWorksDW2012] ON 
( FILENAME = N'C:\Data\AdventureWorksDW2012_Data.mdf' )
 FOR ATTACH_REBUILD_LOG
GO

Return SQLResult set into a HTML format.(Microsoft Sql server)

Please find the following script, which will show the backup status of db's in html format.

                                           
-- 
























=============================================
-- HariPrasad Ere 08/06/2013
-- Send BackUpStatus For each db
-- =============================================

--Create a Temp table to insert last backcompleted records.
USE msdb 
CREATE TABLE #Backuptbl
(
backup_finish_date DATETIME,
database_name      VARCHAR(50)
)
--Insert the previous day backup completed records into #Backuptable 

INSERT INTO #Backuptbl (backup_finish_date,database_name )
SELECT MAX(backup_finish_date) AS BackUp_Finish_Date ,database_name
FROM backupset b inner join
sys.databases d
ON b.database_name = d.name 
WHERE backup_finish_date < GETDATE() 
GROUP BY database_name 
ORDER BY database_name DESC

--Create Email Message

DECLARE @Subject VARCHAR(100)
SET @Subject = ' DataBase BackUp Status' + CONVERT(VARCHAR(10),GETDATE()-1,101)
Declare @Message VARCHAR(MAX)
SET @Message = 'Team, <BR> <BR>' 
SET @Message = @Message + CHAR(13) + CHAR(10) + 'LAST BackUp Finish Date : ' 

SET @Message = @Message + CHAR(13) + CHAR(10) + '<Table border = 1 width = 50%>'
SET @Message = @Message + CHAR(13) + CHAR(10) + 
'<tr><td width  = 25%><font face = Arial align = left ><B>DataBaseName</B></font></td><td width  = 25%><font face = Arial align = left><B>backup_finish_date</B></font></td></tr>'

Select @Message = @Message + CHAR(13) + CHAR(10) +'<td><font face = Arial>' + database_name

+ '</font></td>' +'<td nowrap><font face = Arial>' +  CONVERT(VARCHAR(10),backup_finish_date, 101)+ '</font></td></tr>'

FROM #Backuptbl
--copy the fllowing text message and paste into a notepad, Save the notepad as HTMl. open the saved html to view the results. you can call variable@message from sp_sendmail master db stored procedure.
SELECT @message
--clean up the objects
DROP  TABLE #Backuptbl