Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nstimely
Starting Member
6 Posts |
 Posted - 2004-09-09 : 16:32:19
|
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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-09 : 16:38:24
|
| It might not be in FULL recovery model. The maintenance plan doesn't change it for you. But the default is FULL, so if it weren't ever changed, then it's still FULL. To see what it is, right click on your db in EM, go to properties, then to Option tab.And are you using a domain account that has local admin privileges for the 2 SQL services? If not, you should fix that and make sure your jobs are owned by sa.Tara |
 |
|
|
nstimely
Starting Member
6 Posts |
Posted - 2004-09-09 : 16:41:35
|
| Thank you so much for the quick response!! Yes, it is in simple mode! What would it hurt if I put it in full recovery mode? Thanks again! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-09 : 16:45:55
|
| Disk space might be an issue. Just verify you have enough disk space for an increased TLOG file and also enough disk space for the trn backup files. The TLOG file (LDF) might increase if you don't backup the tlog enough (we do it every 15 minutes due to the criticality of our data) and also if you've got optimization job to reindex. Performance shouldn't be an issue as long as you do it often enough so that the trn files aren't very large.Tara |
|
|
|
|
|
|
Help! Transaction Log backup problem