Skip to content
April 12, 2011 / kiranpatils

Timeout error when running CleanUpDatabases job

Challenge:

If you are running CleanupDatabase (control panel -> clean up databases) and facing Timeout error as shown below:

Job started: CleanUpDatabases|System.Exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. —> System.Data.DataException: Error executing SQL command:  declare @x bigint set @x = 0 DECLARE @item TABLE(ID uniqueidentifier,parentID uniqueidentifier) INSERT INTO @item (ID,parentID)   SELECT  [ID],[ParentID] FROM [Items]  DECLARE @temp TABLE(ID uniqueidentifier) WHILE (SELECT count(id) FROM @item ) <> @x begin set @x = (SELECT count(id) FROM @item ) delete from @temp; insert into @temp (ID)   SELECT  id FROM @item where parentID  = @nullId update @item SET Parentid =@nullId where Parentid  in (select id from @temp) delete from @item where  id  in (select id from @temp) end UPDATE [Items] SET [Parentid] = @nullId where [ID]  in (select id from @item) ; DELETE from [Items] where [ID] in (select id from @item) —> System.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.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery()
— End of inner exception stack trace —
— End of inner exception stack trace —
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery()
at Sitecore.Data.DataProviders.Sql.SqlDataApi.Execute(String sql, Object[] parameters)
at Sitecore.Data.DataProviders.Sql.SqlDataProvider.CleanupDatabase(CallContext context)
at Sitecore.Data.DataProviders.DataProvider.CleanupDatabase(CallContext context, DataProviderCollection providers)
at Sitecore.Shell.Applications.Databases.CleanUp.CleanUpForm.Cleaner.CleanUp()|Job ended: CleanUpDatabases (units processed: )

Then this post has a solution for you! 🙂

Solution:

Open your Web.Config file and try increasing the “DefaultSQLTimeout” setting value and adding the “DataProviderTimeout” setting in your Web.config as follows:

<setting name=”DataProviderTimeout” value=”00:05:00″ />

You can increase this parameter (it should be less than 24 hrs) or set it to “00:00:00”(infinity).

We set the value to 50 Minutes for 47 GB database and it worked.  But it all depends on your Server’s Hardware configuration as well.

Why it was causing an error?

It was causing an error due to Default timeout for running SQL query was not enough for job to get its job done!

Happy Cleanup! 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: