September 19, 2012 at 8:36 am
It is an 8 years project.I am working on this project since 1 year.ther is a stored procedure to verifiy user login credentials.If every thing is correct then user is logined to that website.
I didn't get any problem till now, from last 2 days i am getting some problems. Users are unable to login into my website.They are entering user name and password and clicking login button, after clicking the login button it saying user and password is incorrect.
But every users is getting same problem.
When i open the log file , in the log file there is exception i.e get_login_user is an stored procedure (skg is username and password is skg@123)
[09-19-2012 07:51:31:786]Inside Exceptionexec get_login_user 'skg', 'skg@123'
[09-19-2012 07:51:31:817]ExceptionSystem.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
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.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
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(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at DbUtil.ExecuteQuery(String sqlStr, String dtblName) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2010\DbUtil.cs:line 27
public static DataTable ExecuteQuery(string sqlStr, string dtblName)
{
SqlConnection Conn = DbUtil.GetDbConn();
SqlCommand sqlCommand = new SqlCommand(sqlStr, Conn);
sqlCommand.CommandTimeout = 120;
DataTable result;
try
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, dtblName);
DataTable dataTable = dataSet.Tables[dtblName];
DbConn.Close();
DbConn.Dispose();
if (dataTable.Rows.Count != 0)
{
result = dataTable;
}
else
{
result = null;
}
}
catch (Exception ex)
{
SnAppLogger.Write("Inside Exception" + sqlStr.ToString());
SnAppLogger.Write("Exception" + ex.ToString());
DbConn.Close();
DbConn.Dispose();
result = null;
}
return result;
}
The Line 27 is sqlDataAdapter.Fill(dataSet, dtblName);
This Problem getting Daily Morning at 7:30 AM, no body is logined to that website.
If i restart the Database Server, then they are able login into website.
I am unable to find the problem.
Please help me........
Thanks,
Suresh
September 19, 2012 at 9:11 am
The first thing that I would check is if you have blocking on the table. Both of us are not native English speaking, so maybe I didn't understand you correctly, but from what I understood, it happens at 7:30 in the morning. Did you add a new job or modified an existing job? If the answer is yes, then try checking the job. In any case, if it happens again you can use the query bellow to get the blocking chain header (in case that the problem is blocking).
select blocking.session_id, program_name, login_name, status
from sys.dm_exec_sessions blocking inner join sys.dm_os_waiting_tasks blocked
on blocked.blocking_session_id = blocking.session_id and blocked.blocking_session_id <> blocked.session_id
where blocking.session_id not in (select session_id from sys.dm_os_waiting_tasks where blocking_session_id <> session_id)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 19, 2012 at 9:33 am
suresh0534 (9/19/2012)
09-19-2012 07:51:31:817] ExceptionSystem.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
Suresh
for me, this says that there's a connection problem, long before you get to query any tables.
i would think you need to check the following:
1. there really is network connectivity between the clietn and the SQL server.
2. the SQL server itself is up and running at teh operating system level.
3. the SQl service is running.
4. the server was not renamed, or it's ip address ont he network was not changed receitnly.
5. no new fireall changes were created by teh IT department which now prevents connectivity., say at the port 1433/1434.
Lowell
September 19, 2012 at 9:52 am
We are not running any job at that time, no sql job running.
But problem is occurring at that time.
So i am restarting the server.
Without restarting the server, what should i can do.
If i refresh the DataBase it will work or not?
Thanks,
Suresh
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply