Re: Will this shrinkfile method work?

On Dec 9, 7:21 pm, pbrill1 <pbri...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Thanks, Andrew -

Your response - and those to 2 other questions I posted with this yesterday
- have been very informative with understanding how to handle log files.  
Thanks,
--
pbrill1



"Andrew J. Kelly" wrote:
You might want to look at using other consultants if that is the advice they
are giving:).   There is no need to change the recovery model at all. As
long as you have issued a recent Log backup and there are no long running
open transactions you should be able to shrink the log in FULL recovery
mode. If the active VLF is near the end of the log file it may take a little
while for it to shrink but it will.  Then if you issue regular log backups
and ensure there are no long running trans there should be no reason why it
will grow that large again.

--
Andrew J. Kelly    SQL MVP
Solid Quality Mentors

"pbrill1" <pbri...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A1EB6C90-1C97-4694-A92D-AE3E1C248BCD@xxxxxxxxxxxxxxxx
We are running SQL2005 - and have a few databases with large log files
(the
biggest a 13GB datafile, with what is now a 24.5GB transaction log).

My ERP consultant gave us this method to shrink logfiles from our FULL
RECOVERY model.  Will this work, or might I lose data by doing this?

STEPS TO SHRINK LOGFILE

1. Change the recovery model from FULL RECOVERY to SIMPLE
a. Since simple recovery model does not use transaction logs in the
recovery technique, it allows you to trucate the logfile
2. Right click the database, choose TASKS - SHRINK - DATABASE
3. Select OK on the restulting dialog box to shrink database.
4. A dialog box appears, showing the database has been shrunk.
5. Change the recovery model back to FULL.

Question:  Will this work for a small database shop, or is there a chance
that I could lose data?
--
pbrill1- Hide quoted text -

- Show quoted text -

it's not a good idea to shrink a log file unless you absolutely have
to. if it grows again then it can fragment over multiple sections of
your disk and cause performance issues. the rule with SQL files is to
create them with as much white space as you can and leave it alone.
this way you have one contignous block of space on your hard disk that
it uses and it will cut down on your hard disk looking for the data.
.

Relevant Pages

  • Re: Detach - Attach in SQL Server 7
    ... >> It is not rquired to detach and attach the database to shrink the file. ... If the log file really huge make the databse single user after ... >> DBCC SHRINKFILE('Logical_ldf_name','truncateonly' ...
    (microsoft.public.sqlserver.server)
  • Re: Simple recovery model + checkpoint = no need to save .ldf file?
    ... The files can also be moved to another server and ... writes all changes to the data files before the database is detached. ... you do not need the log file as sp_attach_single_file_db can be used ... > If I have a sql server 2000 database using the simple recovery model and ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2000: Cant get the trans log to shrink
    ... to the log file? ... the t-log backup appears to have worked. ... Manager> All Tasks> Shrink Database> Shrink Files ...
    (microsoft.public.sqlserver.setup)
  • Re: Log file is increeasing too much
    ... > turned the recovery mode to simple and I put the option auto shrink on. ... >> If you are using the FULL recovery model, you would need to backup the log ... >> transaction, hence all the data is either committed or rolled back. ... >>> i have a database and a proccess which delete everything from database and ...
    (microsoft.public.sqlserver.server)
  • Re: Log file growing very large??
    ... > BULK_LOGGED.Please change the recovery model for the database to ... You could truncate the transaction ... > Now execute the below command to see log file size and usage. ...
    (microsoft.public.sqlserver.msde)
Loading