Recovery Models

In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Regards
Ed.
No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>
|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>
|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas

"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Models

In an attempt to manage the size of the transaction log , I would like to us
e
the
Simple verses the Full Recovery model. Other than the loss of transaction lo
g
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Reg
ards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Models

In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other â'landminesâ' I should consider? Thanks for being there to help. Regards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Model What Database/table stores info

What database/table stores information on the recovery models used for all
database on an SQL server? I want to change all to FUll. ThanksThis will create a script. Run it in query analyzer, then copy the lower
pane, paste to the top, and run again:
SELECT 'ALTER DATABASE '+name+' SET RECOVERY FULL'
FROM master..sysdatabases
WHERE name NOT IN ('master','tempdb','msdb','model')
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:1B6EC27B-F874-4051-90DE-C21338E393EE@.microsoft.com...
> What database/table stores information on the recovery models used for all
> database on an SQL server? I want to change all to FUll. Thanks|||Use "alter database" instead. Copy the result of the following "select"
statement annd execute it in QA.
select
'alter database ' + quotename(catalog_name) + 'set recovery full'
from
information_schema.schemata
where
catalog_name not in ('master', 'model', 'tempdb', 'msdb', 'pubs',
'northwind')
and databasepropertyex(catalog_name, 'Recovery') != 'FULL'
go
AMB
"Lontae Jones" wrote:

> What database/table stores information on the recovery models used for all
> database on an SQL server? I want to change all to FUll. Thanks|||> from
> information_schema.schemata
FWIW, this no longer works the same in SQL Server 2005. I think it's the
only Information_schema view that breaks backward compatibility, but in
truth, it currently doesn't work the way it should anyway. SoI have been
shying away from recommending the schemata view specifically, because its
useful lifetime is rather short.
A|||Do we have a schema database diagram poster for 2005 yet?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23Kj03b9rFHA.3340@.TK2MSFTNGP15.phx.gbl...
> FWIW, this no longer works the same in SQL Server 2005. I think it's the
> only Information_schema view that breaks backward compatibility, but in
> truth, it currently doesn't work the way it should anyway. SoI have been
> shying away from recommending the schemata view specifically, because its
> useful lifetime is rather short.
> A
>
>|||Gosh no
"JT" <someone@.microsoft.com> wrote in message
news:%23zYU1e9rFHA.904@.tk2msftngp13.phx.gbl...
> Do we have a schema database diagram poster for 2005 yet?|||Aaron,
Thanks for sharing that.
AMB
"Aaron Bertrand [SQL Server MVP]" wrote:

> FWIW, this no longer works the same in SQL Server 2005. I think it's the
> only Information_schema view that breaks backward compatibility, but in
> truth, it currently doesn't work the way it should anyway. SoI have been
> shying away from recommending the schemata view specifically, because its
> useful lifetime is rather short.
> A
>
>sql