DTS job is not copying all the data but still the Job History shows Successful?

Hello everybody.
I've got 4 servers running Windows 2003 Standard edition with SQL 2000 SP3a.
On each SQL server there is a database that is copied to all the other
servers every night using a DTS scheduled job.
The database sizes are ranging from 50 Mb to 520 Mb.
Now the problem is that sometimes the job fails but it still reports
Successful in the Job History. The typical problem is that one ore more of
the tables on one of the destination server are empty. The table is there
but none of the records are there. The rest of the tables and their records
are fine.
Can anybody give me some hints as to how I can get a more detailed logging
of the process so we can investigate what is going wrong?
Martin MoustgaardJob Shows Successfull of the Job ;
not the each data copy process..
Check ur DTS pack. is it well build. i mean the work flow of the package.. ?
What happens when a step in DTS raise Error , u handle this ?
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||Martin
I'd run DTS from Data Transformation Services.
See what is going on , what task is failed.
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Hello everybody.
> I've got 4 servers running Windows 2003 Standard edition with SQL 2000
SP3a.
> On each SQL server there is a database that is copied to all the other
> servers every night using a DTS scheduled job.
> The database sizes are ranging from 50 Mb to 520 Mb.
> Now the problem is that sometimes the job fails but it still reports
> Successful in the Job History. The typical problem is that one ore more of
> the tables on one of the destination server are empty. The table is there
> but none of the records are there. The rest of the tables and their
records
> are fine.
> Can anybody give me some hints as to how I can get a more detailed logging
> of the process so we can investigate what is going wrong?
> Martin Moustgaard
>|||The DTS is actually very simple.
I only have a Copy SQL Server Objects Task in the package that is set up to
create destination objects (drop destination object first, include all
dependent objects, include extended properties), copy data (replace existing
data), use collation, copy all objects. Under options I've selected
everything except the 3 selections under secirity options.
The only error handeling I have is that the DTS jopb is set to retry 2 time
with 30 minutes interval if it fails.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> Job Shows Successfull of the Job ;
> not the each data copy process..
> Check ur DTS pack. is it well build. i mean the work flow of the package..
?
> What happens when a step in DTS raise Error , u handle this ?
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > Hello everybody.
> >
> > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> SP3a.
> >
> > On each SQL server there is a database that is copied to all the other
> > servers every night using a DTS scheduled job.
> >
> > The database sizes are ranging from 50 Mb to 520 Mb.
> >
> > Now the problem is that sometimes the job fails but it still reports
> > Successful in the Job History. The typical problem is that one ore more
of
> > the tables on one of the destination server are empty. The table is
there
> > but none of the records are there. The rest of the tables and their
> records
> > are fine.
> >
> > Can anybody give me some hints as to how I can get a more detailed
logging
> > of the process so we can investigate what is going wrong?
> >
> > Martin Moustgaard
> >
> >
>|||That's the problem :-)
Every time I run it manually it works fine. And 90% of the time it also runs
fine at night at the scheduled job. So I haven't been able to reproduce it
by running the jobs manually.
Martin Moustgaard
"Uri Dimant" <urid@.iscar.co.il> skrev i en meddelelse
news:uSMRBykwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Martin
> I'd run DTS from Data Transformation Services.
> See what is going on , what task is failed.
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > Hello everybody.
> >
> > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> SP3a.
> >
> > On each SQL server there is a database that is copied to all the other
> > servers every night using a DTS scheduled job.
> >
> > The database sizes are ranging from 50 Mb to 520 Mb.
> >
> > Now the problem is that sometimes the job fails but it still reports
> > Successful in the Job History. The typical problem is that one ore more
of
> > the tables on one of the destination server are empty. The table is
there
> > but none of the records are there. The rest of the tables and their
> records
> > are fine.
> >
> > Can anybody give me some hints as to how I can get a more detailed
logging
> > of the process so we can investigate what is going wrong?
> >
> > Martin Moustgaard
> >
> >
>|||May be a Table Lock while dropping this tables?
Or.. Check the Owner Of the Tables...
"Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> The DTS is actually very simple.
> I only have a Copy SQL Server Objects Task in the package that is set up
to
> create destination objects (drop destination object first, include all
> dependent objects, include extended properties), copy data (replace
existing
> data), use collation, copy all objects. Under options I've selected
> everything except the 3 selections under secirity options.
> The only error handeling I have is that the DTS jopb is set to retry 2
time
> with 30 minutes interval if it fails.
> Martin Moustgaard
>
> "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> > Job Shows Successfull of the Job ;
> >
> > not the each data copy process..
> >
> > Check ur DTS pack. is it well build. i mean the work flow of the
package..
> ?
> >
> > What happens when a step in DTS raise Error , u handle this ?
> >
> >
> > "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> > news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > > Hello everybody.
> > >
> > > I've got 4 servers running Windows 2003 Standard edition with SQL 2000
> > SP3a.
> > >
> > > On each SQL server there is a database that is copied to all the other
> > > servers every night using a DTS scheduled job.
> > >
> > > The database sizes are ranging from 50 Mb to 520 Mb.
> > >
> > > Now the problem is that sometimes the job fails but it still reports
> > > Successful in the Job History. The typical problem is that one ore
more
> of
> > > the tables on one of the destination server are empty. The table is
> there
> > > but none of the records are there. The rest of the tables and their
> > records
> > > are fine.
> > >
> > > Can anybody give me some hints as to how I can get a more detailed
> logging
> > > of the process so we can investigate what is going wrong?
> > >
> > > Martin Moustgaard
> > >
> > >
> >
> >
>|||A table lock is also what I'm thinking. But since the jobs are run at 2
o'clock in the morning there shouldn't be anybody working at that hour :-)
But the only way to find out if it is a table lock is to increase the
logging level on the DTS job and that is my problem since I don't know where
to do this.
Owner of the table shouldn't be a problem since it works fine most of the
time.
Martin Moustgaard
"Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
news:%23XB1zylwEHA.3376@.TK2MSFTNGP12.phx.gbl...
> May be a Table Lock while dropping this tables?
> Or.. Check the Owner Of the Tables...
>
> "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> news:eAL0hYlwEHA.3620@.TK2MSFTNGP09.phx.gbl...
> > The DTS is actually very simple.
> >
> > I only have a Copy SQL Server Objects Task in the package that is set up
> to
> > create destination objects (drop destination object first, include all
> > dependent objects, include extended properties), copy data (replace
> existing
> > data), use collation, copy all objects. Under options I've selected
> > everything except the 3 selections under secirity options.
> >
> > The only error handeling I have is that the DTS jopb is set to retry 2
> time
> > with 30 minutes interval if it fails.
> >
> > Martin Moustgaard
> >
> >
> > "Melih SARICA" <melihsarica@.hotmail.com> skrev i en meddelelse
> > news:ulPtvvkwEHA.1204@.TK2MSFTNGP10.phx.gbl...
> > > Job Shows Successfull of the Job ;
> > >
> > > not the each data copy process..
> > >
> > > Check ur DTS pack. is it well build. i mean the work flow of the
> package..
> > ?
> > >
> > > What happens when a step in DTS raise Error , u handle this ?
> > >
> > >
> > > "Martin Moustgaard" <nospam_moustrup@.hotmail.com> wrote in message
> > > news:ux2wptkwEHA.1984@.TK2MSFTNGP14.phx.gbl...
> > > > Hello everybody.
> > > >
> > > > I've got 4 servers running Windows 2003 Standard edition with SQL
2000
> > > SP3a.
> > > >
> > > > On each SQL server there is a database that is copied to all the
other
> > > > servers every night using a DTS scheduled job.
> > > >
> > > > The database sizes are ranging from 50 Mb to 520 Mb.
> > > >
> > > > Now the problem is that sometimes the job fails but it still reports
> > > > Successful in the Job History. The typical problem is that one ore
> more
> > of
> > > > the tables on one of the destination server are empty. The table is
> > there
> > > > but none of the records are there. The rest of the tables and their
> > > records
> > > > are fine.
> > > >
> > > > Can anybody give me some hints as to how I can get a more detailed
> > logging
> > > > of the process so we can investigate what is going wrong?
> > > >
> > > > Martin Moustgaard
> > > >
> > > >
> > >
> > >
> >
> >
>