Re: recover mode and the transaction log file question

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 04/12/04 

Date: Sun, 11 Apr 2004 20:46:55 -0500

To add to Andrew's response, you need to perform a full backup after
changing the recovery model to FULL. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Kent Eilers" <kent@kaeServices.com> wrote in message
news:shlec.33467$YC5.30597@twister.rdc-kc.rr.com...
> It's been about seven years since i've worked with SQL Server...(ouch) and
> I"ve been playing around with it on my home PC.
>
> My goal is to get the transaction log filled.  I've reviewed the new
> 'recovery mode' options and have set the recovery mode for my test
database
> to 'full' with the following command:
>
>     <alter database leads set recovery full>
>
> I have setup the log file to not grow at all.  It is set at 3 MB.
>
> I then have added records into a table with an insert statement.
>
> I check the log file status using the "DBCC SQLPERF(logspace)"  command
> after every insert.
>
> I can get the 'log file' full message triggered if I insert a large enough
> batch.  If I insert smaller amounts of data the log space fluxuates -up
and
> down!  It maxes out in the 60 - 70 % full then is at 20% after the next
> insert!
>
> It this the correct behavior given the recovery mode/T-log parameters I've
> mentioned above?  If so what am I missing here?  It seems like the
database
> is doing automatic truncate log commands behind my back - but nothing of
the
> sort is being logged in the SQL Server log files.
>
> Any feedback much appreciated.
>
> -- 
> kent eilers
> kent@kaeServices.com
>
>

Relevant Pages

  • Re: SQL Database Purge
    ... Are you performing log backups regularly? ... set the database to simple recovery mode. ... > recovery model is set to FULL and the log file is nearly 66 GB. ...
    (microsoft.public.sqlserver.server)
  • Re: optimum log file size
    ... Fixing of Transaction log file based up on the amount of bulk transaction ... CUrrent log growth will be because of FULL recovery mode. ... If you do not require a point in time recovery or if your database ...
    (microsoft.public.sqlserver.clients)
  • Re: How can I back up a log-shipped database?
    ... It's clear I don't understand the whole RECOVERY business. ... I had *hoped* that, by temporarily suspending the log file processing, I could somehow get ... sounds as if, by virtue of performing a backup on the DB, I'd be "marking" the transaction ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Monsterlog file - short term solution?
    ... Sounds like you have your recovery plan set to FULL. ... You can then issue a DBCC SHRINKFILE command ... > The log file has grown to an enormous size. ... > and the db admin has fixed it. ...
    (microsoft.public.sqlserver.server)
  • Re: Transact Log truncate
    ... detach the database, delete the .ldf file, then attach agian the .mdf ... I have received mail from more than one person who delete the log file - ... Are you running with full recovery but never backup the transaction log? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)