Closed Transaction Keeps Running...awaiting Command
Jul 20, 2005
I am debugging an app which blocks many processes in a SQL7 server DB.
The app log writes every transaction "open" and "close".
The weird thing is : when the app logfile says the transaction is
dropped (object closed) the db keeps showing the process "running", in
a sleeping mode, with open_tran in 2 or even 3 and in an awaiting
command status.
We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on
IIS side, an equal box for MTS (same patches and updates) and a SQL
Server 7 (on NT4, same fixes ans SPs) database on another box.
Is this normal? Those sleeping processes are blocking other apps and
everything gets slow and messy....the only solution is to kill those
blocking processes.
Thanks!
View 1 Replies
ADVERTISEMENT
Sep 4, 2015
During our DR drill we found that the same code which used to run perfectly fine on our Primary Data Centre is running very slow on Disaster Recovery DB Server and there are Lot's of open transaction with sleeping status and waittype as 'AWAITING COMMAND'.CPU, Memory and disk utilization are good. The ping reply between the app server and the DB server is well within the limit's even blocking is not their, also nothing is reported in the error logs. We are using SQL server 2014 STD 64 BIT on Windows Server 2012.
View 6 Replies
View Related
Dec 3, 2005
I am using SQL Server 2000. In Enterprise Mgr, under Management/CurrentActivity, I see a list SPIDs.I have a multi-tiered .NET web app that uses ADO.NET to connect to thedatabase. Each time a Stored Proc is called, a new connection is opened, thedata retreieved, and the connection closed and set to Nothing (in VB.NET).Yet, I see several (15 or so) SPIDs for the user ID that the web app uses toconnect to the database.The database is local and private, so I know all connections are from me.What does Status=SLEEPING Command=Waiting Command mean?What will happen when we get 1000's users on the web app?Is SQL doing some kind of connection pooling?
View 1 Replies
View Related
Nov 1, 2006
Hi,Database Server running slow. I used SP_WHO2 ACTIVE and the resulthas many ofXXXX SA RUNNABLE MASTER AWAITINGCOMMAND..........(XXXX is spid)and they stay for couples seconds.Last week , the server was running fine and during weekend ,noneof errors record in Windowsevent log or SQL Server log.Server is Windows 2003 server with 4 CPUs of xeon , 4 GB of memoryandMSSQL SERVER 200 SP4.I found out that every runnable master db tasks has shared lock onkey of'master.dbo.sysxlogins.sysxlogins' objectIs this the problem of slow running database server?Please advice.Peeud
View 1 Replies
View Related
Oct 15, 2007
I have gathered from reading online that I need to create a 2nd connection to SQL Server if I want to insert records into the database while in a "while (reader.Read())" loop.
I am trying to make my code as generic as possible, and I don't want to have to re-input the connection string at this point in my code. Is there a way to generate a new connection based on the existing open one? Or should I just create 2 connections up front and carry them around with me like I do for the 1 connection now?
Thanks.
View 7 Replies
View Related
Jun 26, 2007
This is my code:1 If Session("ctr") = False Then
2
3 Connect()
4
5 SQL = "SELECT * FROM counter"
6 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
7 dbRead()
8
9 If dbReader.HasRows = True Then
10
11 dbReader.Read()
12 hits = dbReader("hits")
13 hits = hits + 1
14 dbClose()
15
16 SQL = "UPDATE counter SET hits=" & hits
17 SQL = SQL & " WHERE ipaddress='" & Request.ServerVariables("REMOTE_ADDR") & "'"
18 dbExecute()
19
20 Else
21
22 SQL = "INSERT INTO counter(ipaddress,hits)"
23 SQL = SQL & " VALUES('" & Request.ServerVariables("REMOTE_ADDR") & "',1)"
24 dbExecute()
25
26 End If
27
28 Session("ctr") = True
29
30 End If
1 Public Sub Connect()
2 Conn = New SqlConnection("Initial Catalog=NURSETEST;User Id=sa;Password=sa;Data Source=KSNCRUZ")
3 If Conn.State = ConnectionState.Open Then
4 Conn.Close()
5 End If
6 Conn.Open()
7 End Sub
8
9 Public Sub Close()
10 Conn.Close()
11 Conn = Nothing
12 End Sub
13
14 Public Sub dbExecute()
15 dbCommand = New SqlCommand(SQL, Conn)
16 dbCommand.ExecuteNonQuery()
17 End Sub
18
19 Public Sub dbRead()
20 dbCommand = New SqlCommand(SQL, Conn)
21 dbReader = dbCommand.ExecuteReader
22 End Sub
23
24 Public Sub dbClose()
25 SQL = ""
26 dbReader.Close()
27 End Sub
View 2 Replies
View Related
May 3, 2008
the class code:
Dataase.cs: using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Xml.Linq; using System.Data.SqlClient; using System.Data.Common; using System.Web; /// <summary> /// Summary description for DataBase /// </summary> public class DataBase { private SqlConnection con=new SqlConnection(); private void Open() { if (con==null) { con = new SqlConnection("Data Source=58.17.30.81;Initial Catalog=a1230192748;Persist Security Info=True;User ID=a1230192748;Password=***"); } if (con.State == System.Data.ConnectionState.Closed) { con.ConnectionString = "Data Source=58.17.30.81;Initial Catalog=a1230192748;Persist Security Info=True;User ID=a1230192748;Password=****"; con.Open(); } } public void Close() { if (con != null && con.State != System.Data.ConnectionState.Open) con.Close(); } public DataBase() { // // TODO: Add constructor logic here // } public string liuyan(string id,string sign) { string com=string.Empty; switch(sign) { case "xiaobiaoti": com="Select subject from liuyan where liuyanid='"+id+"'"; break; case "def_message": com="Select message from liuyan where liuyanid='"+id+"'"; break; } SqlCommand myCommand=new SqlCommand(com,con); Open(); try { SqlDataReader sdr=myCommand.ExecuteReader(); if (sdr.Read()) { return sdr[0].ToString(); } else { return ""; } sdr.Close(); //what i have written.} catch (Exception ex) { HttpContext.Current.Response.Write("<script>alert('error:" + ex.Message + "')</script>"); return ""; } finally { myCommand.Dispose(); Close(); } } }
it was instantiated once in aspx.cs code.I invoke liuyan(string id,string sign) twice.The first one is OK and the second one makes an exception.
View 3 Replies
View Related
Oct 17, 2000
Sorry for the new thread.
How to run a DTS job from command prompt? Any help is appreciated!
Thanks.
View 1 Replies
View Related
Jul 25, 2006
I have a requirment, when a user logs in to the pc, I want to run some SQL scripts which is been saved as a stored procedure on another pc. Could this be possible. I know in oracle you could do it using SQLLDR. Is there any way to do it in SQL?
Please advice. Any help is greatly appreciated.
View 3 Replies
View Related
Jan 23, 2001
I have created linked servers and successfully run the following stored procedure.
exec [db-2001server].sales..sp_test
Can anyone give me the exact syntax for running the same from command promt(without using the query analyzer).I am new to isql/osql.So,please help me with the syntax.
Thanks!!
View 1 Replies
View Related
May 4, 2004
Hi all
I currently have a problem where I need to create a backup from MSSQL, via a command line, ideally from a batch script. This is on a PC. Each machine has SQL Server 2000 on.
I just need to know the code that dos will tell it to run the sql statement. Ideally the command will also give the directory to place the backup.
Regards MiloJ
View 2 Replies
View Related
Nov 7, 2007
HiWe're using SqlServer 2000.I want to run a DTS using the DTSRUN command.The commmand is inside a ".bat" file which my application is runningfrom an SQL client.No matter how I run it from the client it fails giving me the message:[ConnectionOpen (Connect()).]SQL Server does not exist or access deniedI've tried to run it from the client from the application, from thecommand prompt and it fails.I went into enterprise on the client and tried to run the DTS fromthere. The run failed already on the 1st step which is a call to astored procedure, also giving me the same message. This really stuck meas wierd because in enterprise I'm accessing the server successfully sowhy the above messgae. It identifies the server and I am accessing it.Because it failed on the 1st step which is a call to a stored procedureI tried running the sp from within query analyzer and it succeeded.Has anyone got any idea what the problem is and ghow to solve it.Thanks !David Greenberg
View 1 Replies
View Related
Oct 9, 2007
Hi Techs,
I have created several DTS Packages those are working fine, tested and verified.
I need to run them by a single click, either by batchfile or some sort of command.
Thanks
Iqbal Ansari
View 4 Replies
View Related
May 3, 2006
Hi,
i've got a function where i have a sql command with a transaction....
i make some updates,
then i make a select (using a datareader),
then i make some more updates
[red]then i make another select (using a datareader)
(this is where the thing fails)[/red]
and in the end i should make some inserts....
Code:
The Exception given is:
The SqlCommand is currently busy Open, Fetching.
at System.Data.SqlClient.SqlCommand.set_Transaction(SqlTransaction value)
at System.Data.SqlClient.SqlTransaction.GetServerTransactionLevel()
at System.Data.SqlClient.SqlTransaction.Rollback()
at Monitor_Regime_Livre.MonitorRegLivre.AnaliseFichRetorno()
at Monitor_Regime_Livre.MonitorRegLivre.TemporizadorFichRetorno_Elapsed(Object sender, ElapsedEventArgs e)
Well i do close the 1st Datareader...
So i was thinking can this be happening because i'm doing a select
that it's supose to give me back results that i've updated in the transaction?
Thanks in advance...
View 1 Replies
View Related
Jun 14, 2001
Hi All,
How can I run a host command (OS command ) from ".sql" script of sql server?
-- Rajesh
View 2 Replies
View Related
Feb 29, 2008
Hello friends I want to ask that by which command I can know about how many process are currently running on the server ?
Waiting for reply.......
View 7 Replies
View Related
Apr 17, 2007
Hi Everyone!
I'm trying to run have SQL Server Agent excute an SSIS package from the command line and I keep recieving an error message. I will assume that I have miss typed something. Can someone validate that the execute line looks workable?
"C:Program Files (x86)Microsoft SQL Server90DTSBinndtexec.exe" /F "F:ProjectsSSISCustomerMaster_1CustomerMaster_1Package.dtsx" /CONNECTION "APLUS70F70.AKIN";""uid=AKIN;Dsn=APLUS70F70;"" /CONNECTION "CRMPSQL.IBT_Aplus";""Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Thanks in advance!
Anthony Akin
View 10 Replies
View Related
Jan 11, 2008
My table has a running number primary key. I want to insert data to this table and automatically generate running number pk. I try to write SQL command like this.
SELECT MAX(ID) AS MaxId from test
INSERT INTO test (ID, data1, data2, data3) VALUES (MaxId+1, @data1, @data2, @data3)
but it fails. How should I do? Thank you in advance
View 1 Replies
View Related
Apr 22, 2000
I need to start a job task from the command line. Does anyone know of the correct command(s) to do this?
I also need to check to see if a specific job task is currently executing from the command line. Does anyone know of the correct command(s) to do this?
Thanks for any assistance.
Jim
View 4 Replies
View Related
Jun 17, 2015
For reasons beyond the scope of my question, is there a way to run this command within a Stored Procedure from a low privileged user login? I can grant the entity "db_ddladmin" privilege and the proc runs, but I'd rather not give out that level of permission to what is basically a glorified web access login.
View 5 Replies
View Related
Jul 20, 2005
Hi,I have dumped a very large database from mysql (using mysqldump program)as a raw sql file. The reason was, convert this database to a MSSQLdatabase. Since mysqldump creates the file as raw sql file with thedatabase-table structures and the data in it, I thought using OSQL commandline utilities should work to out this whole database in MSSQL server.I have run this command from command line:osql -u sa -i mysqldump.sqlIt is going since yesterday. It has been almost 36 hours that it'sstarted. And in the mssql server, I see no database created yet. On thescreen of the command line, I see bunch of numbers are going in order. Iassume they are row numbers of the tables processed. But, if it is doing it,then where is it saving all this data ? I have checked the tempdb, pub db,other dbs, and I see no tables related to the database I am inserting. Willit populate it at the and of the job ? Or, am I doing something wrong here?Regards.Murtix.
View 2 Replies
View Related
Dec 23, 2005
I have created ssis package. It has been successfully running at UI level.
But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error
i have entered the command as follows.
dtexec /f "c:somefolderpackage1.dtsx"
Any points regarding this issue would be helpful.
View 7 Replies
View Related
Sep 12, 2007
I am working on reformating, cleaning, adding derived elements, etc... on data with no data validity checks on the front-end, source data. Its an incremental process where each month I add new data to the prior months' history. Sometimes the new monthly source data "surprises" me -- different values, different data definitons, etc. than expected.
I deal with these surprises by accumulating the fields/values that are unexpected after converting them to varchar with some explanatory language into a table [program_newdataprobs]
If there are any records in [program_newdataproblem] I rollback the transactions so the prior months' history remains unaltered. The problem is I then "lose" the contents of [program_newdataproblem] which I would like to hand to the source data people to troubleshoot.
I have tried the following:
Begin Transaction tran1
.... code that reformats data here
if (select count(*) from program_newdataproblem)>0
BEGIN
Begin Transaction tran2
select * into #t1 from program_newdataproblem
Commit Transaction tran2
Rollback Transaction tran1
insert into program_newdataproblem
Select * from #t1
END
This bombs because because #t1 no longer exits
Any way I can "keep" the data from program_newdataproblem when I Rollback the other transactions? (without having to store the history in separate tables that I can then access if new data errors occur)
Thanks,
View 3 Replies
View Related
Jun 17, 2015
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using
exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92
Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92
RESTORE DATABASE is terminating abnormally.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
View 8 Replies
View Related
Jun 11, 2011
How to launch Oracle SQL Developer through "Run" window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
View 4 Replies
View Related
Jan 30, 2008
Hi All,
I created a SSIS package to import an excel spreadsheet into my data warehouse.
When I run the package it runs fine. When I created a SQL Job to run the package I get the following error:
Option "Source=D:HelpDeskImportBook2.xls;Extended" is not valid. The command line parameters are invalid.
Now if I look at my source connection string in the job it looks like the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:HelpDeskImportBook2.xls;Extended Properties=HDR=YES;EXCEL 8.0;HDR=YES";
Does anyone know why it appears that my connection string looks like it is being cut off and does anyone know how I can correct the problem?
Thanks,
Scott
View 8 Replies
View Related
Dec 6, 2005
I just wanted to post a follow up to a message I posted some months agoabout a long running transaction that was blocking all other users...The link is belowhttp://groups.google.com/group/comp...649bee2002646a2By using the new "Row versioning" functionality of SQL 2005, itcompletely solved this problem. By reading the books online, it saysthere is a performance impact, but that the better performance of SQL2005 in general might offset it. So far this seems to be the case. justposting it here in case anyone else has the problem. The SQL command Iihad to execute to get everything working properly was:ALTER DATABASE DBnameSET READ_COMMITTED_SNAPSHOT ON;
View 1 Replies
View Related
Apr 3, 2006
That is a SqlException I got at a...
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
Anyone an idea what THAT means? How do I cause it? How can I work around it?
View 4 Replies
View Related
Jul 20, 2006
Hello,
I seem to be misunderstanding the way transactions work with service broker queues. We have developed and deployed a service broker application that 5 queues and a windows service for each queue on multiple servers (3 currently). Due to a last minute issue, we had to not use transactions when the services executed a recieve and I am not updating the code base to use transactions and am running into blocking issues. One of the services runs for 90 seconds (spooling to the printer) and all of the servers block on the receive operation for this queue. I thought that if I was receving messages from a single conversation, other receives against this queue would not block.
Thanks,
Jim Stallings
View 9 Replies
View Related
Dec 26, 2002
Hi Guys,
We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour
from 3.00 AM to 9.00 PM.
We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM
It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.
But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.
Please advise.
Thanks,
Anu
View 7 Replies
View Related
Aug 25, 2007
How can I execute a long running transaction using something similar to the fire and forget pattern?
I intend to start the execution of a very long stored proc from within IIS. I would like to execute a sql script that will start the job and return immediately so that it doesn't hold an IIS thread.
View 3 Replies
View Related
Oct 24, 2001
Is it safe to backup while the database is running 1000 transaction/sec.? If yes - why should I buy Veritas Backup Exec Server Edition and Veritas Backup Exec Online Backup Pack ?
Michael
View 2 Replies
View Related
Jul 6, 2014
Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies
View Related