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 how a record was deleted

Is there a way to tell who or what process deleted a record in a SQL table.
I know you can setup triggers or Profiler - but is there a way to see how a
record was deleted if you DO NOT have a trigger or Profiler already running?
This is SQL 2000 Standard, sp3a with default options and configuration.

ThanksHi rdraider,
It may help your cause
Step 1 : Login as sa or user with sa rights
Step 2 : Run a trace with minimum TSQL (Statement Start , Statement
Complete)
If you wish to know from where(EM or SQL Query Analyzer) record is
deleted then the column Applicationname of Trace output will help you.

With warm regards
Jatinder|||If you have log backups, then there are several third-party tools which
can read them and recover information about when data was deleted. For
example:

http://www.lumigent.com/products/le_sql.html

Simon

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!