Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql executing timeout

Posted on 2014-02-14
4
Medium Priority
?
288 Views
Last Modified: 2014-02-16
Hello,
In SQL server manager I was deleting rows from a table.  When I returned an hour later it was still executing even though the rows that should be deleted were only about 2000.
While it was executing other calls to the database timed out, so that users could not login to my website.  The reason may be that it could not delete a row because of foreign key.
Why did it continue to execute and not stop? Can I change the sql settings so this does not happen again? I am using sql 2008.
0
Comment
Question by:johnson1
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39860178
there may be various reasons why SQL Server took that much amount of time to delete such a small data

The most probables one are listed below in the high to low probablity factor

1) The table that you are deleted is locked by some other user
2) An explict transasction is opened by an user in another SSMS window and left it open
3) The Forieghn key constraint table has a huge data in the underlying tables probably millions
4) the foreighn key constraint table is locked...
0
 

Author Comment

by:johnson1
ID: 39861196
Thank you Surendra. Is it possible to have sql stop executing if it exceeds certain timelimit?
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39861200
if the SQL is triggered from an application code (for example .Net / Java)... then yes the SQL can be stopped by adding connectionTimeOut property in the connection string.

But if the SQL is triggered in SSMS, you can use the below tuotorial to do the same

http://www.hosting.com/support/sql/configure-query-timeout-period-in-sql-management-studio/
0
 

Author Closing Comment

by:johnson1
ID: 39863102
Thank you
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
Loops Section Overview

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question