HELP! Transaction Log Backup!

Hello!!

I am relatively new to SQL and am having a problem. I have taken over
a SQL 2000 db that did not have any backup plans scheduled for it. I
created a maintenance plan that backs up the database every night and
another plan that backs up the system databases once a week. I ran
both these jobs successfully, but when I created a third plan to
backup the transaction log it fails with the following message:
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed. I then executed the following
statement: sp_grantlogin 'NT Authority\System' which did not work.
I still do not understand why I can't backup the log? The database
backs up fine with no errors so it can't be a permission thing and I
created the job with a maintenance plan so it has to be in full
recovery mode. Right? How do I tell? Thank you for all your
responses!!

NS"tech_guru" <nstimely@.cityofpasadena.net> wrote in message
news:3ff7efe5.0409091230.2126a3af@.posting.google.c om...
> Hello!!
> I am relatively new to SQL and am having a problem. I have taken over
> a SQL 2000 db that did not have any backup plans scheduled for it. I
> created a maintenance plan that backs up the database every night and
> another plan that backs up the system databases once a week. I ran
> both these jobs successfully, but when I created a third plan to
> backup the transaction log it fails with the following message:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed. I then executed the following
> statement: sp_grantlogin 'NT Authority\System' which did not work.
> I still do not understand why I can't backup the log? The database
> backs up fine with no errors so it can't be a permission thing and I
> created the job with a maintenance plan so it has to be in full
> recovery mode. Right? How do I tell? Thank you for all your
> responses!!

It probably is a permissions problem.

Where are you backing up to? If it's any place other than the same box, you
need to run SQL admin as a domain account, SYSTEM only has rights on the
local system.

> NS|||tech_guru (nstimely@.cityofpasadena.net) writes:
> I am relatively new to SQL and am having a problem. I have taken over
> a SQL 2000 db that did not have any backup plans scheduled for it. I
> created a maintenance plan that backs up the database every night and
> another plan that backs up the system databases once a week. I ran
> both these jobs successfully, but when I created a third plan to
> backup the transaction log it fails with the following message:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed. I then executed the following
> statement: sp_grantlogin 'NT Authority\System' which did not work.
> I still do not understand why I can't backup the log? The database
> backs up fine with no errors so it can't be a permission thing and I
> created the job with a maintenance plan so it has to be in full
> recovery mode. Right? How do I tell? Thank you for all your
> responses!!

I would double-check that the database is not in simple recovery mode.

One way to find out what the problem is, would be to go to the job
step, find the Advanced tab, and specify an output file. Hopefully
you can get a better error message that way.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I have the same problem. I output the log and recevied the following
message:

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
Server 'O4' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
Backup can not be performed on database 'LouiseTest'. This sub task is
ignored.

End of maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
1:59:53 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)

What setting must I change? Is it an access thing?

Erland Sommarskog wrote:
> tech_guru (nstimely@.cityofpasadena.net) writes:
> > I am relatively new to SQL and am having a problem. I have taken
over
> > a SQL 2000 db that did not have any backup plans scheduled for it.
I
> > created a maintenance plan that backs up the database every night
and
> > another plan that backs up the system databases once a week. I ran
> > both these jobs successfully, but when I created a third plan to
> > backup the transaction log it fails with the following message:
> > Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed.
[SQLSTATE
> > 42000] (Error 22029). The step failed. I then executed the
following
> > statement: sp_grantlogin 'NT Authority\System' which did not
work.
> > I still do not understand why I can't backup the log? The
database
> > backs up fine with no errors so it can't be a permission thing and
I
> > created the job with a maintenance plan so it has to be in full
> > recovery mode. Right? How do I tell? Thank you for all your
> > responses!!
> I would double-check that the database is not in simple recovery
mode.
> One way to find out what the problem is, would be to go to the job
> step, find the Advanced tab, and specify an output file. Hopefully
> you can get a better error message that way.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> I have the same problem. I output the log and recevied the following
> message:
>
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> Server 'O4' as 'NT AUTHORITY\SYSTEM' (trusted)
> Starting maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
> 1:59:53 PM
> Backup can not be performed on database 'LouiseTest'. This sub task is
> ignored.
> End of maintenance plan 'LouiseTest MaintenancePlan_2' on 1/28/2005
> 1:59:53 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>
> What setting must I change? Is it an access thing?

And just like the guy who had the same problem, you need to find the
job under SQL Server Agent->Jobs, right-click it and Select View job
history and check the box "Show step details" in the upper-right corner.
Then you will find what the error actually is.

--
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 - that worked. Another problem is transaction backups:

Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.

The database is on a network drive.
I am creating the plan locally. Could that be an issue? The same thing
is done for index tuning and database backup (i figured out you should
save the files by the default location) and those tasks work.|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> Thanks - that worked. Another problem is transaction backups:
> Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
> 42000] (Error 22029). The step failed.
> The database is on a network drive.

And the actual error message is?

When performing any access to network drives from SQL Server, including
backups, the account under which SQL Server runs must have access to
the network drive. Typically this is not the case if the SQL Server runs
as Local System. The remedy is run SQL Server under a domain user with
the necessary access rights.

You change the account for SQL Server by right-clicking My Computer,
select Manage. Find Services, then the MS SQL Server service. Open it,
and log-on information is on the second tab.

--
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, for the help and for tolerating my easy questions...I'm very
new to the 'admin' of NT, SQL, etc.
Also, I noticed the account was locked out, so see if that does the
trick.|||Update: that didn't work - i'm frustrated! Is there a book or website
that can give further instructions on windows security?|||chrispycrunch (chrispycrunch@.gmail.com) writes:
> Update: that didn't work - i'm frustrated!

And the error message is?

To repeat from earlier in the thread:

And just like the guy who had the same problem, you need to find the
job under SQL Server Agent->Jobs, right-click it and Select View job
history and check the box "Show step details" in the upper-right corner.
Then you will find what the error actually is.

> Is there a book or website that can give further instructions on windows
> security?

Probably, but Windows is not my speciality. Anyway, without the error
message, books are not going to help you.

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

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

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas?
At this stage any suggestions will be greatly appriciated.
TIA
Rea
Rea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas?
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas'
At this stage any suggestions will be greatly appriciated.
TIA
ReaRea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas'
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>

help! scheduled package does nothing...

I am totally lost on this one:
I have a dts package which transforms data from tabula database into sql
server through a propriatary odbc driver.
When executed manually (through 'execute package') all is well, connection
is made to both sides
and data flows nicely.
When I schedule this package to a job it ran but nothing happaned.. Not a
single record
retrieved, nor did I get any error massage. It just vanished after 1 second
into thin air...
Both the sqlserver and the sqlserveragent services are ran by the same user
(a domain administrator) which is also the logged on user on
the sqlserver machine.
Sqlserver version is 2000 with sp installed.
The server's os is windows server 2003 enterprise edition.
Any ideas'
At this stage any suggestions will be greatly appriciated.
TIA
ReaRea
Have you checked that SQL Agent is running?
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uw18R6SLEHA.268@.TK2MSFTNGP11.phx.gbl...
> I am totally lost on this one:
> I have a dts package which transforms data from tabula database into sql
> server through a propriatary odbc driver.
> When executed manually (through 'execute package') all is well, connection
> is made to both sides
> and data flows nicely.
> When I schedule this package to a job it ran but nothing happaned.. Not a
> single record
> retrieved, nor did I get any error massage. It just vanished after 1
second
> into thin air...
> Both the sqlserver and the sqlserveragent services are ran by the same
user
> (a domain administrator) which is also the logged on user on
> the sqlserver machine.
> Sqlserver version is 2000 with sp installed.
> The server's os is windows server 2003 enterprise edition.
> Any ideas'
> At this stage any suggestions will be greatly appriciated.
> TIA
> Rea
>sql

Help!

I have a SQL Server 7 running on NT 4.0 (not by choice). Last night at about 10:00 pm a scheduled DTS package failed. It has been running hourly for months without any problems. Suddenly I'm getting an error "Server could not load DCOM."

Has anyone else experienced this, and if so, how did you fix it?

THANKS!!!check security context of the account the server runs under.

help with understanding transactional replication

Hi everybody,
I was told that for transactional replication (see posts below) I need to
have snapshot scheduled to run for instance each night. But i dont
understand this.
This is my way of thinking how transactional replication should be
initiated:
- logreader, snapshot and distributer are stopped
- logreader is started so that it captures transactions that snapshot might
miss out on
- run snapshot immediately after logreader is started.
- snapshot starts doing its thing (copying the schema and the data in the
tables of the ddatabase). If for example snapshot has already processed
TableA and a change in data is made to TableA, logreader will pick this
change up and record it.
- once snapshot has completed its task, the distributer is started. The
distributer moves the snapshot to its destination and then reindexes the
tables. Finally it processes those transactions captured by the logreader.
Is this right?
I cant understand why the snapshot agent should be scheduled for
transactional replication, if the logreader is processing all future
transactions. My thought was that:
initial_snapshot
+
ongoing_transactions (as processed by the logreader)
=
current state of database
So why is there is a need to schedule snapshot for transactional
replication?
Any help in clearing up any of my misconceptions would be fantastic!
cheers, john
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23$2wX4c7FHA.3648@.tk2msftngp13.phx.gbl...
> The snapshot agent should be scheduled, perhaps each hour, or at a time
> when there are few users on your system. Note that a snapshot will only be
> generated if a subscriber needs one. Otherwise no snapshot will be
> generated. So, the only time you need to start this agent is when a
> subscriber needs one.
> The log reader agent should be running continuously.
> I normally run the distributation agent continuously. It doesn't matter in
> which order you start it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "john r" <johnr@.trailer.com> wrote in message
> news:ucO85ub7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
John,
the snapshot agent runs for initialization and reinitialization only. If you
have loads of anonymous subscribers where you have no idea when they'll come
online, then perhaps there is a case for frequent snapshots (is this what
was being referred to by whoever it was who told you that the snapshot agent
needs to run every night?), but most likely this isn't the case for you. In
my setup, we have only ever run the snapshot agent once on some
publications. Certainly the snapshot agents are all disabled and only run
manually when necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)