Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980s
On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Monday, March 19, 2012
another reason why backup files do not get deleted
Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormall
y
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "con
tinue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report,
I now see that they are
considering it for sp2, so it might be worth checking if they implemented su
ch an option...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007...se-backups.html
http://realsqlguy.blogspot.com/2007...-old-files.html
http://realsqlguy.blogspot.com/2007...gmentation.html
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormall
y
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "con
tinue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report,
I now see that they are
considering it for sp2, so it might be worth checking if they implemented su
ch an option...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despit
e
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bi
ll
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason a
s
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefor
e,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is n
ot
> enough space and the backups of those last few databases terminate abnorma
lly
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups
.
> basically, if the server has run out of space, the maintenance plan does n
ot
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007...se-backups.html
http://realsqlguy.blogspot.com/2007...-old-files.html
http://realsqlguy.blogspot.com/2007...gmentation.html
another reason why backup files do not get deleted
Hello:
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "continue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report, I now see that they are
considering it for sp2, so it might be worth checking if they implemented such an option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
A couple of times on this board, I posed the question as to why expired
backup files may not get deleted from a server folder automatically despite
the fact that the maintenance plan in SQL 2000 tells the server to delete
files older than x number of days.
I want to thank everyone for pointing me to that really good article by Bill
Hollinshead. The part of the article about reading the maintenance plan's
history report solved the problem for me.
You see, by reading the report at my client site, I found a third reason as
to why backup files do not get deleted. (The first two reasons are
Permissions and Sharing Violation.)
The client did not have enough space on his server's hard drive. Therefore,
the last few databases in the list of databases were not getting deleted.
So, here is what a SQL maintenance plan does in this case where there is not
enough space and the backups of those last few databases terminate abnormally
therefore.
The maintenace plan quits and does not bother deleting the expired backups.
basically, if the server has run out of space, the maintenance plan does not
try to delete expired backups!
In a way, this should be added as a third reason as to why backup files do
not get deleted!
Thanks, again!
childofthe1980sThanks for reporting back to us. This is essentially why I would like a "continue on error" option.
Basically, this is the same as connect issue 125025. Re-reading this report, I now see that they are
considering it for sp2, so it might be worth checking if they implemented such an option...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:B8454FDE-F33B-4BAD-BF70-7C18261A4677@.microsoft.com...
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s|||On Feb 8, 10:15 pm, childofthe1980s
<childofthe19...@.discussions.microsoft.com> wrote:
> Hello:
> A couple of times on this board, I posed the question as to why expired
> backup files may not get deleted from a server folder automatically despite
> the fact that the maintenance plan in SQL 2000 tells the server to delete
> files older than x number of days.
> I want to thank everyone for pointing me to that really good article by Bill
> Hollinshead. The part of the article about reading the maintenance plan's
> history report solved the problem for me.
> You see, by reading the report at my client site, I found a third reason as
> to why backup files do not get deleted. (The first two reasons are
> Permissions and Sharing Violation.)
> The client did not have enough space on his server's hard drive. Therefore,
> the last few databases in the list of databases were not getting deleted.
> So, here is what a SQL maintenance plan does in this case where there is not
> enough space and the backups of those last few databases terminate abnormally
> therefore.
> The maintenace plan quits and does not bother deleting the expired backups.
> basically, if the server has run out of space, the maintenance plan does not
> try to delete expired backups!
> In a way, this should be added as a third reason as to why backup files do
> not get deleted!
> Thanks, again!
> childofthe1980s
This is one argument I have for NOT using the maintenance plans - you
have no control over the inner workings. I'd encourage everyone to
build their OWN maintenance routines. Write your own backup scripts,
your own reindex scripts, your own cleanup scripts. I have examples
of all three here:
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
http://realsqlguy.blogspot.com/2007/02/cleaning-up-old-files.html
http://realsqlguy.blogspot.com/2007/02/smart-index-defragmentation.html
Sunday, February 19, 2012
Analyzing db Growth per day
Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.
Any help would be grately aprreciated.sp_helpdb 'MyDatabaseName'|||The easiest way to do it is to check in Enterprise manager daily to see what the current size is, then keep the daily records in a spreadsheet, or database. If you are looking for a pre-packaged too, I think Quest Software has something for that. I think Microsoft is also getting into the game, as well, with a product I can not remember the name of. Some SQL Server add on.|||Or you could write a job that copies sysobjects and sysindexes into a table that you aggregate on a regualr basis. Swipe code from sp_spaceused to understand how to use that information.
Any help would be grately aprreciated.sp_helpdb 'MyDatabaseName'|||The easiest way to do it is to check in Enterprise manager daily to see what the current size is, then keep the daily records in a spreadsheet, or database. If you are looking for a pre-packaged too, I think Quest Software has something for that. I think Microsoft is also getting into the game, as well, with a product I can not remember the name of. Some SQL Server add on.|||Or you could write a job that copies sysobjects and sysindexes into a table that you aggregate on a regualr basis. Swipe code from sp_spaceused to understand how to use that information.
Analyze LDF files MS SQL Server 2000
Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
I mean, a tool that converts a LDF file in a set of SQL transactions?
(similar to dbtran in sybase)
I mean, a tool that converts a LDF file in a set of SQL transactions?
(similar to dbtran in sybase)
thanks!"Esteban" <ecastillo@.gmail.com> wrote in message
news:8f83567b.0406180752.1b24d5ff@.posting.google.c om...
> Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
> I mean, a tool that converts a LDF file in a set of SQL transactions?
> (similar to dbtran in sybase)
> thanks!
There is nothing in MSSQL itself (except for some undocumented commands),
but there are third-party tools available, such as this one:
http://www.lumigent.com/
If you are trying to achieve something specific, you might want to post more
details of your goal, and someone may be able to suggest something.
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40d3fef1_2@.news.bluewin.ch>...
> "Esteban" <ecastillo@.gmail.com> wrote in message
> news:8f83567b.0406180752.1b24d5ff@.posting.google.c om...
> > Anybody nows a tool to analyze LDF files in MS SQL Server 2000?
> > I mean, a tool that converts a LDF file in a set of SQL transactions?
> > (similar to dbtran in sybase)
> > thanks!
> There is nothing in MSSQL itself (except for some undocumented commands),
> but there are third-party tools available, such as this one:
> http://www.lumigent.com/
> If you are trying to achieve something specific, you might want to post more
> details of your goal, and someone may be able to suggest something.
> Simon
Thanks Simon, I was trying this product, but I can't find an option
that shows me a list of SQL transactions made in the database ...
maybe I don't know how to use it.
For example, I want to know if a certain register was deleted from the
database.
thanks.|||Esteban (ecastillo@.gmail.com) writes:
> Thanks Simon, I was trying this product, but I can't find an option
> that shows me a list of SQL transactions made in the database ...
> maybe I don't know how to use it.
> For example, I want to know if a certain register was deleted from the
> database.
In the version I have, there is a "View DDL commands" under Browse, and it
seems to have the information of the kind you are looking for. There is
also a filter function.
Note that for Log Explorer to be really useful, you need to have the
database in FULL or BULK_LOGGED recovery mode.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)