manjunathcbhat's blog

Simulate Blocking in SQL Server

There can be various scenarios where a DBA Needs to simulate Blocking in SQL Server.

Below are the Step by Step Instruction to simulate blocking in SQL Server

Step1:

Lets Create a Table and Insert Some Values in it to start our Blocking SImulates

Open a New Query Window and Execute the below code.

CREATE TABLE SIMULATEBLOCK
(
ID int identity(1,1)
,Value nvarchar(50)
)
GO
INSERT INTO SIMULATEBLOCK(Value) values
('FirstValue')
,('SecondValue')
,('ThirdValue')
,('FourthValue')
GO
Select * from SIMULATEBLOCK

Step2:

SQL Server Audit feature

SQL Server Audit feature is one of the powerful and good feature provided by Microsoft to audit server-level and database-level groups of events and individual events. You can find more information on this in BOL or here at microsoft site http://msdn.microsoft.com/en-us/library/cc280663.aspx

In this post, I am going to explain how to deal with Inbuilt SQL Server Audit feature.

To setup and use this SQL Server Audit feature, First You need to follow below steps.

SQL Server DDL Audit Server Level

In my previous post, SQL Server Database DDL Audit I had written on how to track/audit DDL changes on Database level, which would track any DDL statement issued against the user databases and store in a Table.

Now in this Post I will post on how to track DDL Statements issued on Server. This can help you track DDL Changes done on Server level such as create database, drop database etc. Its again a Server level Trigger.

Code:

SQL Server Database DDL Audit

How many times as a DBA or business needs we need to Audit our database for DDL changes occured. It very much necessary to keep track of who is doing DDL Changes to your database and what are the DDL changes being done. There isnt any direct way to achieve this and if you want to go way forward you need to look out for some third party tools.

But, thanks to Microsoft, there is a tricky and accepted method of setting up the DDL Audit for your databases.

Below is the piece of code which will help you achieve in creating audting process for all of your user databases.

T-SQL to Find all tables without any Index

Its worst practice always not to have any index on database. Based on requirement the table should contain necessary Indexes to make data retrieval faster.

Below is the script which will give you all tables which are not having any Index defined on it.

select [name] as [Table_Name] from sys.objects
Where type = 'U' and [name] not in
(SELECT OBJECT_NAME(i.object_id)
 FROM sys.indexes AS i
 INNER JOIN sys.index_columns AS ic
 ON i.OBJECT_ID = ic.OBJECT_ID
 AND i.index_id = ic.index_id
 INNER JOIN sys.objects as so
 on i.OBJECT_ID = so.OBJECT_ID
 WHERE so.type = 'U' )

Pages