How to flush the buffer to trc file

I want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)

After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.

After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.

How can I flush the buffer to trc file frequently without need of stopping trace process?

Thanks for helps in advance.

LeonHere is the script of sp_login_trace (Leon)

CREATE PROCEDURE sp_login_trace
AS
BEGIN
/************************************************** **/
/* The following statements contain the SQL Server Profiler-generated */
/* script to create the trace with the required events and data columns. */
/************************************************** **/
-- Create a queue
DECLARE @.rc int
DECLARE @.TraceID int
DECLARE @.maxfilesize bigint
SET @.maxfilesize = 5
EXEC @.rc = sp_trace_create @.TraceID output, 2,
N'C:\Allprojects\SQL_Server_2000\login_trace',
@.maxfilesize, NULL
IF(@.rc != 0) GOTO error

-- You can't script the client-side file and table.
-- Set the events.
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 14, 1, @.on
exec sp_trace_setevent @.TraceID, 14, 6, @.on
exec sp_trace_setevent @.TraceID, 14, 9, @.on
exec sp_trace_setevent @.TraceID, 14, 10, @.on
exec sp_trace_setevent @.TraceID, 14, 11, @.on
exec sp_trace_setevent @.TraceID, 14, 12, @.on
exec sp_trace_setevent @.TraceID, 14, 13, @.on
exec sp_trace_setevent @.TraceID, 14, 14, @.on
exec sp_trace_setevent @.TraceID, 14, 16, @.on
exec sp_trace_setevent @.TraceID, 14, 17, @.on
exec sp_trace_setevent @.TraceID, 14, 18, @.on

-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint

exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'

-- Set the trace status to start.
EXEC sp_trace_setstatus @.TraceID, 1

GOTO finish

error:
SELECT ErrorCode=@.rc

finish:
END

GOsql

How to find table from pageno in trace 1204 output?

We have trace output (trace flags 1204/1205) showing that
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
Value:0x533e9d00 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
Value:0x5344cb60 Cost:(0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
Value:0x5341ebc0 Cost:(0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec:(0x5CD00098)
> Value:0x533e9d00 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec:(0x63C1D528)
> Value:0x5344cb60 Cost:(0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec:(0x5CD02098)
> Value:0x5341ebc0 Cost:(0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>

How to find table from pageno in trace 1204 output?

We have trace output (trace flags 1204/1205) showing that
a deadlock occurred on a page (PAG).
How can we detemine ** which table ** the page belongs to?
(PAG is represented as PAG:db_id:file_id:page_no)
TIA, -- Brian
Here is the trace output:
Deadlock encountered ... Printing deadlock information
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Wait-for graph
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:1
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
Statement Type: DELETE Line #: 12
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:4 Ec0x5CD00098)
Value:0x533e9d00 Cost0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:2
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
2004-02-11 15:22:31.09 spid4 Grant List 0::
2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
Statement Type: INSERT Line #: 118
2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
sp_executesql;1
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: IX SPID:70 ECID:0 Ec0x63C1D528)
Value:0x5344cb60 Cost0/43544)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 Node:3
2004-02-11 15:22:31.09 spid4 PAG:
7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
2004-02-11 15:22:31.09 spid4 Wait List:
2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
2004-02-11 15:22:31.09 spid4 Requested By:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
Value:0x5341ebc0 Cost0/0)
2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
2004-02-11 15:22:31.09 spid4 ResType:LockOwner
Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
Value:0x5341ebc0 Cost0/0)
2004-02-11 15:22:31.09 spid4
2004-02-11 15:22:31.09 spid4 End deadlock search
1082 ... a deadlock was found.
2004-02-11 15:22:31.09 spid4 --
--Hi Brian
You can use dbcc page() to dump the page header, read the object id & follow
it back through index, to the table etc. Keep in mind that there are various
page types, but given this is a deadlock resource coming from a delete, it's
likely an index / table page.
There's a utility at www.sqlfe.com which helps with reading raw pages,
rather than using dbcc page..
HTH
Regards,
Greg Linwood
SQL Server MVP
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:f1ff01c3f113$4551fea0$a601280a@.phx.gbl...
> We have trace output (trace flags 1204/1205) showing that
> a deadlock occurred on a page (PAG).
> How can we detemine ** which table ** the page belongs to?
> (PAG is represented as PAG:db_id:file_id:page_no)
> TIA, -- Brian
> Here is the trace output:
> Deadlock encountered ... Printing deadlock information
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Wait-for graph
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:1
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x28090dc0 Mode:
> IX Flg:0x0 Ref:69 Life:02000000 SPID:70 ECID:0
> 2004-02-11 15:22:31.09 spid4 SPID: 70 ECID: 0
> Statement Type: DELETE Line #: 12
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:4 Ec0x5CD00098)
> Value:0x533e9d00 Cost0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:2
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241034 CleanCnt:1 Mode: SIU Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Grant List 0::
> 2004-02-11 15:22:31.09 spid4 Owner:0x5341eba0 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:3
> 2004-02-11 15:22:31.09 spid4 SPID: 65 ECID: 3
> Statement Type: INSERT Line #: 118
> 2004-02-11 15:22:31.09 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: IX SPID:70 ECID:0 Ec0x63C1D528)
> Value:0x5344cb60 Cost0/43544)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 Node:3
> 2004-02-11 15:22:31.09 spid4 PAG:
> 7:1:2241035 CleanCnt:2 Mode: IX Flags: 0x2
> 2004-02-11 15:22:31.09 spid4 Wait List:
> 2004-02-11 15:22:31.09 spid4 Owner:0x533e9d00 Mode:
> S Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:4
> 2004-02-11 15:22:31.09 spid4 Requested By:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
> Value:0x5341ebc0 Cost0/0)
> 2004-02-11 15:22:31.09 spid4 Victim Resource Owner:
> 2004-02-11 15:22:31.09 spid4 ResType:LockOwner
> Stype:'OR' Mode: S SPID:65 ECID:3 Ec0x5CD02098)
> Value:0x5341ebc0 Cost0/0)
> 2004-02-11 15:22:31.09 spid4
> 2004-02-11 15:22:31.09 spid4 End deadlock search
> 1082 ... a deadlock was found.
> 2004-02-11 15:22:31.09 spid4 --
> --
>

How to filter Profiler trace by dbid in 2005?

Is there any way to filter a SQL Profiler trace by dbid? This was a feature of earlier versions, but I can't figure it out in 2005.

Thanks!

Hi there.

From the Profiler GUI, create a new trace, move to the Events Selection tab, click the Show all Columns check box (this is important, as the filters box will only show columns that are selected in the interface), then click the Column Filters button in the lower right corner of the box, you should see the DatabaseID option about 1/3 of the way down the list box on the left...click it, then set the appropriate value in the text box in the lower right of the screen...

HTH,

|||

Yep... it's a little tricky because it's not shown by default.

Steps:

1) Create a new trace.
2) Click the Events Selection tab.
3) Check the box Show All Columns.
4) Click the Column Filters... button.
5) Choose DatabaseID from the listbox on the left.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Aha! This is one of those 2 step processes. Thanks a lot!