BODI service account altering database recovery model

dataservices administration
1

Our DBA is complaining that the BODI Service account is executing:

ALTER DATABASE [dbname] SET RECOVERY BULK_LOGGED WITH NO_WAIT

about 15 times/day.

There is only one ETL job on that database, and it runs only once per day, so this is clearly something system related.

This is in violation of their database security policy, so any assistance or clarification on resolving this would be appreciated.

[UPDATE]

These ALTER commands are only being run while the ETL job is running… so we are back to it being related to something in the job.

[UPDATE 2]

It appears that if you select the BULK LOAD option in your Target table, then the database recovery model gets changed at runtime.

Is there a way to disable this?

Thanks

fblau (BOB member since 2005-02-07)

2

Um, don’t use bulk load? :smiley: Sorry Frank, I couldn’t resist. I’ve worked on a few tuning scenarios over the past few weeks where I got better throughput when NOT using bulk load. This was with Oracle as the target. Let me know if you want more details.

I was unable to find any declared properties in the DSConfig.txt file that seem to directly relate to the issue. There may be properties that could change this behaviour but they aren’t listed.

Otherwise, here’s all I could find on the forum related to this:

eganjp :us: (BOB member since 2007-09-12)

3

check if this command is set in the Datastore Session Parameters by some developer of the DI Job

manoj_d (BOB member since 2009-01-02)

4

What do you mean by this?

I have tested in a new job with just a source > target using the bulk load option and it changes the Recovery Model.

There are no additional session parameters set for this datastore.

Where else would this setting be?

fblau (BOB member since 2005-02-07)

5

In the Datastore you can code a SQL statement that is executed every time the Datastore connects to the database. It’s possible that someone put something in there that is causing the issue.

But if you only see the issue when bulk load is used then the Datastore session parameter has nothing to do with it.

eganjp :us: (BOB member since 2007-09-12)

6

I created a blank source > target mapping dataflow. There are no additional session parameters set for the datastore.

When Bulk Load is ON, you can see the Recovery Model change in the EventLog. When you turn Bulk Load OFF, it does not throw this Event message.

fblau (BOB member since 2005-02-07)

7

Is this in the DS log? Or is it in the SQL Server log? I know the Oracle API for Bulk Loader (aka Direct Path) does some things under the covers that at first you think DS is doing. But it’s Oracle.

eganjp :us: (BOB member since 2007-09-12)

8

It shows up in the EventLog for the database server.

fblau (BOB member since 2005-02-07)

9

Have you tried using the command line bulk loader for SQL Server (BCP)to see if you get the same message in the SQL Server log? I have a vague recollection that it would tweak the recovery model if you used just the right options.

eganjp :us: (BOB member since 2007-09-12)

10

That is sort of our fallback position… but it would mean changing all of our dataflows in this case if it comes to that.

fblau (BOB member since 2005-02-07)

11

No, I mean do a load with BCP to see if the same message shows up in the SQL Server log. Just to prove that it is the API - not DS - that is flipping the recovery mode.

Keeping in mind that it’s been a couple years since I worked closely with SQL Server…

Why is the DBA complaining? Is the normal recovery mode supposed to be FULL? It could be argued that it is normal for a Data Warehouse database to be in the bulk-logged mode not the full mode to support fast loading of the database while minimizing the impact to the transaction log. Yes, bulk-logged mode makes recovery difficult. I implemented a Data Warehouse a couple years ago that used the SIMPLE recovery model. Everything performed since the last full backup was rerunnable so we didn’t care about the transaction logs.

Tell the DBA that you never have this problem with Oracle. :smiley:

eganjp :us: (BOB member since 2007-09-12)

12

I will test that… but it would surprise me if it does… since being able to control the recovery model is at the database level and there are a variety of scenarios that need to be supported:

eg:

fblau (BOB member since 2005-02-07)

13

Any progress on this Frank?

eganjp :us: (BOB member since 2007-09-12)

14

Since there was nothing forthcoming from SAP, we have re-written the jobs to not use Bulk Load.

fblau (BOB member since 2005-02-07)

15

did you file a case for this with SAP ? what is the case number, I did a quick check in the code (DS 4.0) but didn’t find this being set anywhere, what is the Database type to which you are loading the data using bulk load and also what method and options that you are using for bulk load

if you have a case filed then please attach the ATL to the case

manoj_d (BOB member since 2009-01-02)

16

This is with SQL Server 2008… and DS 4.0

We have not filed a case as I don’t see anything that SAP can offer as a workaround other than not using the Bulk Load option and to rewrite the jobs.

This should be a checkbox on the Bulk Load option page. Hopefully that can be built-in at some point.

fblau (BOB member since 2005-02-07)