Re: Locate T-SQL Referenced by Job Step?
- From: "Tibor Karaszi" <tibor_please.no.email_karaszi@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 5 May 2009 16:25:26 +0200
.... and as for being in the master database:
I tend to do backup and restore operations while being in the master database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kevin3NF" <kevin@xxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:eunyNvYzJHA.3404@xxxxxxxxxxxxxxxxxxxxxxx
Unless I'm missing something, 'backup log myDB to myDBBackup' is the actual code that is running. Its not a reference to a stored proc in master or elsewhere.
Its essentially telling the SQL Server to run a transaction log backup of myDB and send the results to the backup device named myDBBackup. Its a direct call, not a reference to another routine or SP
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
Twitter: Kevin3NF
"Gerry Hickman" <gerry666uk2@xxxxxxxxxxxxxxxx> wrote in message news:%23hoXJQYzJHA.1380@xxxxxxxxxxxxxxxxxxxxxxxHi,
In SQL Server 2005, each job conatains one or more job steps. There's a type of job step called TSQL that can run a T-SQL command. The job step uses parameters to reference the T-SQL command, e.g. the name of the database within which the command resides, and the command to be run. This information is stored in 'msdb'. Below is an extract from a job script that shows the job step.
/****** Object: Step [transaction backup] Script Date: 05/05/2009 13:09:07 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'transaction backup',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'backup log myDB to myDBBackup',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
It's making reference to a "command" called 'backup log myDB to myDBBackup' in the 'master' database.
1. It seems very odd to me that a backup command would be burried in master?
2. I can NOT find any stored procedure in 'master' called 'backup log myDB to myDBBackup'
3. The job seems to run fine. Job history claims success and files are backed up.
I'm trying to locate the 'backup log myDB to myDBBackup' command so I can look at the raw T-SQL. I did try things like SELECT name from sys.objects where type='P'; but nothing matches.
Strangely, I am able to resolve other T-SQL commands that reside in other job steps on the same server, but in these cases they reside in user databases, not master...
--
Gerry Hickman
London (UK)
.
- References:
- Locate T-SQL Referenced by Job Step?
- From: Gerry Hickman
- Re: Locate T-SQL Referenced by Job Step?
- From: Kevin3NF
- Locate T-SQL Referenced by Job Step?
- Prev by Date: Re: Shrinking Log Files - How important?
- Next by Date: Denormalization Question
- Previous by thread: Re: Locate T-SQL Referenced by Job Step?
- Next by thread: RE: Locate T-SQL Referenced by Job Step?
- Index(es):
Relevant Pages
|
