asked
DBCC SHRINKDATABASE(db name) job fails
Hi, In My orgn Last week we did nt take Full Backup of all DB and also did nt take T-log Backup of all DB. Then this Weekends using maintenance plan one Weekly Shrink database job was fails. May i know why? **Is Shrink job fail related to Backup job cancelled on Server?** **Error Description:** 02/24/2013 01:30:00,Weekly LogFile Shrink.Job,Unknown,1,DEVSERVER,Weekly LogFile Shrink.Job,Job,,Executed as user: Devserver\SYSTEM. ...FILE(db1_log 1) ".: 19% complete End Progress Progress: 2013-02-24 01:30:05.27 Source: Execute T-SQL Statement Task Executing query "ALTER DATABASE db1 SET RECOVERY FULL ".: 21% complete End Progress Progress: 2013-02-24 01:30:05.27 Source: Execute T-SQL Statement Task Executing query " USE db2 ".: 23% complete End Progress Progress: 2013-02-24 01:30:08.02 Source: Execute T-SQL Statement Task Executing query "ALTER DATABASE db2 SET RECOVERY SIMPLE ".: 25% complete End Progress Progress: 2013-02-24 01:30:08.46 Source: Execute T-SQL Statement Task Executing query "DBCC SHRINKFILE(db2_log 1) ".: 26% complete End Progress Progress: 2013-02-24 01:30:08.47 Source: Execute T-SQL Statement Task Executing query "ALTER DATABASE db2 SET RECOVERY FULL ".: 28% complete End Progress Progress: 2013-02-24 01:30:08.47 ... The package executed failed The step succeeded.,00:00:17,0,0,,,,0 **Note:** Recovery Model of DB: Full Every week we took Database full Backup and T-log backup except lastweek/daily
Replication can run under any of the recovery models. I don't know what other jobs you have on the server, so I can't tell you what will happen to them.
Like I said, replication can run under any of the recovery models.
Hi Grant Fritchey thanking for your valuable time. I understood only two option to maintain/prevent log grows 1) Recovery model full, log backup hour/mint basis. 2)Recovery model simple, full backup daily basis So One last question If Now I decide to change the recovery model full to simple means Is it affect the Replication or any other Server jobs on server?
hi This is due to backup is in progress on the database PublicSitesTrackingArchive Executing query "USE [PublicSitesTrackingArchive] ".: 50% complete End Progress Error: 2013-02-23 23:30:58.04 Code: 0xC002F210 Source: Shrink Database Task Execute SQL Task Description: Executing the query "DBCC SHRINKDATABASE(N'PublicSitesTrackingArchive' 10 TRUNCATEONLY) " failed with the following error: "Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Re... The package execution fa... The step failed.,00:01:16 Please change the schedule of shrinkjob. Thanks, Pradyothana
Www.msqlserver.com,hi This is due to backup is in progress on the database PublicSitesTrackingArchive Executing query "USE [PublicSitesTrackingArchive] ".: 50% complete End Progress Error: 2013-02-23 23:30:58.04 Code: 0xC002F210 Source: Shrink Database Task Execute SQL Task Description: Executing the query "DBCC SHRINKDATABASE(N'PublicSitesTrackingArchive' 10 TRUNCATEONLY) " failed with the following error: "Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Re... The package execution fa... The step failed.,00:01:16 Please change the schedule of shrinkjob. Thanks, Pradyothana
Www.msqlserver.com
The full message is not there. But, here's a strong suggestion. I would not be running transaction log backups on a weekly basis. You're going to be dealing with huge logs then. Run the log backups once an hour, all day long. That will keep the log files small, reducing or eliminating the need for shrinking the files over and over. It will also make it possible to do a point in time recovery. The weekly log backup will not allow for any better recovery than you'll get with the full backup.
You can do anything you want. But daily log backups just doesn't make much sense. Log backups should be run fairly frequently. Most of the businesses I've supported wanted to lose as little data as possible, so we ran log backups every 10 minutes. It just makes no sense to keep your database in full recovery, but then not run frequent log backups. So you should either run the logs frequently, or just take the database to Simple Recovery and then you won't have to worry about logs. You'll also lose the ability to recover to a point in time though.
The log will fill up the drive until it takes the database offline. Your choices are very clear. Full recovery and then frequent log backups. Simple recovery and no log backups. Full recovery and the ability to recover to a point in time. Simple recovery and only the ability to recover to the last full backup.
No. If you don't backup the log, you will fill the drive. Full backups don't do anything at all to the log.
Hi Grant Fritchey as per your request i gave the full error details so kindly see and inform what kind of error it is [link text][1] see below for your ref: [1]: /storage/temp/698-shrink+job+fails+desc+full.txt
Still incomplete. This: The package execution fa... The step failed.,00:02:13,0,0,,,,0 Is missing the full error. Without that, I don't know what happened. Is there anything in the error log? If not, can you set up the job to output to a file and then look in the file for errors? What you posted is just from the job history and that is frequently truncated.
13 People are following this question.
