Hi All,
I am going over the output of a Profiler trace and I've found that the duration for many occurrences of EventClass 15 (Logout) is several seconds, up to a maximum of 20 seconds. That seems excessive just to complete a logout, so my question is, does the duration figure reflect only the time to complete the logout operation or does it include the total time that the connection has been active for?
Thanks in advance
Lempster
sql server was using a lot of the cpu so I ran a trace.
Now i see the results and there are lines with high cpu number with textdata NULL and AppicationName Internet Information Services
how can i figure out what this is?
Hi all,
Anyone can tell me how I can take out the events which produce SSMS? (open query windows create three rows). I don't see anythinq filter for SSMS in filter events of profiler. I'm doing at trace and this is not confortable.
Thanks.
im trying to understand how the profiler works. so i started 2profilers,one listen to another and I saw the profiler is running:exec sp_trace_create @P1 output, 1, NULL, NULL, NULLwhich means @tracefile = NULLso where from the profiler read the results?!
View 1 Replies View RelatedHiI want read a trace file generated by SQL Server 2005 througr SQLServer 2000.But fn_trace_gettable function in SQL 2000 does not recognize the fileas of proper format.If there is some other tool or utility available through which i canread the file generated by SQL Server 2005.Or if I can get the file format of the file then I will write my owntool.ThanksPushkar
View 1 Replies View RelatedGreetings,I have been attempting to develop a useful and functional template fordatabase tracing/profiling that will enable me to collect metrics forperformance tuning. The database is used as an OLTP database as well asrunning reports. Below is a list of my trace properties and data columns.I would be interested to see other examples and strategies for the Profiler.thanxPerformanceExecution planSecurityAudit LoginAudit LogoutSessionsExisting ConnectionStored ProceduresRPC: CompletedTSQLSQL:Batch completedDATA COLUMNSEvent classtextdataapplication nameNTUsernameLoginNamesCPUreadwritedurationclient proc idSPIDStarttime
View 3 Replies View RelatedIf a table has a trigger on it, and I am profiling.. on StmtCompleted... no filters... all teh stored proc code comes up, but, is there any way at all to see the same for trigger statements? I want to trace thru the proc and thru all trigger code also. Any ideas on work-around to trace trigger code, if Profiler can't do it? Thanks, Bruce
View 4 Replies View Related
What causes SQL Profiler to show "SQL Profiler Trace Skipped Records"?
SQL2005 SP2 64bit
Thanks.
Hi,
Could anyne help me on which options in Profiler could be used to trace the bind variables in DML?
Given a table, X (col1 number, col2 varchar(10)), where col1 is primary key.
Example DML statements:
1) insert into X values (@parameter1, @parameter2)
2) update X set col2 = @parameter2 where col1 = @parameter1
3) delete X where col1 = @parameter1
where @parameter1 and @parameter2 are bind variables.
Can Profiler be configured to log the actual values of @parameter1 and @parameter2 in the example statements in trace log?
Thanks a lot.
Hi
We have two servers each running SQL7. I cannot run a trace on one server from the other. Whatever server name I enter in the drop down box, the trace only records activity on the server that profiler is running on. Even if I put a non-existent server name in the box (!), the trace accepts the name but still only runs on the host server.
Any explanations please.
Keith
Can anyone provide with an example of how to script a profiler trace to have the data wind up in a SQL Table. The scripting mechnism that comes with SQL Server will not allow you to put the results in a table.
Thanks
Bill
I've set the Duration of my trace to "Greater than or Equal to: 1000". However when I start my trace the Duration column is now empty. Prior to the setting, there were values showing in this column. Any ideas on how to fix this?
View 1 Replies View RelatedIs there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.
Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.
I have discovered trace output in MSSQLDATAMSSQL.1MSSQLLOG that I have not kicked off. It is at various times and limited to 20MB. So that tells me a server event is kicking off a pre-defined trace. The trace contains mostly hash warnings and sort warnings.
I have looked through my Agent Jobs, Agent Alerts, and perfmon and don't find anything that is set up to kick off a trace under a specified condition.
I have checked the job activity, SQL error logs, SQL server logs, and the server's event viewer for any odd events or event times that correlate with the times of the traces.
I have checked each database's sys.sql_modules for a definition containing '%sp_trace%'.
Where else can I check to find what would be triggering these traces?
Our app logins don't have permissions high enough to run traces, I verified:
You do not have permission to run 'SP_TRACE_CREATE'
I am the DBA, not a .NET programmer -- so I am lacking experience if there's anything on the .NET side.
This is SQL 2005 64-bit running active/passive on a Win2003 clustered pair.
With SQL Server 2005 there is an option to grant a person access to Profiler for tracing SQL. This is done with the "GRANT ALTER TRACE" statement. The statement has to be executed at server level i.e. the master database.
The user in question only has access to certain databases on that server. The security problem that arises is that with the Profiler rights active, he can see the sql commands that are executed on the databases he has no rights for. Those SQL commands are executed by others users.
How do I configure security rules so that the person in question can use Profiler, but can only see the SQL statements that are executed on the databases he has the rights for? TIA!
Someone Please Help!
How on earth can a Profile Trace be run where SSE 2005 is installed??? In the past, with MSDE, we always installed the 'tools' on a local workstation, so that we had EnterPrise Manager and its suite of tools...no problem. Yet, with Management Studio Express (err...Distress?), there's no way to do this! I've scoured the net, and I see threads where people have done it, yet, no one seems to be clear...including Microsoft...on how to obtain this MOST IMPORTANT of all tools for an SQL deployment.
Please Help! Someone...Anyone!...Thank You!...Michael
I am attempting to create a new trace but I get the following error message: "failed to start a new trace".
I have been doing some digging and as I understand it, I had to find the directory Profiler uses for temporary files. So, I typed the following in the command window "SET TMP" and I received the following reply:
C:UsersRossAppDataLocalTemp
Now, according to the forum: [URL] ...
I am supposed to check that the system folder pointed to by the TMP environment variable exists and is not crammed with files.
Well, when I went to the directory C:UsersRossAppDataLocalTemp, it is indeed full of both files and directories. The size is 16.3 MB and has 133 files and 63 folders.
When I had a look at the Environment Variables window and chose TMP the value is "%USERPROFILE%AppDataLocalTemp" which according to my limited understanding is the equivalent to C:UsersRossAppDataLocalTemp.
So, what I am wondering is am I supposed to totally clear out this directory? I am not too keen on doing this because I don't want to stuff my PC up.
This is for SQL Server 2005 SP4 Build 5266. We have been having performance issues in production. There are tight deadlines to be met and it is important that they are solved promptly.
Yesterday we replicated the situation in the acceptance testing environment. The jobs take 8 hours to run and we started at 2:00 PM.
Just before the jobs ran I set up an SQL Server Profiler trace to catch processes with a duration of longer then 12 seconds. I set it to save the results to a database table.
Last night I checked the table at 5:00 PM and there were entries in the table. However, I could be mistaken.
At 9:00 PM I checked the table and it was empty.
This morning I arrived at work and checked SQL Server Profiler. The trace was running and within SQL Server Profiler, there are 100s of results. I stopped the trace. However, checking the table, it is empty.
I thought I would be able to save the trace results to a file. When I chose "Save As" from the file menu, all the options are greyed out (trace file, trace template, trace table, etc).
The results are there but there is no way of saving them and no way of exporting them. How could this have happened?
Is there a location, where SQL Server Profiler saves the results in a temporary space. I may be able to open them and retrieve them. How can I save the results? Why are all my options greyed out?
Set up a trace with the events RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StmtCompleted.
When I issue the statement: SELECT * FROM XyzView there is nothing captured in Profiler. If I script out the view and then execute the select statement that defines the view, it does show up in Profiler.
I've tried adding a lot of the other events, i.e. SP:StmtCompleted and the various other StmtStarting events and the trace still does not capture anything.
Am I capturing the wrong events or is this known behavior? My goal is to see what the overhead is for using a view versus persisting the results of the view as a table and referencing that instead. The view in question is against static data, joins 9 tables, and is referenced a lot.
I can use the stats generated when I execute the select that defines the view but I still find this to be curious behavior so I assume I'm doing something wrong.
I am trying to load all the MDX queries that run on a Analysis Server instance into a database for further analysis. A SQL Profiler is setup which captures the MDX queries, and when I am loading the Profiler info to database, some of the queries are not coming up in full length.The TextData field doestn't show full MDX query. When loading to the database, the field is next data type. Is there any workaround to get the complete MDX query?
View 2 Replies View Related
Hi there - can anyone advise on the following issue. We have recently performed some server side tracing on a particular SQL instance over 24hr period. We are now attempting to load these into a database for analysis. Here lies the problem.
When we are loading the profiler trace files (one at a time) into the database the transaction log is growing at an excessive rate. Even though the database is in SIMPLE mode.
We are loading the traces using the command:
INSERT INTO sqlTableToLoad
SELECT * FROM ::fn_trace_gettable('MytraceFileName', DEFAULT)
Can anyone advise how we could possibly get round this issue as we're running out of space due to the transaction log.
Thanks
Hi knights,
Pls tell me how to analyzing the db log. I'd appreciate your help!
Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.
Any help would be grately aprreciated.
Has anyone written routines to analyze sysjobhistory? I'm looking for a tool/routine to analyze jobs for failures, trends (such as constantly increasing run times) and other information. It's not as straighforward as I had originally hoped it would be.
I am specifically prohibited from using any 3rd-party tools (too expensive per mgmt).
I would be grateful for any input/insight.
Regards,
hmscott
I have to duplicate a bunch of reports that were produced by a VB6 app. Now I'm using C#, ASP.net and Crystal Reports via VS2003. Each executes a stored procedure in SQL Server (before and now).
For each report I have a Stored Procedure and a View. The View's SQL code is identical to the Stored Procedure except that the two input parameters (startDate and endDate) are removed because Views don't allow parameters.
Some of the reports work perfectly right off the bat. But others are timing out. My initial test of the timing out is to just display the View. If it fails then I know that the the report engine will fail too.
So now I'm trying out the SQL Query Analyzer tool to execute the code in one of the Views. It's now at 29 minutes and still going - at least it hasn't timed out!
My question is this: Is there a way I could examine what's going on with the query to see why it's taking so long?
Robert Werner
Hi guys,
We have 2 databases: DataBaseSource and DatabaseDestination. We need to truncate all the data in DatabaseDestination and put all the data from DataBaseSource into DatabaseDestination.
What is the best way to do that, we have a lot of data?
And what is the best way if we also need to keep a trace of what happened in case we wanna go back and see what happened.
Also , pls, if we use DTS, is it possible that if someone wants to see what the DTS does, is it possible to read the DTS? I mean if I give a dts to sompeone, a new DBA guy in 2 years for example, how can he know what a certain DTS does? I mean does SQL 2005 put the DTS packaege scripts somewhere or is there a friendly way to know what a DTS dsoes exactly?
Also the trace to see if something went bad is important for us?
Sory if i didn t express myself well enough, and thanks a lot for your help.
Rachid.
For one of our SQL server 2005 Ent edition 64 bit SP4 which has transnational replication set up and used for heavy reporting, i was trying to counter out the performance of slow running queries which basically runs and get suspended and most often are seeing waiting:So i tried to analyse the wait stats and come up with below stats where ASYNC_NETWORK_IO dominated for a collection of two weeks data.
View 15 Replies View RelatedI am trying to setup a means to analyze our servers using SQL Server 2005 Data Mining. I will be collecting all the logs from the Event Log of each server and store them in a SQL Server database for analysis.
Has anybody done this before? Can you give some pointers? I would appreciate your assistance.
For one of our database we have an issue where its log file got increased rapidly last week on Fri and Sat. The database is on SQL server 2008 R2 with compatibility level at 80. Please see below log grow events :
First, we thought Index maintenance like Re-index and update stats could have been the reason, but when check the schedule that job ran on 16th using below code:
USE ABC
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC sp_updatestatsGO
I know above is OLD fashioned, but we believe that should not be the major cause here? How can i determine what happened on 14th and 15th which cause the event to trigger and log file bumps to 80 and 70 GB both days.
I need to discover the actual order in which locks are acquired on a table during a query.
This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.
I'm using SQL Server 2008 R2.
From Management Studio I execute:
begin transaction
<my query>
exec sp_lock
rollback transaction
In the output I see interesting information about which locks are acquired, but:
- are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
- if not, how can I get this information?
Hello,
I have successfully installed two instances SQL Server 2005 Standard Ed. 64. and integration services (for the MSSQLSERVER instance).
When I try to install SP2 x64 everything exept the Database services of the MSSQLSERVER instance was updated well.
Here is the part of the inst.summary:
Time: 12/25/2007 13:09:26.420
KB Number: KB921896
Machine: xxxxxxx
OS Version: Professional (Build 6000)
Package Language: 1031 (DEU)
Package Platform: x64
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Unterstützungsdateien für das Setup DEU 9.2.3042 x64
Datenbankdienste (AUTODESKVAULT) DEU SP2 2005.090.3042.00 x64 STANDARD
Datenbankdienste (MSSQLSERVER) DEU SP2 2005.090.3042.00 x64 STANDARD
Integration Services DEU SP2 9.00.3042.00 x64 STANDARD
SQL Server Native Client DEU 9.00.3042.00 x64
Clientkomponenten DEU SP2 9.2.3042 x64 STANDARD
SQLXML4 DEU 9.00.3042.00 x64
Abwärtskompatibilität DEU 8.05.2004 x64
Microsoft SQL Server VSS Writer DEU 9.00.3042.00 x64
----------------------------------------------------------------------------------
Product : Datenbankdienste (MSSQLSERVER)
Product Version (Previous): 3042
Product Version (Final) :
Status : Fehler
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29504
Error Description : MSP Error: 29504 SQL Server-Setupfehler beim Analysieren von SQL-Skript 'c:ProgrammeMicrosoft SQL ServerMSSQL.1MSSQLInstallsysdbupg.sql'. Fehlercode: Zugriff verweigert
. Beheben Sie das Problem, und führen Sie dann das SQL Server-Setup erneut aus, um den Vorgang fortzusetzen.
----------------------------------------------------------------------------------
Any ideas ? Olaf
I 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 Related