Backup to Network Drive: plan

Showing posts with label plan. Show all posts

Tuesday, March 20, 2012

Bakup dosen't delete files

When using SQLserver to take backup throw a maintance plan
i've selected to delete files older that 2 day's this
dosent hapen. Why?Thanks that solved the problem!!!
>--Original Message--
>Deleting th files is the last thing the plan does so if
your backup fails it
>won't delete the files.
>Here is a very good summary of the possible issues with
that from Bill at
>MS:
>
>-- Log files don't delete --
>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.
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Fredrik" <fredrik.ehrenholm@.proserva.com> wrote in
message
>news:073801c36569$dcbb26d0$a301280a@.phx.gbl...
>> When using SQLserver to take backup throw a maintance
plan
>> i've selected to delete files older that 2 day's this
>> dosent hapen. Why?
>
>.
>

Monday, March 19, 2012

bad query plan when using parameterised queries on partitioned vie

Recently I found rather a nasty consequence by using parameterised queries o
n
a partitioned view.
I have a .net app that sends parameterised queries to SQL Server 2000. SQL
Server obliges by caching the query plan to increase efficiency of subsequen
t
queries.
In my case the query is running against a partitioned view, partitioned by a
date constraint.
A query is run when the cache is cleared and the query optimiser correctly
creates a query plan specifying only the correct partition and ignoring the
other partitions, so far so good…
Subsequent queries run fine and use the cached query plan until we get a
query where the date range selected now relates to a different underlying
partition – blam, full table scan against all partitions! As you might gue
ss
the query execution times blow out by several orders of magnitude! It seems
it tries to use a bad query plan from the cache and when it realises it can
t
get the data it needs it is not smart enough to re-evaluate the query plan
properly.
Currently I am forcing a recompile on these guys go make sure the cached
plan is not used as it obviously cannot be trusted! This workaround does the
trick but I’m wondering if anyone has experienced something similar before
?
Or is there a better way to deal with this? It seems to me to be in fact a
bug as the optimiser should handle this a lot better than it does.
Thoughts anyone?
Cheers,
Chrissounds like parameter sniffing problem. Sure there are ways better than
WITH RECOMPILE. Search for "parameter sniffing" in this group or google.

bad query executionplan

Hallo!
I'm having a problem with bad query executionplans. The optimizer won't
get the right plan to execute the query fast.
The optimizer perform cluster index scan instead of using a index s

an also using a hash join instead of a loop join.
Now, when I set a join hint to use the loop join, the optimizer uses
the right index to.
Another methode i have found is to set forceplan to on. Then the
optimzer will also uses loop join and the correct indexes.
Anyone have an idea?
Server:
Language German
SQL 2000 SP3a and SP4
Windows 2003 SP1 and without
I already have reindexed all participating indexes (drop & create) an
drop an recreate all statistics.
Regards
LutzPlease post your DDL (including constraints and indexes) and problem query
or stored procedure so that we can help.
Hope this helps.
Dan Guzman
SQL Server MVP
<lutz.jahnke@.nord-com.net> wrote in message
news:1125053676.823090.8480@.g49g2000cwa.googlegroups.com...
> Hallo!
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s

> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
> Regards
> Lutz
>|||In addition to Dan's reply: please specify if you are using a stored
procedure with parameters (which can benefit from parameter sniffing) or
running an ad-hoc query or a stored procedure with variables. Do you get
a 'good' query plan if you use literals instead of variables/parameters?
Gert-Jan
lutz.jahnke@.nord-com.net wrote:
> Hallo!
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s

> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
> Server:
> Language German
> SQL 2000 SP3a and SP4
> Windows 2003 SP1 and without
> I already have reindexed all participating indexes (drop & create) an
> drop an recreate all statistics.
> Regards
> Lutz|||(lutz.jahnke@.nord-com.net) writes:
> I'm having a problem with bad query executionplans. The optimizer won't
> get the right plan to execute the query fast.
> The optimizer perform cluster index scan instead of using a index s

> an also using a hash join instead of a loop join.
> Now, when I set a join hint to use the loop join, the optimizer uses
> the right index to.
> Another methode i have found is to set forceplan to on. Then the
> optimzer will also uses loop join and the correct indexes.
> Anyone have an idea?
In additions to Dan's and Gert-Jan's suggestions, try running UPDATE
STATISTICS on the involved tables. Statistics may be out of date. SQL
Server updates statistics automatically, but there is a lag and if
your search condition includes a column with monotonically growing
values, the optimizer will make incorrect estimates.
For more heavy-duty, you can add WITH FULLSCAN to the UPDATE STATISTICS
statement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, March 11, 2012

Bad executed Plan and wrong Result by SQL

Hi, I didn't receive any answer about my problem. I'll repeat that!:
I have one query:
SELECT count(*) FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G WHERE G.PEG= 752074
AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
This query, after many updates after rebuid at saturday, return column
E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
merger join with hash aggregate, that too lazy. I test this at monday(one day
after rebuil), but the result is correct, but when that query are executed at
middle of week, change some G.PEG values, the result is total wrong.
Plese, anyone help me to this question!
Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan
> mount merger join with hash aggregate, that too lazy. I test this at
> monday(one day after rebuil), but the result is correct, but when
> that query are executed at middle of week, change some G.PEG values,
> the result is total wrong.
> Plese, anyone help me to this question!
Could be a parallel plan issue. When using COUNT(*) it can help to add a
MAXDOP 1 to the query to avoid the possible issue:
http://support.microsoft.com/kb/822746
http://support.microsoft.com/kb/q277738/
David Gugick
Imceda Software
www.imceda.com
|||In addition to David's reply: Service Pack 4 included a few fixes with
respect to parallelism, so installing SP4 could solve your problem.
Gert-Jan
Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
> merger join with hash aggregate, that too lazy. I test this at monday(one day
> after rebuil), but the result is correct, but when that query are executed at
> middle of week, change some G.PEG values, the result is total wrong.
> Plese, anyone help me to this question!

Bad executed Plan and wrong Result by SQL

I have one query that executes many times in a week.
I created one Maintenances plan that Rebuild all index in my Database that
has been executed at 23:40 Saturday until stop finished at Sunday.

However at middle of week (Wednesday or Thursday), that query dont return
result like that must be. The time exceeded and the result are total wrong.

I compare the normal executed plan and the crazy one that SQL create to
mount result.

The normal is nested with index seek (very fast, the wrong is Merger with
hash aggregate (very slow). After Index Rebuild, the executed plan bring
result that must be, but when the merge plan are executed with many updates
on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of week, the
result are total wrong, with many rows back.

I recommended Index Seek force by coalesce function on one column
aggregate, but everyone here were very panic with that behavior of SQL
Server.

Please , anyone help me to explain that!

Krisnamourt!

P.S: Attachments :

--Force Index Query with coalesce
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=coalesce(G.HANDLE,G.HANDLE) AND E.CLASSEGERENCIALPAGTO is NULL

--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL

--
Message posted via http://www.sqlmonster.comStmtText
-----------------------
-----------------------
------------
--Normal Query
SELECT count(*)
FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G
WHERE G.PEG=736740
AND E.GUIA=G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
option(merge join)

(1 row(s) affected)

StmtText
-----------------------
-----------------------
----------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM([partialagg1003])))
|--Parallelism(Gather Streams)
|--Merge Join(Inner Join, MERGE:([G].[HANDLE])=([E].[GUIA])
, RESIDUAL:([G].[HANDLE]=[E].[GUIA]))
|--Parallelism(Distribute Streams, PARTITION COLUMNS:
([G].[HANDLE]))
| |--Index Seek(OBJECT:([Saude].[dbo].[SAM_GUIA].
[AX_1603PEG] AS [G]), SEEK:([G].[PEG]=736740) ORDERED FORWARD)
|--Sort(ORDER BY:([E].[GUIA] ASC))
|--Hash Match(Aggregate, HASH:([E].[GUIA]),
RESIDUAL:([E].[GUIA]=[E].[GUIA]) DEFINE:([partialagg1003]=COUNT(*)))
|--Parallelism(Repartition Streams,
PARTITION COLUMNS:([E].[GUIA]))
|--Clustered Index Scan(OBJECT:([Saude]
..[dbo].[SAM_GUIA_EVENTOS].[PK__SAM_GUIA_EVENTOS__68736660] AS [E]), WHERE:(
[E].[CLASSEGERENCIALPAGTO]=NULL))

(10 row(s) affected)

--
Message posted via http://www.sqlmonster.com|||I Mean...the wrong result bring back many row with E.CLASSEGERENCIALPAGTO
not null(this column shows many data )...CRAZY!!!

Anyone help me to explain that!!

Kris

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I have one query that executes many times in a week.
> I created one Maintenances plan that Rebuild all index in my Database that
> has been executed at 23:40 Saturday until stop finished at Sunday.
> However at middle of week (Wednesday or Thursday), that query don't
> return result like that must be. The time exceeded and the result are
> total wrong.
> I compare the normal executed plan and the "crazy" one that SQL create to
> mount result.
> The normal is nested with index seek (very fast, the wrong is Merger
> with hash aggregate (very slow). After Index Rebuild, the executed plan
> bring result that must be, but when the merge plan are executed with
> many updates on that tables (SAM_GUIA_EVENTO and SAM_GUIA), at middle of
> week, the result are total wrong, with many rows back.
> I recommended Index Seek force by coalesce function on one column
> aggregate, but everyone here were very panic with that behavior of SQL
> Server.

Do I understand you clarifiation in the other article correctly, that
when you say "results are total wrong", you do in fact mean the query
plan? If you really get incorrect resuls from the query, this is a
serious bug, and you should definitely open a case with Microsoft to
have it investigate.

If the problem is "only" the incorrect query plan, and the slow execution
time, this is more "normal" behaviour.

Recall that SQL Server uses a cost-based optimizer that estimates the
cost of various query plans from statistics about the data. A small
error in the estimate can have serious consequences.

Since you have good performance after index rebuild, it might be a good
idea to schedule index rebuild on these two tables daily.

I also notice that the bad plan involves parallelism. If you add
OPTION (MAXDOP 1), you tell SQL Server not to use parallelism. This
is often enough to get a good plan.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The real problem is incorret result. I cant rebuild index on these two
table , because our scenario works 24 hours by day. These table are too big
(17 Gbytes one and 4 Gbytes other), with many Index. The Index Rebuild only
can do at weekends. I intend to eliminated some Index that are redundant(I
just begun), but that bug is very crazy. That became SQL Server not a good
solution for OLTP that grows up strongly. I saw many scenarios like
that...bad performance when the Database became too large.

--
Message posted via http://www.sqlmonster.com|||Krisnamourt Correia via SQLMonster.com (forum@.SQLMonster.com) writes:
> The real problem is incorret result. I cant rebuild index on these two
> table , because our scenario works 24 hours by day. These table are too
> big (17 Gbytes one and 4 Gbytes other), with many Index. The Index
> Rebuild only can do at weekends. I intend to eliminated some Index that
> are redundant(I just begun), but that bug is very crazy. That became SQL
> Server not a good solution for OLTP that grows up strongly. I saw many
> scenarios like that...bad performance when the Database became too
> large.

Looking at your query, the incorrect results may be a known issue.
I think I recognize the type of query. I would suggest that you open
a case with Microsoft to investigate this.

If there is a fix, it is likely to be available in SP4 which was recently.
Unfortunate there is an issue which concerns AWE which I would expect to
concern you, given your table sizes. I would expect Microsoft to have a fix
for this issue soon, though. See further
http://www.microsoft.com/sql/downloads/2000/sp4.asp.

Note that SP4 is only likely to address the incorrect result. The query
plan and the fragmentation is less likely to improve.

Some questions:
o Do you have autostats enabled on these tables? (Maybe you should turn
them off)
o What actual fragmentation do you have by the middle of the week?

If the fragmentation increases rapidly, maybe you should look at changing
the clustered index to one that is less prone to fragmentation given
the update pattern. But here is of course a tradeoff with queries
that may depend on the clustered index.

Could you post the CREATE TABLE and CREATE INDEX statements for the
two tables?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Bad executed Plan and wrong Result by SQL

Hi, I didn't receive any answer about my problem. I'll repeat that!:
I have one query:
SELECT count(*) FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G WHERE G.PEG= 752074
AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
This query, after many updates after rebuid at saturday, return column
E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
merger join with hash aggregate, that too lazy. I test this at monday(one da
y
after rebuil), but the result is correct, but when that query are executed a
t
middle of week, change some G.PEG values, the result is total wrong.
Plese, anyone help me to this question!Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan
> mount merger join with hash aggregate, that too lazy. I test this at
> monday(one day after rebuil), but the result is correct, but when
> that query are executed at middle of week, change some G.PEG values,
> the result is total wrong.
> Plese, anyone help me to this question!
Could be a parallel plan issue. When using COUNT(*) it can help to add a
MAXDOP 1 to the query to avoid the possible issue:
http://support.microsoft.com/kb/822746
http://support.microsoft.com/kb/q277738/
David Gugick
Imceda Software
www.imceda.com|||In addition to David's reply: Service Pack 4 included a few fixes with
respect to parallelism, so installing SP4 could solve your problem.
Gert-Jan
Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
> merger join with hash aggregate, that too lazy. I test this at monday(one
day
> after rebuil), but the result is correct, but when that query are executed
at
> middle of week, change some G.PEG values, the result is total wrong.
> Plese, anyone help me to this question!

Bad executed Plan and wrong Result by SQL

Hi, I didn't receive any answer about my problem. I'll repeat that!:
I have one query:
SELECT count(*) FROM SAM_GUIA_EVENTOS E,
SAM_GUIA G WHERE G.PEG= 752074
AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
This query, after many updates after rebuid at saturday, return column
E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
merger join with hash aggregate, that too lazy. I test this at monday(one day
after rebuil), but the result is correct, but when that query are executed at
middle of week, change some G.PEG values, the result is total wrong.
Plese, anyone help me to this question!Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan
> mount merger join with hash aggregate, that too lazy. I test this at
> monday(one day after rebuil), but the result is correct, but when
> that query are executed at middle of week, change some G.PEG values,
> the result is total wrong.
> Plese, anyone help me to this question!
Could be a parallel plan issue. When using COUNT(*) it can help to add a
MAXDOP 1 to the query to avoid the possible issue:
http://support.microsoft.com/kb/822746
http://support.microsoft.com/kb/q277738/
--
David Gugick
Imceda Software
www.imceda.com|||In addition to David's reply: Service Pack 4 included a few fixes with
respect to parallelism, so installing SP4 could solve your problem.
Gert-Jan
Krisnamourt wrote:
> Hi, I didn't receive any answer about my problem. I'll repeat that!:
> I have one query:
> SELECT count(*) FROM SAM_GUIA_EVENTOS E,
> SAM_GUIA G WHERE G.PEG= 752074
> AND E.GUIA= G.HANDLE AND E.CLASSEGERENCIALPAGTO is NULL
> This query, after many updates after rebuid at saturday, return column
> E.CLASSEGERENCIALPAGTO with not null(many data). The execution plan mount
> merger join with hash aggregate, that too lazy. I test this at monday(one day
> after rebuil), but the result is correct, but when that query are executed at
> middle of week, change some G.PEG values, the result is total wrong.
> Plese, anyone help me to this question!

Thursday, March 8, 2012

backups working but backup files are not being removed

Hello:
We have a SQL Server 2000 client who has a maintenance plan that does a full
backup of course of its databases. And, each database is using the Full
recovery model.
The backup of the databases itself is working perfectly. But, the backup
files (mdf files) are not being removed. You see, the maintenance plan
specifies that backup files are to be removed every 2 days. That's not
happening?
Why would that be? I mean, why would part of the maintenance plan (the
backing up of the databases) work but another part of the plan (the removal
of the database files) not work?
Thanks!
childofthe1980s
MDF files are actually the real data files. Maintenance plans will remove
only .BAK files pertaining to FULL database or Transaction log.
Thanks
Hari
"childofthe1980s" wrote:

> Hello:
> We have a SQL Server 2000 client who has a maintenance plan that does a full
> backup of course of its databases. And, each database is using the Full
> recovery model.
> The backup of the databases itself is working perfectly. But, the backup
> files (mdf files) are not being removed. You see, the maintenance plan
> specifies that backup files are to be removed every 2 days. That's not
> happening?
> Why would that be? I mean, why would part of the maintenance plan (the
> backing up of the databases) work but another part of the plan (the removal
> of the database files) not work?
> Thanks!
> childofthe1980s
|||Actually, that's what I meant. Sorry for the confusion.
Yes, it is "bak" files I'm concerned about.
Here's an update. I spoke with the client and had him add a group that
contains that SQL Server Agent startup account to have Full Control of the
MSSQL folder so that the root backup folder wold contain Full Control for
this group.
Now, the startup account already had Full Control anyway. But, if the group
that the startup account had "Access is Denied", then the startup account
would be denied permissions. I don't know how to tell if the group was being
denied access. But, that's what I have done so far to eliminate this issue.
Is there anything else?
childofthe1980s
"Hari Prasad" wrote:
[vbcol=seagreen]
> MDF files are actually the real data files. Maintenance plans will remove
> only .BAK files pertaining to FULL database or Transaction log.
> Thanks
> Hari
> "childofthe1980s" wrote:

backups working but backup files are not being removed

Hello:
We have a SQL Server 2000 client who has a maintenance plan that does a full
backup of course of its databases. And, each database is using the Full
recovery model.
The backup of the databases itself is working perfectly. But, the backup
files (mdf files) are not being removed. You see, the maintenance plan
specifies that backup files are to be removed every 2 days. That's not
happening?
Why would that be? I mean, why would part of the maintenance plan (the
backing up of the databases) work but another part of the plan (the removal
of the database files) not work?
Thanks!
childofthe1980sMDF files are actually the real data files. Maintenance plans will remove
only .BAK files pertaining to FULL database or Transaction log.
Thanks
Hari
"childofthe1980s" wrote:

> Hello:
> We have a SQL Server 2000 client who has a maintenance plan that does a fu
ll
> backup of course of its databases. And, each database is using the Full
> recovery model.
> The backup of the databases itself is working perfectly. But, the backup
> files (mdf files) are not being removed. You see, the maintenance plan
> specifies that backup files are to be removed every 2 days. That's not
> happening?
> Why would that be? I mean, why would part of the maintenance plan (the
> backing up of the databases) work but another part of the plan (the remova
l
> of the database files) not work?
> Thanks!
> childofthe1980s|||Actually, that's what I meant. Sorry for the confusion.
Yes, it is "bak" files I'm concerned about.
Here's an update. I spoke with the client and had him add a group that
contains that SQL Server Agent startup account to have Full Control of the
MSSQL folder so that the root backup folder wold contain Full Control for
this group.
Now, the startup account already had Full Control anyway. But, if the group
that the startup account had "Access is Denied", then the startup account
would be denied permissions. I don't know how to tell if the group was bein
g
denied access. But, that's what I have done so far to eliminate this issue.
Is there anything else?
childofthe1980s
"Hari Prasad" wrote:
[vbcol=seagreen]
> MDF files are actually the real data files. Maintenance plans will remove
> only .BAK files pertaining to FULL database or Transaction log.
> Thanks
> Hari
> "childofthe1980s" wrote:
>

Backups of tran log not working

I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.

Wednesday, March 7, 2012

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
Craig
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig
|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now jus
t
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan.
I
> have set it to delete files older than 1 day. This used to work, but now j
ust
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now i
t
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:

> Below KB might help:
> http://support.microsoft.com/defaul...sinternals.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 woul
d
> 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 t
o
> local drive), then you will need to modify the above (with respect to wher
e
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>
>

BackUps not deleting

I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
have set it to delete files older than 1 day. This used to work, but now just
backs up without deleting the old files.
Any help would be appreciated,
CraigBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
>I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> have set it to delete files older than 1 day. This used to work, but now just
> backs up without deleting the old files.
> Any help would be appreciated,
> Craig|||I also have the same problem. Our maintenance plan was working however now it
doesnt.
The Service Account has full control to the backup location and nothing is
running at the same time as the backup. Any ideas?
Andrew
"Tibor Karaszi" wrote:
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
> news:B509ECB9-DAE6-49CD-8CD4-4A9F4905DA23@.microsoft.com...
> >I am backing up databases on SQL Server 2000 using a Database BackUp Plan. I
> > have set it to delete files older than 1 day. This used to work, but now just
> > backs up without deleting the old files.
> >
> > Any help would be appreciated,
> > Craig
>
>

Backups files not deleted with maintenance plan

Hello All,
I have a SQL server maintenance plan setup with my SQLServer 2000 MMC.
The backup works perfectly fine, but it does not delete the backup files
scheduled to cleanup files older then a week. Any suggestions where to look
to sort this out?
The backups take up the disk space and I have to go in and manually deleted
backups for around 40 databases.
Thanks a lot.
Imran.
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
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
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> I have a SQL server maintenance plan setup with my SQLServer 2000 MMC. The backup works
> perfectly fine, but it does not delete the backup files scheduled to cleanup files older then a
> week. Any suggestions where to look to sort this out?
> The backups take up the disk space and I have to go in and manually deleted backups for around 40
> databases.
> Thanks a lot.
> Imran.
>
|||Thanks for the link and the details. It cannot be a permissions issue or a
sharing violation since I have the same issue on two servers, the
development server and the live server both don't delete backup files.
I ran the maintenance plan right now and there wasn't any other process
holding the backup files. but still the files were not deleted. The backup
log did not indicate any errors relating to deletion of files, rather it
does not even show the attempt to delete the older files.
Any clues ?
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
> Below KB might help:
> http://support.microsoft.com/default...&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> 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
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:OS$PCEq6FHA.3880@.TK2MSFTNGP12.phx.gbl...
>
|||Do you have any databases in simple recovery mode included in the plan? I've seen this for
logbackups. Log backup cannot be performed in simple recovery mode, so the maint plan never gets to
the part where to delete old log backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Imran Aziz" <imran@.tb2.net> wrote in message news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks for the link and the details. It cannot be a permissions issue or a sharing violation since
> I have the same issue on two servers, the development server and the live server both don't
> delete backup files.
> I ran the maintenance plan right now and there wasn't any other process holding the backup files.
> but still the files were not deleted. The backup log did not indicate any errors relating to
> deletion of files, rather it does not even show the attempt to delete the older files.
> Any clues ?
> Imran.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Osha9Qq6FHA.2384@.TK2MSFTNGP12.phx.gbl...
>
|||Thanks for the tip, No of databases were on simple recovery mode. After
changing the recovery mode to Bulk it has sorted out the problem on both
development and production machine. Thanks a lot.
Imran.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvSsIwr6FHA.1944@.TK2MSFTNGP14.phx.gbl...
> Do you have any databases in simple recovery mode included in the plan?
> I've seen this for logbackups. Log backup cannot be performed in simple
> recovery mode, so the maint plan never gets to the part where to delete
> old log backup files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Imran Aziz" <imran@.tb2.net> wrote in message
> news:uYQg4fr6FHA.1416@.TK2MSFTNGP09.phx.gbl...
>

Tuesday, March 20, 2012

Bakup dosen't delete files

Monday, March 19, 2012

bad query plan when using parameterised queries on partitioned vie

bad query executionplan

Sunday, March 11, 2012

Bad executed Plan and wrong Result by SQL

Bad executed Plan and wrong Result by SQL

Bad executed Plan and wrong Result by SQL

Bad executed Plan and wrong Result by SQL

Thursday, March 8, 2012

backups working but backup files are not being removed

backups working but backup files are not being removed

Backups of tran log not working

Backups of tran log not working

Backups of tran log not working

Wednesday, March 7, 2012

BackUps not deleting

BackUps not deleting

BackUps not deleting

Backups files not deleted with maintenance plan

Backup to Network Drive

Blog Archive

About Me