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,
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
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
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
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedI have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies View RelatedCan only member in the System Administrators role use the SQL Profiler’s? Is there any other role or way to allow user to use this tool other then adding them to this role? Thanks you
View 1 Replies View RelatedDoes anybody have any idea if there is a bug in SQL 7.0 where running multiple traces causes the SQL 7.0 to crash ??
This is running in a clustered environment.
Thanks in advance.
Ajay
I've looked high and low for information regarding this problem to no avail. Profiler works fine from the local server where SQL7 is running,
(using NT security). But even though my client is multiprotocol, I cannot connect from my desktop using SQL security (ODBC error) or NT
authentication. NT security gives me ConnectionOpenRcpBindingSetAuthInfo(). I do have a valid id on the NT machine as well and am not
getting any errors in the security events log.
Can/How do you connect remotely to the profiler using TCP/IP or Mulitprotocol client?
Why doesn't NT authentication work?
Thanks in advance for any help.
Hi Everybody,
Can anyone tell me what is a profiler in SQL server 7.0? What I exactly want to know is, in which practical situation u will find the profiler useful. Explaination with example will be appreciated.
Regards,
Samir.
How do I write a process in Profiler to categorise the performance of Stored procedures into buckets of <4 secs,>4secs,between 4 and 10 secs
View 4 Replies View RelatedHello,
My qn is related to profiler.
my app calls an sp, say, sp_DisplayResults. in profiler, i can see multiple entries for this sp call eventhough it is invoked only once from the UI.
no other process/users is invoking the sp.
could any one please let me know why the profiler is displaying it multiple times.
im using MSSQL2k
I have a small doubt through profiler how can we find out the long running queries.specifically which events we have to capture.
View 2 Replies View RelatedHi
I need to track the connections hitting to my server through application???
I m doing some work in Profiler but i m not sure from where to start it..I m using SQL SERVER 2005...I m accesing few pages on application and that i want to track that activity involving at server side
T.I.A
I have a small doubt.
where is the ideal place to run a profiler .
can we run a profiler on production server
or we have to run profiler on a seperate instance and connect to production server
Lets say I have a username & password , I dont want people to see it being passed on to the SQL Server.
What are the ways I can prevent the parameters being passed on .
Thanks,
Regards
Kidddddddddddddddddd
Any one show me the basic guide or tutorial about SQL Profiler for begginer .
Thank in advance !
hello friends sorry for this basic question.
i heard a word sql profiler.
i tried through BOL, but not got any good information how to start and from where to start. can anybody give me some brief about this
thank you very much
1/When I create a new trace, it gives me sevral events including the SQL server s own functions and provcedures. How do I tell profiler to display only the requests we send SQL server from a VB program or other company made programs instead of including sql server own events as well. That will help me filter the requests and locate the problem easily.
2/ also ,. sometimes, my text data column description gets truncated and as a result I can't see the whole text of a request.
What causes that and how can I fox it pls?
Thank you, i am not familiar with profiler.