Showing posts with label statements. Show all posts
Showing posts with label statements. Show all posts

Monday, March 12, 2012

How can I kill sleeping processes in SQL Server?

Dear,

Our ASP.NET scripts send SQL statements (as inline SQL or SP) to process the requested job. After the job execution, the process ID stays in the server and waits for next command with sleeping status.Since this process does not go away, next job adds another process and eventually, the server is overloaded with these processes and dies.

How can I kill this sleeping processes?

Regards,

Echo

Off the top of my head if you
EXECsp_who
it will give you the SPID's for all your processes. Armed with that you could then issue  
KILL 42
which will terminate SPID 42
So I would guess that you could set up a job in SQL Server to get a list of all offending SPID's, schedule it to run every hour for arguments sake, and then step through that list issuing the KILL command.
HTH|||

Please do take care when using KILL to kill SQL sessions.SPID<=50 are SQL system processes, so you'd better not kill such processes. Always confirm that the session is no longer useful (has been idle for a long time or invovled in blocking/deadlock) before you KILL it. For more information about the KILL command in T-SQL, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp

|||

Iori_Jay:

Please do take care when using KILL to kill SQL sessions

I very much agree with Iori_Jay, and really should have advised caution in my earlier post.

|||

For example, you can use such query to Kill SQL sessions which are sleeping and have been idle more than 1 hour:

DECLARE @.v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND status='sleeping'
AND DATEDIFF(mi,last_batch,GETDATE())>=60
AND spid<>@.@.spid

OPEN c_Users
FETCH NEXT FROM c_Users INTO @.v_spid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
PRINT 'KILLing '+CONVERT(VARCHAR,@.v_spid)+'...'
EXEC('KILL'+@.v_spid)
FETCH NEXT FROM c_Users INTO @.v_spid
END

CLOSE c_Users
DEALLOCATE c_Users

|||

Dear all,

Thank U everybody for replying. But I do not want to run any process at SQL server. Can't I manage it from ADO.NET. Is it possible that after executing a process it will automatically die.

I think the problem may related to connection pooling. Can I stop connection pooling of ADO.NET programatically?

Regards,

Sultan

|||

Why do you want to stop connection pooling...Connection Pooling can improve performance for database connections. If you just want to prevent unexpected remaining connecitons, you can just set the connection timeout when you create the connection in your code.

You can also take a look at this article:

Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

Sunday, February 19, 2012

How can I generate INSERT statements from Management Studio

Hello All,

How are you guys doing? Hope all is well.

This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.

Thanks a bunch!

Mohammed

No, you can′t SSMS does not support that yet. You can create the statements using the script from here:

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi Mohammed,

in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp, where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign..

regards

|||

Hello Jens!

Good to hear from you and thank you for responding to my post. I really appreciate your assistance. I used to live in Germany myself...in Bonn....and I had an amazing time...and I would love to go back there!!!

Thanks again,

Mohammed

|||

Hello Jens,

Sorry to bug you again. Would I be able to generate the INSERT statements if I was using full-blown SQL Server 2005?...or does this have nothing to do with the fact that I am using SQL Server Express?

Thanks,

Mohammed

|||

hi Mohammed,

even SQL Server Management Studio "full version" does not provide that feature..

regards

How can I generate INSERT statements from Management Studio

Hello All,

How are you guys doing? Hope all is well.

This is my problem...I accidentally deleted a bunch of my SQL scripts and would like to generate the INSERT statements from Management Studio. I am currently using SQLExpress. Is there a way I can accomprish my task? Kindly advise.

Thanks a bunch!

Mohammed

No, you can′t SSMS does not support that yet. You can create the statements using the script from here:

http://vyaskn.tripod.com/code.htm#inserts

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi Mohammed,

in addition to Jens, you can have a look at http://www.karaszi.com/sqlserver/info_generate_script.asp, where "several" option are listed.. for your convenience, you can perhaps try a free prj of mine, amInsert, available for free at the link following my sign..

regards

|||

Hello Jens!

Good to hear from you and thank you for responding to my post. I really appreciate your assistance. I used to live in Germany myself...in Bonn....and I had an amazing time...and I would love to go back there!!!

Thanks again,

Mohammed

|||

Hello Jens,

Sorry to bug you again. Would I be able to generate the INSERT statements if I was using full-blown SQL Server 2005?...or does this have nothing to do with the fact that I am using SQL Server Express?

Thanks,

Mohammed

|||

hi Mohammed,

even SQL Server Management Studio "full version" does not provide that feature..

regards

How can I generate insert statements for sample data?

Hi,
I want to post a question but I want to also include a script for you guys
to create the table on your end as well as some sample data.
I used to have a tool that would take the data in a table and generate a
bunch of INSERT statements. I can't seem to locate it. Could someone please
give me a pointer on where I can find that script?
--
Thanks,
SamSam wrote:
> Hi,
> I want to post a question but I want to also include a script for you
> guys to create the table on your end as well as some sample data.
> I used to have a tool that would take the data in a table and
> generate a bunch of INSERT statements. I can't seem to locate it.
> Could someone please give me a pointer on where I can find that
> script?
www.aspfaq.com/5006
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Find my post of yesterday :)
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:DF35D0A0-C291-4B03-9860-37DF09F92846@.microsoft.com...
> Hi,
> I want to post a question but I want to also include a script for you guys
> to create the table on your end as well as some sample data.
> I used to have a tool that would take the data in a table and generate a
> bunch of INSERT statements. I can't seem to locate it. Could someone
> please
> give me a pointer on where I can find that script?
> --
> Thanks,
> Sam|||To generate Insert statements from the existing data Check out
http://vyaskn.tripod.com/code.htm its really good.
Source code:: http://vyaskn.tripod.com/code/generate_inserts.txt
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Mark Nijhof" wrote:

> Find my post of yesterday :)
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:DF35D0A0-C291-4B03-9860-37DF09F92846@.microsoft.com...
>
>|||Try www.sqlscripter.com to generate INSERT, UDPATE, Delete data scripts.
"Sam" wrote:

> Hi,
> I want to post a question but I want to also include a script for you guys
> to create the table on your end as well as some sample data.
> I used to have a tool that would take the data in a table and generate a
> bunch of INSERT statements. I can't seem to locate it. Could someone pleas
e
> give me a pointer on where I can find that script?
> --
> Thanks,
> Sam

How can I force some data maintenance statements whether the transactions success or

How can I force some data maintenance statements whether the transactions
success or failure?
The case is as:
When the insert statement to do, the insert trigger will execute, when the
triggers has errors, then the triggers should write the custom messages to
the another tables and then rollback the insert statements. The client
sides will access the another tables gather the messages. How to write this
case?Refer these error handlings
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Madhivanan|||Hi, ABC
If you want something logged in a table even when an error appears
(because of a constraint violation, for example), you cannot do this.
If you only want something logged according to a certain condition
which is verified by the trigger, you have two solutions:
a) if it's OK to just ignore the incorrect value, use an "INSTEAD OF"
trigger which updates only the correct values and returns (maybe with a
RAISERROR, but with no ROLLBACK)
b) use a normal trigger, but after RAISERROR and ROLLBACK, insert the
message in the error log table, with something like this:
USE tempdb
SET NOCOUNT ON
GO
CREATE TABLE Employees (
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(50) NOT NULL UNIQUE,
Salary money NOT NULL CHECK (Salary>0)
)
INSERT INTO Employees VALUES ('Adam', 50000)
INSERT INTO Employees VALUES ('Bob', 70000)
GO
CREATE TABLE ErrorLog (
MessageID int IDENTITY PRIMARY KEY,
EventDate datetime NOT NULL DEFAULT GETDATE(),
UserName varchar(30) NOT NULL DEFAULT SYSTEM_USER,
HostName varchar(30) NOT NULL DEFAULT HOST_NAME(),
Message nvarchar(1000) NOT NULL
)
GO
CREATE TRIGGER Employees_U_SmallRaisesOnly ON Employees
FOR UPDATE
AS
IF @.@.ROWCOUNT>0 AND UPDATE(Salary) BEGIN
SET NOCOUNT ON
IF EXISTS (
SELECT * FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
) BEGIN
DECLARE @.Messages TABLE (Message nvarchar(1000))
INSERT INTO @.Messages (Message)
SELECT 'Someone tried to change the salary for '+i.EmployeeName
+' from '+CONVERT(varchar(23),d.Salary,1)
+' to '+CONVERT(varchar(23),i.Salary,1)+' !'
FROM inserted i
INNER JOIN deleted d ON i.EmployeeID=d.EmployeeID
WHERE i.Salary>d.Salary*2
RAISERROR ('You cannot raise a salary to more than double !',16,1)
ROLLBACK
INSERT INTO ErrorLog (Message)
SELECT Message FROM @.Messages
END
END
GO
UPDATE Employees SET Salary=60000 WHERE EmployeeName='Adam'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
UPDATE Employees SET Salary=200000 WHERE EmployeeName='Bob'
GO
SELECT * FROM Employees
SELECT * FROM ErrorLog
GO
DROP TABLE Employees, ErrorLog
Razvan
PS. I don't know if this behaviour is documented and/or supported.|||On Thu, 24 Nov 2005 14:19:17 +0800, ABC wrote:

>How can I force some data maintenance statements whether the transactions
>success or failure?
>The case is as:
>When the insert statement to do, the insert trigger will execute, when the
>triggers has errors, then the triggers should write the custom messages to
>the another tables and then rollback the insert statements. The client
>sides will access the another tables gather the messages. How to write thi
s
>case?
>
Hi ABC,
I know of only two ways to persist something over a ROLLBACK.
If you want to log a single error, best is to use variables:
SET @.ErrorMsg = 'Whatever the error code should be'
SET @.ErrorSrc = 'Something to identify where the error was made'
SET @.ErrorWhen = CURRENT_TIMESTAMP
ROLLBACK TRANSACTION
INSERT INTO Logging (ErrorMsg, ErrorSrc, ErrorWhen)
VALUES (@.ErrorMsg, @.ErrorSrc, @.ErrorWhen)
In this example, the variables are not actually needed since I use
hard-coded strings. But if the error message or error source has to be
taken from inserted or updated rows that will be rolled back, then use
this method.
If you have to log an unknown number of error messages (e.g. a multi-row
update fails and you want to log all rows that vioalte the business
constraint), then use a table variable:
DECLARE @.Errors TABLE(...)
INSERT INTO @.Errors (..., ..., ...)
SELECT ..., ..., ...
FROM inserted
WHERE business rule is violated
ROLLBACK TRANSACTION
INSERT INTO Logging (..., ..., ...)
SELECT ..., ..., ...
FROM @.Errors
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)