Can't start a job that was created by user A from user B

Hello all,
I have a job I created with user A, but I need user B to be able to execute
this job programatically. I've given user B permission to EXECUTE the
"msdb.dbo.sp_start_job" sproc and the user is able to execute it but with an
error thrown. The following is the error I get when I run it with user B.
This error does not appear when I run it with user A.
The specified @.job_name ('MyJobName') does not exist.
After doing some research by looking at the system sprocs, I found that the
error is thrown from a call made to 'msdb.dbo.sp_verify_job_identifiers'. I
researched that sproc and then found the system code that is throwing the
error, listed below.
-- Check if the job name is ambiguous
IF ((SELECT COUNT(*)
FROM msdb.dbo.sysjobs_view
WHERE (name = @.job_name)) > 1)
BEGIN
RAISERROR(14293, -1, -1, @.job_name, @.name_of_id_parameter,
@.name_of_name_parameter)
RETURN(1) -- Failure
END
I performed a SELECT * query to the sysjobs_view view with user B and 0 rows
were returned. So it appears that the view is filtering the results by user
and the job is not found which is causing the error.
Do you guys know of a way I can make this work? I would appreciate any help.
Thanks!
JohnnyHi Johnny,
Only members of the sysadmin fixed role or the jobowner can start a job.
HTH
Adam
--
Adam J Warne, MCDBA
"Johnny" wrote:
> Hello all,
> I have a job I created with user A, but I need user B to be able to execute
> this job programatically. I've given user B permission to EXECUTE the
> "msdb.dbo.sp_start_job" sproc and the user is able to execute it but with an
> error thrown. The following is the error I get when I run it with user B.
> This error does not appear when I run it with user A.
> The specified @.job_name ('MyJobName') does not exist.
> After doing some research by looking at the system sprocs, I found that the
> error is thrown from a call made to 'msdb.dbo.sp_verify_job_identifiers'. I
> researched that sproc and then found the system code that is throwing the
> error, listed below.
> -- Check if the job name is ambiguous
> IF ((SELECT COUNT(*)
> FROM msdb.dbo.sysjobs_view
> WHERE (name = @.job_name)) > 1)
> BEGIN
> RAISERROR(14293, -1, -1, @.job_name, @.name_of_id_parameter,
> @.name_of_name_parameter)
> RETURN(1) -- Failure
> END
>
> I performed a SELECT * query to the sysjobs_view view with user B and 0 rows
> were returned. So it appears that the view is filtering the results by user
> and the job is not found which is causing the error.
> Do you guys know of a way I can make this work? I would appreciate any help.
> Thanks!
> Johnny
>|||Thanks. I didnt want to do that but I guess I have no choice.
Johnny
"Adam Warne" wrote:
> Hi Johnny,
> Only members of the sysadmin fixed role or the jobowner can start a job.
> HTH
> Adam
> --
> Adam J Warne, MCDBA
>
> "Johnny" wrote:
> > Hello all,
> >
> > I have a job I created with user A, but I need user B to be able to execute
> > this job programatically. I've given user B permission to EXECUTE the
> > "msdb.dbo.sp_start_job" sproc and the user is able to execute it but with an
> > error thrown. The following is the error I get when I run it with user B.
> > This error does not appear when I run it with user A.
> >
> > The specified @.job_name ('MyJobName') does not exist.
> >
> > After doing some research by looking at the system sprocs, I found that the
> > error is thrown from a call made to 'msdb.dbo.sp_verify_job_identifiers'. I
> > researched that sproc and then found the system code that is throwing the
> > error, listed below.
> >
> > -- Check if the job name is ambiguous
> > IF ((SELECT COUNT(*)
> > FROM msdb.dbo.sysjobs_view
> > WHERE (name = @.job_name)) > 1)
> > BEGIN
> > RAISERROR(14293, -1, -1, @.job_name, @.name_of_id_parameter,
> > @.name_of_name_parameter)
> > RETURN(1) -- Failure
> > END
> >
> >
> > I performed a SELECT * query to the sysjobs_view view with user B and 0 rows
> > were returned. So it appears that the view is filtering the results by user
> > and the job is not found which is causing the error.
> >
> > Do you guys know of a way I can make this work? I would appreciate any help.
> > Thanks!
> >
> > Johnny
> >sql

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000
Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
Regards
Pawel Potasinski
Uytkownik <-> napisa w wiadomoci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>
|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> Uytkownik <-> napisa w wiadomoci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
--
Regards
Pawel Potasinski
U¿ytkownik <-> napisa³ w wiadomo¶ci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> U¿ytkownik <-> napisa³ w wiadomo¶ci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> Any ideas why I can't select anything from 1 table? If I try to execute a
>> select statement (even Top 1), the statement just hangs. We constantly
>> have to restart SQL Server to "free" up the problem, but it keeps coming
>> back.
>> Any ideas what's going on?
>> Thanks.
>> Sql 2000
>

Can't select from 1 table

Any ideas why I can't select anything from 1 table? If I try to execute a
select statement (even Top 1), the statement just hangs. We constantly have
to restart SQL Server to "free" up the problem, but it keeps coming back.
Any ideas what's going on?
Thanks.
Sql 2000Have your tried to select data from the table using NOLOCK hint?
There can be some blocking problem (some locks on your table that do not
allow other locks). Use sp_lock system stored procedure to find the locks on
your table (especially look for X - eXclusive locks, identify your table by
object_id - use OBJECT_ID function to know the ID). Then you can use sp_who
system stored procedure to find the process (its spid is returned by
sp_lock) that puts those locks and finally you can kill the proper session
(using kill statement). You cal also use Enterprise Manager and its
Management section.
The remedies for blocking:
- always close (commit or rollback) transactions,
- keep transactions short,
- use the proper transaction isolation level.
If it is blocking problem, try to isolate the query or procedure causing the
problem (use Profiler to catch batch executions, locks being required and
lock escalations).
Regards
Pawel Potasinski
Uytkownik <-> napisa w wiadomoci
news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
> Any ideas why I can't select anything from 1 table? If I try to execute a
> select statement (even Top 1), the statement just hangs. We constantly
> have to restart SQL Server to "free" up the problem, but it keeps coming
> back.
> Any ideas what's going on?
> Thanks.
> Sql 2000
>|||Thanks...we just found that there was a lock on the table by a process id,
but this has happened several times in the last few days, whereas it never
used to happen.
Still looking into it. Thanks for the info.
"Pawel Potasinski" <pawel.potasinski@.gmail.com> wrote in message
news:eUxUwtT0HHA.4816@.TK2MSFTNGP04.phx.gbl...
> Have your tried to select data from the table using NOLOCK hint?
> There can be some blocking problem (some locks on your table that do not
> allow other locks). Use sp_lock system stored procedure to find the locks
> on your table (especially look for X - eXclusive locks, identify your
> table by object_id - use OBJECT_ID function to know the ID). Then you can
> use sp_who system stored procedure to find the process (its spid is
> returned by sp_lock) that puts those locks and finally you can kill the
> proper session (using kill statement). You cal also use Enterprise Manager
> and its Management section.
> The remedies for blocking:
> - always close (commit or rollback) transactions,
> - keep transactions short,
> - use the proper transaction isolation level.
> If it is blocking problem, try to isolate the query or procedure causing
> the problem (use Profiler to catch batch executions, locks being required
> and lock escalations).
> --
> Regards
> Pawel Potasinski
>
> Uytkownik <-> napisa w wiadomoci
> news:OPD%232lT0HHA.1484@.TK2MSFTNGP06.phx.gbl...
>

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can''t run SSIS package via Agent

I've seen many postings on various forums on how to get SQL agent to execute an SSIS package. I have one that was originally created via the import wizard in SSMS, later modified in VS2005, and then re-imported in SSMS using the Object Explorer interface to load the dtsx file into SQL storage. I've tried several package protection options when importing the package: "Don't save sensitive data" (the package has no passwords in it), server storage and roles, and a specified package password (which I entered as a /DECRYPT command line parameter in the Agent job step). Agent is running using a domain admin account. No matter what I've tried, I still get an error during job execution that it failed to decrypt the password XML node. The package runs just fine when executed manually in the SSIS Object Explorer.

How can I resolve this?

Are you sure you have been talking about the same packge? You have clearly tried the options, including those which mean there is no encryption to decrypt, so it makes no sense. I have often got confused between versions or a similar mix up when frustated and trying to debug an issue like this.

|||

And by this, I believe that darren means versions of the same package. I have had similar problems in the past where I will continue to bang my head against the wall knowing that something should be working before remembering that I didn't copy the latest version of the package over to the release folder, or from the release folder to the sql package storage etc...

Anywho, I would try pushing it out with encrypt sensitive with password and if it asks you for a password when you are setting up the package in the job step you know that you have the correct version... If you use the sql package store on your ssis server, I would use the serverstorage method of encryption.

|||

Bear with me -- this is my first SSIS project; I've only used DTS in the past. I have only one version of the dtsx file. I've been setting the protection options when I import the file into SSIS via the object explorer. When you say "push it out with 'encrypt sensitive with password'", do you mean there is some way to set that option in the VS designer IDE? When I tried the pw option on import to SSIS, I did not get prompted for the pw when I created the Agent job step. Instead, I put a /DECRYPT switch in the job step's command line options. Please explain.

(And thanks very much for your help!)

|||yes there is a way to set this in the visual studios designer. Right click on the background of the control flow task of your package and select properties. About half way down the list there is a property for "protection level" where there is a drop down for the different types of encryption. If you choose encrypt sensitive w/ password it will prompt you for a password and a confirm password to encrypt the package with. After you have filled this out, any time that you go to the job step maintenance screen it will prompt you for the password (to verify your right to see the connection strings and whatnot).|||

That worked! I found the property setting in the designer and set it as you suggested. When I re-imported the dtsx file into SSIS on the production server, I also set the same protection option and entered the package pw set in the designer. I then deleted the old Agent job step and created a new one. It did not prompt for the package password, but it did execute the SSIS package this time. I saw no place in the various settings tabs for a package pw, nor did it prompt me for one, and I did not alter the command line defaults. Should it have prompted for a pw?

At any rate, the Agent job now works, and I am most grateful!

edit: BTW, when I set the 'Encrypt sensitive with password' option in the designer, it did not prompt for a package pw. There was a line in the property sheet a few lines up for entering a password.

Can't run OpenQuery against OLAP Database

I'm trying to execute a query against an AS 2000 database from query analyzer. I've created the linked server using the "Microsoft OLE DB Provider for OLAP Services" ("Allow InProcess" checked) but I'm getting an error message: "Could not get the data of the row from the OLE DB provider 'MSOLAP'.
[OLE/DB provider returned message: An unexpected internal error has occurred.]
OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetData returned 0x80040e21: Data status returned from the provider: [COLUMN_NAME=[Measures].[Act] STATUS=DBSTATUS_E_UNAVAILABLE]]."

Has anyone seen this problem before? Do you know what is needed to fix it? I had been able to execute an OpenQuery against another AS2000 database but that was with another machine and I can't pin point what I did to get it to work (the machine had a lot installed on it and I'm not sure what did the trick).

Any help is greatly appreciated.

Carmen.

Please make sure that AS2000 SP4 client components are installed on that machine. I recall similar bug that was fixed in one of the SPs.|||

Thanks, Irina. All machines involved have SP4 installed on them. I think there is something missing on the client as well as on the server. Here is why:

Old laptop connecting to Server A: OK

Old laptop connecting to Server B: Error

New laptop connecting to Server A: Error

New laptop connecting to Server B: Error

If the problem was only on the new laptop, the old one should be able to connect to Server B. And if the problem was only on Server B, the new laptop should connect to Server A.

Thanks,

Carmen.

|||

Actually, I found that what is different on my old laptop is that it has SP3 instead of SP4.... And I think I read it somewhere about SP4 causing this problem. Does anyone knows of a fix for this?

Thanks,

Carmen

Can''t run integration services package as a sql server agent task

Hi guys, I 've been trying to run an integration services package as server agent scheduled task so that I dont have to manually execute the package every 2 hours. I was following how to guide in msdn to set up a server agant task for integration services package but I'm kind a lost. First of all when I ran the Management Studio and connected to the integration services the server agent does not show up it only shows me runing and stored packages. I can access to the server agent when I connect to the database engine but when I create a task and try to add a step to open a integration services package it does not give me the choice in type box. I couldn't figure out the problem. Is there a way to enable server agent to run under integration services too (not just under database engine) if databse engine is the only place server agents works , why dont I see the option to open a ssis package at adding a step to tasks? or is there a way to automate the process to run the package automatically without using the server agent at all?

Thanks,

Burak

PS I was following the http://msdn2.microsoft.com/en-us/library/ms139805.aspx article

You will find SQL Server Agent only under the relational DB and that is fine.; You should be able to create a job with an step to run the package you want to.

You have 2 option for the step type; you can use a SSIS package type or a command line (CmdExc) type.

This is a KB article with the most common problems when scheduling a package and their workarounds:

http://support.microsoft.com/kb/918760

BTW, Where is the package stored?

|||

The server agent is part of the database engine.

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

package is stored in the same file system as the sql server.

By the way when I chose the type as CmdExec it gives me sql error #14250 and it says my command is longer than 3200 chars as it tries to run the whole xml file as a command line prompt I guess. I believe if I can find a way to activate SSIS package option at the package type field I might be able to get pass this problem I'm going to try the solution with the proxy account maybe that will help.

|||

Ok I tried to follow the tutorials about how to create proxies and credentials unfortunatly when I expand the security tab of the database engine it just shows me logins and server roles no credentials. I do wonder wouldn't be easier if Microsoft just added the functionality to schedule the packages through Business Intelligence. I'm just frusturated following through all those how to's and at the end of the day still having the same problem.

|||

You tried the security folder of the overall server, and not the database that you are using? Are you using SQL Server Management Studio? It should be there....

Can''t run integration services package as a sql server agent task

Hi guys, I 've been trying to run an integration services package as server agent scheduled task so that I dont have to manually execute the package every 2 hours. I was following how to guide in msdn to set up a server agant task for integration services package but I'm kind a lost. First of all when I ran the Management Studio and connected to the integration services the server agent does not show up it only shows me runing and stored packages. I can access to the server agent when I connect to the database engine but when I create a task and try to add a step to open a integration services package it does not give me the choice in type box. I couldn't figure out the problem. Is there a way to enable server agent to run under integration services too (not just under database engine) if databse engine is the only place server agents works , why dont I see the option to open a ssis package at adding a step to tasks? or is there a way to automate the process to run the package automatically without using the server agent at all?

Thanks,

Burak

PS I was following the http://msdn2.microsoft.com/en-us/library/ms139805.aspx article

You will find SQL Server Agent only under the relational DB and that is fine.; You should be able to create a job with an step to run the package you want to.

You have 2 option for the step type; you can use a SSIS package type or a command line (CmdExc) type.

This is a KB article with the most common problems when scheduling a package and their workarounds:

http://support.microsoft.com/kb/918760

BTW, Where is the package stored?

|||

The server agent is part of the database engine.

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

package is stored in the same file system as the sql server.

By the way when I chose the type as CmdExec it gives me sql error #14250 and it says my command is longer than 3200 chars as it tries to run the whole xml file as a command line prompt I guess. I believe if I can find a way to activate SSIS package option at the package type field I might be able to get pass this problem I'm going to try the solution with the proxy account maybe that will help.

|||

Ok I tried to follow the tutorials about how to create proxies and credentials unfortunatly when I expand the security tab of the database engine it just shows me logins and server roles no credentials. I do wonder wouldn't be easier if Microsoft just added the functionality to schedule the packages through Business Intelligence. I'm just frusturated following through all those how to's and at the end of the day still having the same problem.

|||

You tried the security folder of the overall server, and not the database that you are using? Are you using SQL Server Management Studio? It should be there....

Can''t run integration services package as a sql server agent task

Hi guys, I 've been trying to run an integration services package as server agent scheduled task so that I dont have to manually execute the package every 2 hours. I was following how to guide in msdn to set up a server agant task for integration services package but I'm kind a lost. First of all when I ran the Management Studio and connected to the integration services the server agent does not show up it only shows me runing and stored packages. I can access to the server agent when I connect to the database engine but when I create a task and try to add a step to open a integration services package it does not give me the choice in type box. I couldn't figure out the problem. Is there a way to enable server agent to run under integration services too (not just under database engine) if databse engine is the only place server agents works , why dont I see the option to open a ssis package at adding a step to tasks? or is there a way to automate the process to run the package automatically without using the server agent at all?

Thanks,

Burak

PS I was following the http://msdn2.microsoft.com/en-us/library/ms139805.aspx article

You will find SQL Server Agent only under the relational DB and that is fine.; You should be able to create a job with an step to run the package you want to.

You have 2 option for the step type; you can use a SSIS package type or a command line (CmdExc) type.

This is a KB article with the most common problems when scheduling a package and their workarounds:

http://support.microsoft.com/kb/918760

BTW, Where is the package stored?

|||

The server agent is part of the database engine.

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

package is stored in the same file system as the sql server.

By the way when I chose the type as CmdExec it gives me sql error #14250 and it says my command is longer than 3200 chars as it tries to run the whole xml file as a command line prompt I guess. I believe if I can find a way to activate SSIS package option at the package type field I might be able to get pass this problem I'm going to try the solution with the proxy account maybe that will help.

|||

Ok I tried to follow the tutorials about how to create proxies and credentials unfortunatly when I expand the security tab of the database engine it just shows me logins and server roles no credentials. I do wonder wouldn't be easier if Microsoft just added the functionality to schedule the packages through Business Intelligence. I'm just frusturated following through all those how to's and at the end of the day still having the same problem.

|||

You tried the security folder of the overall server, and not the database that you are using? Are you using SQL Server Management Studio? It should be there....

Can''t run integration services package as a sql server agent task

Hi guys, I 've been trying to run an integration services package as server agent scheduled task so that I dont have to manually execute the package every 2 hours. I was following how to guide in msdn to set up a server agant task for integration services package but I'm kind a lost. First of all when I ran the Management Studio and connected to the integration services the server agent does not show up it only shows me runing and stored packages. I can access to the server agent when I connect to the database engine but when I create a task and try to add a step to open a integration services package it does not give me the choice in type box. I couldn't figure out the problem. Is there a way to enable server agent to run under integration services too (not just under database engine) if databse engine is the only place server agents works , why dont I see the option to open a ssis package at adding a step to tasks? or is there a way to automate the process to run the package automatically without using the server agent at all?

Thanks,

Burak

PS I was following the http://msdn2.microsoft.com/en-us/library/ms139805.aspx article

You will find SQL Server Agent only under the relational DB and that is fine.; You should be able to create a job with an step to run the package you want to.

You have 2 option for the step type; you can use a SSIS package type or a command line (CmdExc) type.

This is a KB article with the most common problems when scheduling a package and their workarounds:

http://support.microsoft.com/kb/918760

BTW, Where is the package stored?

|||

The server agent is part of the database engine.

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

package is stored in the same file system as the sql server.

By the way when I chose the type as CmdExec it gives me sql error #14250 and it says my command is longer than 3200 chars as it tries to run the whole xml file as a command line prompt I guess. I believe if I can find a way to activate SSIS package option at the package type field I might be able to get pass this problem I'm going to try the solution with the proxy account maybe that will help.

|||

Ok I tried to follow the tutorials about how to create proxies and credentials unfortunatly when I expand the security tab of the database engine it just shows me logins and server roles no credentials. I do wonder wouldn't be easier if Microsoft just added the functionality to schedule the packages through Business Intelligence. I'm just frusturated following through all those how to's and at the end of the day still having the same problem.

|||

You tried the security folder of the overall server, and not the database that you are using? Are you using SQL Server Management Studio? It should be there....

Can''t run integration services package as a sql server agent task

Hi guys, I 've been trying to run an integration services package as server agent scheduled task so that I dont have to manually execute the package every 2 hours. I was following how to guide in msdn to set up a server agant task for integration services package but I'm kind a lost. First of all when I ran the Management Studio and connected to the integration services the server agent does not show up it only shows me runing and stored packages. I can access to the server agent when I connect to the database engine but when I create a task and try to add a step to open a integration services package it does not give me the choice in type box. I couldn't figure out the problem. Is there a way to enable server agent to run under integration services too (not just under database engine) if databse engine is the only place server agents works , why dont I see the option to open a ssis package at adding a step to tasks? or is there a way to automate the process to run the package automatically without using the server agent at all?

Thanks,

Burak

PS I was following the http://msdn2.microsoft.com/en-us/library/ms139805.aspx article

You will find SQL Server Agent only under the relational DB and that is fine.; You should be able to create a job with an step to run the package you want to.

You have 2 option for the step type; you can use a SSIS package type or a command line (CmdExc) type.

This is a KB article with the most common problems when scheduling a package and their workarounds:

http://support.microsoft.com/kb/918760

BTW, Where is the package stored?

|||

The server agent is part of the database engine.

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

package is stored in the same file system as the sql server.

By the way when I chose the type as CmdExec it gives me sql error #14250 and it says my command is longer than 3200 chars as it tries to run the whole xml file as a command line prompt I guess. I believe if I can find a way to activate SSIS package option at the package type field I might be able to get pass this problem I'm going to try the solution with the proxy account maybe that will help.

|||

Ok I tried to follow the tutorials about how to create proxies and credentials unfortunatly when I expand the security tab of the database engine it just shows me logins and server roles no credentials. I do wonder wouldn't be easier if Microsoft just added the functionality to schedule the packages through Business Intelligence. I'm just frusturated following through all those how to's and at the end of the day still having the same problem.

|||

You tried the security folder of the overall server, and not the database that you are using? Are you using SQL Server Management Studio? It should be there....

Can''t run integration services package as a sql server agent task

Hi guys, I 've been trying to run an integration services package as server agent scheduled task so that I dont have to manually execute the package every 2 hours. I was following how to guide in msdn to set up a server agant task for integration services package but I'm kind a lost. First of all when I ran the Management Studio and connected to the integration services the server agent does not show up it only shows me runing and stored packages. I can access to the server agent when I connect to the database engine but when I create a task and try to add a step to open a integration services package it does not give me the choice in type box. I couldn't figure out the problem. Is there a way to enable server agent to run under integration services too (not just under database engine) if databse engine is the only place server agents works , why dont I see the option to open a ssis package at adding a step to tasks? or is there a way to automate the process to run the package automatically without using the server agent at all?

Thanks,

Burak

PS I was following the http://msdn2.microsoft.com/en-us/library/ms139805.aspx article

You will find SQL Server Agent only under the relational DB and that is fine.; You should be able to create a job with an step to run the package you want to.

You have 2 option for the step type; you can use a SSIS package type or a command line (CmdExc) type.

This is a KB article with the most common problems when scheduling a package and their workarounds:

http://support.microsoft.com/kb/918760

BTW, Where is the package stored?

|||

The server agent is part of the database engine.

You will need to set up the proxy / credential / etc...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

|||

package is stored in the same file system as the sql server.

By the way when I chose the type as CmdExec it gives me sql error #14250 and it says my command is longer than 3200 chars as it tries to run the whole xml file as a command line prompt I guess. I believe if I can find a way to activate SSIS package option at the package type field I might be able to get pass this problem I'm going to try the solution with the proxy account maybe that will help.

|||

Ok I tried to follow the tutorials about how to create proxies and credentials unfortunatly when I expand the security tab of the database engine it just shows me logins and server roles no credentials. I do wonder wouldn't be easier if Microsoft just added the functionality to schedule the packages through Business Intelligence. I'm just frusturated following through all those how to's and at the end of the day still having the same problem.

|||

You tried the security folder of the overall server, and not the database that you are using? Are you using SQL Server Management Studio? It should be there....

CAn't run DTSX from Visual Studio

I can create and edit DTSX packages in Visual Studio 2005, but I can't execute them because the start button is grayed out. Can someone help?

I am able to run them using the execution utility, but that's a hassle.

Kathy

Are you editing the packages stand-alone or are they part of a project/solution? You can only execute them if they are in a project.

-Jamie

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -
Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>
|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential[vbcol=seagreen]
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
log[vbcol=seagreen]
> execution
> event
> So
operation?[vbcol=seagreen]
don't
>

Can't remove inactive entries from transaction log

My database is set to Full Recovery model. I can shrink my transaction log
by executing a logfile backup, but when I execute a Full or Incremental
backup, this option is disabled. I don't understand why. With the execution
of a successful backup, entries in the log prior to the point of backup
become irrelevant! I might need subsequent logfile transactions in the event
of a failure, but I won't need the logfile prior to the point of backup. So
why can't I shrink the logfile as part and parcel of the backup operation?
Must I always execute a separate logfile backup prior to my Full /
Incremental backup for the sole purpose of shrinking the logfile? I don't
get it.
Thanks for your advice!
- Joe Geretz -Joseph
If I understood you correctly you have to run LOG file backup with INIT
option to clear all entries prior the FULL backup database.
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Joe,
A full database backup or differential backup DO NOT truncate the log ( as
you have discovered.) This is by design... Here is a sample backup plan
Full1 Full2
log1 log2 log3 log4 log5,,,, etc
If we die are the log 5 backup, we can restore from FULL2, then restore all
of the logs and we are good to go... But what if the FULL2 backup tape is
missing or corrupted? You can begin by restoring the FULL1 backup, then
log1, log2, log3, log4. etc and still get up-to-the-minute recover without
loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
full or differential database backup was done between 3 and 4. The full,
and differential backups do NOT truncate the log SPECIFICALLY to give us the
opportunity to restore from a grandparent ( FULL1 ) or even older full or
differential backup and not lose data...
If you do not need the log backups, and wish to keep the log small, you
might wish to consider using the Simple recovery model for the database...
This means you will only be able to recover to the last full or differential
backup however.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joseph Geretz" <jgeretz@.nospam.com> wrote in message
news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> My database is set to Full Recovery model. I can shrink my transaction log
> by executing a logfile backup, but when I execute a Full or Incremental
> backup, this option is disabled. I don't understand why. With the
execution
> of a successful backup, entries in the log prior to the point of backup
> become irrelevant! I might need subsequent logfile transactions in the
event
> of a failure, but I won't need the logfile prior to the point of backup.
So
> why can't I shrink the logfile as part and parcel of the backup operation?
> Must I always execute a separate logfile backup prior to my Full /
> Incremental backup for the sole purpose of shrinking the logfile? I don't
> get it.
> Thanks for your advice!
> - Joe Geretz -
>|||Wayne
Why not just clean up with INIT option the LOG file after each FULL backup?
Why to blow the LOG file?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eqD5jF9cEHA.3944@.tk2msftngp13.phx.gbl...
> Joe,
> A full database backup or differential backup DO NOT truncate the log ( as
> you have discovered.) This is by design... Here is a sample backup plan
> Full1 Full2
> log1 log2 log3 log4 log5,,,, etc
> If we die are the log 5 backup, we can restore from FULL2, then restore
all
> of the logs and we are good to go... But what if the FULL2 backup tape is
> missing or corrupted? You can begin by restoring the FULL1 backup, then
> log1, log2, log3, log4. etc and still get up-to-the-minute recover without
> loss of data... Log 4 contains ALL OF THE CHANGES since log3, Even when a
> full or differential database backup was done between 3 and 4. The full,
> and differential backups do NOT truncate the log SPECIFICALLY to give us
the
> opportunity to restore from a grandparent ( FULL1 ) or even older full or
> differential backup and not lose data...
> If you do not need the log backups, and wish to keep the log small, you
> might wish to consider using the Simple recovery model for the database...
> This means you will only be able to recover to the last full or
differential
> backup however.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joseph Geretz" <jgeretz@.nospam.com> wrote in message
> news:Ol669krcEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > My database is set to Full Recovery model. I can shrink my transaction
log
> > by executing a logfile backup, but when I execute a Full or Incremental
> > backup, this option is disabled. I don't understand why. With the
> execution
> > of a successful backup, entries in the log prior to the point of backup
> > become irrelevant! I might need subsequent logfile transactions in the
> event
> > of a failure, but I won't need the logfile prior to the point of backup.
> So
> > why can't I shrink the logfile as part and parcel of the backup
operation?
> > Must I always execute a separate logfile backup prior to my Full /
> > Incremental backup for the sole purpose of shrinking the logfile? I
don't
> > get it.
> >
> > Thanks for your advice!
> >
> > - Joe Geretz -
> >
> >
>