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

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

Maintenance Plan Backup Failing Time to time

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

sql server maintenance plan schedule time and execution time difference


Hi All,

One of my customer db there is one maintenance plan(full backup / Transaction log backup  ) schedule time for the full backup is 11:00:00 PM daily and for transaction log backup 12.30 pm Sunday to Friday ,I checked the job created by maintenance plan there also schedule time is same but when i check the history of the job it shows it happened at 7:00:00 pm every day. why there is time difference ?

Product version is 8.00.2055(SP4)

i checked the MSDB.dbo.sysjobschedules table and the next_run_date is not updated properly,it is showing yesterday 11:00:00 pm


please help me to resolve this issue




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.


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 =

SQL Server 2005 - Maintenance Jobs take a long time after OS Upgrade


We have SQL 2005 Ent Edition (SP2) on W2k3 R2 Ent Edition. This OS was previously on Std Edtion and we had done an In-Place upgrade to Ent Edition some time back. Since then the Maintenance plans - Update Stas & Index Rebuild seem to take double the time to execute. I do believe this could be due to an already busy disk subsystem (RAID 5 array).

Can someone pls advice what could be possible reason for this sudden increase in the maintenance job execution time.

Paul - SQL DBA

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'

Scripting a change to Backup time



I have a backup plan defined in SQL Server that runs every night at 8PM. However sometimes I need to change this time to 5PM to allow a reboot of the server due to Windows updates. Some other tasks get rescheduled as well. I want to script this time change so that I do not need to manually change the backup time.

Is there a command line tool that will let me change the configuration of SQL Server? and specifically let me reschedule the time of the backup?

The scripts are written in Ruby not C#, this is fine as long as there is a command line tool.



.we each have more potential than we might ever presume to guess. (Blog: http://dsmyth.blogspot.com/)

Site or Site Collection backup for first time Production deployment



I want to know if there is any issue using Site or Site Collection backup for first time Production deployment for either collaboration/Publishining site.



Error : SQL server Backup time


Hello Friends,

i am working with internal sql server 2008 with VS-2010

Now, I want to add new functionaliti in my website for taking whole backup of database

I tried following query for backup:


But Following Error is Generated At the time of Execution:

Database 'DB1' does not exist. Make sure that the name is entered correctly.
BACKUP DATABASE is terminating abnormally.


So, Any one help me in this problem




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,


create one-time backup files


(using SQL 2008)

Here is my situation.  I have administrative rights to a particular database, but not the entire server.  The server admin makes a new backup every night for all the databases. 

At certain points in time, I would also like to save a backup file of my database, but of course don't want to alter the existing backup schedule.  Is this possible?

Maintenance Plan Backup Database Task timeout



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?



Round off time to the nearest minute

How would you round this up to the nearest minute? There isn't a built in function to do this so you have to use a little bit of maths to get there. There are 60 seconds in a minute. We already have 38 seconds on the clock. So we need to add on 60 - 38 = 22 more seconds.

Performance Tests: Precise Run Time Measurements with System.Diagnostics.Stopwatch

Everybody who does performance optimization stumbles sooner or later over the Stopwatch class in the System.Diagnostics namespace. And everybody has noticed that the measurements of the same function on the same computer can differ 25% -30% in run time. This article shows how single threaded test programs must be designed to get an accuracy of 0.1% - 0.2% out of the Stopwatch class. With this accuracy, algorithms can be tested and compared.

Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

How to programmatically add controls to Windows forms at run time by using Visual C#

Create a Windows Forms Application
Start Visual Studio .NET or Visual Studio 2005 or a later version, and create a new Visual C# Windows Application project named WinControls. Form1 is added to the project by default.
Double-click Form1 to create and view the Form1_Load event procedure.
Add private instance variables to the Form1 class to work with common Windows controls. The Form1 class starts as follows:

.NET 4 Web Application Startup Time

I was chatting with Jonathan Hawkins and some of the folks on the ASP.NET team about performance and Jonathan mentioned the startup time for large ASP.NET applications is improved on .NET 4. There are some improvements in the CLR and in ASP.NET itself that helped. If you have a giant app, you should do some tests.
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