Disconnect error while running re-indexing job.

Hi,
I have a 24Gb database with half a dozen indexes against various
tables which is failing during the reindexing task (as part of the
maintenance plan) at least 80% of the time.
The error given in the logs is:
"[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has
been disconnected. You must reconnect to perform this operation."
Now, I am aware that this means the server disconnected or became
unavailable during the re-indexing, but I cannot find any logical
reason why this should happen. There are no access violations, either
in the SQL logs or the event viewer. No-one manually stopped SQL
Server. No other part of the maintenance plan has any issues. There
are no other errors around the time of this failure.
If anyone has any suggestions as to why this might be occurring, I'd
be grateful to hear them.
Thanks in advance,
Andy.
One of the things I would check is the autogrow/autoshrink options... If
those options are enabled AND your database picks an inopportune time to
grow (or shrink) errors like that are possible.
Steve
"Andy D." <clotho42@.yahoo.com> wrote in message
news:279509a6.0404260826.2b769d01@.posting.google.c om...
> Hi,
> I have a 24Gb database with half a dozen indexes against various
> tables which is failing during the reindexing task (as part of the
> maintenance plan) at least 80% of the time.
> The error given in the logs is:
> "[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has
> been disconnected. You must reconnect to perform this operation."
> Now, I am aware that this means the server disconnected or became
> unavailable during the re-indexing, but I cannot find any logical
> reason why this should happen. There are no access violations, either
> in the SQL logs or the event viewer. No-one manually stopped SQL
> Server. No other part of the maintenance plan has any issues. There
> are no other errors around the time of this failure.
> If anyone has any suggestions as to why this might be occurring, I'd
> be grateful to hear them.
> Thanks in advance,
> Andy.

Disconnect error while running re-indexing job.

Hi,
I have a 24Gb database with half a dozen indexes against various
tables which is failing during the reindexing task (as part of the
maintenance plan) at least 80% of the time.
The error given in the logs is:
"[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has
been disconnected. You must reconnect to perform this operation."
Now, I am aware that this means the server disconnected or became
unavailable during the re-indexing, but I cannot find any logical
reason why this should happen. There are no access violations, either
in the SQL logs or the event viewer. No-one manually stopped SQL
Server. No other part of the maintenance plan has any issues. There
are no other errors around the time of this failure.
If anyone has any suggestions as to why this might be occurring, I'd
be grateful to hear them.
Thanks in advance,
Andy.One of the things I would check is the autogrow/autoshrink options... If
those options are enabled AND your database picks an inopportune time to
grow (or shrink) errors like that are possible.
Steve
"Andy D." <clotho42@.yahoo.com> wrote in message
news:279509a6.0404260826.2b769d01@.posting.google.com...
> Hi,
> I have a 24Gb database with half a dozen indexes against various
> tables which is failing during the reindexing task (as part of the
> maintenance plan) at least 80% of the time.
> The error given in the logs is:
> "[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has
> been disconnected. You must reconnect to perform this operation."
> Now, I am aware that this means the server disconnected or became
> unavailable during the re-indexing, but I cannot find any logical
> reason why this should happen. There are no access violations, either
> in the SQL logs or the event viewer. No-one manually stopped SQL
> Server. No other part of the maintenance plan has any issues. There
> are no other errors around the time of this failure.
> If anyone has any suggestions as to why this might be occurring, I'd
> be grateful to hear them.
> Thanks in advance,
> Andy.

Disabling the TransactionLog

I have an SSIS task that transforms 70 million rows of data nightly.

The data is dumped and imported fresh every day.

Obviously I don't want my production Transaction Log getting cluttered up with 70 million rows of transaction logs daily.

Is there any way to avoid logging to the transaction log with an SSIS task?Are you using bulk insert or are you running 70 million insert statements? There is a varitety of ways to truncate / shrink the transaction log after you are done processing records.|||

Greg Van Mullem wrote:

Are you using bulk insert or are you running 70 million insert statements? There is a varitety of ways to truncate / shrink the transaction log after you are done processing records.

I am going to be using bulk inserts.|||

You can either put your database into simple recovery which will provide 0 recovery and then put back into "full" mode and do a full backup after your finished.

With "simple" you will only be able to recover from your previous backup up to the point you flipped into simple mode

The other option is to use BULK Logged mode. This mode logs extent allocations, once completed you should put your database back into full mode at which point when you back up the log all pages/extents changed during the bulk logged period are written to the backup.

Your transaction log will always grow until a in simple mode a checkpoint occurs at which point it is emptied of commited transactions, or in bulk logged/full the transaction log is backed up. If you have 1 transaction for all the work then the transaction log will need to be big enough to handle all the changes made in the transaction

|||Have you consided solving this problem with a hardware purchase instead of software tweaks? Buy big disk drives and let the transaction log explode in size. If it's too slow then buy faster disk drives or servers.

This is often the most cost effective approach these days. More often than not, when I run the numbers on an issue like this, an expensive hardware purchase turns out be cheaper, faster and easier! Just a thought.

Later,
Greg Van Mullem|||

Use the batchsize parameter (I hope bulkinsert has one, as the bcp utility has one). This will leasd into a checkpoint. If you now define an alert for the database (log is used by e.g. 75 percent) and you define a job which will dump the transaction log, you should have no problems with it.

Maybe you have to deal with the percent value. That's it.

The advantage is, that you do not change the recovery model of the database and even your inserts will be part of the backup.

Regards

Norbert

Disable a task in the current package programmatically?

Hi,

I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names.

I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist.

So the Script Task checks whether the file name exists. If not, I would like to disable the Task that uses this connection. But how do I reference a task within the package that contains my Script Task, by name, to set the Disabled attribute to true?

I want to do something like CurrentPackage.Tasks("MyDataFlowTask").Disabled = True.

I would also welcome alternative suggestions for how to achieve this.

Many thanks,

Keith.

You cannot reference inwards within a package, so you cannot reference a task. The task must reference the value. The way to do this is to set a variable, and use a property expression that uses that variable value. So you would set a variable, DisableMyTask as true to false in the script then set the property expression on the task's Disable property to that variable, "@.DisableMyTask".

I would however be careful using the Disable property, as the evaluation can be done before you set the variable, so it is too late. A better way to do this is to use an expression on Workflow. Have a constraint leading to your task, and then just add the variable as the test, or rather the negative, so that the constraint is only satisfied when the variable is false. If you have no preceding task to anchor the constraint, just use a sequence container. It will act as an anchor, but do nothing else.

|||

Keith,

You can also add an Expression to the constraint from your Script Task that is modifying the connection string. By doing so you can control the flow without having to enable/disable the invalid tasks.

Here's a link on how to add expressions to a precendence constraint: http://msdn2.microsoft.com/en-us/library/ms140040.aspx

Cheers,
Patrik

|||

Hi,

Thanks for the replies. I have tried adding expressions to the precedence constraints, but it is not working correctly.

I originally had 7 Tasks, with a simple precedence constraint of Success between one and the next. I've now added an expression based on a variable, and changed the precedence constraint to "Constraint and Expression", but with this change, steps 5 to 7 no longer execute. They are still validated, but do not Start.

In simplistic terms, the logic I have added is:

After Task 3, if Success and (@.boolPCStatsLastYearExists) then Execute Task 4.

After Task 4, if Success, execute Task 5.

After Task 3, if Success and (@.boolPCStatsLastYearExists = False) then Execute Task 5.

So, whether the boolean value @.boolPCStatsLastYearExists is True or False, Task 5 should be executed. In fact I think it is set to True, because Task 4 executes and creates rows in the database, so it appears Task 4 is executing but Task 5 isn't.

I am developing on my local machine, but cannot run locally (I get an error on the first Data Flow Task), because the SQL database is on a server. So I am deploying to the server and running there.

Can you advise how I should get debugging information to indicate what happens after Task 4 executes, and why Task 5 isn't reached?

Many thanks,

Keith.

|||

To debug stuff, use the debugger. Creating a mock DB on your local machine would be a good idea.

Sounds like you have two constraints leading to task 5, but they follow different, mutually exclusive execution paths. So you will never satisfy both constraints in a single execution, correct? If so, have you turned the Or logic option on for those constsraints? It is in the constraint UI, or the LogicalAnd property of the constraint, set it to false.

|||

Hi again,

Thanks for that. I had left the default setting (AND) on the constraints. Changing all the constraints where 2 constraints affect one task, led to some progress.

Tasks 1 - 6 now execute fine. But Task 7 never executes.

The constraints on it are:

From Task 5, Success AND @.boolPCFDR1LastYearExists = False

OR

From Task 6, Success.

The first of these conditions should be met, as Task 5 succeeds and writes data, and the boolean is set to False by Task 1 (the Script task), which I can verify in the debugger.

I would greatly appreciate any further suggestions. However, I suspect I will now need to install SQL Server on my already overloaded laptop and recreate the server database locally, so that I can debug. But having looked at the debugger, I am not confident I will have a sufficiently granular view to identify the problem. I'll report back!

Thanks for all your help so far,

Keith.

|||

Hi,

OK, I've installed SQL Server locally, copied the database to it, and changed my connection to point to the local database. The same error occurs, and I don't see how to use the debugger to help me resolve it.

I can put a stop in Step 5 Post-Execute, but I can't do any more than verify that the boolean is set to False as expected. What can I do to identify why the processor does not proceed to execute Step 7?

Thanks,

Keith.

|||

Solved! (Sort of). I've had a problem with conditions on a precedence constraint. I can set a condition of @.myBooleanVariable, and it executes correctly, only when the variable is set to true. However, I can't find a syntax to say execute when this boolean is False. I tried the obvious NOT @.myBooleanVariable, but this does not pass the syntax check. @.myBooleanVariable = False passes the syntax check, but crucially, does not execute. I've not found another syntax that is accepted.

So reluctantly, I created 2 new variables, set them to the boolean opposite of the 2 existing variables, and used them in my "negative" conditions. So all my conditions on Precedence Constraints now comprise just the name of a boolean variable, prefixed with @..

Thanks,

Keith.

|||

Keith,

In the expressions not is represented with ! and equivalency is tested with ==. This should help you remove the additional variable.

Cheers,
Patrik