Databases Reference
In-Depth Information
Run the script in a Windows PowerShell console:
. C:\DBAScripts\dbaLib.ps1
C:\DBAScripts\MonitorErrorLog.ps1
While the script is running, open a SQL Server Management Studio (SSMS) query window and run the
following SQL query on the default instance to generate an error message with severity 16:
Use AdventureWorks2008
Raiserror ('This is a test message', 16, 1) with log
Figure 10-1 shows the output after the script captures the error.
Figure 10-1
As shown in the output, the login PowerDomain\PowerUser connects with the default instance
MSSQLSERVER
on
POWERPC
in a session with
SPID 52
. The login causes an error with error number
50000
and severity
16
in the
AdventureWorks2008
database. The
TextData
column provides the complete error
information. After you are notified of the errors from the SQL Server error log, you can take appropriate
action.
Monitoring Deadlocks
A deadlock occurs when two or more sessions permanently block each other because each session has
a lock on a resource which the other sessions are trying to acquire. The SQL Server Database Engine
has a lock monitor thread that periodically initiates a search through all of the tasks to detect deadlocks.
After a deadlock is detected, the Database Engine ends a deadlock by choosing one of the threads as
a deadlock victim, and a 1205 error is thrown by SQL Server. To collect information about the threads
and the resources involved in the deadlock, you can monitor the
DEADLOCK_GRAPH
trace event class. The
MonitorDeadlocks.ps1
script defines the event query, the namespace for the default instance, and the
properties of deadlock events:
Search WWH ::
Custom Search