Ms Sql Server Giving Timeout Expired

We are using MS SQL 2000 it is giving timeout expired proble Intermittently and the users getting slow response in the Transactions.
Need Help to solve the same.The ConnectionTimeout property of the Connection object, should be increased as much as is needed (applicable to VB6, .Net). I think that any languagae has a similar property.|||Take also in account this method .|||Hi,

In some servers, if the Select statement is run with out specifying WITH (NOLOCK) then it will throw timeout expired.

Suppose you run the following query from your application

Select * from emp;

then it will throw time out expired

Please use

Select * from emp WITH (NOLOCK);

then it wont expire. I am not sure of which server setting that causes this problem.
The application will be slow if some one is trying to run a select statement without using "WITH (NOLOCK)"

Try to avoid users from executing the Select statement, which poses this problem.

Also if many Users are accessing the DB, then we should give some time for the DB to process it. If many processes queues up, then finally some will expire.This is purely due to the load on the DB.

Also monitor the performance of DB, some stored procedures that we write will eat up all DB resources because of which the application will hang up until that is executed.

It may also be due to increase in number of users using the application.

Hope this will be useful.|||Ye, good observation, but I think that it's not always recommended to use NOLOCK, and for any Select. Eventually for special queries where it's expected to take a long time.

MS SQL Deadlock but no SQL transactions exists - Help

The deadlock victim occurred executing the line

DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)
NotesDisplay is the .aspx page

You can clearly see that no transaction was in use, so the deadlock should not have occurred as far as I am concerned (3rd time I seen something like this).

This happened on MS SQL 2005 SP1.

The other part of this deadlock was caused by a delete statement (which did have a transaction) deleting rows that may have formed part of this view but that should only resulted in a normal lock, not a deadlock.

I have included the complete code that was executed along with the error message for reference.
Can anyone explain this?

PartialClass NotesDisplay

Inherits System.Web.UI.Page

Private mUserInfoAs Database.UserInfo

Private mDbAs Database

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim RegOccIDAsInteger =CInt(Request.QueryString(("RegOccID")))

Dim DT3As Data.DataTable

Main.GetSession(Me.Page, mUserInfo, mDb)

DT3 = mDb.MatchingRegs(RegOccID)

PublicFunction MatchingRegs(ByVal RegOccIDAsInteger)As DataTable

Dim DT3As Data.DataTable

DT3 = GetSQLTable("Select * from vw_RegsBeingMarked where RegOccID = " & RegOccID)

If DT3.Rows.Count = 0Then

ThrowNew Exception("Reg occ not found")

EndIf

Dim StartsAsDate

Dim EndsAsDate

Dim WhereAsString

With DT3.Rows(0)

Starts = .Item("Starts")

Ends = .Item("Ends")

If .Item("Ends")Is DBNull.Value =FalseAnd .Item("RoomID")Is DBNull.Value =FalseAnd .Item("RegStaff")Is DBNull.Value =FalseThen

' Posible merge with other regs

Dim cmdAsNew Data.SqlClient.SqlCommand

cmd.Parameters.AddWithValue("@.Starts", Starts)

cmd.Parameters.AddWithValue("@.Ends", Ends)

Where =" Where RoomID = " &CInt(.Item("RoomID")) &" AND Starts = @.Starts AND Ends = @.Ends AND RegStaff = " &CInt(.Item("RegStaff")) &" AND RoomID2 "

If .Item("RoomID2")Is DBNull.ValueThen

Where &=" is null"

Else

Where &=" = " &CInt(.Item("RoomID2"))

EndIf

DT3.Rows.Clear()

DT3 = GetSQLTable("Select * from vw_RegsBeingMarked " & Where, cmd)

EndIf

Return DT3

EndWith

EndFunction

PublicFunction GetSQLTable(ByVal SQLStringAsString,OptionalByRef CmdAs SqlCommand =Nothing)As DataTable

Dim myConnAs SqlConnection

Dim LocalConAsBoolean =False

If CmdIsNotNothingAndAlso Cmd.ConnectionIsNotNothingThen

myConn = Cmd.Connection

If SQLString =""Then

SQLString = Cmd.CommandText

EndIf

Else

myConn = DBOpenSQLConnection(SqlConEnum.Timetables)

LocalCon =True

EndIf

Try

GetSQLTable = GetSQLTableInternal(SQLString, myConn, Cmd)

Catch exAs Exception

Throw

Finally

If LocalConThen

DBCloseSQLConnection(myConn)

EndIf

EndTry

EndFunction

PrivateFunction GetSQLTableInternal(ByVal SQLStringAsString,ByRef myConnAs SqlConnection,OptionalByRef CmdAs SqlCommand =Nothing,OptionalByRef TransAs SqlTransaction =Nothing)As DataTable

Dim mySQLCommandAs SqlCommand

Dim mySQLAdaptorAs SqlDataAdapter

If CmdIsNothingThen

mySQLCommand =New SqlCommand

Else

mySQLCommand = Cmd

EndIf

mySQLCommand.Transaction = Trans

mySQLAdaptor =New SqlDataAdapter(mySQLCommand)

mySQLCommand.Connection = myConn

mySQLCommand.CommandText = SQLString

GetSQLTableInternal =New DataTable

mySQLAdaptor.Fill(GetSQLTableInternal)

EndFunction

Screen shot of SQL profiler deadlock information (showing lock types etc)
http://img299.imageshack.us/img299/9625/deadlockmb9.jpg

System.Data.SqlClient.SqlException: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlDataReader.HasMoreRows()

at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

at System.Data.SqlClient.SqlDataReader.Read()

at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)

at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)

at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at Database.GetSQLTableInternal(String SQLString, SqlConnection& myConn, SqlCommand& Cmd, SqlTransaction& Trans)

at Database.GetSQLTable(String SQLString, SqlCommand& Cmd)

at Database.MatchingRegs(Int32 RegOccID)

at NotesDisplay.Page_Load(Object sender, EventArgs e)

at System.Web.UI.Control.OnLoad(EventArgs e)

at System.Web.UI.Control.LoadRecursive()

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

********************

at System.Web.UI.Page.HandleError(Exception e)

at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

at System.Web.UI.Page.ProcessRequest()

at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)

at System.Web.UI.Page.ProcessRequest(HttpContext context)

at ASP.notesdisplay_aspx.ProcessRequest(HttpContext context)

at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

The answer can be found here

http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/a05e7d7f4eabf61e/337c7c6d75c38452?lnk=st&q=ms+sql+server+2005+deadlock+no+transaction&rnum=25&hl=en#337c7c6d75c38452