Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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

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
25112
Asked:
25112
  • 7
  • 4
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
25112Author Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
25112Author Commented:
that link is helpful.. file shrink cannot happen with backup..

is
ALTER DATABASE GOTN SET RECOVERY SIMPLE
a "Filegroup Operation"?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
25112Author Commented:
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
 
25112Author Commented:
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
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
25112Author Commented:
>>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
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
>> 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
 
25112Author Commented:
thanks for that..

can you comment on the code.. will that avoid the errors? (by making it wait for minute by minute?)
0
 
25112Author Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now