Locating Idle Sessions Using Dm_exec_... DMV's
Apr 6, 2006
Hi
I have been looking at the new DMV's prefixed with dm_exec_....and found a limitation with them.
Books online says sysprocesses is replaced with sys.dm_exec_connections, sys.dm_exec_requests and sys.dm_exec_sessions. The problem I came accross is identifying any sessions connected to a specific database which were idle. This is the sort of thing you need to know if you tried to restore a database and it says the database is in use.
I wonder if this is a bug or by design?
View 8 Replies
ADVERTISEMENT
Jun 9, 2003
Should these two stats added together give me the time (ms) since SQL Server has been restarted? Maybe I don't understand but @@cpu_busy is the amount of time sql server has accessed the cpu and @@idle is the amount of time sql server has no cpu activity. When I add these two figures together, I don't get the total amount of time (ms) SQL Server has been running. What am I not doing? Any help is appreciated.
Thanks
View 6 Replies
View Related
Jan 29, 2008
Hello everybody,I have a ASP.NET (3.5) Web site which uses an data connection to my Sql Server 2005 Express. When I browse in my data-driven pages of my website, I am satisfied with the performance. But when I try my website after a long break, e.g. in the morning, the data-driven pages take a long time to come up, about 10 seconds. After this gap, when surfing on the other pages, there is no problem. It is only the first page. So it seems that my SQL Server goes "to sleep" after a certain time of idle.First I read a post about this problem which came up with the user instances of Sql Server. So I got rid of my user instance and set up my database "normally". But still I have that performance gap.I am using Linq To SQL and a couple of ListViews. I am totally confused how to manage this problem.Any suggestions?Thanks in advance,Hannes
View 1 Replies
View Related
Jun 18, 2001
Hi all,
We got this message on our production server event viewer for source MSDTC.
"String message: Session idle timeout over,tearing down the session"..
Even though it is marked as informational, just wanted to know what it is?..
thanks guys,
Marx
View 1 Replies
View Related
Sep 20, 2007
folks
We have a situation where people just login run complex queries and then just don't shut off their query analyzer of apps.
Some of these chew up a lot of CPU cycles and unless we kill the query the CPU cycles are not released.
I see some of them are idle for over 1-2 hours or more, their last_batch would be at least 2 hours old.
The question is, "Is there a setting in the sql server whereby we can automatically disconnect idle threads?"
Regards
Paresh Motiwala
Boston, USA
View 4 Replies
View Related
Mar 21, 2006
Is it possible to stop the server from going to sleep when it is idle to improve responsiveness to new connections?
View 21 Replies
View Related
Aug 21, 2006
I have been seeing this issue for several weeks now. The distribution agent will become idle even though there is activity occurring within the replicated database. If I manually start the distribution agent, it will begin retrieving the transactions. I thought that this might be due to inactivity but I have modified the inactivity threshold and I am not receiving an actual inactive agent warning. There are no warnings -- it simply sits there and does not resume sending transactions.
Any thoughts? This is a transactional replication with a distribution agent that is run at the subscriber.
Thanks,
MV
View 1 Replies
View Related
Jun 8, 2005
Hi.. I have application which is connected to my SQL 2000 database. Normally our users use Terminal service to the SQL 2000 and application (same box) remotely and connect to the database. But some time the connection break and I have idle login stuck there.
For example, if I use sp_who, I can see many logins still login to DB even they are out. And sometimes I see duplicate logins too even there is only one person using that login to logon to DB. Is there a way to find out what is the idle timeout of the user login session??
Is that under SQL server properties -->remote server connections-->Query time-out--> >> sec ?
Is there a way to limit only 1 session can be allow for each login ??
View 4 Replies
View Related
Dec 7, 2007
I am using MS JDBC driver 2005 1.2 and in-house written connection pooling that was working fine for a number of years. This combination worked OK with SQLserver 2000. But recently we switched to SQLServer 2005 (x64) on WIN Server 2003 Standard x64 eddition. Everything seems work OK during business hours, however, after business hours when there are lesser users and connection stays idle for sometime, I am getting the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.setAutoCommit(Unknown Source)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.freeConnection(OnlineTransactionManager.java:420)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:707)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.releaseConnection(OnlineTransactionManager.java:688)
at com.nms.serverframework.dbaccess.OnlineTransactionManager.finalize(OnlineTransactionManager.java:399)
at java.lang.ref.Finalizer.invokeFinalizeMethod(Native Method)
at java.lang.ref.Finalizer.runFinalizer(Finalizer.java:83)
at java.lang.ref.Finalizer.access$100(Finalizer.java:14)
Any help would be appreciated
thanks in advance
View 6 Replies
View Related
Feb 20, 2007
Just upgraded to SQL Express 2005 from MSDE for Firehouse. Upgrade went great and the db is accessable and running fine. Except when not used for a while. Then I need to restart the db to allow connections of any kind. If I don't I get an error that the db is not accessable and I can't connect. Everything runs fine after until it goes to "sleep" on me again. We upgraded to make use of the 4GB limit as we were at 1.9GB and MSDE was getting a little unhappy.
Any suggestions? Is this a "feature" and if so, is there a script I can run to kick it into working mode again?
Thanks
Chris
View 1 Replies
View Related
Mar 13, 2008
Does SQLServer go to sleep when it's been idle? How can I connect without an error?
I have SQLExpress on a server. Server Name is SqlExpress. I have 2 DBs on that server... Tuna & Guppy. I'm connecting to the DBs from a remote PC which runs my App... an exe created with Visual Studio 2008, Visual Basic.
If I run my App at say 9:30PM, after the server has been idle for several hours, I get a "Can't open connection to Guppy" error, which is my error message when the connection attempt fails. If I quit & relaunch the app, it connects fine, and will continue connecting fine unless you leave the server idle for serveral hours again.
If I'm developing a 2nd app all evening... which connects to a 2nd DB...Tuna, that activity seems to keep the SqlServer awake and then launching the 1st app at 9:30PM connects to Guppy just fine.
I tried adding a "Connection Timeout=30" to my connection string but that did not help.
My connection code is as follows (note the server's name is SQLExpress):
Dim strConnG As String = "Data Source=SQLEXPRESSSQLEXPRESS;Initial Catalog=Guppy;Integrated Security=True;Connection Timeout=30"
Using ConnG As New SqlConnection(strConnG)
'open Guppy connection
Try
ConnG.Open()
Catch ex As Exception
MsgBox("Can't open connection to guppy")
Return False
End Try
'Code that does stuff
End using
Can anyone tell me how to fix this so I can connect without error, even if the server has been idle?
Thank you,
Joe A
View 6 Replies
View Related
Feb 10, 2007
I've noticed that after the database have been idle for some time, it takes up to 10 seconds to get it started when something needs to access it. In the event viewer it says that the database <name> have been started.
Obviously, there is some idle timeout setting.
I saw an option in the database properties that is called "Auto Close" which is set to true. I assume this is what i'm looking for. Can someone confirm that? (it could take some time to test myself...)
But what i'm actually wondering is:
1. Is it possible to adjust how long it would wait before timing out?
2. What advantages does closing the database bring? Does it free up (a noticeable amount of) ressources? Or is it only that it's unlocking the files, so that it's possible to copy the database source files?
View 4 Replies
View Related
Feb 21, 2008
Hi all;
I want to implement "Auto disconnection" (or kill) of user session that exceed a specified idle duration in SQL Server 2005.
I know how do that in oracle by creating a profile and set the IDLE_TIME paramter and asign that profile to all users. But in SQL Server i don't know how to do it.
Can anyone help me.
Kindly
View 2 Replies
View Related
Feb 13, 2007
Hi, everyone!
I have this strange problem... After every time my application leaves sql-server idle (doesn't send anything, doesn't retrieve anything) next command to sql-server processes really long.
I've also noticed this bug/feature/misconfiguration even if I open a DB in Management Studio...
Please, could someone tell me, is there any timer that "puts a DB to sleep" if no one is using it for some time? Can I change the way server behaves in this situation?
View 4 Replies
View Related
Aug 25, 2015
I know I should probably be posting this in the RS section but I have a Win 2008 R2 server and RS 2012 along with SSIS and Database server installed. I also have SQL 2005 instance with Sql 2005 SSIS running on the server.
I saw that Reporting services was consuming 9gb of ram a few days ago with no published reports. It's just a default install.
So I investigated the settings and the document: [URL]......
I added this section to the reportserver.config file to restrict memory usage
<WorkingSetMaximum>512000</WorkingSetMaximum>
<WorkingSetMinimum>128000</WorkingSetMinimum>
Any example altering the reportserver.config file in your standard build of sql server reporting services?
View 0 Replies
View Related
Jul 30, 2006
Hi,
Here is my code on how i connect to my database
Dim rst As New ADODB.Recordset Dim conn As New ADODB.Connection Dim vresponse As String ' On Error GoTo err_proc Try conn.Open(GetConn(getconnid)) Select Case getmode Case 0 'execute then return first field rst = conn.Execute(getsql) If rst.EOF = False And rst.BOF = False Then Try vresponse = IIf(IsDBNull(rst.Fields.Item(0).Value), "", rst.Fields.Item(0).Value) ' vresponse = rst.Fields.Count Catch ex As Exception vresponse = "Error :" + ex.Message End Try End If Case 1 'execute only If bTransactional = True Then conn.BeginTrans() End If conn.Execute(getsql) vresponse = "OK" If bTransactional = True Then conn.CommitTrans() End If End Select
Return vresponse.Trim
* line 1)
rst = Nothing conn = Nothing
Exit Function
supposed to be *line 1) terminates the connection/session form SQL server. But when i check to the SQL Query Analyzer, there is still some connections related to this command. So when i run again this commands, it creates another connection. How can i terminate/end my sessions in SQL.
Thanks
View 1 Replies
View Related
Jan 12, 2000
Hi all!
First of all, thanks to those who gave me suggestions to use sql-dmo
to generate sql scripts of a database.
I've found it extremely useful!
Here's my new problem.
I have VB code that connects to a database, extracts the schema
and generates the sql.
After some work with this script, I have some new scripts to alter the database and I have tried running these scripts in a separate project
with the ExecuteImmediate method of the Database object, which works well.
However, when I put this code into the first project, to reconnect to the database and execute the batch command,
I get SQL-DMO error -2147199229 (80045203)
...whatever that means..
Can anyone help me?
Thanks
View 1 Replies
View Related
Feb 27, 2008
Hi Friends,
We need a small suggestion regarding releasing the resources utilized before closing the session we established with the SQL CE database. To the best of our knowledge we are releasing the resources properly but still some how some resources get locked and we were not able to open a new session after closing the existing session.
Are there any functions or methods available to identify the existing resources, rowsets and other components who have connection with the existing session, kindly help us in this regard since we struck up mainly with this issue.
We work with EVC++ 3.0 and SQL CE 2.0
Thanks in advance.
Regards,
Sasi.
View 2 Replies
View Related
Oct 21, 1999
Hello:
We have a new sales force automation application for sales rep to log their contacts, etc. It runs on mssql 6.5, sp 4.
They log in and leave their connections on without logging off or leave their coonections idle .
We set the user connections fiarly high but we are concerned that the max amount of user connections weill be consumed and the sql serve becomes locked.
My questions are:
1) Is there any parameter in the sql server configuration that will log off idle users have a certain time and dsiconnect them?
2) A backup of the database will be made each night. If there is no parameter, would it be a good idea to stop and start the sql server every night to release the idle connections?
3) If so, how to stop sql server from the commadn and start it with a command line command and do this in a batch file as a scheduled task?
4) Is there any other way to release these idle connections each night?
Any information you can supply will be appreciated. THanks.
David Spaisman
View 1 Replies
View Related
Jan 19, 2007
hi all
i am using .net web application with sql server as my database, i want to login by authentication from my database which i ave done but now i want to add session but dont know the code so if anyone can help me with stepwise process to assign username in the seeion also to create and destroy the session.urgent help required
View 1 Replies
View Related
Apr 25, 2008
hi is it possible to insert a session into a database E.G. i'm sure this code is fine but i duno why its not inserting the session into the database the form parameter is going in fine but the session aian't
UserName.Text = Session("User")<asp:sqldatasource id="SqlDataSource1" runat="server"
selectcommand="SELECT Party.Party FROM Party"
insertcommand="INSERT INTO Vote(UserName,Vote) VALUES (User,Party)"
ConnectionString="<%$ ConnectionStrings:VotingConnectionString %>"
ProviderName="<%$ ConnectionStrings:VotingConnectionString.ProviderName %>">
<InsertParameters>
<asp:SessionParameter Name="User" Type="String" SessionField="User" />
<asp:FormParameter Name="Party" Type="String" FormField="PartyList" />
</InsertParameters>
</asp:sqldatasource>
View 7 Replies
View Related
Mar 13, 2001
How can many users share the same .adp front end, just like it as if it was a .mdb file? The problem I've ran was the following...user A logs in, user B tries to use the same .adp front end, but gets a message that is already in use therefore can't get in. Thanks in advance!
View 1 Replies
View Related
May 19, 2008
I have a process that seems to leave many orphaned sessions over a period of time. The software is by a 3rd party vendor and they can't seem to fix it. It is safe to end the orphaned sessions and I do that on a regular basis. My questions is: What is the best way to do this via T-SQL?
I can select the orphaned sessions by using a simple query on the sys.dm_exec_sessions table. How do I then run a kill command for each session_id from that query?
I appreciate any info/advice anyone might have!
View 5 Replies
View Related
Sep 6, 2006
Hey
I have a problem in controlling the report session.
I created a Reporting Model using SQL reporting Services 2005 with Forms authentication on which I implemented the security Filter based on the function GetUserID() to report only against data that belong to the login User, and I deployed the Model.
Using report Builder application I created a report that contain in the first column the user name (UserName) from entity €œUsers€?, and in the remaining columns data from other entities related to table users, I saved the report on the report server.
I logged in into report manager with user €œUser1€? and I ran the report, the result was the same as expected (in the first column €œUser1€? appeared and in the remaining column other data related to this user appeared) every thing went good
After that I logged in with the user €œUser2€? and I ran the report and here was the surprise the same data that appeared for "User1" appeared for "User2". but what was Expected is different data that belong to "User 2"
After some research that says that this problem may be caused by a session issue (the session created for the first user who enters the report server will remain for the other users that enter after him), I reset IIS and I logged in with €œUser2€?, and I ran the report the correct data for €œUser2€? appeared So it may be a session issue.
My question is:
Is there any way to control the session content and the session expiration?
Please note I used all the solution provided in my research such as URL parameter rs:ClearSession = true, and the Report Execution Options in report manager but non of this helped me.
I will be thankful for any one who could help me in this urgent issue.
Thank you
BOB
View 3 Replies
View Related
Mar 16, 2004
I'm using SQL server 7 on Win NT. I have Access 97 as a front end, with linked tables though ODBC to SQL Server. Everytime I open a table in Access, a session appears when I type sp_who2. I close that table in Access, but I when I type sp_who2 the table session is still present. Does anyone know a cause for this?
I am researching why sometimes when we close are queries and tables in Access we have sessions in SQL server that becomes orphans/ghost. I try to kill the session but can't, so therefore I have to recycle the database.
Any help would be appreciated.
View 2 Replies
View Related
Aug 29, 2011
When I probe sys.dm_exec_sessions (joining with other DMVs to get active-session info) I get the login_name column to be blank for a certain session.
Yet, for the same spid, the Login shows up properly (not blank) when I execute "sp_who2 active".
It also shows up properly in sys.sysprocesses.
What could be the reason for the faulty output of sys.dm_exec_sessions ?
View 9 Replies
View Related
Feb 15, 2008
I just have a simple question on font dependencies in reference to deployed reports. In my company, we run citrix presentation servers feeding terminal sessions out to users in remote areas.
My question is, in a terminal session, do the fonts used in the reports need to be installed on the users' local machine (who is connecting via terminal session) or is it enough to just have it on the servers feeding out the terminal session?
For example, we create a report with Arial Narrow font. Our servers hosting citrix presentation server did not have this font installed, so we installed this font on the servers (thus they're displaying correctly). Obviously the server hosting our reporting services has the font. Now a user connects to the server, is granted a terminal session, launches reporting services, report is generated with the correct font displayed, and finally prints the report.
Will the report print in Arial Narrow if the users' local machine doesn't have this font install? I remember reading somewhere that the fonts do not embed, hence the reason for installing the font on our citrix servers.
I realize this is kinda out of the scope of this forum, but perhaps someone on here uses terminal services and can answer this for me. I do plan to test this out when I get home tonight. I'd test it right now, but my computer at home isn't on therefore I can't remote home (plus wake-on-lan isn't configured) to do an actual test. Just thought I'd get some insights from other people.
Thanks in advance!
View 3 Replies
View Related
Feb 2, 2015
Is there a way to find the max number of sessions that were on a sql server since its last restart? I know how to get how many total connections there were since last restart, and the current number of sessions, but not the max number of sessions that were running since last restart. Also, opinion on the optinum number of allowable sessions, or a good way to figure out the balance?
View 0 Replies
View Related
Mar 8, 2015
So I've been monitoring long-running transactions on a SQL Server that hosts a couple of vendor-supplied databases that look after our factory.Today I noticed a pair that have confused my Excel spreadsheet (that I've been using to analyze these transactions).So here's the weird thing that I spotted. Given this query:
SELECT p.spid, p.login_time, at.transaction_begin_time, datediff(second, p.login_time, at.transaction_begin_time) as [difference]
FROM sys.sysprocesses AS p INNER JOIN
sys.dm_tran_session_transactions AS st ON st.session_id = p.spid INNER JOIN
sys.dm_tran_active_transactions AS at ON st.transaction_id = at.transaction_id
[code]....
I had a look in the event log on the server, which had just been rebooted at around that time. It seems that the clock got changed on boot-up, with the size of it quite surprising. This meant that these processes were able to start their transactions *before* they logged on. Hopefully this doesn't cause any other weird problems.So I've requested an investigation about time synchronization on our virtualization hosts... and in the mean time, have set the SQL Server services to 'delayed start'.
View 0 Replies
View Related
Jul 23, 2005
I have a table EugeneTest(id_num, fname, minit, lname)where field "id_num" is type IDENTITY and has UNIQUE constraintLet's say 2 user execute this code at the same time:DECLARE @return integeruse EugeneTestINSERT employees( fname, minit, lname)VALUES( 'Eugene3', 'F', 'Josephs')SET @return = @@IDENTITYIs is not possible to get duplicated value in id_num column becuase ofUNIQUEconstraint, but is it possible the both user get the same @@IDENTITYnumber( for example, if @@IDENTITY is evaluated and kept somewhere in thebuffer before the data actually get written to the disk )Thanks, Eugene
View 5 Replies
View Related
Feb 9, 2006
Hi,
Can you help me how to find out sessions in SQL Server 2000.
Also is there any way to see the outstanding sessions for a database?
Thanks in advance,
Vasu.
View 4 Replies
View Related
Nov 21, 2015
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time.The table has clustered index on ID column
----session 1 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
----session 2 --------
begin transaction
select * from a1
update a1
set id = 22
where id = 2
Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock.so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation.
View 3 Replies
View Related
Jun 29, 2015
We have a big software that run a warehouse distribution center, written in .NET Backhand is a SQL Server 2008 R2 STD database.
Now, it seems there is a problem with the sessions not being properly closed after each call to the DB. Here is the message got form SQL:
DESCRIPTION:A new connection was rejected because the maximum number of connections on session ID 57 has been reached. Close an existing connection on this session and retry.
In the .NET code, connection is made with the following code:
If oConn Is Nothing Then oConn = New SqlConnection
If oConn.State = ConnectionState.Open Then oConn.Close()
With oConn
.ConnectionString = "Server=" & Server & ";Database=" & DB & ";User ID=" & User & ";Password=" & Pass & ";Connection Timeout=" & 5 & ";MultipleActiveResultSets=" & True
.Open()
End With
This code is called once, at the opening of the software
StoredProc are call with the code:
Try
oCmd = New SqlCommand
With oCmd
.Connection = oConn
.CommandType = CommandType.StoredProcedure
[Code] .....
So every Command is closed after execution, yet, they stay active in the SQL Server. Is there something I'm missing here?
View 3 Replies
View Related
