.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Maintenance Plan Backup Database Task timeout

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :


I am using SQL Server 2008.  I am trying to create a new Maintenance Plan.  I create the plan, and then add a backup database task to the plan.  When chosing Databases to backup, if I chose "All Databases" or "All user databases", and then click OK, the UI hangs for a while, then eventually, it comes back with an unhandled exception.  If I look at the details, I see the following exception:

Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

If I chose the option "Choose databases" and pick databases to backup, things seem to work just fine.  New databases on are added to this server on a regular basis.  I do not want to maintain the list of DBs to backup.  Any suggestions?



View Complete Post

More Related Resource Links

Maintenance Plan Backup Failing Time to time

See ERRORO Below: I am using SQL Server 2008 64 bit enterprise edition and backing up on network drive. This differential backup run every weekdays and up to sat, it works just fine and sometimes fails every other week. Need some advice. I use a domain service account on SQL Server agent. Executed as user: DOMAIN\SERVER_ag_def. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  12:45:04 AM  Progress: 2010-09-11 00:45:34.09     Source: {8006A2EB-FD95-45C3-8426-554DAE1895B4}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Progress: 2010-09-11 00:58:12.09     Source: Back Up Database (Differential)      Executing query "BACKUP DATABASE [DataCollectorDW] TO  DISK = N'\\w...".: 50% complete  End Progress  Progress: 2010-09-11 01:01:17.26     Source: Back Up Database (Differential)      Executing query "declare @backupSetId as int  select @backupSetId =...".: 100% complete  End Progress  Progress: 2010-09-11 01:01:22.22     Source: Mai

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?


As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)


Maintenance Plan - Full Backup


Hey all

I am running a maintenance plan (Sql Server 2008 Enterprise) every night at mid-night. All the plan entails is doing a full backup of one database (about a 3GB database). We are getting timeouts on our site at exactly that point when the database is being backed up and they last up to 20min. I was under the impression that doing backups this way does not take the db down? Or am I wrong about this?

Has anyone else experienced this and if so what steps did you take to rectify it? Sorry if I have not provided other information, just let me know and I will give it to you.


Database mail process does not start, no error generated, for only one maintenance plan

I have three maintenance plans:

1. Application DBs - 2am (checks integrity, full backup, log backup)
2. System DBs - 3am (checks integrity, updates statistics, full backup)
3. Backup and log file cleanup - 3:30am (removes backup history, .bak and .trn files)

For items 1 and 3 above, I see the database mail process start up and receive emails that the jobs completed.  For item 2, no email....the database mail process does not start and there are no errors in the event log.  The job history shows that the job completed successfully.

I'm having the same problem on a different server, except that items 2 and 3 email me their results, but item 1 does not.  Same exact job steps and times of execution.  None of the jobs are set to notify the operator, the notification is coming from the maintenance plan.

Obviously I've gone through the basics:

Configured an operator
Created a default, public profile
Enabled mail profile in SQL Server agent
Tested mail delivery

How do I diagnose the database mail process just not starting?  Has anyone seen this before?

SQL Server 2005 SP3, with KB 970892 applied.

Backup Maintenance Plan Failure SQL Server 2005

Hi All,
I have three maintenance Plans Full, Differential and Transaction Log backup. Full and Transaction Log backup plans are working fine. In the all the maintenance plans I am taking backup of almost 60 databases.

The problem is with the differential backup plan. The backup plan is keep failing with the following errors:

FailedSad-1073548784) Executing the query "declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MY_Database' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'My_Database' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''Giants_Security_4'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'E:\\MSSQL.1\\MSSQL\\Backup\\MY_Database\\MY_database_backup_200808240500.DIFF.BAK' WITH  FILE =

Backup maintenance plan fails due to space in t-sql statement...



I have a maintenance plan that  is failing on one backup. I went into look at the t-sql code and it shows a space after the database name. I copied the t-sql code to a query window | removed the spaces and the backup ran fine. My question is how do I remove the spaces for this one backup. The other servers in the maintenance plan work fine.

Note: I copied this part out so you can see "N'T:\SQL Backup\PackagingForms \" I removed the space after PackagingForms and it worked!

t-sql code:

EXECUTE master.dbo.xp_create_subdir N'T:\SQL Backup\PackagingForms '
BACKUP DATABASE [PackagingForms ] TO  DISK = N'T:\SQL Backup\PackagingForms \PackagingForms _backup_201010281050.bak' WITH NOFORMAT, NOINIT,  NAME = N'PackagingForms _backup_20101028105056', SKIP, REWIND, NOUNLOAD,  STATS = 10
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'PackagingForms ' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'PackagingForms ' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''PackagingForms '' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'T:\SQL Backup\PackagingForms \PackagingForms _backup_201010281050.bak'

Troubleshoot Maintenance Plan (backup) Long Running



Sql Server 2005 SP2
Small databases

I have a maintenance plan to perform CHECKDB, Reindex, Backup, Cleanup tasks in that order. Lately, its been running very very long. For more than 2 days. Initially it was because of a bunch of reasons i.e. disk ran out of space, TRN and BAK jobs running on top of each other, etc. But now, I created a new plan with just backup. Even that runs forever. Just simple backup, nothing else (small database).

Now, if I create a job with T-Sql command and run it, it runs fast in under a second. But if I create a Maintenance Plan on the same database it takes forever. How do I troubleshoot this? I do not get any errors because, I stop the job after running for a few hours.

Any ideas,


Database Maintenance Plan


Hi Experts,

I would like to check the integrity of all user databases on our SQL Server, so i have used the maintenance plan and scheduled for it to run at 03:00 hrs but the scheduled job fails everyday with below error message on certain databases.

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Wellcome'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[18] Database Wellcome: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

Deleting old text reports...    0 file(s) deleted.

NB: No one is using the database at 3:00 mid-night.

Any help will be highly appreciated !

Kind Regards



Export Maintenance Plan in SSMS

Where are the subplan schedules when I exported /imported a Maintenance Plan in SSMS ? How can I ensure that they are exported with ?

Maintenance plan to rebuild Full-text index ? (SQL 2005)

Is it possible, through the GUI Wizard, to create a Maintenance plan that rebuilds (rather than reorganise/optimise) a Full text index in 2005 ? The full text index is based on one table only - so would using the "Rebuild index task" in the Wizard do the trick ? I wouldn't mind if the other indexes on the table were rebuilt too, but main concern in the Full text Index. If not - then I guess it would be a scheduled job which does a ALTER FULLTEXT CATALOG catalog_name REBUILD, but I thought it would be nice to keep it within the - Management - Maintenance plans - section of SQL Management Studio. Also - should this be preceeded with a transaction log backup ? Had some issues rebuilding them last night - seemed a Log backup was needed.

SQL2008 - Cannot Restore a new database from a full backup of a different one, database in use

I am trying to restore a brand new database from a copy of another one and am encountering an error message stating: System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten.  It is being used by database <Backup Source Database>. (Microsoft.SqlServer.Smo) I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database. This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios. Any thoughts on why this is happening? Thanks in advance for any help!

Maintenance Plan Works, But Not Through SQL Job

If I am in the wrong sub-forum, please let me know where I belong. I submitted this issue to this sub, b/c I believe I have an accessibility issue. I have a maintenance place that executes a stored proc. The stored proc is as followings: CREATE PROCEDURE spFirst     @Err AS INT = -1 AS     EXEC @Err = xp_cmdshell 'dir C:\temp\*.exe'     EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Mail_Proj',                                        @recipients = 'me@xyz.com',                                              @body = 'Complete',                                           @subject = 'Dir Listing' The proc runs successfully and when done, I always get an email message in my Inbox, as expected. When the proc is executed directly from the maintenance plan, it still runs successfully, and the email message is successfully received in my Inbox. A SQL Job was setup to run the maintenance plan. The history of the job indicates that it is completing successfully, but this cannot be true, as my inbox never receives the expected email

Cannot create maintenance plan in SQL 2008 SP1

I receive the following error trying to create a maintenance plan in SQL 2008 SP1 on a Windows 2008/R2 server: The action you attempted to perform on a remote instance of SQL Server has failed because the action requires a SQL Server component that is not installed on the remote computer. To proceed, install Microsoft SQL Server Management Tools on the remote computer, and then try again. The components that installed are Database Engine Services, Reporting Services, Client Tools Connectivity, Management Tools Basic/Complete, and SQL Client Connectivity SDK.  I have another independent SQL 2008 SP1 server running on the same platform with the same features installed and I have no problem creating maintenance plans there.  However, it does have IIS as it was upgraded from a SQL 2005 server which required IIS for reporting services while SQL 2008 does not. I'm familiar with http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/06c80a78-a877-4a48-a032-b0aef78a598c but that doesn't seem to apply as this was a new SQL 2008 installation on a freshly installed OS.  I've seen several other postings of this on the web but they all appear specific to SQL 2005. What else could be missing? Thanks.

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

Maintenance Update Statistics plan

Hi, I've created a maintenance plan for many databases with these tasks (SQL 2005) 1- shrink Database 2- Update Statistics 3- Cleanup History 4- Backup Database (Full) 5- Maintenance Cleanup task The maintenance plan failed at task Update Statics.  Here the error message : Error number : -1073548784 Error message : Executing the query "UPDATE STATISTICS [dbo].[LOG_DETAILS] WITH FULLSCAN " failed with the following error: "Could not continue scan with NOLOCK due to data movement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. My question :How do I know which database is causing the problem and how can I fix the problem ? I don't know a dbo database? thanks,   Jasmin  

How to Include Password during Database Backup over Tape

Hi All, We have EMC Backup Solution in our organization which does backup of all the databases. I want to know is there any way that i can include the password option whenever Backup runs. I dont want to modify any script at the EMS Backup solution side. I want to do it on the fly.         Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

SQL 2008 Maintenance Plan configuration help

A client has purchased a software package that runs on SQL 2008, They did the initial install but that is a they want to do on there end. They have sent us a list on maintenance jobs to be configured but are no help in getting them in place. I have very little SQL expereince so I am looking for direction. Here is a list of jobs they wanted setup. 1.       Check database integrity – set plan to fire off daily. 2.       Reorganize index – set weekly – except on the week that you Rebuild indexes.  Start index page size at 50%. 3.       Rebuild index – set monthly 4.       Update statistics – set weekly after reorganize indexes and rebuild indexes. 5.       Clean up history – set monthly 6.       Back Up Database (FULL) – set daily 7.       Maintenance Cleanup Task – this is to control the number of locally available backups.          Can anyone help me get these in place? Thanks in advanced for any help. I am doing manual backups only a this time.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend