question

askmlx121 avatar image
askmlx121 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
shrink-database
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Like I said, replication can run under any of the recovery models.
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
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?
0 Likes 0 ·
askmlx121 avatar image askmlx121 commented ·
we have 4 jobs of replication copy of one server to another server of snapshot replication and transaction replication using full recovery only So After change of recovery models full to simple. It would affect or not? if affect what can i do next to prevent?
0 Likes 0 ·
pradyothana avatar image
pradyothana answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image askmlx121 commented ·
Good Pradyothana.............now i check that
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
No. If you don't backup the log, you will fill the drive. Full backups don't do anything at all to the log.
1 Like 1 ·
askmlx121 avatar image askmlx121 commented ·
Could we do any one of log backup or full backup daily basis enough to control/prevent log file grows?
0 Likes 0 ·
askmlx121 avatar image askmlx121 commented ·
Thanks for your advice..... if i decided to use full recovery of db and do daily full backups only did nt take log backup means what would happen? log grows or not?
0 Likes 0 ·
Show more comments
askmlx121 avatar image
askmlx121 answered
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

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
askmlx121 avatar image askmlx121 commented ·
ya i knew in the advance option i can post the full details. i will post you soon
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.