Trimming HUGE Transaction Log

#41

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote

Quote:

Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#42

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote

Quote:

Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#43

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote

Quote:

Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#44

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote

Quote:

Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#45

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote

Quote:

Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#46

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote

Quote:

Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#47

I deserve another DUH on that one. I typically backup the database and the
transaction log at the same time (in the same maintenance plan), so when I
say backup the database, I really mean both. I added differential backups
during the day, to increase the frequency of backups with minimal impact to
users on the system.
--
Thanx a GIG! Your help is very much appreciated.

Angelo

"Tibor Karaszi" wrote:

Quote:

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote in message
news:ED7E1267-3C5D-4411-BCF6-D38933608CC4 (AT) microsoft (DOT) com...
Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#48

I deserve another DUH on that one. I typically backup the database and the
transaction log at the same time (in the same maintenance plan), so when I
say backup the database, I really mean both. I added differential backups
during the day, to increase the frequency of backups with minimal impact to
users on the system.
--
Thanx a GIG! Your help is very much appreciated.

Angelo

"Tibor Karaszi" wrote:

Quote:

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote in message
news:ED7E1267-3C5D-4411-BCF6-D38933608CC4 (AT) microsoft (DOT) com...
Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#49

I deserve another DUH on that one. I typically backup the database and the
transaction log at the same time (in the same maintenance plan), so when I
say backup the database, I really mean both. I added differential backups
during the day, to increase the frequency of backups with minimal impact to
users on the system.
--
Thanx a GIG! Your help is very much appreciated.

Angelo

"Tibor Karaszi" wrote:

Quote:

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote in message
news:ED7E1267-3C5D-4411-BCF6-D38933608CC4 (AT) microsoft (DOT) com...
Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.

#50

I deserve another DUH on that one. I typically backup the database and the
transaction log at the same time (in the same maintenance plan), so when I
say backup the database, I really mean both. I added differential backups
during the day, to increase the frequency of backups with minimal impact to
users on the system.
--
Thanx a GIG! Your help is very much appreciated.

Angelo

"Tibor Karaszi" wrote:

Quote:

Hmm, where did those differential backups come from. Your suggested plan is no better than your
original plan since no BACKUP DATABASE command empties the log.Change them to BACKUP LOG instead and
you have a plan.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Always OpenTo Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote in message
news:ED7E1267-3C5D-4411-BCF6-D38933608CC4 (AT) microsoft (DOT) com...
Duh - I should have figured that one out! Based on this, my plan will be:
-- Keep the Recover Model FULL
-- Do differential backups hourly during the day.
-- Do full backups at night.
Seems like this would allow Point-In-Time recovery with a much smaller
transaction file.

Did I interpret your suggestions properly?
Thanx in advance - I really do appreciate the help!

Angelo Michel

"Aaron Bertrand [SQL Server MVP]" wrote:

If you want the benefits of full recovery model without the log growth you
are experiencing, then back up the log more often between full backups!

On 10/13/08 2:04 PM, in article
E84FFBD1-83A4-472A-91A0-C8C6CA2737B7...soft (DOT) com, "Always OpenTo
Suggestions" <AlwaysOpenToSuggestions (AT) discussions (DOT) microsoft.com> wrote:

But if I keep the recovery model SIMPLE - wouldn't that eliminate the ability
to restore transactions to a point in time. I was hoping that turning the
recovery model to SIMPLE would trim the transaction log and that putting it
back to FULL would allow recovery to a point in time.