我已经设置了SQL Server扩展事件来捕获特定数据库中最近的1000个失败查询并将其存储在环形缓冲区中:
Create Event Session [Errors] on Server
Add Event sqlserver.error_reported (
Action(
sqlos.task_time,
sqlserver.sql_text
)
Where sqlserver.database_name=N'MyDatabase'
And error_number<>5701 -- Ignore changed db context messages
)
Add target package0.ring_buffer(Set max_memory=102400)
With (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON
)
Go
我现在可以这样查询这些错误:
Select
xmldata = Cast(xet.target_data as xml)
Into #rbd
From sys.dm_xe_session_targets xet
Join sys.dm_xe_sessions xe ON (xe.address = xet.event_session_address)
Where xe.name='errors' and target_name='ring_buffer'
; With Errors as (
Select
e.query('.').value('(/event/@timestamp)[1]', 'datetime') as "TimeStamp",
e.query('.').value('(/event/data[@name="message"]/value)[1]', 'nvarchar(max)') as "Message",
e.query('.').value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') as "SqlText",
e.query('.').value('(/event/action[@name="task_time"]/value)[1]', 'bigint')/@@TIMETICKS as "Duration"
From #rbd
Cross Apply XMLData.nodes('/RingBufferTarget/event') as Event(e)
)
Select * from Errors
Where SqlText is not null
Drop Table #rbd
目前为止,一切都很好:
这已经对跟踪应用程序级别抛出的错误细节有很大帮助了。
不过,如果我们能够像在SQL Profiler中一样看到传递给这些查询的参数值,那将会使它变得更加有用。因此,当我们看到这样的错误时...
Conversion failed when converting date and/or time from character string.
对于这个SQL查询,如果你想要查找一个列中包含特定字符的行,可以使用LIKE运算符。例如,如果你想要查找名字中包含"John"的人,可以使用以下代码:
(@NewValue nvarchar(10),@KeyValue int)
Update SomeTable
Set SomeField=@NewValue Where SomeTableID=@KeyValue
了解@NewValue
和@KeyValue
参数的值将很有帮助。
你有什么想法可以找到这些信息吗?