Need your insights. Thank you for taking time and sharing. **Context** We have a 10 sql scripts which has more than 10000 lines of code and most of them are scripts and some code in each script are irrelevant **Problem** I need to identify which scripts and which parts of the script actually being applied on the DB so that we can re-structure the scripts . I at least need to share stats for my safety. I don't have any idea how to start - DML operations - DDL operations - Deadlocks I'm a junior DBA never had done this activity and I don't what compelled them to push it to me. May be I'm asking too much kindly at least push me in right direction so I can start somewhere.

I have captured a trace file using the replay template. It includes all columns available, of course including ERROR. When I replay this trace file, the "trace configuration" window pops up, I declare the target server, save the result to a new trace file and run the trace. When I then load the new trace file, most of the columns are gone, including the error column. I was hoping to filter on ERROR > 0 in the resulted trace file of the replay action to find queries having problems on the replayed-against server. Why are the columns not included anymore - and why does the new trace file contain not all events anymore but instead includes these "Replay Result Row Events" (e.g. Name = blabla...) ? Edit: Meanwhile I followed a suggestion to look at the trace file using a sql query: SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\my_trace.trc', default) to see ALL columns. Unfortunately here the columns are displayed but contain all a NULL value where I had expected to see values e.g. for ERROR.

I'm using a trace in Oracle (11.2) to elucidate the behavior of a client application, and would like to view a list of all of the SQL statements that it submits, in the order in which they were submitted. Toward this end, I enable the trace using: EXECUTE DBMS_SYSTEM.SET_EV(, , 10046, 12, '') This produces a trace file with the information I'm seeking, though in a format that is difficult for human consumption. Accordingly, I format the raw trace using: tkprof raw.trc formatted.out explain=user/password aggregate=no sys=no waits=yes record=formatted.rec This facilitates readability, but also obscures the real-world sequence of events in both of the output files (formatted.out, formatted.rec). Specifically, the client appropriately holds open the cursors for popular statements and re-executes them on subsequent invocations. The default (and only?) behavior of tkprof is to: - In the **main output** file, combine the stats for all executions - In the **record** file, only list the statement when initially parsed (The latter of which assumes I'm lucky enough to catch the parse event in the first place, which usually requires flushing the shared pool before enabling the trace, but I digress.) For example, if the client submits statements like the following and **holds the cursors open**: SELECT column1 FROM table1 WHERE column2 = :value; INSERT INTO table1 SET column1 = :value1, column2 = :value2; SELECT column1 FROM table1 WHERE column2 = :value; The output record file will only show two statements: SELECT column1 FROM table1 WHERE column2 = :value; INSERT INTO table1 SET column1 = :value1, column2 = :value2; There is no evidence, as far as I can tell, that the first statement was executed a second time. Similarly, the main trace file will only show reports for two statements, though the first one will indicate that the SELECT statement was executed/fetched twice (only a partial excerpt follows): call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 4 6 0 Fetch 2 0.00 0.00 0 0 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.00 0.00 0 4 6 2 It's not clear from this formatted output which statements were executed in which order - only that one of them was executed twice at some point. As far as I can see, I need to read the raw trace file and laboriously follow the trail of cursor numbers (some of which get reused for different statements over time) to understand the actual sequence of events, as they occurred in the real world. My question is: - **Is there some tkprof option, or tkprof alternative, that will extract from a raw trace file a list of all SQL statements in the order that they were executed, even in cases where the same cursor is held open and executed multiple times?** Ideally, this output would include the bind variable values for each execution, or at least the SQL ID or cursor ID, so I could go back and find them in the raw trace. Thanks in advance.

On Db2 v11.5.7.0 I would like to enable CLI trace and perform some tests. From official IBM documentation, CLI tracing can be done in two ways: a) dynamic b) with db2clini **A. DYNAMIC** 1. According to CLI documentation I did: db2trc on -cli -f dumpFile It returned: "Trace is turned on". I see file "dumpFile" is created with 465456 bytes. 2. Now I am reproducing the problem. But dumpFile does not increases in size at all. It looks like nothing is traced. 3. Turing off trace: db2trc off Output: "Trace is turned off". 4. Format from binary to readable file: db2trc fmt -cli dumpFile clitrcfile.txt and output is: Trace truncated : NO Trace wrapped : NO Total number of trace records : 0 Nothing has been captured. **B: WITH DB2CLI.INI** Now to db2cli.ini documentation: db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1 db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName C:\trace db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1 db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1 db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimeStamp 3 Now reproduce the problem and turn off tracing. db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 0 Nothing has been captured. **UPDATE:** What is my problem? I have establish SSL between Db2 client and Db2 server using CLI. I can confirm with Wireshark network sniffing tool that SSL is really working. Now I would like to add hostname validation for CLI applications. From my database alias test1 I have added hostname validation: db2 "UPDATE CLI CONFIGURATION FOR SECTION test1 USING SSLClientHostnameValidation Basic" Now when I test the connection: db2 connect to test1 user using connection works true, but it shouldn't, because at Db2 client I have cataloged "localhost" to point to my virtual machine Db2 server, but in SSL certificate's Subject Alternative Name is my FQDM. If host validation if performed then connection should fail. Now to the part of "reproducing problem". In the same window that "db2trc" or "db2 update cli" commands are executed I have executed "db2 connect to test1 user myuser using pass" and no CLI traces were produced.

I've written a SQL-Server Profiler script file in order to see why some database actions are unsuccessful. The idea behind the script is: - Show me the starting statements (where an application starts writing something in the database) - Show me the finishing statements (where the mentioned application has finished writing something in the database) - Show me the "Attention" ones (where something goes wrong) The script looks as follows:

`
DECLARE @TraceID INT;
DECLARE @MaxFileSize BIGINT = 50; -- File size limit in MB
DECLARE @TraceFilePath NVARCHAR(256) = N'C:\SQLTraces\Trace_OrderPart'; -- Base trace file path

-- Step 1: Create a new trace
EXEC sp_trace_create 
    @traceid = @TraceID OUTPUT,
    @options = 2, -- File rollover enabled
    @tracefile = @TraceFilePath,
    @maxfilesize = @MaxFileSize,
    @stoptime = NULL; -- No automatic stop


-- Step 2: Set events to capture
-- Add events to monitor SQL statements and related information
EXEC sp_trace_setevent @TraceID, 16,  1, 1; -- EventClass (SP:Attention) TextData
EXEC sp_trace_setevent @TraceID, 16,  4, 1; -- EventClass (SP:Attention) TransactionId
EXEC sp_trace_setevent @TraceID, 16,  9, 1; -- EventClass (SP:Attention) ClientProcessID
EXEC sp_trace_setevent @TraceID, 16, 10, 1; -- EventClass (SP:Attention) ApplicationName
EXEC sp_trace_setevent @TraceID, 16, 11, 1; -- EventClass (SP:Attention) LoginName
EXEC sp_trace_setevent @TraceID, 16, 12, 1; -- EventClass (SP:Attention) SPID
EXEC sp_trace_setevent @TraceID, 16, 13, 1; -- EventClass (SP:Attention) ClientProcessID
EXEC sp_trace_setevent @TraceID, 16, 14, 1; -- EventClass (SP:Attention) StartTime
EXEC sp_trace_setevent @TraceID, 16, 15, 1; -- EventClass (SP:Attention) EndTime
EXEC sp_trace_setevent @TraceID, 16, 20, 1; -- EventClass (SP:Attention) Severity
EXEC sp_trace_setevent @TraceID, 16, 23, 1; -- EventClass (SP:Attention) Success
EXEC sp_trace_setevent @TraceID, 16, 25, 1; -- EventClass (SP:Attention) IntegerData
EXEC sp_trace_setevent @TraceID, 16, 27, 1; -- EventClass (SP:Attention) EventClass
EXEC sp_trace_setevent @TraceID, 16, 31, 1; -- EventClass (SP:Attention) Error Number
EXEC sp_trace_setevent @TraceID, 16, 34, 1; -- EventClass (SP:Attention) ObjectName
EXEC sp_trace_setevent @TraceID, 16, 49, 1; -- EventClass (SP:Attention) RequestID
EXEC sp_trace_setevent @TraceID, 16, 55, 1; -- EventClass (SP:Attention) IntegerData2

EXEC sp_trace_setevent @TraceID, 40,  1, 1; -- EventClass (SP:StmtStarting) TextData
EXEC sp_trace_setevent @TraceID, 40,  4, 1; -- EventClass (SP:StmtStarting) TransactionId
EXEC sp_trace_setevent @TraceID, 40,  9, 1; -- EventClass (SP:StmtStarting) ClientProcessID
EXEC sp_trace_setevent @TraceID, 40, 10, 1; -- EventClass (SP:StmtStarting) ApplicationName
EXEC sp_trace_setevent @TraceID, 40, 11, 1; -- EventClass (SP:StmtStarting) LoginName
EXEC sp_trace_setevent @TraceID, 40, 12, 1; -- EventClass (SP:StmtStarting) SPID
EXEC sp_trace_setevent @TraceID, 40, 13, 1; -- EventClass (SP:StmtStarting) ClientProcessID
EXEC sp_trace_setevent @TraceID, 40, 14, 1; -- EventClass (SP:StmtStarting) StartTime
EXEC sp_trace_setevent @TraceID, 40, 15, 1; -- EventClass (SP:StmtStarting) EndTime
EXEC sp_trace_setevent @TraceID, 40, 20, 1; -- EventClass (SP:StmtStarting) Severity
EXEC sp_trace_setevent @TraceID, 40, 23, 1; -- EventClass (SP:StmtStarting) Success
EXEC sp_trace_setevent @TraceID, 40, 25, 1; -- EventClass (SP:StmtStarting) IntegerData
EXEC sp_trace_setevent @TraceID, 40, 27, 1; -- EventClass (SP:StmtStarting) EventClass
EXEC sp_trace_setevent @TraceID, 40, 31, 1; -- EventClass (SP:StmtStarting) Error Number
EXEC sp_trace_setevent @TraceID, 40, 34, 1; -- EventClass (SP:StmtStarting) ObjectName
EXEC sp_trace_setevent @TraceID, 40, 49, 1; -- EventClass (SP:StmtStarting) RequestID
EXEC sp_trace_setevent @TraceID, 40, 55, 1; -- EventClass (SP:StmtStarting) IntegerData2

EXEC sp_trace_setevent @TraceID, 41,  1, 1; -- EventClass (SP:StmtCompleted) TextData
EXEC sp_trace_setevent @TraceID, 41,  4, 1; -- EventClass (SP:StmtCompleted) TransactionId
EXEC sp_trace_setevent @TraceID, 41,  9, 1; -- EventClass (SP:StmtCompleted) ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 10, 1; -- EventClass (SP:StmtCompleted) ApplicationName
EXEC sp_trace_setevent @TraceID, 41, 11, 1; -- EventClass (SP:StmtCompleted) LoginName
EXEC sp_trace_setevent @TraceID, 41, 12, 1; -- EventClass (SP:StmtCompleted) SPID
EXEC sp_trace_setevent @TraceID, 41, 13, 1; -- EventClass (SP:StmtCompleted) ClientProcessID
EXEC sp_trace_setevent @TraceID, 41, 14, 1; -- EventClass (SP:StmtCompleted) StartTime
EXEC sp_trace_setevent @TraceID, 41, 15, 1; -- EventClass (SP:StmtCompleted) EndTime
EXEC sp_trace_setevent @TraceID, 41, 20, 1; -- EventClass (SP:StmtCompleted) Severity
EXEC sp_trace_setevent @TraceID, 41, 23, 1; -- EventClass (SP:StmtCompleted) Success
EXEC sp_trace_setevent @TraceID, 41, 25, 1; -- EventClass (SP:StmtCompleted) IntegerData
EXEC sp_trace_setevent @TraceID, 41, 27, 1; -- EventClass (SP:StmtCompleted) EventClass
EXEC sp_trace_setevent @TraceID, 41, 31, 1; -- EventClass (SP:StmtCompleted) Error Number
EXEC sp_trace_setevent @TraceID, 41, 34, 1; -- EventClass (SP:StmtCompleted) ObjectName
EXEC sp_trace_setevent @TraceID, 41, 49, 1; -- EventClass (SP:StmtCompleted) RequestID
EXEC sp_trace_setevent @TraceID, 41, 55, 1; -- EventClass (SP:StmtCompleted) IntegerData2

-- Step 3: Set a filter for the table CommandOrderParts
-- exec sp_trace_setfilter @TraceID, 34, 0, 6, N'%VehicleOrderPart%'; -- ObjectName filter
exec sp_trace_setfilter @TraceID,  1, 0, 6, N'%VehicleOrderPart%'; -- TextData filter

-- Optional: Output TraceID for reference
SELECT @TraceID AS TraceID;

-- Optional: Add a filter for ApplicationName to capture specific applications
-- EXEC sp_trace_setfilter @TraceID, 10, 0, 6, N'XClarys.Server'; -- Filter for XClarys.Server
-- EXEC sp_trace_setfilter @TraceID, 10, 1, 6, N'Cerastore.JBT'; -- Filter for Cerastore.JBT

-- Step 4: Start the trace
EXEC sp_trace_setstatus @TraceID, 1; -- Start the trace

-- Step 5: Stop the trace
-- EXEC sp_trace_setstatus @TraceID, 0; -- Stop the trace
-- EXEC sp_trace_setstatus @TraceID, 2; -- Stop the trace

` The results look as follows: The result of SELECT @@VERSION on that database is Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor). I am looking at the *.trc file on my computer, where the result of SELECT @@VERSION equals Microsoft SQL Server 2022 (RTM-GDR) (KB5046861) - 16.0.1135.2 (X64) Oct 18 2024 15:31:58 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 19045: ) (Hypervisor). The question is rather obvious: where are my starting and finishing statements???

I am getting the following alert only for one trace file: File 'xxx.trc' is not a recognizable trace file. I can select from all other trace files using fn_trace_gettable. Is it safe to delete this 0 bytes file to stop the alert? thank you

These are my SQL default trace files: To review the trace logs I run the following query: SELECT min(tf.StartTime) MIN_STARTTIME, max(tf.StartTime) MAX_STARTTIME FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1 f.[value] FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT ) tf The output is: MSDN doc: sys.fn_trace_getinfo ( { trace_id | NULL | 0 | DEFAULT } ) > Specify NULL, 0, or DEFAULT to return information for all traces in > the instance of SQL Server. Based on the above my query is selecting top 1 which is the latest file. fn_trace_gettable ( 'filename' , number_files ) > If number_files is specified as default, fn_trace_gettable reads all > rollover files until it reaches the end of the trace. Since the fn_trace_gettable has parameter DEFAULT, should the start time not have been an earlier time (for example: 20-09-2022)? Why doesn't it roll over to the other files?

With SQL Server we can trace for a period of time, to get what queries are running. How can I do this with Firebird? I'm reading something on their website but there's only user trace like if the login failed or not. Found something here: https://stackoverflow.com/questions/30248937/how-to-capture-firebird-sql-queries Will test it.

We have a Sql server 2016 Sp2. Noticed the trace file path D:\Data\MSSQL13.MSSQLSERVER\MSSQL\Log\log_298.trc is getting filled with trace files and the files are more than 2 months old. To my understanding the max number of trace files should be 5 and if that is the case why it is not deleting files above 5 max trace files. The only option for me is to disable the default trace. Please advice if there are settings where I can check or any other option I can implement to that the Log drive don't get filled up.

**My Task** When user updates some values on the front end UI of an application, I want to know which SQL table/fields are updated, which SQL statements were executed, etc. **My method** Using SQL profiler, I updated some value from the applications user interface. then try to track the SQL statements executed. In Events Selection Tab, I selected all RPC & TSQL. **Problem** SQL Profiler does not show the value of incoming parameter, showing e.g. exec [sys].sp_describe_undeclared_parameters N'SELECT FIELD_NAME FROM EXTRA_FIELDS WHERE TABLE_NAME = @P1 AND EDITABLE = ''N'' ORDER BY CONTROL_NO, SEQNO' Is there anyway I can get the @P1 value? **See screenshot**

Because of an outdated SQL Server version I have to use server-side traces to collect some information, but if I try to implement it I get the error Filters with the same event column ID must be grouped together. My code: exec sp_trace_setfilter 2, 10, 0, 7, N'SQL Server Profiler%' exec sp_trace_setfilter 2, 10, 0, 7, N'DatabaseMail%' exec sp_trace_setfilter 2, 10, 0, 7, N'SQLAgent%' KnowledgeBase example (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-trace-setfilter-transact-sql?view=sql-server-ver16) : sp_trace_setfilter 1, 10, 0, 6, N'SQLT%'; sp_trace_setfilter 1, 10, 0, 6, N'MS%'; As often it may be something obvious, but I do not have an idea why it doesn't work (it's not a semicolon). Does one of you have an idea?

I am exploring the SQL trace replay feature. My IT admin has given me a replica of the production SQL server (without network access) to perform some testing. I intend to perform the patching and config changes on this new server and then replay the trace captured from the production SQL server. I understand that I need to restore the database backup on this new server and they replay the trace via SQL profiler. I will explain my concern with an example: 1. Say I take the database backup 2. New row is added to the Product table 3. I start capturing the trace now 4. New row is added to the sales table that references the Product added at step 2. 5. Other CRUD queries get executed.. 6. Stop trace Now when I restore the above database onto the new server, it will not have the Product record from step 2. So when I replay the trace, the command to insert the new sales record (step 4) will fail as the database is missing the corresponding value in product table. How are such issues to be handled (or timed) when working with replaying trace? For example - do I ask for downtime on the moment when I am about to start taking the database backup, and start capturing the trace?

I am exploring the default sys trace and it contains various records for the security changes on the SQL server (event record examples are shown in screenshot below). Since the trace feature can be removed in a future SQL version, is there an extended events equivalent that captures this by default (for example in the system health extended events)? Or does this need setting up?

Sql server automatically creates the default trace into multiple files. These can be seen in the LOGS directory of sql server installation path. The sys.fn_trace_getinfo has a trace_id parameter which I'm not sure where I can find. How to view the trace_id of the default trace files?

We recently discovered one of our drives was being filled up by audittrace files. One of our DBAs turned off C2 auditing which should normally stop the files filling up the drive. However, it appears turning off C2 auditing has not had any affect. I've tried looking at the existing traces to determine why the files are still being written. SELECT * FROM sys.traces ID Status Path 1 1 \\?\D:\MSSQL11.MSSQLSERVER\MSSQL\Data\audittrace20161113132900_260.trc 2 1 D:\MSSQL11.EPDM\MSSQL\Log\log_485.trc ID 2 is currently set to the default but cycles after a max 5 files. This trace isn't the one filling up my hard drive. ID 1 path and trace file are the ones filling up. I've already turned off C2 auditing. What other troubleshooting can I do to see why the audittrace files are even being written to in the first place? EDIT: I've restarted the services. It would seem the correct solution would be to turn off this audit trace by using the sp_trace_setstatus 1,0 but when I run that command I get the error: Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 6 You do not have permission to run 'SP_TRACE_SETSTATUS'.

SQL Server database -- a read only node specifically (not exactly sure how that works). Our database keeps crashing due to memory issues - various error messages indicate this. It's hard to tell if it's various user queries, or other background processes. Our team does not administer said database. But -- as you might intuit, there are no strong resources (re: anybody) that really is dba'ing the database effectively. It seems using the SQL Server Profiler (alter trace) is almost, if not actually, at the sys-admin level of privileges, which the actual sys-admins are reluctant to grant. Is there a way to grant alter-trace without sys-admin-level security risks?

I am learning about capturing trace via profiler. I understand that the purpose of capturing trace via profiler is to log events happening on the sql server without impacting performance, because logging via the profiler GUI impacts performance. Once I have the trace I can open it in profiler and view the events without having impact on sql server. What else can I do with the trace?

Can anybody explain for what purposes exists ucs package {C0AB75C5-B1EA-445B-B7DF-F897686F94E7} "Extended events for Unified Communications Stack" I have tried to catch some events like ucs_connection_recv_io, usc_connection_setup but always events session is empty. What conditions/settings/etc need to events start captured?

I've managed to start SQLPlus and have logged in successfully. I can run queries but I can't trace any of them since when I input @settrcon and it gives me the error SP2-0310: unable to open file "settrcon.sql" so it means I have to navigate to the directory where this settrcon is so I can trace but I have no idea where it is. The settrcon is meant to alter the session because I need to be able to generate tracefiles.

We are looking to continuously run server side trace on Analysis Services and record its output in a table in database in the Database Services. There are tons of articles showing server side trace for Database Services but I have yet to find one for Analysis Services. Any suggestions on where to start recording and automating server-side traces for Analysis Services to run always?