Prev: Report Header Parameter Max(Value) from Table...
Next: Determine a server specific ID
From: . on 1 Jul 2010 20:15
Hello, after completing the Mainentance Plan wizard in which the Integrity
Checks and Transaction Log Backup job fail I came across this
http://www.mssqltips.com/tip.asp?tip=1219 mentioning of the SIMPLE Recovery
mode is hindering it. Think I'm missing something...so does anyone know how
to put the other dbs into FULL Recovery mode and is it ok to do? Thanks in
advance.


From: Dazza on 3 Jul 2010 11:09
Right click on the database and select Properties then select the Options
tab. You change change the recovery model here.

The only difference between simple and full recovery is that simple does not
keep a transaction log of all the goings on in the database whereas Full
will so you will need to keep an eye on the log sizes (they will shrink on
backup though)

Hope his helps
Daz


"." <nothing(a)nothing.com> wrote in message
news:#8j7Y7XGLHA.1716(a)TK2MSFTNGP06.phx.gbl...
> Hello, after completing the Mainentance Plan wizard in which the Integrity
> Checks and Transaction Log Backup job fail I came across this
> http://www.mssqltips.com/tip.asp?tip=1219 mentioning of the SIMPLE
> Recovery mode is hindering it. Think I'm missing something...so does
> anyone know how to put the other dbs into FULL Recovery mode and is it ok
> to do? Thanks in advance.
>
From: Jeffrey Williams on 3 Jul 2010 13:20
Dazza, this is not quite right.

The transaction log is used in all recovery models. The difference between
SIMPLE and FULL is that entries in the transaction log are marked as
reusable in SIMPLE after a checkpoint, and in FULL are marked as reusable
after you perform a transaction log backup.

In SIMPLE recovery model - you cannot perform transaction log backups. In
FULL and BULK_LOGGED you *have* to perform transaction log backups (at least
every hour, or more frequent depending on your disaster recovery
requirements).

The transaction log doesn't shrink automatically (unless you set auto-shrink
on, and that is not recommended for any production databases). The
transaction log will get as large as it needs to be to handle the largest
transactions.

Jeff

"Dazza" <Post2Group(a)only.com> wrote in message
news:u7EhLHsGLHA.5500(a)TK2MSFTNGP05.phx.gbl...
> Right click on the database and select Properties then select the Options
> tab. You change change the recovery model here.
>
> The only difference between simple and full recovery is that simple does
> not keep a transaction log of all the goings on in the database whereas
> Full will so you will need to keep an eye on the log sizes (they will
> shrink on backup though)
>
> Hope his helps
> Daz
>
>
> "." <nothing(a)nothing.com> wrote in message
> news:#8j7Y7XGLHA.1716(a)TK2MSFTNGP06.phx.gbl...
>> Hello, after completing the Mainentance Plan wizard in which the
>> Integrity Checks and Transaction Log Backup job fail I came across this
>> http://www.mssqltips.com/tip.asp?tip=1219 mentioning of the SIMPLE
>> Recovery mode is hindering it. Think I'm missing something...so does
>> anyone know how to put the other dbs into FULL Recovery mode and is it ok
>> to do? Thanks in advance.
>>
From: . on 12 Jul 2010 13:31
Thanks guys for the insightful info, appreciate it.

I tried to change all the dbs to 'Full' (Recover Model) except for 'tempdb'
(which prompted it can't be set) which then in looking at the SQL Agent job
it shows it failed again. Thought the Recovery model setting would've fixed
it but would anyone know how to have the 'Integrity Checks Job...' and
'Transaction Log Backup Job...' to succeed?


"Jeffrey Williams" <jeff.williams3188(a)verizon.net> wrote in message
news:58AF2B15-F1DC-4F28-8817-7F065F1333D6(a)microsoft.com...
> Dazza, this is not quite right.
>
> The transaction log is used in all recovery models. The difference
> between SIMPLE and FULL is that entries in the transaction log are marked
> as reusable in SIMPLE after a checkpoint, and in FULL are marked as
> reusable after you perform a transaction log backup.
>
> In SIMPLE recovery model - you cannot perform transaction log backups. In
> FULL and BULK_LOGGED you *have* to perform transaction log backups (at
> least every hour, or more frequent depending on your disaster recovery
> requirements).
>
> The transaction log doesn't shrink automatically (unless you set
> auto-shrink on, and that is not recommended for any production databases).
> The transaction log will get as large as it needs to be to handle the
> largest transactions.
>
> Jeff
>
> "Dazza" <Post2Group(a)only.com> wrote in message
> news:u7EhLHsGLHA.5500(a)TK2MSFTNGP05.phx.gbl...
>> Right click on the database and select Properties then select the Options
>> tab. You change change the recovery model here.
>>
>> The only difference between simple and full recovery is that simple does
>> not keep a transaction log of all the goings on in the database whereas
>> Full will so you will need to keep an eye on the log sizes (they will
>> shrink on backup though)
>>
>> Hope his helps
>> Daz
>>
>>
>> "." <nothing(a)nothing.com> wrote in message
>> news:#8j7Y7XGLHA.1716(a)TK2MSFTNGP06.phx.gbl...
>>> Hello, after completing the Mainentance Plan wizard in which the
>>> Integrity Checks and Transaction Log Backup job fail I came across this
>>> http://www.mssqltips.com/tip.asp?tip=1219 mentioning of the SIMPLE
>>> Recovery mode is hindering it. Think I'm missing something...so does
>>> anyone know how to put the other dbs into FULL Recovery mode and is it
>>> ok to do? Thanks in advance.
>>>


From: Erland Sommarskog on 12 Jul 2010 16:18
.. (nothing(a)nothing.com) writes:
> I tried to change all the dbs to 'Full' (Recover Model) except for
> 'tempdb' (which prompted it can't be set) which then in looking at the
> SQL Agent job it shows it failed again. Thought the Recovery model
> setting would've fixed it but would anyone know how to have the
> 'Integrity Checks Job...' and 'Transaction Log Backup Job...' to
> succeed?

The more interesting question is why they fail.

Find the errors with a maintenance plan on SQL 2000 is not always that
easy, but I believe they are in
C:\Program Files\Microsoft SQL Server\MSSQL\LOG
Note: if you install SQL Server differently, so may the exact location
of the log directory.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Report Header Parameter Max(Value) from Table...
Next: Determine a server specific ID