are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980s
Hi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killersql

are backups not successful?

Hello:
A client of mine is using SQL Server 2000 and has constructed a database
maintenance job to do "Complete Backup" and "Transaction Log Backup".
In another thread, I had asked why the job was not deleting its own backups
files as the job is configured to delete backups after one day.
Someone had replied and said that the deletions will not take effect until
the backup job completes successfully.
Since the backup files are not deleting, does that mean that the backup job
is not being successful?
If that's the case, then, I don't know what the client is doing wrong. It's
not hard to set up a backup job in the database maintenance plan.
I mean, does the SQL Server Service need to be stopped in order for these
backups to take place successfully?
Thanks!
childofthe1980sHi,
Before i suggest u any thing just check the maintinance plan log file.
--
It is not true that backup will not delete till the next backup is
successful.
Check maintinance plan again and make sure that u had selected one
day(delete old files)
check the path on the backup.
hope this helps
from
Killer

Apply compression - XMLA

Hi there,

I intend to create a backup job for my cube, however I didn't find anywhere the XMLA configuration command for the "Apply Compression" option in the cube backup properties. Even if I check this box and script the setting it doesn't show up. I haven't find anything even in the XMLA command reference .

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>test</DatabaseID>
</Object>
<File>test.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Any help would be appreciated!

Thanks,

Greg

There is an ApplyCompression element, which is in the latest BOL. You do not see this element when you turn compression on as the default value is on, you only see it when you turn compression off.

Code Snippet

<Backup xmlns=http://schemas.microsoft.com/analysisservices/2003/engine>

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

</Object>

<File>Adventure Works DW.abf</File>

<ApplyCompression>false</ApplyCompression>

</Backup>

Application times out while doing the updates

here's the scenario..

I am running a DTS to collect the summarized info from Oracle database
into SQL server. I then have a update job which updates my
transactional table from the summarized table.

The update takes a very long time (~ 3 minutes)even though it has
around 1500 rows which causes the application to timeout. I want this
job to be done in less than a minute.

Thoughts on improving performance. Is stored procedure a way to go?
(I have used Isolation,row hints etc etc..nothing seems to be working)

AJI suggest you review the execution plan of the UPDATE query. Perhaps
additional indexes or query changes will speed it up substantially. If you
need additional help, please post you table DDL (including constraints,
indexes and triggers) along with you query and sample data.

Simply encapsulating the query in a proc is unlikely to improve performance.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0408272303.752889bc@.posting.google.c om...
> here's the scenario..
> I am running a DTS to collect the summarized info from Oracle database
> into SQL server. I then have a update job which updates my
> transactional table from the summarized table.
> The update takes a very long time (~ 3 minutes)even though it has
> around 1500 rows which causes the application to timeout. I want this
> job to be done in less than a minute.
> Thoughts on improving performance. Is stored procedure a way to go?
> (I have used Isolation,row hints etc etc..nothing seems to be working)
> AJ|||Thanks Dan,

Here's the entire structure and query

Table A has these columns

a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
and few other columns

ftq,break_offs, completes,assigned are the summarized cols. which are
updated every hour from the summary_table.

Summary table has these cols.

a_id,ftq break_offs,completes,assigned.

I am issuing this update statement

UPDATE A SET
break_offs = (select case when break_offs<0 then 0 else break_offs end
break_offs from summary_table where A.ano = summary_table.ano),
ftq=(select ftq from summary_table where A.ano=summary_table.ano),
actually_assigned=(select member_assigned from summary_table where
A.ano=summary_table.ano),
qualified_completes=( select member_completes from summary_table where
A.ano=summary_table.ano)
WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
summary_table.ano) and
end_date>=getdate().

------------

Apart from this we also have stored procs, trigger on the same
table.But the thing is the jobs runs 15 minutes past the hour and
stalls everything.

Thanks again

AJ|||AJ (aj70000@.hotmail.com) writes:
> Here's the entire structure and query
> Table A has these columns
> a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
> and few other columns
> ftq,break_offs, completes,assigned are the summarized cols. which are
> updated every hour from the summary_table.
> Summary table has these cols.
> a_id,ftq break_offs,completes,assigned.

Note here: Dan asked for the DDL. By this he means the CREATE TABLE
statements. These are useful if you want to test a query, and they
are also easier to read than a free-form list. In this case, the
cure appears simple enough anyway:

> UPDATE A SET
> break_offs = (select case when break_offs<0 then 0 else break_offs end
> break_offs from summary_table where A.ano = summary_table.ano),
> ftq=(select ftq from summary_table where A.ano=summary_table.ano),
> actually_assigned=(select member_assigned from summary_table where
> A.ano=summary_table.ano),
> qualified_completes=( select member_completes from summary_table where
> A.ano=summary_table.ano)
> WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
> summary_table.ano) and
> end_date>=getdate().

This should be faster:

UPDATE A
SET break_offs = case when s.break_offs < 0 then 0
else s.break_offs
end,
ftq = s.ftq,
actually_assigned = s.member_assigned,
qualified_completes = s.member_completes,
FROM A
JOIN summary_table s ON a.ano = s.ano
and A.end_date >= getdate()

This is uses syntax that is proprietary to MS SQL Server and Sybase,
but it is a lot more effecient, since in your version each subquery
is evaluated separately.

Permit me also to note that the last condition looks funky. getdate()
returns the current time, so if A.end_date is a date only, this condition
may not do what you expect. That is, rows where end_date = today will
not be updated.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Dan/Erland,

Is there a way to save the execution plan?..I ran trace/execution. My
update job seems to be firing at the end since I have lots of table
level triggers. The changes that Erland is suggesting is infact taking
longer than my query.

Thoughts/Comments

AJ

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||AJ Jr (aj70000@.hotmail.com) writes:
> Is there a way to save the execution plan?..I ran trace/execution. My
> update job seems to be firing at the end since I have lots of table
> level triggers. The changes that Erland is suggesting is infact taking
> longer than my query.

You can run a query from Query Analyzer and press CTRL/K to get a graphical
showplan. Or you can press CTRL/L to get an estimated plan, so that the
query is not run. Tnis is not useful if you run the entire procedure, but
mainly if you run the troublesome statement separately.

If you have a Profiler trace with the Execution Plan event, you can find
the plan for the query, select that row, and the cut and paste from the
lower pane. Normally the plan you are looking for is the one before
StmtCompleted, but if there are triggers on the table, it may not be in
this case.

Too bad that my suggestion performed worse. That was indeed a bit of a
surprise. It would be interesting to see both plans.

It would help to have the complete CREATE TABLE and CREATE INDEX statements
for the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erland's response, you may want to add unique constraints or
indexes on the ano columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0408281317.3e394ab2@.posting.google.c om...
> Thanks Dan,
>
> Here's the entire structure and query
> Table A has these columns
> a_id(pkey),ftq,break_offs,completes,assigned,s_id, end_date (Indexed)
> and few other columns
> ftq,break_offs, completes,assigned are the summarized cols. which are
> updated every hour from the summary_table.
> Summary table has these cols.
> a_id,ftq break_offs,completes,assigned.
> I am issuing this update statement
> UPDATE A SET
> break_offs = (select case when break_offs<0 then 0 else break_offs end
> break_offs from summary_table where A.ano = summary_table.ano),
> ftq=(select ftq from summary_table where A.ano=summary_table.ano),
> actually_assigned=(select member_assigned from summary_table where
> A.ano=summary_table.ano),
> qualified_completes=( select member_completes from summary_table where
> A.ano=summary_table.ano)
> WHERE EXISTS (select break_offs,ftq from summary_table where A.ano =
> summary_table.ano) and
> end_date>=getdate().
>
> ------------
> Apart from this we also have stored procs, trigger on the same
> table.But the thing is the jobs runs 15 minutes past the hour and
> stalls everything.
> Thanks again
> AJ|||Hi Dan/Erland

I have identified a problem : It is the Update trigger which has a
cursor which checks all the records before allowing the update.

Comments/Thoughts on this one :

The update job is the scheduled job..it runs as user x (I can specify
sa or DBO).
Is there a way in SQL server that I can fire the trigger on a table
for a particular user only (Application user).

Thanks ia advance

AJ|||AJ (aj70000@.hotmail.com) writes:
> I have identified a problem : It is the Update trigger which has a
> cursor which checks all the records before allowing the update.
> Comments/Thoughts on this one :
> The update job is the scheduled job..it runs as user x (I can specify
> sa or DBO).
> Is there a way in SQL server that I can fire the trigger on a table
> for a particular user only (Application user).

No, but you can use ALTER TRIGGER to disable the trigger. But this is
a little risky if the job dies for some reason in the middle of and leaves
the trigger disabled.

A better technique is to define a temp table, call it #triggerdisable, just
before the update, and then in the trigger add:

if object_id('tempdb..#triggerdisable') IS NOT NULL
RETURN

Note: what columns the table has or what data there is does not matter. It's
the sheer existence that matter.

But of course the best solution would be rewrite the trigger to use set-
based operations!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,

But I am kinda lost...By defining #triggerdisable..What do you mean?

Thanks again

AJ|||AJ (aj70000@.hotmail.com) writes:
> But I am kinda lost...By defining #triggerdisable..What do you mean?

CREATE TABLE #triggerdisable(a int NOT NULL)

The temp table serves as a process-global flag to test for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erland/Dan for all your help.

AJ

appending to the end of current file

Hello all! I have a dts package exporting a text file. I would like for the dts job to append to the end of the file each time it is ran, rather than overwriting it. Is there a simple solution for this?
ThanksDarren Green suggested in earlier posts that :
The text file provider does not support this. The simplest thing to do is to export to a new file, then use the DOS copy command to merge the files.

This can be done with the Execute Process Task, and any dynamic filename stuff can be handled with an ActiveX Script Task to change the connection and the Exec Proc Task at the same time.

copy FileOriginal.txt+FileNew.txt FileResult.txt

HTH|||That is what I am currently doing. I was hoping for something within the dts package. Thanks for you help.

val|||As suggested its not available using TEXT provider with DTS.
May be this is also other way around, is to export to an excel sheet and save that as text file.:)