Default Database brought offline SQL Server Agent Job fails!!!

Hi All,

There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.

I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.

Any pointers ?

Have you tried tracing the connection attempt using SQL Profiler?

This is a SQL engine security issue - its nothing to do with SSIS.

-Jamie

|||

Jamie,

You were right this has nothing to do with SSIS indded, I was using a Proxy account and I tried resetting the password, and it seems to work now. Thanks in helping me look at the right area.

Decreased memory usage when the workload is heavy?

I have a specific job that should be run with a decreased memory usage when the workload is heavy on the SQL Server.

This is a heavy job that has no specific requirement when it comes to response time.

It is important that the rest of the application shouldn't be affected with longer response time when this job is running.

How can this job bee handled from the application, without having to create a separate batch job.

You cannot limit the amount of memory to be used for a certain process. If you are worried the production would be affected during peak hours, have a reporting server that you replicate to and run your process off of it.

Decoding a Maintenance Plan

How can I "decode" exactly what is done during a scheduled Maintenance Plan
(or its job/step)?
The job steps only indicate EXECUTE master.dbo.xp_sqlmaint N'-PlanID <uuid>
etc.
I tried sp_help_maintenance_plan, but received the error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_help_maintenance_plan'.
I am a member of the sysadmin fixed role.
TIA,
Tore.Tore
In EM go to your maint plan (in the management folder) and
look at the properties. This will show you what it is
doing and you can make changes there.
Regards
John|||Thanks. It isn't there, either.
Another post pointed me to the "Database Maintenance Plans" under the
Management folder in EM. (I'm not blind, just focused... :->).
Tore.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23MUito2WDHA.652@.TK2MSFTNGP10.phx.gbl...
> Hi Tore,
> sp_help_maintenance_plan exists in the msdb database, not in the master
> database, which means that you either have to be in msdb when you run it,
or
> prefix it with msdb.. to run it from any database:
> EXEC msdb..sp_help_maintenance_plan will work.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
> news:OUGPWf2WDHA.212@.TK2MSFTNGP12.phx.gbl...
> > How can I "decode" exactly what is done during a scheduled Maintenance
> Plan
> > (or its job/step)?
> >
> > The job steps only indicate EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> <uuid>
> > etc.
> >
> > I tried sp_help_maintenance_plan, but received the error
> >
> > Server: Msg 2812, Level 16, State 62, Line 1
> > Could not find stored procedure 'sp_help_maintenance_plan'.
> >
> > I am a member of the sysadmin fixed role.
> >
> > TIA,
> > Tore.
> >
> >
>|||I'm not blind, just focused... :->
Thanks,
Tore.
"John Bandettini" <johnbandettini@.yahoo.co.uk> wrote in message
news:8ed101c35b6b$305067f0$a001280a@.phx.gbl...
> Tore
> In EM go to your maint plan (in the management folder) and
> look at the properties. This will show you what it is
> doing and you can make changes there.
> Regards
> John