Thursday, May 22, 2014

SP_WHO in SQL Server

Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.

SP_WHO

OR

SP_WHO2

OR

SP_WHO3

OR

SP_WHOISACTIVE


Monday, May 5, 2014

SQL Script to Delete Millions of Record

DECLARE @continue int
DECLARE @rowcount int
Declare @total BIGINT

Select @total = count(*) from Tablename WITH (NOLOCK) where condition
While @total>0
BEGIN
SET @continue = 1
WHILE @continue = 1
BEGIN
SET ROWCOUNT 10000

BEGIN TRANSACTION
Delete from Tablename where CONDITION
SET @rowcount = @@rowcount 
COMMIT
 
IF @rowcount = 0
BEGIN
SET @continue = 0
END
END
Set @total =@total-10000
END