How can I Truncate/Clear SQL Server Error Log ?

We may have faced an issue like SQL Server error log is getting filled with entries...!

So, We just want to clear the error log entries.. right ?

Oh.. Wait!

First, Tell me that How to read the entries from my SQL Server Error Log ?  (from Active/current file) then we can move into further ...:)

Read SQL Server Error log (Active file)
EXEC master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'

Ok. Then how to write a new entry into the SQL server Error log ?

Log an entry into SQL Server Error log
RAISERROR('SQL Server Buddy',16,1) WITH LOG

Now,  Can we go further to clear the log entry ? YES

we can use either...

To clear SQL Server error log
DBCC errorlog
--or
EXEC sp_cycle_errorlog

NULL = NULL

One of my friend had discussion with me some days back that NULL is not equal to another NULL...! 

Is that TRUE ?

I said that "NO, IT IS NOT ALWAYS...!"

What that means.. "NOT ALWAYS" ?

In SQL Server, we have SET option called "ANSI_NULLS"

Is there any way to identify that what are all the options have been set to ON ?

DBCC USEROPTIONS
GO


Its a session specific option. So, It will list out What are all the user options have been set to ON along with some other options!

OK.

NULL is NOT EQUAL to another NULL. How ? 


When ANSI_NULLS is set to ON

SET ANSI_NULLS ON
GO


IF (NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT
'NOT EQUAL'
GO


Result : NOT EQUAL

 

NULL is EQUAL to another NULL. How ? 

When ANSI_NULLS is set to OFF

SET ANSI_NULLS OFF
GO

IF
(NULL=NULL)
    PRINT 'EQUAL'
ELSE
    PRINT
'NOT EQUAL'
GO

Result : EQUAL


So, NULL is not equal to another NULL - NOT ALWAYS, It depends on ANSI_NULLS option!

Unique Vs. Primary Key - Referential Integrity - Have you tried...?

When we create a Referential integrity, Parent column can be a PRIMARY KEY or UNIQUE column!
 

OK.
1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(10)
)
GO

2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO

So, The above statement uses only the Parent Table name. But, not Primary Key column.

By default, It refers the PRIMARY KEY column of the reference table(MasterTable1). So, no need to give the PRIMARY KEY column name explicitly.

But, Have you tried with UNIQUE key for the same scenario ?


1. Crete a Parent Table
CREATE TABLE MasterTable1
(
Id    INT IDENTITY(1,1) UNIQUE,
Column1 VARCHAR(10)
)
GO


2. Create a Child table & Refer MasterTable1
CREATE TABLE ChildTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1,
Column2 VARCHAR(10)
)
GO

You will get an Err message!

Msg 1773, Level 16, State 0, Line 1
Foreign key 'FK__ChildTabl__Maste__658C0CBD' has implicit reference to object 'MasterTable1' which does not have a primary key defined on it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


So, when referring an UNIQUE column, It should be TableName(ColumnName)


CREATE TABLE ChildTable1
(
Id    INT IDENTITY(1,1) PRIMARY KEY,
MasterID INT FOREIGN KEY REFERENCES MasterTable1(Id),
Column2 VARCHAR(10)
)
GO


So, Using TableName(columnName) is mandatory when referring an UNIQUE key column!!!
Simple way to identify the SQL Server service is running or not!

CREATE TABLE #Services(ServerName VARCHAR(255))

INSERT  #Services
EXEC  xp_cmdshell 'NET START'

IF  EXISTS (SELECT 1 FROM #Services WHERE ServerName LIKE '%SQL SERVER%')
   SELECT 'SQL Service is running' [Status]
ELSE
   SELECT 'SQL Service is not running' [Status]


DROP  TABLE #Services
GO

Is there any way to added to the SQL Server wish list ?

YES!

Just send your feedback to sqlwish@microsoft.com and these feedbacks/ideas gathered this way is used to select features that go into new SQL Server version!

How to get GMT (Greenwich Mean Time) rather than current time in earlier version of SQL Server ?

In SQL Server 2000 only a new function introduced to get GMT

But, What about the earlier version of SQL Server 2000/Before SQL Server 2000 ?

Before SQL Server 2000
DECLARE @GMTTimeZone INT
EXEC MASTER.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias',
@GMTTimeZone OUT

SELECT DATEADD(MINUTE,@GMTTimeZone, GETDATE()) [ANCIENT METHOD]
GO


SQL Server 2000 onwards
SELECT GETUTCDATE() [GETUTCDATE()]
GO

Note: This method applies to all SQL Server versions

Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75 Could not drop article. A subscription exists on it.

When we try to delete a Publication, It says "Could not drop article. A subscription exists on it". But, subscriber also deleted! (Still was getting the following Err when I delete the Publication!)

So, I Tried to delete the article first using the script given below!

Use  PublicationDB
Go
sp_droparticle  @publication = N'Publication Name',

@article = N'Article Name',
@force_invalidate_snapshot = 1
Go


But, I could not delete the article! and occured the following Error message!

Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

But, No subscription was not there for the Publication!

So, I could realize that the Subscription not removed properly but may be partially(May be some of the flag not updated internally)!

So, I tried to remove the Subscription using the following way...!

Use  distribution
Go
Delete
MSS From MSsubscriptions MSS JOIN msarticles MSA

On (MSS.article_id = MSA.article_id)
Where MSA.article ='Article Name you trying to Delete'
And MSS.publisher_db ='Publisher DB Name'
Go

I removed the subscription for the particular publication and I tried to remove the article now

Use PublicationDB
Go

sp_droparticle @publication = N'Publication Name',
@article = N'Article Name',
@force_invalidate_snapshot = 1
Go

Wow...! I removed the article successfully from the Publication without any error!

Finally, I could able to remove the publication also without any error! :)

Where is the Object ? In which Database ?

I want to use one stored procedure or any other object name contains 'Blog' from current Server or Instance.

But, I don't know where is/are the procedure or object actually exists! or In which database ?

Is there any way to search an object in all the databases in current Instance ?
 
Use  Master
Go
Declare
@WhereIs Table

(
DBName Varchar(100),
ObjectName Varchar(150),
ObjectType Varchar(150)
)

Insert @WhereIs
Exec sp_msforeachdb 'use [?]; select DB_NAME(), name, Type_desc from sys.objects where name like ''%Blog%'''

Select * from @WhereIs
Go

Result:
 

Restoring Database - while the database is in use!

The following error will be thrown...! When try to restore a database while the same being used/accessed.

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)

The reason is very common as the Database being tried to restore is/are used/accessed somehere else through SSMS/Application(s)...

But, somebody says that, "First change the Database restrict access to SINGLE USER Mode and try to restore the same instead of KILLing the opened/accessed connections(SPIDs)"

Is that True ?

I don't think so!

Because, When you try to change the database restrict access to SINGLE USER mode using the below script/using the wizard. It'll close/KILL the opened/accessed sessions(SPID)

USE [Master]
GO
ALTER
DATABASE [DatabaseName] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

Because, "SQL Server will close all other connections to the database" before change the Restrict access mode to SINGLE USER mode!

So, Changing the Database restrict access mode to SINGLE_USER will also close(KILL) all the connection(s) to the database!

Identifying DeadLock Issue/Rootcause using Extended Events!

SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. It has been intorduced in SQL Server 2008!

So, We don't want to enable/apply Trace Flag on StartUp parameters! to log detailed DeadLock info in SQL Server Error Log. Instead, DeadLock info can be retrived using Extended Events!

It has some issue with system_health session! But, Still it can be used...

/*Sample Table and test data*/
Create Table Table1
(
Id Int Identity(1,1),
Column1 Varchar(25)
)
Go

Create Table Table2
(
Id Int Identity(1,1),
Column1 DateTime
)
Go

Insert
Table1(Column1) Values('SQL Server')

Insert Table2(Column1) Values(GETDATE())
Go

/*Session #1(i.e: 59)*/
Begin Tran
Update Table1 Set Column1 ='SQL Server Buddy' Where Id=1
Waitfor Delay '00:00:02'
Update Table2 Set Column1 =GETDATE() Where Id=1


/*Session #2(i.e: 61)*/
Begin Tran
Update Table2 Set Column1 =GETDATE() Where Id=1
Waitfor Delay '00:00:02'
Update Table1 Set Column1 ='SQL Server Buddy' Where Id=1

/*DeadLock occurred in one of the session*/
Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


/*Trackdown and Extract the DeadLock info from Extended Events*/
If OBJECT_ID('DeadLockInfo') Is Null
Create Table DeadLockInfo
(
Id Int Identity(1,1),
DeadLockInfo XML
)
Go

If
OBJECT_ID('DeadLockInfo_Temp') Is Null
Create Table DeadLockInfo_Temp
(
Id Int Identity(1,1),
DeadLockInfo NVARCHAR(MAX)
)
Go

Truncate
Table DeadLockInfo
Truncate Table DeadLockInfo_Temp
Go

Declare
@Max Int, @Min Int

Insert DeadLockInfo_Temp(DeadLockInfo)
select
REPLACE(
REPLACE(XED.X.value('(data/value)[1]', 'varchar(max)'),
'', ''),
'','
') as DlockInfo
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XED (X)
where XED.X.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Select @Max = @@IDENTITY
Select @Min = 1

While(@Min <= @Max)
Begin
Begin Try
Insert DeadLockInfo(DeadLockInfo)
Select DeadLockInfo from DeadLockInfo_Temp where Id = @Min
End Try
Begin Catch
--Just Skip the Error/Unformated Record
End Catch

Select @Min = @Min +1

End

;With CTE
As
(
Select DeadLockInfo.value('(deadlock-list/deadlock/victim-list/victimProcess/@id)[1]','varchar(20)') [Victim Process],
DeadLockInfo.value('(deadlock-list/deadlock/process-list/process/@spid)[1]','Int') [SPID1]
From DeadLockInfo
), FinalCTE
AS
(
Select Id [Batch No],case when [Victim Process] Is Not Null Then 'Yes' Else 'No' End [Victim Process],[Process],[Wait Resource],[Wait Time],[Transaction Name],[Lock Mode],[SPID],[Batch Started],
[Batch Ended],[Client Application],[Hosted From],[Login],[Isolation],[DBID],[Proc Name],[Line],[SQL Handle],
[Script] from
(

Select Id,
DeadLock.Node.value('(@id)[1]','varchar(20)') [Process],
DeadLock.Node.value('(@waitresource)[1]','varchar(50)') [Wait Resource],
DeadLock.Node.value('(@waittime)[1]','BigInt') [Wait Time],
DeadLock.Node.value('(@transactionname)[1]','varchar(100)') [Transaction Name],
DeadLock.Node.value('(@lockMode)[1]','varchar(25)') [Lock Mode],
DeadLock.Node.value('(@spid)[1]','Int') [SPID],
DeadLock.Node.value('(@lastbatchstarted)[1]','Datetime') [Batch Started],
DeadLock.Node.value('(@lastbatchcompleted)[1]','Datetime') [Batch Ended],
DeadLock.Node.value('(@clientapp)[1]','Varchar(100)') [Client Application],
DeadLock.Node.value('(@hostname)[1]','Varchar(100)') [Hosted From],
DeadLock.Node.value('(@loginname)[1]','Varchar(100)') [Login],
DeadLock.Node.value('(@isolationlevel)[1]','Varchar(100)') [Isolation],
DeadLock.Node.value('(@currentdb)[1]','Int') [DBID],
DeadLock.Node.value('(executionStack/frame/@procname)[1]','Varchar(100)') [Proc Name],
DeadLock.Node.value('(executionStack/frame/@line)[1]','Int') [Line],
DeadLock.Node.value('(executionStack/frame/@sqlhandle)[1]','Varchar(50)') [SQL Handle],
DeadLock.Node.value('(inputbuf)[1]','Varchar(Max)') [Script]
From DeadLockInfo Cross Apply DeadLockInfo.nodes('//deadlock-list/deadlock/process-list/process') DeadLock(Node)
) as A Left Join CTE On (A.Process = CTE.[Victim Process] And A.SPID = CTE.SPID1)
)

Select * from FinalCTE Order By [Batch No] Desc
Go

/*Result*/