32GB log file

Recently, I discovered the log file of testing database server which grow up
a lot. The log file is around 32GB.
any idea on shrink/ Purge the log file?
Thanks
Alan
Hi,
It seems you have set recovery model for this database to FULL. In this
recovery model all the activity inside the
database is logged. So you have schedule a transaction log backup to clear
of the trasnaction logs. THis backup can
be used when a recovery is needed. Since this database is a test environment
I recomment you to make the recovery model as SIMPLE.
Show to set to simple
ALTER database <dbname> set recovery SIMPLE
How to clear the existing logs and shrink the file:-
Since the file is really huge, I recomment you to set the database to single
user model befotre doing the below steps.
-- Setting database single user.
Alter database <dbname> set single_user with rollback immediate
-- Truncate the transaction log
BACKUP log <dbname> with truncate_only
-- Shrink the transacton log file
DBCC SHRINKFILE('logical_ldf_name',truncateonly)
After doing the above steps execute the below command to see the transaction
log size and usage.
DBCC SQLPERF(LOGSPACE)
-- Now set the database multi user
Alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"izumi" <test@.test.com> wrote in message
news:#AzZUnZWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> Recently, I discovered the log file of testing database server which grow
up
> a lot. The log file is around 32GB.
> any idea on shrink/ Purge the log file?
> Thanks
> Alan
>
|||see
http://www.nigelrivett.net/Transacti...leGrows_1.html
|||thanks all.
actually my company will backup the database 2 times a day...
But we dont shrink(Truncate) the log..so it grows up a lot...
do we have any problems after i delete the whole log?
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:4E79A30A-2807-4652-921A-7BCC48B6199E@.microsoft.com...
> see
> http://www.nigelrivett.net/Transacti...leGrows_1.html
>
|||no real Problems deleting the log except that you wont be able to recover
transactions potentially lost.
If you are not using the logs for your recovery model, I would just
recommend setting the database recovery model to "SIMPLE". This will turn
logging off then you wont have to worry about the files at all.
Greg Jackson
PDX, Oregon
|||Yes if we can restore mdf withour log
i think it is ok...
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:%23kaIa5fWEHA.1048@.tk2msftngp13.phx.gbl...
> no real Problems deleting the log except that you wont be able to recover
> transactions potentially lost.
> If you are not using the logs for your recovery model, I would just
> recommend setting the database recovery model to "SIMPLE". This will turn
> logging off then you wont have to worry about the files at all.
>
> Greg Jackson
> PDX, Oregon
>
|||Just wanted to make a distinction between shrinking and truncating the file. Truncation is a TLog specific activity and it deletes old log records
in the file that are no longer necessary. This activity doesnot result in any reduciton in the physical size of the LDF file , and change in file size
is noticed at the file level. This is an internal operation and only makes room inside the file, for re-use.
On the otherhand, shrinking is a physical operation intended for reducing the size of the LDF file.
To address your problem, I would not recommend a flat deletion of the Tlog file. Rather, you should schedule a periodic BACKUP LOG
operation, which will ensure that the TLog is truncated. Then , on a as-needed-basis, you can perform a manual DBCC SHRINKFILE operation
to claim this unused (truncated) space from the ldf and release it back to the OS.
Thanks
Ananth Padmanabham
Microsoft SQL Server support
Please reply only to the newsgroup so that others can benefit. When posting, please state the version of SQL Server being used and the error
number/exact error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.