Log file growing very large?

Hello All,
All you SQL dba guru's.
I have na MSDE server. All seems to be working with app but I looked at the
server log file and saw that it has grown to 65 gigs. The mdf file is only
50 megs.
What is causing the log file to grow so large.?
Can I just dump the log file?
Thanks, Bill
You are using FULL recovery mode, and you've never performed a backup or
dumped the transaction logs?
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hi,
Add on to Aaron,
Looks like the recovery model for your database is FULL or
BULK_LOGGED.Please change the recovery model for the database to
SIMPLE if your database is non production. Use the below command to set the
database to SIMPLE.
ALTER database <DBNAME> set recovery SIMPLE
If you do not have the 65 G to take a backup or if you do not want the
transaction log backup. You could truncate the transaction
log and shrink the LDF file.
backup log <dbname> with truncate_only
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
If you need the transaction log backup do:-
backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail since
you do not have hard disk space)
go
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Now execute the below command to see log file size and usage.
DBCC SQLPERF(LOGSPACE)
Note:
If you need to keep the recovery model to FULL then schedule a transaction
log backup in frequent intervals (atleast 30 minutes once).
This will keep the log file size under control.
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Hello All,
> All you SQL dba guru's.
> I have na MSDE server. All seems to be working with app but I looked at
the
> server log file and saw that it has grown to 65 gigs. The mdf file is
only
> 50 megs.
> What is causing the log file to grow so large.?
> Can I just dump the log file?
> Thanks, Bill
>
|||Hello and thanks,
does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
clear out and reduce the size of the log as part of its process?
Or do I need to do a "backup log <dbname> with truncate_only" also?
Thanks,
Bill
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Add on to Aaron,
> Looks like the recovery model for your database is FULL or
> BULK_LOGGED.Please change the recovery model for the database to
> SIMPLE if your database is non production. Use the below command to set
the
> database to SIMPLE.
> ALTER database <DBNAME> set recovery SIMPLE
> If you do not have the 65 G to take a backup or if you do not want the
> transaction log backup. You could truncate the transaction
> log and shrink the LDF file.
> backup log <dbname> with truncate_only
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> If you need the transaction log backup do:-
> backup log <dbname> to disk='d:\backup\dbname.trn' ( This might fail
since
> you do not have hard disk space)
> go
> DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
> Now execute the below command to see log file size and usage.
> DBCC SQLPERF(LOGSPACE)
>
> Note:
> If you need to keep the recovery model to FULL then schedule a transaction
> log backup in frequent intervals (atleast 30 minutes once).
> This will keep the log file size under control.
> Thanks
> Hari
> MCDBA
>
> "Bill D" <Bill@.test.com> wrote in message
> news:eFPpo#5gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> the
> only
>
|||Hi,
Either one will do.
If you need the transaction log backup execute below;
backup log <dbname> to disk='d:\backup\dbname.trn
Incase if you do not require the transaction log backup go for;
backup log <dbname> with truncate_only
The above commands will clear the log , bit to reduce the physical LDF file
size you will have to execute the below command:-
DBCC SHRINKFILE (db1_log1_logical_name,truncateonly)
Thanks
Hari
MCDBA
"Bill D" <Bill@.test.com> wrote in message
news:ONVLKt6gEHA.632@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hello and thanks,
> does performing a "backup log <dbname> to disk='d:\backup\dbname.trn' "
> clear out and reduce the size of the log as part of its process?
> Or do I need to do a "backup log <dbname> with truncate_only" also?
> Thanks,
> Bill
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OpOydK6gEHA.3992@.TK2MSFTNGP11.phx.gbl...
> the
> since
transaction[vbcol=seagreen]
at
>