making JOINS

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join
>
nice weekeend
>
Table 1
>
10 5012 10
1600502
100 502 3
1500800 2
>
>
>
>
Table 2
>
1500503 1
14004000
100502 10
100600
100502 3
>

Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)|||Hi Daniel,
thanks alot for the prompt reply. this is what i had:

100 502
100 502
1500 800

as seen in the table 100 502was ordered and shipped but more articles
were shipped not ordered by customerID(100). I do not need this
information. I need a list where a customer ordered and received
shippment where no product ordered was shipped. in this case
customer(1500) should be the right answer. He ordered and recived
shippement but within the list no article oredered was shiped. but
customer (100) received the ordered product plus some extra
Hope to read from u and any one else

DA Morgan wrote:

Quote:

Originally Posted by

MORALBAROMETER wrote:

Quote:

Originally Posted by

Hi alll
I have these tables below with 3 fields each. I want to get the record
in table 1 whose field number value is same in table 2 but field number
2 on both tables are different. I mean i want the record
1500 800 2. Insight: Table 1 is modules ordered and table 2 is modules
delivered. I want to get 1500 800 2.beacuse module 800 was ordered but
in table 2 module 503 was delivered. can some one help me with a join

nice weekeend

Table 1

10 5012 10
1600502
100 502 3
1500800 2

Table 2

1500503 1
14004000
100502 10
100600
100502 3

>
Write a statement where:
field1 = field1
and
field2 <field2
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)

|||On 23 Oct 2006 00:52:26 -0700, MORALBAROMETER wrote:

Quote:

Originally Posted by

>Hi Daniel,
>thanks alot for the prompt reply. this is what i had:
>
>100 502
>100 502
>1500 800
>
>as seen in the table 100 502was ordered and shipped but more articles
>were shipped not ordered by customerID(100). I do not need this
>information. I need a list where a customer ordered and received
>shippment where no product ordered was shipped. in this case
>customer(1500) should be the right answer. He ordered and recived
>shippement but within the list no article oredered was shiped. but
>customer (100) received the ordered product plus some extra
>Hope to read from u and any one else

Hi MORALBAROMETER,

Maybe something like this?

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
LEFT JOIN Shipments AS s
ON s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID
WHERE s.CustomerID IS NULL

or the following (logically equivalent, but easier to understand for
beginning SQL coders):

SELECT o.CustomerID, o.ArticleID
FROM Orders AS o
WHERE NOT EXISTS
(SELECT *
FROM Shipments AS s
WHERE s.CustomerID = o.CustomerID
AND s.ArticleID = o.ArticleID)

If these are not what you're after, then I recommend that you post your
table structure (as CREATE TABLE statements, including constraints,
properties and indexes), some rows of sample data (as INSERT statements)
and expected results. See www.aspfaq.com/5006 for more info.

--
Hugo Kornelis, SQL Server MVP

Making a T-SQL Query

I have a table like below(bolds are field names)

R_IdNameQ1Q2Q3Q4Q5

M001Mikeabcde

J001 Johnabcde

P001 Peterabcde

I want results based on above table as below. The columns (Q1 to Q5) are put as Question_id for each user like M001 and Question (a, b ,c,d,e ) as column Question exactly as below.

Could anyone help me please writing the script to achieve below from above table.

Result

R_idNameQuestion_idQuestion

M001MikeQ1a

M001MikeQ2b

M001MikeQ3c

M001MikeQ4d

M001MikeQ5e

J001JohnQ1a

J001JohnQ2b

J001JohnQ3c

J001JohnQ4d

J001JohnQ5e

........ ....... ....... ....

Thanks all

If you use sql server 2005,

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select

R_Id

,Name

,Question_id

,Question

From

#Data

UNPIVOT

(

Question For Question_idin

([Q1],[Q2],[Q3],[Q4],[Q5])

) UPVT

|||

Assuming you are using SQL 2005, you need to use the UNPIVOT operator.

The code will be something like this:

Code Snippet

DECLARE @.MyTable table
( R_Id varchar(10),
Name varchar(20),
Q1 char(1),
Q2 char(1),
Q3 char(1),
Q4 char(1),
Q5 char(1)
)

INSERT INTO @.MyTable VALUES ( 'M001', 'Mike', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'J001', 'John', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'P001', 'Peter', 'a', 'b', 'c', 'd', 'e' )

SELECT
R_ID,
Name,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

R_ID Name Question Response
- -- --
M001 Mike Q1 a
M001 Mike Q2 b
M001 Mike Q3 c
M001 Mike Q4 d
M001 Mike Q5 e
J001 John Q1 a
J001 John Q2 b
J001 John Q3 c
J001 John Q4 d
J001 John Q5 e
P001 Peter Q1 a
P001 Peter Q2 b
P001 Peter Q3 c
P001 Peter Q4 d
P001 Peter Q5 e

|||

If you use sql server 2000

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select R_Id,Name,'Q1' Question_id,[Q1] Question From #Data

Union All

Select R_Id,Name,'Q2' Question_id,[Q2] Question From #Data

Union All

Select R_Id,Name,'Q3' Question_id,[Q3] Question From #Data

Union All

Select R_Id,Name,'Q4' Question_id,[Q4] Question From #Data

Union All

Select R_Id,Name,'Q5' Question_id,[Q5] Question From #Data

|||

Arnie, Missed column...

SELECT
R_ID,
Name,
Response,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

Make Stored Proc Faster

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@.ExtractDate DateTime,
@.DateFrom DateTime,
@.DateTo DateTime,
@.96hrPlusFlag int,
@.AppointmentsCount int OUTPUT

AS

IF @.96hrPlusFlag = 0

BEGIN
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @.DateFrom AND @.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @.96hrPlusFlag = 1

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime >@.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...You need to LEFT OUTER JOIN tbl_Appointment rather than use NOT IN on it. And in the WHERE clause filter in records where SurgerySlotKey IS NOT NULL.

Maintenance Plans and Delete files

We have been experiencing a "problem" with errors running Maintenance Plan
Jobs.
This is a Transaction Log and Full Backup Maintenance Plan.
Below is the code for the Full Backup Job step...............
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia DISK -BkUpDB
"L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
We are deleting backups over 36 hours old because we cannot trust the end
time of the previous backup. The SQLServer Agent Job shows a failure for the
MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I have
possibly traced it back to the "DELETE FILES" step of the MaintPlan. This
appears to fail with no error in the MaintPlan History.
The free drivespace is tight, there is slightly more room (~1gb) than 3
fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
needs at least enough room to create the new backup before it can delete the
oldest backup. The Backup succeeds, but the delete fails (leaves behind the
oldest backup, total 3 fulls, should be 2), the job fails, but the MaitPlan
doesn't and there is no record of a delete or any attempt to delete or an
error. My guess is that if a delete does not happen for any reason, a
MaintPlan history entry for the delete step is not made.
Any ideas..............
Frankm
You know you are in trouble when a
learning curve becomes a death spiralMake sure you don't have any of the Expire or Retain Dates set but here is
a pretty good overview of why this usually happens by Bill from MS:
-- Log files don't delete --
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> We have been experiencing a "problem" with errors running Maintenance Plan
> Jobs.
> This is a Transaction Log and Full Backup Maintenance Plan.
> Below is the code for the Full Backup Job step...............
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
DISK -BkUpDB
> "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> We are deleting backups over 36 hours old because we cannot trust the end
> time of the previous backup. The SQLServer Agent Job shows a failure for
the
> MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I have
> possibly traced it back to the "DELETE FILES" step of the MaintPlan. This
> appears to fail with no error in the MaintPlan History.
> The free drivespace is tight, there is slightly more room (~1gb) than 3
> fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
> needs at least enough room to create the new backup before it can delete
the
> oldest backup. The Backup succeeds, but the delete fails (leaves behind
the
> oldest backup, total 3 fulls, should be 2), the job fails, but the
MaitPlan
> doesn't and there is no record of a delete or any attempt to delete or an
> error. My guess is that if a delete does not happen for any reason, a
> MaintPlan history entry for the delete step is not made.
> Any ideas..............
> Frankm
> You know you are in trouble when a
> learning curve becomes a death spiral
>|||SQL Server service and SQL Server Agent both run under the same domain login
that has local admin rights on the machine. There are no problems logging in
with that login and doing anything, we do it everyday. The job owner is also
that account.
This does not happen all the time, only occasionally. The fact that the
DELETE FILES fails is one part. There is also the problem with the fact that
there is a job failure with a message pointing to the sqlmaint.exe, but
there is absolutely no indication of any failure at the Maintenance Plan.
Good point about another process holding the file(s). I will check to see if
another process has the file, but I am unclear as to why the Maintenance
Plan does not show an error.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> Make sure you don't have any of the Expire or Retain Dates set but here is
> a pretty good overview of why this usually happens by Bill from MS:
>
> -- Log files don't delete --
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer
to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to
consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it
would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed
to
> local drive), then you will need to modify the above (with respect to
where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > We have been experiencing a "problem" with errors running Maintenance
Plan
> > Jobs.
> > This is a Transaction Log and Full Backup Maintenance Plan.
> > Below is the code for the Full Backup Job step...............
> > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> DISK -BkUpDB
> > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> >
> > We are deleting backups over 36 hours old because we cannot trust the
end
> > time of the previous backup. The SQLServer Agent Job shows a failure for
> the
> > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
have
> > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
This
> > appears to fail with no error in the MaintPlan History.
> >
> > The free drivespace is tight, there is slightly more room (~1gb) than 3
> > fulls of the largest backup(~20gb each) (we are saving 2). The MaintPlan
> > needs at least enough room to create the new backup before it can delete
> the
> > oldest backup. The Backup succeeds, but the delete fails (leaves behind
> the
> > oldest backup, total 3 fulls, should be 2), the job fails, but the
> MaitPlan
> > doesn't and there is no record of a delete or any attempt to delete or
an
> > error. My guess is that if a delete does not happen for any reason, a
> > MaintPlan history entry for the delete step is not made.
> >
> > Any ideas..............
> >
> > Frankm
> > You know you are in trouble when a
> > learning curve becomes a death spiral
> >
> >
>|||The lack of traceability with the maintenance plan is one of the many
reasons I never recommend using it. If you created your own scheduled jobs
to do those tasks you would have a much better grasp on the situation when
things go wrong.
--
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> SQL Server service and SQL Server Agent both run under the same domain
login
> that has local admin rights on the machine. There are no problems logging
in
> with that login and doing anything, we do it everyday. The job owner is
also
> that account.
> This does not happen all the time, only occasionally. The fact that the
> DELETE FILES fails is one part. There is also the problem with the fact
that
> there is a job failure with a message pointing to the sqlmaint.exe, but
> there is absolutely no indication of any failure at the Maintenance Plan.
> Good point about another process holding the file(s). I will check to see
if
> another process has the file, but I am unclear as to why the Maintenance
> Plan does not show an error.
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > Make sure you don't have any of the Expire or Retain Dates set but here
is
> > a pretty good overview of why this usually happens by Bill from MS:
> >
> >
> > -- Log files don't delete --
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> >
> > This is likely to be either a permissions problem or a sharing violation
> > problem. The maintenance plan is run as a job, and jobs are run by the
> > SQLServerAgent service.
> >
> > Permissions:
> > 1. Determine the startup account for the SQLServerAgent service
> > (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
> This
> > account is the security context for jobs, and thus the maintenance plan.
> > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
domain
> > account) then skip step 3.
> > 3. On that box, log onto NT as that account. Using Explorer, attempt to
> > delete an expired backup. If that succeeds then go to Sharing Violation
> > section.
> > 4. Log onto NT with an account that is an administrator and use Explorer
> to
> > look at the Properties|Security of the folder (where the backups reside)
> > and ensure the SQLServerAgent startup account has Full Control. If the
> > SQLServerAgent startup account is LocalSystem, then the account to
> consider
> > is SYSTEM.
> > 5. In NT, if an account is a member of an NT group, and if that group
has
> > Access is Denied, then that account will have Access is Denied, even if
> > that account is also a member of the Administrators group. Thus you may
> > need to check group permissions (if the Startup Account is a member of a
> > group).
> > 6. Keep in mind that permissions (by default) are inherited from a
parent
> > folder. Thus, if the backups are stored in C:\bak, and if someone had
> > denied permission to the SQLServerAgent startup account for C:\, then
> > C:\bak will inherit access is denied.
> >
> > Sharing violation:
> > This is likely to be rooted in a timing issue, with the most likely
cause
> > being another scheduled process (such as NT Backup or Anti-Virus
software)
> > having the backup file open at the time when the SQLServerAgent (i.e.,
the
> > maintenance plan job) tried to delete it.
> > 1. Download filemon and handle from www.sysinternals.com.
> > 2. I am not sure whether filemon can be scheduled, or you might be able
to
> > use NT scheduling services to start filemon just before the maintenance
> > plan job is started, but the filemon log can become very large, so it
> would
> > be best to start it some short time before the maintenance plan starts.
> > 3. Inspect the filemon log for another process that has that backup file
> > open (if your lucky enough to have started filemon before this other
> > process grabs the backup folder), and inspect the log for the results
when
> > the SQLServerAgent agent attempts to open that same file.
> > 4. Schedule the job or that other process to do their work at different
> > times.
> > 5. You can use the handle utility if you are around at the time when the
> > job is scheduled to run.
> >
> > If the backup files are going to a \\share or a mapped drive (as opposed
> to
> > local drive), then you will need to modify the above (with respect to
> where
> > the tests and utilities are run).
> >
> > Finally, inspection of the maintenance plan's history report might be
> > useful.
> >
> > Thanks,
> >
> > Bill Hollinshead
> > Microsoft, SQL Server
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > We have been experiencing a "problem" with errors running Maintenance
> Plan
> > > Jobs.
> > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > Below is the code for the Full Backup Job step...............
> > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > DISK -BkUpDB
> > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > >
> > > We are deleting backups over 36 hours old because we cannot trust the
> end
> > > time of the previous backup. The SQLServer Agent Job shows a failure
for
> > the
> > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
> have
> > > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
> This
> > > appears to fail with no error in the MaintPlan History.
> > >
> > > The free drivespace is tight, there is slightly more room (~1gb) than
3
> > > fulls of the largest backup(~20gb each) (we are saving 2). The
MaintPlan
> > > needs at least enough room to create the new backup before it can
delete
> > the
> > > oldest backup. The Backup succeeds, but the delete fails (leaves
behind
> > the
> > > oldest backup, total 3 fulls, should be 2), the job fails, but the
> > MaitPlan
> > > doesn't and there is no record of a delete or any attempt to delete or
> an
> > > error. My guess is that if a delete does not happen for any reason, a
> > > MaintPlan history entry for the delete step is not made.
> > >
> > > Any ideas..............
> > >
> > > Frankm
> > > You know you are in trouble when a
> > > learning curve becomes a death spiral
> > >
> > >
> >
> >
>|||I am not sure if you were thinking of the MVP's as being part of the
Microsoft respondents but did want to make sure people understand we are not
actually employees of MS. We are all just volunteers that have been
acknowledged by MS in the MVP program. In either case I also want to point
out that there are lots of good answers from people other than MS or MVP's
that shouldn't go unrecognized either<g>.
Thanks
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> Yep, In previous jobs I did my own scripting, but in this job they use
> MaintPlans almost
> exclusively. I guess it's because all you have to do is clickity,
clickity,
> click.... and voila!
> Thanks, I now know that the lack of a error message is a feature, and
adjust
> accordingly..
> *****************************************
> To Microsoft respondents on these newsgroups... Your help is GREATLY
> appreciated and
> worth every penny. It really helps to be able to ask questions, get
answers
> and get on with my job.
> Thanks to all....................................
> *****************************************
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > The lack of traceability with the maintenance plan is one of the many
> > reasons I never recommend using it. If you created your own scheduled
> jobs
> > to do those tasks you would have a much better grasp on the situation
when
> > things go wrong.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > SQL Server service and SQL Server Agent both run under the same domain
> > login
> > > that has local admin rights on the machine. There are no problems
> logging
> > in
> > > with that login and doing anything, we do it everyday. The job owner
is
> > also
> > > that account.
> > > This does not happen all the time, only occasionally. The fact that
the
> > > DELETE FILES fails is one part. There is also the problem with the
fact
> > that
> > > there is a job failure with a message pointing to the sqlmaint.exe,
but
> > > there is absolutely no indication of any failure at the Maintenance
> Plan.
> > >
> > > Good point about another process holding the file(s). I will check to
> see
> > if
> > > another process has the file, but I am unclear as to why the
Maintenance
> > > Plan does not show an error.
> > >
> > >
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > Make sure you don't have any of the Expire or Retain Dates set but
> here
> > is
> > > > a pretty good overview of why this usually happens by Bill from MS:
> > > >
> > > >
> > > > -- Log files don't delete --
> > > >
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > >
> > > > This is likely to be either a permissions problem or a sharing
> violation
> > > > problem. The maintenance plan is run as a job, and jobs are run by
the
> > > > SQLServerAgent service.
> > > >
> > > > Permissions:
> > > > 1. Determine the startup account for the SQLServerAgent service
> > > > (Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup).
> > > This
> > > > account is the security context for jobs, and thus the maintenance
> plan.
> > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
> > domain
> > > > account) then skip step 3.
> > > > 3. On that box, log onto NT as that account. Using Explorer, attempt
> to
> > > > delete an expired backup. If that succeeds then go to Sharing
> Violation
> > > > section.
> > > > 4. Log onto NT with an account that is an administrator and use
> Explorer
> > > to
> > > > look at the Properties|Security of the folder (where the backups
> reside)
> > > > and ensure the SQLServerAgent startup account has Full Control. If
the
> > > > SQLServerAgent startup account is LocalSystem, then the account to
> > > consider
> > > > is SYSTEM.
> > > > 5. In NT, if an account is a member of an NT group, and if that
group
> > has
> > > > Access is Denied, then that account will have Access is Denied, even
> if
> > > > that account is also a member of the Administrators group. Thus you
> may
> > > > need to check group permissions (if the Startup Account is a member
of
> a
> > > > group).
> > > > 6. Keep in mind that permissions (by default) are inherited from a
> > parent
> > > > folder. Thus, if the backups are stored in C:\bak, and if someone
had
> > > > denied permission to the SQLServerAgent startup account for C:\,
then
> > > > C:\bak will inherit access is denied.
> > > >
> > > > Sharing violation:
> > > > This is likely to be rooted in a timing issue, with the most likely
> > cause
> > > > being another scheduled process (such as NT Backup or Anti-Virus
> > software)
> > > > having the backup file open at the time when the SQLServerAgent
(i.e.,
> > the
> > > > maintenance plan job) tried to delete it.
> > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > 2. I am not sure whether filemon can be scheduled, or you might be
> able
> > to
> > > > use NT scheduling services to start filemon just before the
> maintenance
> > > > plan job is started, but the filemon log can become very large, so
it
> > > would
> > > > be best to start it some short time before the maintenance plan
> starts.
> > > > 3. Inspect the filemon log for another process that has that backup
> file
> > > > open (if your lucky enough to have started filemon before this other
> > > > process grabs the backup folder), and inspect the log for the
results
> > when
> > > > the SQLServerAgent agent attempts to open that same file.
> > > > 4. Schedule the job or that other process to do their work at
> different
> > > > times.
> > > > 5. You can use the handle utility if you are around at the time when
> the
> > > > job is scheduled to run.
> > > >
> > > > If the backup files are going to a \\share or a mapped drive (as
> opposed
> > > to
> > > > local drive), then you will need to modify the above (with respect
to
> > > where
> > > > the tests and utilities are run).
> > > >
> > > > Finally, inspection of the maintenance plan's history report might
be
> > > > useful.
> > > >
> > > > Thanks,
> > > >
> > > > Bill Hollinshead
> > > > Microsoft, SQL Server
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > We have been experiencing a "problem" with errors running
> Maintenance
> > > Plan
> > > > > Jobs.
> > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > Below is the code for the Full Backup Job step...............
> > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > DISK -BkUpDB
> > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > >
> > > > > We are deleting backups over 36 hours old because we cannot trust
> the
> > > end
> > > > > time of the previous backup. The SQLServer Agent Job shows a
failure
> > for
> > > > the
> > > > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan.
I
> > > have
> > > > > possibly traced it back to the "DELETE FILES" step of the
MaintPlan.
> > > This
> > > > > appears to fail with no error in the MaintPlan History.
> > > > >
> > > > > The free drivespace is tight, there is slightly more room (~1gb)
> than
> > 3
> > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > MaintPlan
> > > > > needs at least enough room to create the new backup before it can
> > delete
> > > > the
> > > > > oldest backup. The Backup succeeds, but the delete fails (leaves
> > behind
> > > > the
> > > > > oldest backup, total 3 fulls, should be 2), the job fails, but the
> > > > MaitPlan
> > > > > doesn't and there is no record of a delete or any attempt to
delete
> or
> > > an
> > > > > error. My guess is that if a delete does not happen for any
reason,
> a
> > > > > MaintPlan history entry for the delete step is not made.
> > > > >
> > > > > Any ideas..............
> > > > >
> > > > > Frankm
> > > > > You know you are in trouble when a
> > > > > learning curve becomes a death spiral
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I didn't mean to slight anyone. I was unaware of the status and
affiliation of people responding to questions.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eWMgYZTXDHA.2328@.TK2MSFTNGP12.phx.gbl...
> I am not sure if you were thinking of the MVP's as being part of the
> Microsoft respondents but did want to make sure people understand we are
not
> actually employees of MS. We are all just volunteers that have been
> acknowledged by MS in the MVP program. In either case I also want to
point
> out that there are lots of good answers from people other than MS or MVP's
> that shouldn't go unrecognized either<g>.
> Thanks
>
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > Yep, In previous jobs I did my own scripting, but in this job they use
> > MaintPlans almost
> > exclusively. I guess it's because all you have to do is clickity,
> clickity,
> > click.... and voila!
> >
> > Thanks, I now know that the lack of a error message is a feature, and
> adjust
> > accordingly..
> >
> > *****************************************
> > To Microsoft respondents on these newsgroups... Your help is GREATLY
> > appreciated and
> > worth every penny. It really helps to be able to ask questions, get
> answers
> > and get on with my job.
> > Thanks to all....................................
> > *****************************************
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > > The lack of traceability with the maintenance plan is one of the many
> > > reasons I never recommend using it. If you created your own
scheduled
> > jobs
> > > to do those tasks you would have a much better grasp on the situation
> when
> > > things go wrong.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > SQL Server service and SQL Server Agent both run under the same
domain
> > > login
> > > > that has local admin rights on the machine. There are no problems
> > logging
> > > in
> > > > with that login and doing anything, we do it everyday. The job owner
> is
> > > also
> > > > that account.
> > > > This does not happen all the time, only occasionally. The fact that
> the
> > > > DELETE FILES fails is one part. There is also the problem with the
> fact
> > > that
> > > > there is a job failure with a message pointing to the sqlmaint.exe,
> but
> > > > there is absolutely no indication of any failure at the Maintenance
> > Plan.
> > > >
> > > > Good point about another process holding the file(s). I will check
to
> > see
> > > if
> > > > another process has the file, but I am unclear as to why the
> Maintenance
> > > > Plan does not show an error.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > > Make sure you don't have any of the Expire or Retain Dates set but
> > here
> > > is
> > > > > a pretty good overview of why this usually happens by Bill from
MS:
> > > > >
> > > > >
> > > > > -- Log files don't delete --
> > > > >
> > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > > >
> > > > > This is likely to be either a permissions problem or a sharing
> > violation
> > > > > problem. The maintenance plan is run as a job, and jobs are run by
> the
> > > > > SQLServerAgent service.
> > > > >
> > > > > Permissions:
> > > > > 1. Determine the startup account for the SQLServerAgent service
> > > > > (Start|Programs|Administrative
> tools|Services|SQLServerAgent|Startup).
> > > > This
> > > > > account is the security context for jobs, and thus the maintenance
> > plan.
> > > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
> > > domain
> > > > > account) then skip step 3.
> > > > > 3. On that box, log onto NT as that account. Using Explorer,
attempt
> > to
> > > > > delete an expired backup. If that succeeds then go to Sharing
> > Violation
> > > > > section.
> > > > > 4. Log onto NT with an account that is an administrator and use
> > Explorer
> > > > to
> > > > > look at the Properties|Security of the folder (where the backups
> > reside)
> > > > > and ensure the SQLServerAgent startup account has Full Control. If
> the
> > > > > SQLServerAgent startup account is LocalSystem, then the account to
> > > > consider
> > > > > is SYSTEM.
> > > > > 5. In NT, if an account is a member of an NT group, and if that
> group
> > > has
> > > > > Access is Denied, then that account will have Access is Denied,
even
> > if
> > > > > that account is also a member of the Administrators group. Thus
you
> > may
> > > > > need to check group permissions (if the Startup Account is a
member
> of
> > a
> > > > > group).
> > > > > 6. Keep in mind that permissions (by default) are inherited from a
> > > parent
> > > > > folder. Thus, if the backups are stored in C:\bak, and if someone
> had
> > > > > denied permission to the SQLServerAgent startup account for C:\,
> then
> > > > > C:\bak will inherit access is denied.
> > > > >
> > > > > Sharing violation:
> > > > > This is likely to be rooted in a timing issue, with the most
likely
> > > cause
> > > > > being another scheduled process (such as NT Backup or Anti-Virus
> > > software)
> > > > > having the backup file open at the time when the SQLServerAgent
> (i.e.,
> > > the
> > > > > maintenance plan job) tried to delete it.
> > > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > > 2. I am not sure whether filemon can be scheduled, or you might be
> > able
> > > to
> > > > > use NT scheduling services to start filemon just before the
> > maintenance
> > > > > plan job is started, but the filemon log can become very large, so
> it
> > > > would
> > > > > be best to start it some short time before the maintenance plan
> > starts.
> > > > > 3. Inspect the filemon log for another process that has that
backup
> > file
> > > > > open (if your lucky enough to have started filemon before this
other
> > > > > process grabs the backup folder), and inspect the log for the
> results
> > > when
> > > > > the SQLServerAgent agent attempts to open that same file.
> > > > > 4. Schedule the job or that other process to do their work at
> > different
> > > > > times.
> > > > > 5. You can use the handle utility if you are around at the time
when
> > the
> > > > > job is scheduled to run.
> > > > >
> > > > > If the backup files are going to a \\share or a mapped drive (as
> > opposed
> > > > to
> > > > > local drive), then you will need to modify the above (with respect
> to
> > > > where
> > > > > the tests and utilities are run).
> > > > >
> > > > > Finally, inspection of the maintenance plan's history report might
> be
> > > > > useful.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Bill Hollinshead
> > > > > Microsoft, SQL Server
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > > We have been experiencing a "problem" with errors running
> > Maintenance
> > > > Plan
> > > > > > Jobs.
> > > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > > Below is the code for the Full Backup Job
step...............
> > > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > > DISK -BkUpDB
> > > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > > >
> > > > > > We are deleting backups over 36 hours old because we cannot
trust
> > the
> > > > end
> > > > > > time of the previous backup. The SQLServer Agent Job shows a
> failure
> > > for
> > > > > the
> > > > > > MaintPlan (sqlmaint.exe) but there is no failure in the
MaintPlan.
> I
> > > > have
> > > > > > possibly traced it back to the "DELETE FILES" step of the
> MaintPlan.
> > > > This
> > > > > > appears to fail with no error in the MaintPlan History.
> > > > > >
> > > > > > The free drivespace is tight, there is slightly more room (~1gb)
> > than
> > > 3
> > > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > > MaintPlan
> > > > > > needs at least enough room to create the new backup before it
can
> > > delete
> > > > > the
> > > > > > oldest backup. The Backup succeeds, but the delete fails (leaves
> > > behind
> > > > > the
> > > > > > oldest backup, total 3 fulls, should be 2), the job fails, but
the
> > > > > MaitPlan
> > > > > > doesn't and there is no record of a delete or any attempt to
> delete
> > or
> > > > an
> > > > > > error. My guess is that if a delete does not happen for any
> reason,
> > a
> > > > > > MaintPlan history entry for the delete step is not made.
> > > > > >
> > > > > > Any ideas..............
> > > > > >
> > > > > > Frankm
> > > > > > You know you are in trouble when a
> > > > > > learning curve becomes a death spiral
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||We realize most people aren't aware of the situations here in the newsgroups
until they have been around for a while so I just wanted to bring that to
light for everyone's knowledge. Keep up the good questions<g>
Andrew J. Kelly
SQL Server MVP
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:ekwyz1aXDHA.1748@.TK2MSFTNGP12.phx.gbl...
> I didn't mean to slight anyone. I was unaware of the status and
> affiliation of people responding to questions.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eWMgYZTXDHA.2328@.TK2MSFTNGP12.phx.gbl...
> > I am not sure if you were thinking of the MVP's as being part of the
> > Microsoft respondents but did want to make sure people understand we are
> not
> > actually employees of MS. We are all just volunteers that have been
> > acknowledged by MS in the MVP program. In either case I also want to
> point
> > out that there are lots of good answers from people other than MS or
MVP's
> > that shouldn't go unrecognized either<g>.
> >
> > Thanks
> >
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > news:Ouu6hcPXDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > > Yep, In previous jobs I did my own scripting, but in this job they use
> > > MaintPlans almost
> > > exclusively. I guess it's because all you have to do is clickity,
> > clickity,
> > > click.... and voila!
> > >
> > > Thanks, I now know that the lack of a error message is a feature, and
> > adjust
> > > accordingly..
> > >
> > > *****************************************
> > > To Microsoft respondents on these newsgroups... Your help is GREATLY
> > > appreciated and
> > > worth every penny. It really helps to be able to ask questions, get
> > answers
> > > and get on with my job.
> > > Thanks to all....................................
> > > *****************************************
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:OaWxR4OXDHA.2524@.TK2MSFTNGP09.phx.gbl...
> > > > The lack of traceability with the maintenance plan is one of the
many
> > > > reasons I never recommend using it. If you created your own
> scheduled
> > > jobs
> > > > to do those tasks you would have a much better grasp on the
situation
> > when
> > > > things go wrong.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > news:O4crQvOXDHA.2592@.TK2MSFTNGP09.phx.gbl...
> > > > > SQL Server service and SQL Server Agent both run under the same
> domain
> > > > login
> > > > > that has local admin rights on the machine. There are no problems
> > > logging
> > > > in
> > > > > with that login and doing anything, we do it everyday. The job
owner
> > is
> > > > also
> > > > > that account.
> > > > > This does not happen all the time, only occasionally. The fact
that
> > the
> > > > > DELETE FILES fails is one part. There is also the problem with the
> > fact
> > > > that
> > > > > there is a job failure with a message pointing to the
sqlmaint.exe,
> > but
> > > > > there is absolutely no indication of any failure at the
Maintenance
> > > Plan.
> > > > >
> > > > > Good point about another process holding the file(s). I will check
> to
> > > see
> > > > if
> > > > > another process has the file, but I am unclear as to why the
> > Maintenance
> > > > > Plan does not show an error.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > > news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
> > > > > > Make sure you don't have any of the Expire or Retain Dates set
but
> > > here
> > > > is
> > > > > > a pretty good overview of why this usually happens by Bill from
> MS:
> > > > > >
> > > > > >
> > > > > > -- Log files don't delete --
> > > > > >
> > > > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
> > > > > >
> > > > > > This is likely to be either a permissions problem or a sharing
> > > violation
> > > > > > problem. The maintenance plan is run as a job, and jobs are run
by
> > the
> > > > > > SQLServerAgent service.
> > > > > >
> > > > > > Permissions:
> > > > > > 1. Determine the startup account for the SQLServerAgent service
> > > > > > (Start|Programs|Administrative
> > tools|Services|SQLServerAgent|Startup).
> > > > > This
> > > > > > account is the security context for jobs, and thus the
maintenance
> > > plan.
> > > > > > 2. If SQLServerAgent is started using LocalSystem (as opposed to
a
> > > > domain
> > > > > > account) then skip step 3.
> > > > > > 3. On that box, log onto NT as that account. Using Explorer,
> attempt
> > > to
> > > > > > delete an expired backup. If that succeeds then go to Sharing
> > > Violation
> > > > > > section.
> > > > > > 4. Log onto NT with an account that is an administrator and use
> > > Explorer
> > > > > to
> > > > > > look at the Properties|Security of the folder (where the backups
> > > reside)
> > > > > > and ensure the SQLServerAgent startup account has Full Control.
If
> > the
> > > > > > SQLServerAgent startup account is LocalSystem, then the account
to
> > > > > consider
> > > > > > is SYSTEM.
> > > > > > 5. In NT, if an account is a member of an NT group, and if that
> > group
> > > > has
> > > > > > Access is Denied, then that account will have Access is Denied,
> even
> > > if
> > > > > > that account is also a member of the Administrators group. Thus
> you
> > > may
> > > > > > need to check group permissions (if the Startup Account is a
> member
> > of
> > > a
> > > > > > group).
> > > > > > 6. Keep in mind that permissions (by default) are inherited from
a
> > > > parent
> > > > > > folder. Thus, if the backups are stored in C:\bak, and if
someone
> > had
> > > > > > denied permission to the SQLServerAgent startup account for C:\,
> > then
> > > > > > C:\bak will inherit access is denied.
> > > > > >
> > > > > > Sharing violation:
> > > > > > This is likely to be rooted in a timing issue, with the most
> likely
> > > > cause
> > > > > > being another scheduled process (such as NT Backup or Anti-Virus
> > > > software)
> > > > > > having the backup file open at the time when the SQLServerAgent
> > (i.e.,
> > > > the
> > > > > > maintenance plan job) tried to delete it.
> > > > > > 1. Download filemon and handle from www.sysinternals.com.
> > > > > > 2. I am not sure whether filemon can be scheduled, or you might
be
> > > able
> > > > to
> > > > > > use NT scheduling services to start filemon just before the
> > > maintenance
> > > > > > plan job is started, but the filemon log can become very large,
so
> > it
> > > > > would
> > > > > > be best to start it some short time before the maintenance plan
> > > starts.
> > > > > > 3. Inspect the filemon log for another process that has that
> backup
> > > file
> > > > > > open (if your lucky enough to have started filemon before this
> other
> > > > > > process grabs the backup folder), and inspect the log for the
> > results
> > > > when
> > > > > > the SQLServerAgent agent attempts to open that same file.
> > > > > > 4. Schedule the job or that other process to do their work at
> > > different
> > > > > > times.
> > > > > > 5. You can use the handle utility if you are around at the time
> when
> > > the
> > > > > > job is scheduled to run.
> > > > > >
> > > > > > If the backup files are going to a \\share or a mapped drive (as
> > > opposed
> > > > > to
> > > > > > local drive), then you will need to modify the above (with
respect
> > to
> > > > > where
> > > > > > the tests and utilities are run).
> > > > > >
> > > > > > Finally, inspection of the maintenance plan's history report
might
> > be
> > > > > > useful.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Bill Hollinshead
> > > > > > Microsoft, SQL Server
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Andrew J. Kelly
> > > > > > SQL Server MVP
> > > > > >
> > > > > >
> > > > > > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> > > > > > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
> > > > > > > We have been experiencing a "problem" with errors running
> > > Maintenance
> > > > > Plan
> > > > > > > Jobs.
> > > > > > > This is a Transaction Log and Full Backup Maintenance Plan.
> > > > > > > Below is the code for the Full Backup Job
> step...............
> > > > > > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
> > > > > > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
> > > > > > DISK -BkUpDB
> > > > > > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
> > > > > > >
> > > > > > > We are deleting backups over 36 hours old because we cannot
> trust
> > > the
> > > > > end
> > > > > > > time of the previous backup. The SQLServer Agent Job shows a
> > failure
> > > > for
> > > > > > the
> > > > > > > MaintPlan (sqlmaint.exe) but there is no failure in the
> MaintPlan.
> > I
> > > > > have
> > > > > > > possibly traced it back to the "DELETE FILES" step of the
> > MaintPlan.
> > > > > This
> > > > > > > appears to fail with no error in the MaintPlan History.
> > > > > > >
> > > > > > > The free drivespace is tight, there is slightly more room
(~1gb)
> > > than
> > > > 3
> > > > > > > fulls of the largest backup(~20gb each) (we are saving 2). The
> > > > MaintPlan
> > > > > > > needs at least enough room to create the new backup before it
> can
> > > > delete
> > > > > > the
> > > > > > > oldest backup. The Backup succeeds, but the delete fails
(leaves
> > > > behind
> > > > > > the
> > > > > > > oldest backup, total 3 fulls, should be 2), the job fails, but
> the
> > > > > > MaitPlan
> > > > > > > doesn't and there is no record of a delete or any attempt to
> > delete
> > > or
> > > > > an
> > > > > > > error. My guess is that if a delete does not happen for any
> > reason,
> > > a
> > > > > > > MaintPlan history entry for the delete step is not made.
> > > > > > >
> > > > > > > Any ideas..............
> > > > > > >
> > > > > > > Frankm
> > > > > > > You know you are in trouble when a
> > > > > > > learning curve becomes a death spiral
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Hi Frank,
The idea about maintenance plans a great idea for a future product
enhancement. I'd recommend that you forward the recommendation to the
Microsoft Wish Program:
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed later in this article.
Let us know how we can improve our products.
Product Enhancement suggestions can include:
Improvements on existing products.
Suggestions for additional features.
Ways to make products easier to use.
All product enhancement suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
In Internet Explorer 6, click Send Feedback on the Help menu and then click
the link in the Product Suggestion section of the page that appears.
In Windows XP, click Help and Support on the Start menu. Click Send your
feedback to Microsoft, and then fill out the Product Suggestion page that
appears.
Visit the following Microsoft Web site:
http://www.microsoft.com/ms.htm
Click Microsoft.com Guide in the upper-right corner of the page and then
click Contact Us . Click the link in the Product Suggestion section of the
page that appears.
Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.
E-mail - To send comments or suggestions via e-mail, use the following
Microsoft Wish Program e-mail address, mswish@.microsoft.com.
FAX - To send comments or suggestions via FAX, use the following Microsoft
FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.
US Mail - To send comments or suggestions via US Mail, use the following
Microsoft mailing address:
Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team,
classified for easy access, and routed to the product or service team to
drive Microsoft product and/or service improvements. Because we receive an
abundance of suggestions (over 69,000 suggestions a year!) we can't
guarantee that each request makes it into a final product or service. But
we can tell you that each suggestion has been received and is being
reviewed by the team that is most capable of addressing it.
All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
Sincerely,
Alick Ye, MCSD
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
| From: "frankm" <frank@.nxspxm.mallardcentral.com>
| References: <#NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl> <eKPZ7PHXDHA.656
|
| SQL Server service and SQL Server Agent both run under the same domain
login
| that has local admin rights on the machine. There are no problems logging
in
| with that login and doing anything, we do it everyday. The job owner is
also
| that account.
| This does not happen all the time, only occasionally. The fact that the
| DELETE FILES fails is one part. There is also the problem with the fact
that
| there is a job failure with a message pointing to the sqlmaint.exe, but
| there is absolutely no indication of any failure at the Maintenance Plan.
|
| Good point about another process holding the file(s). I will check to see
if
| another process has the file, but I am unclear as to why the Maintenance
| Plan does not show an error.
|
|
|
|
|
| "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
| news:eKPZ7PHXDHA.656@.tk2msftngp13.phx.gbl...
| > Make sure you don't have any of the Expire or Retain Dates set but here
is
| > a pretty good overview of why this usually happens by Bill from MS:
| >
| >
| > -- Log files don't delete --
| >
| > http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
| >
| > This is likely to be either a permissions problem or a sharing violation
| > problem. The maintenance plan is run as a job, and jobs are run by the
| > SQLServerAgent service.
| >
| > Permissions:
| > 1. Determine the startup account for the SQLServerAgent service
| > (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup).
| This
| > account is the security context for jobs, and thus the maintenance plan.
| > 2. If SQLServerAgent is started using LocalSystem (as opposed to a
domain
| > account) then skip step 3.
| > 3. On that box, log onto NT as that account. Using Explorer, attempt to
| > delete an expired backup. If that succeeds then go to Sharing Violation
| > section.
| > 4. Log onto NT with an account that is an administrator and use Explorer
| to
| > look at the Properties|Security of the folder (where the backups reside)
| > and ensure the SQLServerAgent startup account has Full Control. If the
| > SQLServerAgent startup account is LocalSystem, then the account to
| consider
| > is SYSTEM.
| > 5. In NT, if an account is a member of an NT group, and if that group
has
| > Access is Denied, then that account will have Access is Denied, even if
| > that account is also a member of the Administrators group. Thus you may
| > need to check group permissions (if the Startup Account is a member of a
| > group).
| > 6. Keep in mind that permissions (by default) are inherited from a
parent
| > folder. Thus, if the backups are stored in C:\bak, and if someone had
| > denied permission to the SQLServerAgent startup account for C:\, then
| > C:\bak will inherit access is denied.
| >
| > Sharing violation:
| > This is likely to be rooted in a timing issue, with the most likely
cause
| > being another scheduled process (such as NT Backup or Anti-Virus
software)
| > having the backup file open at the time when the SQLServerAgent (i.e.,
the
| > maintenance plan job) tried to delete it.
| > 1. Download filemon and handle from www.sysinternals.com.
| > 2. I am not sure whether filemon can be scheduled, or you might be able
to
| > use NT scheduling services to start filemon just before the maintenance
| > plan job is started, but the filemon log can become very large, so it
| would
| > be best to start it some short time before the maintenance plan starts.
| > 3. Inspect the filemon log for another process that has that backup file
| > open (if your lucky enough to have started filemon before this other
| > process grabs the backup folder), and inspect the log for the results
when
| > the SQLServerAgent agent attempts to open that same file.
| > 4. Schedule the job or that other process to do their work at different
| > times.
| > 5. You can use the handle utility if you are around at the time when the
| > job is scheduled to run.
| >
| > If the backup files are going to a \\share or a mapped drive (as opposed
| to
| > local drive), then you will need to modify the above (with respect to
| where
| > the tests and utilities are run).
| >
| > Finally, inspection of the maintenance plan's history report might be
| > useful.
| >
| > Thanks,
| >
| > Bill Hollinshead
| > Microsoft, SQL Server
| >
| > --
| >
| > Andrew J. Kelly
| > SQL Server MVP
| >
| >
| > "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
| > news:%23NJsmbGXDHA.2344@.TK2MSFTNGP09.phx.gbl...
| > > We have been experiencing a "problem" with errors running Maintenance
| Plan
| > > Jobs.
| > > This is a Transaction Log and Full Backup Maintenance Plan.
| > > Below is the code for the Full Backup Job step...............
| > > EXECUTE master.dbo.xp_sqlmaint N'-PlanID
| > > 789ADDEA-00CD-4956-8CF2-F1BAAA00373C -WriteHistory -BkUpMedia
| > DISK -BkUpDB
| > > "L:\Backup" -DelBkUps 36HOURS -BkExt "BAK"'
| > >
| > > We are deleting backups over 36 hours old because we cannot trust the
| end
| > > time of the previous backup. The SQLServer Agent Job shows a failure
for
| > the
| > > MaintPlan (sqlmaint.exe) but there is no failure in the MaintPlan. I
| have
| > > possibly traced it back to the "DELETE FILES" step of the MaintPlan.
| This
| > > appears to fail with no error in the MaintPlan History.
| > >
| > > The free drivespace is tight, there is slightly more room (~1gb) than
3
| > > fulls of the largest backup(~20gb each) (we are saving 2). The
MaintPlan
| > > needs at least enough room to create the new backup before it can
delete
| > the
| > > oldest backup. The Backup succeeds, but the delete fails (leaves
behind
| > the
| > > oldest backup, total 3 fulls, should be 2), the job fails, but the
| > MaitPlan
| > > doesn't and there is no record of a delete or any attempt to delete or
| an
| > > error. My guess is that if a delete does not happen for any reason, a
| > > MaintPlan history entry for the delete step is not made.
| > >
| > > Any ideas..............
| > >
| > > Frankm
| > > You know you are in trouble when a
| > > learning curve becomes a death spiral
| > >
| > >
| >
| >
|
|
|