June 26, 2008 at 5:49 pm
This is my new place of work. We have a SQL 2000 environment having Full Backup schedule everynight and T-log backup every one hour. The Full backup is a Native one stored on the network with 2 steps involving as
1.wait for T-SQL processes ( with query as exec usp_waitforprocess)
2.Run Full Backup(with query as exec usp_runfullbackup)
Now why isn't the full backup on this dev server running . The job is scheduled and enabled. I am a new Dba with sa role, even though I manually tried to exec the job, it fails on me. What could be the possible reason.Any help really appreciated.
Thanks in Advance 🙂
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 26, 2008 at 6:08 pm
What error message are you getting when the backup fails? If the backup is running from a scheduled job, does the account under which the sql server agent is running have permissions to execute the proc you mentioned and have rights to the target directory for the backup?
June 26, 2008 at 6:42 pm
Ok now..this job has been running for over an year and scheule has been the same too. I am new Dba here..I reviewd the query wherein the job checks for suser_sname = sa, and the job is been scheduled to run under 'sa' too/ I didn't change any role for it. The error is 'backup failed for user "'my id name' the job was ordered to start at step 2" now what is the exact problem. Has it got something to do with the disks that are in place which store the backup files. I dont know what is the actual disk location too..I can find out later.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 27, 2008 at 11:03 am
Check the sql server error log, and the event log on the windows box for more meaningful error messages.
Also, if this is running as a part of a scheduled job, the jobs run under the account that the sql server agent was set up with. So that's the account that has to have write priviledges to the target directory for the backups. You have to know where the backups are going to make sure the sql server agent account has the correct level of access.
June 27, 2008 at 11:42 pm
When I login to my SQL server account and try to execute the job the job fails on me saying 'job failed, invoked by xxxxxx , the job was requested to start at step 2"...How do I change the location or the access level to this job..or for that matter how can I configure the agent to run the jobs on my login frm now on and not on the login of the original user who created it....
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 28, 2008 at 12:51 am
use enterprise manager, double click the job, (i.e. rightclick job, properties)
Alter the jobowner.
For backup jobs, we prefer using sa as jobowner to avoid all issues with windows accounts. (a win-use may change function/responcebility in the company, ...) A service account rarely has to be modified.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 28, 2008 at 9:46 am
why is job starting at step 2 and what does step 1 do?
---------------------------------------------------------------------
June 29, 2008 at 8:30 am
step 1 says : wait for TSQL Process, which calls in a script having SP as usp_WaitForProcesses.
I tried changing the Owner, but it wont let me do it also the step 2 script has a command in it which verifies
suser_sname ='sa' if not it comes out sayin 'insufficient privileges'.
So can I do this like 'changing the above scipt and replacing sa with my login' and then executing.
Thanks.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 29, 2008 at 10:11 am
Please post the script. I understand you're a new DBA, but you're not giving us enough of a picture.
Can you log in as SA and try the job?
Anything that is "usp_" isn't included with SQL Server. Someone at your company created this, so check that procedure to see what it does. Can you execute that from QA?
June 29, 2008 at 10:35 am
yes please post SP usp_waitforprocess, and the full script in step 2. this is unusual logic if all you want to do is backup the databases .
meanwhile try commenting out (dont remove it yet) the check for suser_name = 'sa'
---------------------------------------------------------------------
June 29, 2008 at 12:22 pm
Please have a look at the attachments, this shall help. For that even the T-Log backup hace been failing too, which are scheduled hourly. One issue I am having from few hours is that some databases on this server are shown as suspect, so I need to have a full backup to restore this DB's. Looking forward to some suggestions..
Thanks in Advance
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 29, 2008 at 4:00 pm
jeez, how to make something as simple as backing up databases complicated.
If you have suspect databases that will cause job to fail.
Are you using SQLLitespeed or not, unfortunately these store procs just call umpteen other procs and functions.
If this is a dev server put the databases in simple mode and don't bother with log backups. transaction logging very rarely required on dev databases.
Post the code in the SQLagent jobs and the full error. I'm not even sure anymore this has got anything to do with id job running under.
---------------------------------------------------------------------
June 29, 2008 at 4:31 pm
As George mentioned, please post the code to let us see what is happening and then we can help.
June 29, 2008 at 9:02 pm
Ok, the job includes two steps
1.
exec usp_WaitForProcess 'backup' ( refer WP.doc attachment for the actual code)
2.
exec usp_BackupDatabases 'full', @VerifyBackup = 0 ( refer FB.doc attachment for actual code)
exec usp_BackupDatabases 'xlog', @InitBackupFile = 1
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
June 30, 2008 at 12:18 am
imo the device activation error you see in the errorlog file, aren't related to your backup job.
What has happend to your G-drive ?? :crazy:
(G:\SQL2KData\...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply