There is a bug in one of the service packs where Profiler (7.0) only traces one server (regardless of the server you tell it to trace). Can anyone tell me how to fix this or point me to a KB article? I thought this was fixed in SQL 7 SP 3, however I'm experiencing this problem with SP3 installed.
View 1 RepliesI have a client which makes about 50 connections to my SQL Server using a non-unique username. Under SQL 6.5 I could trace all SQL activity by PC name, is this possible using the SQL Profiler in SQL 7?
Gordon
Hi There,
I hope someone can assist me in tracing the cause of a problem I am experiencing. I have a Web Server with ASP's querying the SQL Server 6.50.416 database. There is only one user db on this machine and yet I am running out of User Connections (current setting 2000) and memory (128MB RAM). Also, NT repeatedly experiences Stack Dumps. I have used the PRINTDMP utility to try and trace the cause of the error. The "Input Buffer" section of the Stack Dump (symptom dump) contains the following:
SELECT FK_SUB_PRODUCT_GROUPING, FK_SUB_PRODUCT_NAME FROM RESOLUTION_PRODUCT_SUB_PRODUCT WHERE FK_PRODUCT_CODE=1072 order by FK_SUB_PRODUCT_GROUPING
Is this SQL Statement the cause of the Stack Dump? Does anyone have any other ideas on what may be causing my problem. Any help would be greatly appreciated.
I would greatly appreciate any help with this problem, as I've been digging thru every resource I can find looking for a solution with no luck.
I'm going to be monitoring a database for all SQL statements containing INSERT, DELETE, or UPDATE. I'm grabbing the user name, time, and the entire text of the query. I can already do this programmatically, no problem. The problem lies in this. When I set up a trace on SQL Server 2000 using the system stored procedures sp_trace_create, sp_trace_setfilter, etc, and set the trace to save to a trace file, I find that I must first stop the trace then close the trace before I can use fn_trace_gettable to get the information that I want. However, this is undesirable, because this database may be accessed worldwide, and stopping the trace to read the data could cause the trace to miss some users making changes. Does anyone know how that I could get my trace data into a table so that I can just run queries on that table to get my data? It's very important that I not stop the trace to do this. Thanks for your help!
JR Rickerson
Software Engineer
Infinite Software Solutions, Inc.
What are different kind of traces we can take regarding the performace and how traces can be used for improving performance ?
spatle
Hi,
I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
How would I go about tracing UDF performance in profiler? I'd like to
specifically know the impact of the UDF without having to dig into the
execution plan of the statement containing it. Is this possible?
I am fairly new to SQL Server. I am writing a tool in stored procedureto identify locks in a table. I have already written the basic frameworkof the SP. It will reside in master database and take two inputs. Databasename and table name. From that it will show all locks at that instanton that table of that database. If table name is omitted, then it will showlocks on all tables.I am using syslockinfo, spt_values tables and joining with SP_WHO procedureto get the table name, user name and the session id.Now what I need is to find out which SQL is causing the lock and since whenlock is being held on the table. Which tables in master database holds therequired information.TIA.Ravi
View 2 Replies View RelatedHiI want to trace all the selects/deletes/modifys whatever on a databasein an application that are performed in a seperate application.I need to look into this, any ideas?- Can triggers do this kind of thing- Can you somehow access the profiler via OLE or similiar to do this?- Anything else?TaF
View 5 Replies View RelatedHi,
I need to trace deadlock, one of article was mentioning €œQL Server Profiler's Create
Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Hi,
I am using sp_executesql to get some data but it is not working. Is there a way to actually see the actual statement where subsituted variables are replaced with the actual values.
SET @SQLString = N'SELECT Top(1) @Passenger_OUT = Name, @Ticket_OUT = TicketNumber , @TAN_OUT= [TAN] '
+ N'FROM dbo.Table1 WHERE 1 = 1 '
+ Case @MatchAmount When 1 Then N' and Amount = @BillingAmount ' Else N'' End
+ Case @MatchTicket When 1 Then N' and LTrim(TicketNumber) = STUFF(STUFF(@TicketNumber,Len(@TicketNumber)-@RemoveRight+1,@RemoveRight,''''),1,@RemoveLeft,'''') ' Else N'' End
+ Case @DaysDiff When 0 Then N'' Else N' and DATEDIFF(d,@BillingDate , InvoiceDate) <= @DaysDiff ' End
+ Case @MatchName When 1 Then N' and Left(Name,@CharsToMatch) = Left(@PassengerName, @CharsToMatch) ' Else N'' End ;
SET @ParmDefinition = N'@BillingAmount decimal, @TicketNumber varchar(15), @RemoveRight tinyint, @RemoveLeft tinyint, @BillingDate datetime, @DaysDiff tinyint, @CharsToMatch tinyint, @PassengerName varchar(35), @Passenger_OUT varchar(35) OUTPUT, @Ticket_OUT varchar(15) OUTPUT, @TAN_OUT varchar(25) OUTPUT';
EXECUTE sp_executesql @SQLString, @ParmDefinition,@BillingAmount=@BillingAmount, @TicketNumber=@TicketNumber, @RemoveRight=@RemoveRight, @RemoveLeft=@RemoveLeft, @BillingDate=@BillingDate, @DaysDiff=@DaysDiff , @CharsToMatch=@CharsToMatch, @PassengerName=@PassengerName, @Passenger_OUT=@Passenger_new OUTPUT, @Ticket_OUT=@Ticket_new OUTPUT, @TAN_OUT=@TAN_new OUTPUT;
Print @SQLString will gives the statement with variables and I need to see the actual data plugged in during runtime.
If I take the ouput of @SQLString and run it myself by adding variables it works fine
Thanks
Shafiq
Hi guys, I'd just like to know how to setup a new Trace in SQL Server Profiler that tells me every time the database is hit with a query. Thanks.
View 1 Replies View RelatedHi I have a problem in my SQL Server 2005. This morning I got a message of "Low Disk Space" in the drive "C" of a virtual machine, when I looked at the directory:
C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12DataMSSQL.4MSSQLData
I found 454 sql server profiler - trace data files in that folder, one of them like this one: audittrace20080207220657_13.trc takes 204,800KB, and totally takes 89GB in that folder. That's why I got "Low Disk Space"
I know that we need profiler to troubleshoot data, view the event...but
these kind of .trc files were keep created in the "Data" folder in every 2 hours everyday and soon it will takes all the hard drive in the server.
Is there anything I can set in SQL Server 2005 or other settings to delete the profile over 1 week or so automaticlly to free the space in the server?
Thanks.
SQL 7 profiler has an event in the Misc. category of Failed Login. It does not, or at least I cannot get it to, produce any output when a failed login occurs. Any hints?
I tried this because every week or so I get this in the error log:
Login failed for user 'Admin'.
It occurs several hundred times within a minute or so. It obviously has to be an automated process as you couldn't click a button or press a key 13 times a second.
The login does not exist as a SQL login so I can't tell which database it is trying to get at. Any suggestions gratefully received.
Simon
I have a very simple piece of code (see below) which when executed sometimes takes around 7 minutes and sometimes around 3.5 hours. the difference is that during the 3.5 hours there is a lot of querying of the table being updated. But I don't know how to find out if this is the case. How can I find out whether my process is waiting (for locks or for any other reason) - is there a trace or debug facility within the tandard Microsoft Toolset which I can use.
Regards
Colin
Problem code below
===============
print 'Updating stm_brnline - Start time is ' + convert(char(25),getdate(),113)
--
update m
set m.branchpgrade = s.branchgrade
from stm_brnline m, tmp_brngrades s
where m.traddiv = s.traddiv and
m.contcode = s.contcode and
m.merchsect = s.merchsect and
m.branch = s.branchcode
--
print 'Updating stm_brnline - End time is ' + convert(char(25),getdate(),113)
Hello
I am running 6.5 sql and work with a traffic and billing software ( called NOvar) from another company(encoda system) which does a lot of scheduling, reporting etc
I dont know the contents of table (100 table ) and their column
or which table its querying to take out reports
Can i create a trace to know the syntax each time some thing is executed.
I also need to create customized reports, can this be done by sql reporting or does i need to go from crystal reports or someone else
For i dont know any language except sql and HTML
sej
Hi
As a newbie to DBA type tasks, how can I trace who has accessed the server/database. I know there is a SPID in the Server log but what does this represent?
Thanks!
I have a Stored Procedure that execute some queries on link server. It takes so long to complete so my application get timeout error. There was no problem until last week. I suspect, remote queries that qorks on link server takes long. How can i trace the time of queries. Any idea about link server timeout problems?
Thanks in advance.
Can anyone help me on this one:
We have several SQL servers in our network. When we try to connect to any of these servers with SQL profiler, the connection is always made with the same server, irrespective of the servername we use.
There is no default server name used in the setting of SQL profiler application.
Thanks.
Gest regards,
Ben Cornelisse
The Netherlands
I am trying to create a trace that informs me when a stored proc is created, modified or dropped in a particular database. The trace event I have selected is TSQL->SQL:StmtCompleted. The only filter I have in place is on database ID. The ID value is set to the database containing the stored proc's I want to monitor.
If I drop a proc, I get an entry in the Profiler. However, if I add or modify a proc, I get nothing. Does anyone know what I might be doing wrong?
BTW, adding another filter on object ID (1 for sysobjects or 8 for syscomments) doesn't help either.
Thanks.
Can you run 6.5 traces through the SQL 7.0 profiler. If so, I am getting a login error. I do have the same db name, login, and perms.
Andy
Is the security granularity in SQL Server 2005 good enough that I can allow certain users to have permissions to use Profiler ONLY on their database with out granting them sysadmin privilege? I know that it uses the master database which makes it complex. Thanks in advance.
View 3 Replies View RelatedHi all,
I am trying to access SQL Server Profiler while connected via a VPN. I am able to connect while I am physically on the network but when I connect via the VPN it gives me the following error:
Cannot connect to xxx.xxx.xxx
Additional Information:
Login timeout expired
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Named Piped Provider: Could not open a connection to SQL Server [53].
(pfutil90)
I am able to access the database and browse tables/run queries/etc. But only when I try to create a trace I get the error. Does SQL Profiler act funny when going through a VPN?
Thanks in advance.
Dear friends,
I got script for creation trace file on server drive.
and it's work fine,when we migrate to sql server 2005 i take the same script for this trace and he create it successfully but the problem when i try to open this file for example today file:
SQL-Server1 SQL Trace20080417000000847.trc
I got the following message:
Sql server profiler :failed to open a file ,access denied.
old files created by sql server 2000 ,i can open it until now but the file created after migrate to sql 2005 ,i got that message above.
Guys/Girls
Have a problem am currently running a sql trace on a database, have the normal options setup.
Stored Procedures
RPC:Completed
RPC:Starting
TSQL
SQL:BatchCompleted
SQL:BatchStarting
But am also using the text data field but am trying to only bring back items that have a certain string like. Does anybody know how to use wild cards in the text data colounm
Lee
Hi everyone,I am steven.
I have some questions about the SQL Server Profiler 2005.
1,haoI am using TransactionScope of .Net Frameworks 2.0 for DB query and update.I want to know the lock level(table,page,row,etc) and status of lock status(u,s,x,etc) by profiler tool when I run the query and update statement.How can I do?
2,I had got some traces when I run the DB query and update.But I cann't understand the meaning of the trace.such as Binary Data,ObjectID,Type,etc.How can I know the meaning of these?
Whould anyone can help me?
Hello,
My client wants to encrypt data using sql server 2005 encryption mechanism. But one of the core requirements is that the DBA should not be able to decrypt my data on the SQL Server.
I have opted for the EncryptByPassPhrase option. But here is my problem:
I call the encryptByPassPhrase function within my stored procedures and I pass in the pass phrase as a parameter in Stored Proc call from the client intranet application.
The problem is that one can clearly see the passphrase in the Profiler when I call my stored procedures.
I did a bunch of research and found out that if I added a commented out "sp_password" command in my stored procedure, then the stored procedure call gets masked from the profiler. This seem to have been true with SQL Server 2000 but is not true for SQL Server 2005.
Does anyone know of a method to mark a stored procedure -- or a parameter to a stored procedure -- as masked so that either the call or the parameter value are masked ?
Thank You,
Tarin
I need to do testing on capturing connection coming from outside using Profiler. Basically I need to do something from other SQL servers connecting to this SQL server and test if it is captured by profiler.
View 4 Replies View RelatedI am using 6.5 and on this i used different program
and people execute query
Can i place a trace to show what query is being executed from which program and at what time
Meaning the syntax of it and by whom
I start tracing and it paging file becomes to large
sej
I have an issue where i have a field that changes data and i need to trace what it is that is changing it. I have setup a trace for the database but i would like to be able to narrow it down even further to the table and even the column if possible. Is there a way that i can trace data changes on such a granular level
View 1 Replies View RelatedHi 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.
Hi,what I am trying to discern is if there is any way of logging SPactivity on a SQL server 2k DB. Ideally I would want to log SP name,parameters, user and time.I found sp_monitor in MSDN but that just gives overall statistics.Not specific enough to aid debugging.Thanks in advance,Finlay Macrae
View 1 Replies View RelatedGuys,what I need is a tool which gives details on the choice of anexecution plan by the SQL Server. For example, the cost for a hashjoin might be 200 and 100 for a nested loop, and therefore a nestedloop is used. Same thing for the access paths for each table/viewinvolved. In Oracle, we turn on event 100053 to see this kind of info.ThanxDaniel
View 2 Replies View Related