Re: Truncating Log file size
From: Leo (none_at_none.com)
Date: 08/12/04
- Next message: Tom Moreau: "Re: How to pad numbers with leading zeros"
- Previous message: Adam Machanic: "Re: How to pad numbers with leading zeros"
- In reply to: Hari Prasad: "Re: Truncating Log file size"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 12 Aug 2004 09:05:00 -0400
Thank you very much gentlemen. Very useful information. Indeed I am not
concerned about keeping the transaction log at that size, since this is only
for developement, so I will run sp_attach_single_file_db after I have
detached and backed up the database and the log file.
Thanks again
Leo
"Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
news:%231HuKvBgEHA.396@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> In Addition to John:-
>
> Since your database is running in test environment change the recovery
model
> of the database to SIMPLE. In this model the trasnaction log
> will be truncated automatically.
>
> Use the below command to see the database recovery mode:-
>
> select databasepropertyex('db_name','recovery')
>
> if the result is not SIMPLE then change the model to simple using
>
> ALTER database <DBNAME> set recovery SIMPLE
>
> THis will truncate the transaction log automatically, but to shrink the
auto
> file ytou should enable the database option AUTO SHRINK
>
> ALTER database <dbname> set AUTO_SHRINK on
>
> Since this is a test environment the AUTO_SHRINK option may not be a
> performance issue. But in production enabling this option is not
> recommended.
>
> -----------------------------------
>
> Follow the procedure mentioned by John to reduce the current Log file size
>
> 1. Detach the database
> 2. Copy the MDF and LDF to a safe place (can be used if attach fails with
> MDF only)
> 3. Delete the current LDF file (Actual file)
> 4. Now attach the database with MDF only (sp_attach_single_file_db)
>
> This will create new LDF file with 0.5 MB
>
> THe above steps are not recommended in production. In production you could
> backup the trasnaction log and shrink the file (DBCC SHRINKFILE)
> to reduce the transaction log file size.
>
> Thanks
> Hari
> MCDBA
>
>
>
>
> "John McLusky" <jmclusky@community.nospam> wrote in message
> news:#dFv0N$fEHA.2028@tk2msftngp13.phx.gbl...
> > Mary Bray wrote:
> > > This often happens in dev and test because people forget about the log
> > > files. If it were me i'd set the database to simple recovery model
> > > (since it's only test) and turn on auto-shrink - that should take
> > > care of everyting over the next day or two.
> >
> > This doesn't always work, unfortunately.
> >
> > If setting the database to Simple recovery model and running DBCC
> SHRINKFILE
> > doesn't help, try detaching the database, backing up the data and log
> files,
> > and then reattaching the .mdf file (sp_attachsinglefiledb). This will
> > create a new log file which will be nice and small :-)
> >
> > If you prefer, you can attach the .mdf file through Enterprise Manager
(on
> > SQL 2000).
> >
> > John.
> >
> >
>
>
- Next message: Tom Moreau: "Re: How to pad numbers with leading zeros"
- Previous message: Adam Machanic: "Re: How to pad numbers with leading zeros"
- In reply to: Hari Prasad: "Re: Truncating Log file size"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
