Hello,
I have to create a trace to monitor how the stored proc.are performing.
The sp are for update/select/insert.
What all counters should I monitor?
The sp are called in from Java applications thru weblogic server and some of them are taking as long as 30 secs to fetch 80 records!
Any help/thoughts appreciated.
I'm trying to create a trace file with the file as part of it's name.For example, I'd like to create a file called FailedLogins-20050428. Sofar I haven't been able to figure out how to get the name of the fileand the date together (I'm sill very new to SQL Server and tracing).What I've done is:declare @rc intdeclare @traceid intdeclare @maxfilesize bigintset @maxfilesize = 50exec @rc=sp_trace_create @traceid=@traceid output, @options=0,@tracefile=N'C: racefailedlogins', @maxfilesize=@maxfilesize,@stoptime=NULLif @rc > 0 print 'sp_trace_code failed with error code ' +rtrim(cast(@rc as char))else print 'traceid for the trace is ' + rtrim(cast(@traceid as char))I can create a trace file on C drive without difficulty. I've triedcreating a file like this:exec @rc=sp_trace_create @traceid=@traceid output, @options=0,@tracefile=N'C: racefailedlogins + convert (varchar,getdate(),112',@maxfilesize=@maxfilesize, @stoptime=NULLBut what I end up created is a file on C calledfailedlogins + convert(varchar,getdate(),112).trcI have no doubt what I want to do can be done. I just done know how todo it.If anyone could tell me where I'm going wrong, I'd really appreciateit.Thanks in advance.
View 2 Replies View RelatedI couldn't find a "sql trace" utility like in sql 6.5... Does anybody know how/if I can do this?
View 2 Replies View Relatedif i dont use the trace to record all the sql transaction. and someone execute the delete command to delete one of the table (ofcourse the person have high enough permissions to do that). is there anyway i can find out who that user's ID is to run the deleted ?? Thanks:D :p
View 2 Replies View RelatedHi,
I'm trying to debug a vendor package and would like to turn on JDBC trace (either client or server side). The only information I found is DBCC TRACE which seems not very useful (I even don't know where the trace result located). Any help is appreciated!
Env: Windows Server 2003 & SQL Server 2000 8.00.818.
Jeff Li
Guys,
How to trace/find out some one has dropped a database from my QA environment? Unfortunately we havent enabled the trace on this server.
We havent find any useful information from SQL Server logs also.
Can any one reply me how to find the details of who dropped the DB, when? is there any query/SP/command/tools avaialble?
Regards
BSR
With Regards
BSR
hi all..
i have 2 tbl..
-- Prepare sample data
DECLARE@tbl1 TABLE (box varchar(10), loc varchar(5) )
INSERT@tbl1
SELECT'P1', 'aa' UNION ALL
SELECT'P1', 'bb' UNION ALL
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'
DECLARE@tbl2 TABLE (box varchar(10), loc varchar(5) )
INSERT@tbl2
SELECT'P1', 'aa' UNION ALL
SELECT'P3', 'cc'
--expected result
SELECT 'P1' as box, 'aa' as Location, 'aa' as HeaderLoc UNION ALL
SELECT 'P1' as box, 'bb' as Location, 'aa' as HeaderLoc
how do i trace from @tbl1, that has 1 Box with more than 1 Location?
in this case P1 has 2 distinct location (aa & bb).. and left join in the loc from @tbl2 just to retrieve the loc for that box..
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
How do you set up the trace in SQL?
thanks
sorry, I am an Oracle guy trying to learn SQL
Hello:I am working with SQL Server 2000. I have a stored procedure that creates 3temporary tables (#temp1, #temp2, #temp3). When I view the trace of thestored procedure I see entries that have Object Created #temp1, ObjectCreated #temp2, Object Created #temp3. How can I fix the trace so it doesnot report any objects that get created in the tempdb.Any help will be appreciated.Alee
View 1 Replies View RelatedWhen we enable tracing for a web application where exactly the file or content of the file "Trace.axd" saves?
View 4 Replies View RelatedHi,
I'm trying to use trace flag 1204 to get some detailed deadlock information
In EM, I add startup parameter -T1204, and then I stop and start the server.
I run two jobs that I have setup to deadlock, and they do. But no information about the deadlock is in my sql error log? anyone know what I am doing wrong? thanks.
Hi ,
I want to trace the deadlock information. I am enabling trace flags 1204 ,1205. Is there any difference in setting these trace flags by using DBCC
TRACEON and by setting them at command prompt by statrting the SQL server with SQLSERVR command. Actually i don't want to bring the server down. I want the information to be logged to Error log. Any help is greatly appreciated.
Hi There,
Is there any way to identify how many times a table is accessed by different users in a day from a database.
Thanks,
Madhu.
Hi all,
I was wondering how much of an overhead would Running this
trace (1204) have over the system. Will my perfomance
degrade significantly. If yes, by what percentage ???
The trace is 1204 to keep a watch on DeadLocking
I am running this command
C:mssql7innsqlservr -T1204 /dc:mssql7datamaster.mdf
How much performance degrade are we talking about here ?
The Application is Peoplsoft and the db sizes is about 10
Gb !!
Regards
Girish
SUPPOSE A THIRD PERSON WHO (MODIFIES THE STRUCTURE OF A TABLE) OR (DOING SOME MODIFICATIONS ON A CERTAIN COLUMN), HOW THE SQL SERVER DBA WILL FIND WHO HAS DONE THIS. WHETHER THESE MODIFICATIONS WILL BE STORED IN SYSTEMS TABLE?. THIS IS MY QUESTION. CAN ANYBODY GIVE SOLUTION TO THIS PLEASE?. THANKS ---- Srinivasan.
View 2 Replies View RelatedIf I issue a DBCC TRACEON (1205) from a query window from a client machine.. Does it continue to run when I close that connection (spid).
Is there a way for me to tell what traceflags are currently on?
Thanks,
Dano
To solve a problem I encountered with Restoring from Backups in 6.5, I had to install a hotfix and thereafter do the load using Trace Flag 3282.I need help on using the trace flag (syntax etc.) Also there is no mention in books online of this particular Trace Flag.
Please help.
One of my SQL servers (v6.5 with SP5a) has begun to display 'DBCC TRACEON 208' in the Error Log. I have not been able to find any reference to the Trace Flag - 208. Can someone please help me?
View 2 Replies View RelatedHi All,
I've just joined a project for improving performance issue on MS-SqlServer(6.5).
I was DBA Sybase a couple years ago I am unsure that MSSQL works in a manner as
Sybase. I want to change some configuration options (sp_compile) and modify
the table and indexes definition for improving the performance and avoid having
deadlocks.
Is true that :
When field, which has a default value assigned to NULL, is equal
at a variable-length field and when you execute an update on it you will
get 2 transactions : first a delete and then insert ?
(On Sybase there is the concept of Index Covering to avoid that overhead
what about Ms-Sqlserver 6.5).
To avoid having deadlock I want to use the FILLFACTOR I think this option
is usefull only on cluster indexes, that's right ?
For huge tables the order of fields is important :
first position put the primary key field
after put the FK, secondary key, short type field (tinyint,int,datetime...)
go on with the alpha fields (char) NOT NULL
at last the NULL fields
Is that design efficient for Ms-SQLserver ?
Many thanks,
Herve
Hi folks
Is there any (easy!) way in which I can see the SQL that has been executed. I'm using stored procs that create & execute other stored procs(via sp_executesql). At the early stages there are often trivial errors in the created procs that cause rather general exception messages that do not give much of a clue as to where the error is. (It's tedious to find these in the debugger) ... and was wondering if there's any trace output type option I can turn on to see what sql has been presented for execution.
Thanks.
I ran a trace on my SQL Server and found that a single SELECT statement showed up the same number of times as the number of id numbers I was selecting on. In the hopes of optimizing my program, it ran worse :(
Instead of having:
SELECT * FROM table_name WHERE id='1'
SELECT * FROM table_name WHERE id='2'
SELECT * FROM table_name WHERE id='3'
...
SELECT * FROM table_name WHERE id='250'
for all the rows i need data from (sometimes up to 250 rows). I changed the select statement to:
SELECT * FROM table_name WHERE id='1' OR id='2' OR id='3' .. id='250'
When i run a SQL Trace if i have 250 id='num's in my SELECT, the same query shows up 250 times in the trace with high CPU and READS. Can Anyone explain and help me get the optimization Im looking for?
Folks, I've inherited a SQL 2K server that has a trace running. I can see the trace file being generated(.trc), I can also see the standard templates(.tdf). But BOL is not helpful in finding information about the
trace when you have no information. I just want to shut the damn thing off.
I can open the trc file in SQL Profiler but I am not proficient in the tool's use. I also don't see a trace table anywere in the database.
If anyone can be of any help it would be much appreciated. Thanks.
Hi,
I need to find the tables in which a particular column exists in the database. Please provide me an appropriate query.
Thanks in advance.......
Hi,
I using SQL SERVER2000,i can see in one table.. often some data is duplicating.
I couldn,t find,how these data are populated.
Even in audit..i am unable to trace the action.
Any idea,how to find the root cause??
Thanks.
How can trace SQL server without SQL profiler?
View 5 Replies View RelatedI have written a script to run profiler trace as far as I can. Guide me if i can add some more so i don't cause performance hog and which can be scheduled as well.
Here is the script:
--Stored Procedure to run trace for 30 minutes
Create Procedure dbo.Usp_Tracefor30minutes
as
set nocount on
declare @stop datetime
set @stop = dateadd(mi,30,getdate())
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 50
-- create traces and saves in folder specified
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, @stop
if (@rc != 0) goto error
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
-- 17 - Existing Connections
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
-- 10 - RPC:Completed
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
-- 43 - SP:Completed
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
-- 37 - SP:Recompile
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
-- 12 - SQL:BatchCompleted
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
-- 13 SQL :BatchStarting
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler'
set @bigintfilter = 5000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
--Exec SP
Exec dbo.Usp_Tracefor30minutes
Folks,
I have been hunting for a way to trace sql requests going to/from the sql express server (from VS.2005 using asp.net). I.E., I want to see the raw SQL commands that asp.net is generating.
Can anyone point me to a tool or article? Thanks,
--stuart
Is there any way I can give a user read only access to the database, yet have privilges to run a trace.
Any suggestions and inputs would help
Thanks
Experts, I've run a profiler trace on a production 2k5 server for two days, resulting in more than 4GB of 50MB rollover files. Profiler was run with default settings plus some columns and a filter for just one database by name.
I want to run Tuning Advisor over this huge log, but it seems that this tool can take one file at a time, but I need only one report instead of more than 80 with different suggestions.
I'm trying to discard event records that aren't "RPC:Completed", "SQL:BatchCompleted" and "SP:StmtCompleted", in order to generate only one trc file that sumarizes all my capture.
Is there a way to do that? Any tool available?
I was thinking about to use the "fn_trace_gettable" method, but I don't want to run the Advisor over a database. BTW, is it possible to filter during the "select into" or "insert" with a "where"? If so, what should I filter?
I can write a file manipulation tool for this task if I know the trc file format, but I didn't find that info. Any hint?
Thanks a lot...
++Vitoco
I have a SQL 2000 enterprise edition single instance server on windows 2003 server, my server has been up for 53 days and I have 197 packet errors. Besides network card, cable and switch port what else could cause this? Is there a way to trace back to the source?
View 1 Replies View RelatedWe set our Deadlock Trace on with the following command:
DBCC TRACEOn (1204, -1)
DBCC TRACEOFF (3605, -1);
DBCC TRACESTATUS (-1)
GO
In 2000 this worked fine but in 2005 we get the following error:
Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
What do I need to do to fix this so we can see the cause of the deadlock in the SQL Server logs?
Hi all,
We had a problem with a database so i used profiler to see where it came from a couple of days ago. Now the performnce on the server has gone really bad, so one of the things i want to check if maybe the trace is still running somewhere on the background. How can i check this? When i run the profiler, i can't see it in current activity. How can be certain this is or is not the problem?
I'm using SQL 2000 SP4.
Thanks in advance,
John
Hi all,
I would like to track the queries submitted to a SQL SERVER 2005 database in my university.
I would like to create a metadata repository that will collect what was the query, who executed it and when.
Can you think of a way to do it?
thanks,
Yoav