Do backups need to be manually deleted?

If I create a backup plan with weekly full, daily differential, and hourly
log backups to local disk, will the backup sets continue to grow over time
or do they get overwritten? IOW, after six months will I have THOUSANDS of
backup files that will need to be deleted manually?
New backups are created each time. You can configure the maintenance plan
to delete old backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bill Fuller" <someone@.nospam.com> wrote in message
news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
If I create a backup plan with weekly full, daily differential, and hourly
log backups to local disk, will the backup sets continue to grow over time
or do they get overwritten? IOW, after six months will I have THOUSANDS of
backup files that will need to be deleted manually?
|||Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
this?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> New backups are created each time. You can configure the maintenance plan
> to delete old backups.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
> If I create a backup plan with weekly full, daily differential, and hourly
> log backups to local disk, will the backup sets continue to grow over time
> or do they get overwritten? IOW, after six months will I have THOUSANDS of
> backup files that will need to be deleted manually?
>
|||When you specify a backup directory, there is also a checkbox to Remove
files older than a certain period.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Bill Fuller" <someone@.nospam.com> wrote in message
news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
this?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
> New backups are created each time. You can configure the maintenance plan
> to delete old backups.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:e3tuWL6UIHA.2464@.TK2MSFTNGP04.phx.gbl...
> If I create a backup plan with weekly full, daily differential, and hourly
> log backups to local disk, will the backup sets continue to grow over time
> or do they get overwritten? IOW, after six months will I have THOUSANDS of
> backup files that will need to be deleted manually?
>
|||Bill,
And to add to Tom's comment, it sounds like you are running SQL Server 2000,
but if you are running SQL Server 2005 there is also a Maintenance Cleanup
Task that deletes old files.
RLF
"Bill Fuller" <someone@.nospam.com> wrote in message
news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Cool. Is there an option somewhere in the Maintence Plan Wizard for doing
> this?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:%23$JqGR6UIHA.6060@.TK2MSFTNGP05.phx.gbl...
>
|||I am running SQL Server 2005 and think I found that task... it defaults to 4
weeks, which I kept.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:um2NmS7UIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Bill,
> And to add to Tom's comment, it sounds like you are running SQL Server
> 2000, but if you are running SQL Server 2005 there is also a Maintenance
> Cleanup Task that deletes old files.
> RLF
> "Bill Fuller" <someone@.nospam.com> wrote in message
> news:eyOiDh6UIHA.1164@.TK2MSFTNGP02.phx.gbl...
>
sql

dm_exec_query_stats/dm_exec_sql_text and plan cache

We are using the following query:
SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
to figure out some of our worst offenders in terms of un-parameterized
queries. The problem is when we run this query in production (we are using
x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in the
plan cache to near zero and an increase in the free buffer pool which in
turns causes our database to become unresponsive and sometimes causes the
CPUs to be pegged for an extended period (on an 8 core box). We might expect
the query to use some memory, but certain not that much and we would expect
the plan cache to come right back up but it does not.
Anyone on this board have any ideas? We have engaged PSS but haven't gotten
very far. We would like to be able to use the management views real-time in
production (one of the reasons we decided to upgrade to 2005).
Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/ 4
gigs of RAM)
It had no problem. Any other actvities on the server at the same time?
"jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
> We are using the following query:
> SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
> REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
> FROM sys.dm_exec_cached_plans AS p
> CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
> WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
> to figure out some of our worst offenders in terms of un-parameterized
> queries. The problem is when we run this query in production (we are using
> x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in
> the
> plan cache to near zero and an increase in the free buffer pool which in
> turns causes our database to become unresponsive and sometimes causes the
> CPUs to be pegged for an extended period (on an 8 core box). We might
> expect
> the query to use some memory, but certain not that much and we would
> expect
> the plan cache to come right back up but it does not.
> Anyone on this board have any ideas? We have engaged PSS but haven't
> gotten
> very far. We would like to be able to use the management views real-time
> in
> production (one of the reasons we decided to upgrade to 2005).
|||There are a lot of activities going on at the same time. We are running this
in the middle of the day in some of our heaviest activity times. Our free
data buffer pool is at 0 and our plan cache is at 2-3 gigs (we do need to do
some parameterization but that's why we want to run this - to know where to
start). We are running at about 1,000 batch requests per second or sometimes
higher. I am trying to replicate this in a test environment by creating load
which will fill up the buffer pool and create a decent amount of plan cache
(run a lot of ad-hoc queries). We wish there was some way which we could set
a minimum plan cache size no matter what the internal memory pressure is -
that would at least probably work around this problem.
Jeff
"Uri Dimant" wrote:

> Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/ 4
> gigs of RAM)
> It had no problem. Any other actvities on the server at the same time?
>
> "jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
> news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
>
>

dm_exec_query_stats/dm_exec_sql_text and plan cache

We are using the following query:
SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
to figure out some of our worst offenders in terms of un-parameterized
queries. The problem is when we run this query in production (we are using
x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in the
plan cache to near zero and an increase in the free buffer pool which in
turns causes our database to become unresponsive and sometimes causes the
CPUs to be pegged for an extended period (on an 8 core box). We might expect
the query to use some memory, but certain not that much and we would expect
the plan cache to come right back up but it does not.
Anyone on this board have any ideas? We have engaged PSS but haven't gotten
very far. We would like to be able to use the management views real-time in
production (one of the reasons we decided to upgrade to 2005).Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/ 4
gigs of RAM)
It had no problem. Any other actvities on the server at the same time?
"jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
> We are using the following query:
> SELECT usecounts, size_in_bytes, cacheobjtype, objtype,
> REPLACE (REPLACE ([text], CHAR(13), ' '), CHAR(10), ' ') AS sql_text
> FROM sys.dm_exec_cached_plans AS p
> CROSS APPLY sys.dm_exec_sql_text (p.plan_handle)
> WHERE p.objtype = 'Adhoc' AND cacheobjtype = 'Compiled Plan'
> to figure out some of our worst offenders in terms of un-parameterized
> queries. The problem is when we run this query in production (we are using
> x64 sql server 2005 sp1 w/ 16 gigs of RAM), we see an immediate drop in
> the
> plan cache to near zero and an increase in the free buffer pool which in
> turns causes our database to become unresponsive and sometimes causes the
> CPUs to be pegged for an extended period (on an 8 core box). We might
> expect
> the query to use some memory, but certain not that much and we would
> expect
> the plan cache to come right back up but it does not.
> Anyone on this board have any ideas? We have engaged PSS but haven't
> gotten
> very far. We would like to be able to use the management views real-time
> in
> production (one of the reasons we decided to upgrade to 2005).|||There are a lot of activities going on at the same time. We are running this
in the middle of the day in some of our heaviest activity times. Our free
data buffer pool is at 0 and our plan cache is at 2-3 gigs (we do need to do
some parameterization but that's why we want to run this - to know where to
start). We are running at about 1,000 batch requests per second or sometimes
higher. I am trying to replicate this in a test environment by creating load
which will fill up the buffer pool and create a decent amount of plan cache
(run a lot of ad-hoc queries). We wish there was some way which we could set
a minimum plan cache size no matter what the internal memory pressure is -
that would at least probably work around this problem.
Jeff
"Uri Dimant" wrote:

> Hmm, I just did some testing on our machine ( x32 sql server 2005 sp1 w/
4
> gigs of RAM)
> It had no problem. Any other actvities on the server at the same time?
>
> "jeffbrewer" <jeffbrewer@.discussions.microsoft.com> wrote in message
> news:99CFB494-3DC8-4FBA-8AAB-6A254FC74AA4@.microsoft.com...
>
>