Solved

error during execute sql task execution, but only at times...

Posted on 2016-08-02
12
81 Views
Last Modified: 2016-08-08
There is an
       execute sql task
       in the package.. it runs fine sometimes.. othertimes, get the following error:
      
        
Description: Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.
Reissue the statement after the current backup or file manipulation operation is completed.  

"  ALTER DATABASE GOTN SET RECOVERY SIMPLE    "
failed with the following error: "ALTER DATABASE statement failed.".

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  

End Error  DTExec: The package execution returned DTSER_FAILURE (1).  

Started:  9:00:12 AM  Finished: 9:01:39 AM  Elapsed:  2687.04 seconds.  The package execution failed.  The step failed.

can you see how this be made fail-proof if possible? (make the statement to give error message instead of crashing.. what is the rootcause for this error which happens only at times...

___
The exact statement in the EXEC TASK are:

USE MASTER

GO

ALTER DATABASE GOTN SET RECOVERY SIMPLE

GO

USE GOTN

GO

DBCC SHRINKFILE (GOTN_Log,5120)
GO

GRANT SHOWPLAN TO [FLYKHS\YK-eKOG_GOTN_Query]
0
Comment
Question by:25112
  • 7
  • 4
12 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41739341
if that task runs instead in parallel while a backup is running, you get thst error.
note that shrinking db files regularly is not recommended anyhow
0
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 250 total points
ID: 41739452
There are certain administrative operations that cannot run in parallel. A list is available here: https://technet.microsoft.com/en-us/library/ms189315(v=sql.105).aspx

It looks like your ALTER DATABASE is running along with another filegroup operation or scheduled backup.
0
 
LVL 5

Author Comment

by:25112
ID: 41739453
SET RECOVERY SIMPLE
and GRANT
are not the problem, right?

if I remove
DBCC SHRINKFILE (GOTN_Log,5120)
from the script
then this error should not happen again, right?
0
 
LVL 5

Author Comment

by:25112
ID: 41739459
that link is helpful.. file shrink cannot happen with backup..

is
ALTER DATABASE GOTN SET RECOVERY SIMPLE
a "Filegroup Operation"?
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41739467
Why do you need to shrink the log file when the DB is in SIMPLE recovery mode?

Maybe there is long running transaction that is eating up the log space. When the transaction needs more space, the log file will need to expand - which is a file operation and cannot be executed with the ALTER statement to change the recovery model.

My recommendation would be to hunt down this rogue transaction by monitoring open transactions and fixing that.

Then you probably will not need the shrink file if the recovery model is SIMPLE.
0
 
LVL 5

Author Comment

by:25112
ID: 41739475
this is the situation..

this is a brand new restore to a new server/instance..

the source was FULL.. but in destination.. we want only SIMPLE.. (reporting only)
then the initial log file may be bloated.. so we wanted to trim it one time and then let it go.. (we have no control what the log size is when the backup is taken in the source before being restored in the destination)..
this whole process happens once a day at a scheduled time...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:25112
ID: 41739506
in that case, do you recommend the below... clears the traffic before it allows the ALTER and shrink:
(keeps checking traffic each minute)

WHILE
(
SELECT   COUNT(*) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE') and database_id = DB_ID ('GOTN')
)<>0
WAITFOR DELAY '00:01'
 

USE MASTER
GO
ALTER DATABASE GOTN SET RECOVERY SIMPLE
GO
USE GOTN
GO
DBCC SHRINKFILE (GOTN_Log,5120)
GO
GRANT SHOWPLAN TO [FLYKHS\YK-eKOG_GOTN_Query] 

Open in new window

0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41739507
Got it - one of the products I worked with also had a similar reporting DB.

I trust reports are not running against this reporting instance when you are doing this operation.

Ensure that when the recovery model is changed and the log file shrink happens, the database is in single user mode.
0
 
LVL 5

Author Comment

by:25112
ID: 41739510
>>I trust reports are not running against this reporting instance when you are doing this operation.

good point: yes, users know that the reporting wont be available at certain time of day...

>>Ensure that when the recovery model is changed and the log file shrink happens, the database is in single user mode.

OK.. let me incorporate that also.. but the bigger issue is the parallel backups/operations?
Is it backup operations of only that database or any database in the instance?
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41739604
>> Is it backup operations of only that database or any database in the instance?

I haven't tried it myself, but from the documentation, it appears to be that concurrent operations cannot be run on the same database, i.e. operations running across databases is fine.
0
 
LVL 5

Author Comment

by:25112
ID: 41739687
thanks for that..

can you comment on the code.. will that avoid the errors? (by making it wait for minute by minute?)
0
 
LVL 5

Author Comment

by:25112
ID: 41742563
when I look in msdb..backupset around this time of error, there is no other activity in msdb..backupset for several hours before and after this...

so, from the list in
https://technet.microsoft.com/en-us/library/ms189315(v=sql.105).aspx
it cant be any backups..

unless the restore that happened a minute ago.. could that be triggering something or needful 2 minutes to finish ,?

when I see restorehistory in msdb, the restore is successful...

should restore be given a minute or two after finishing before running the ALTER statements on the database?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now