BIG LDF file size

How to check for LDF file size I.e. my database ldf size is 10 times the
size of mdf. is it ok. For having smaller ldf do i need to check for some sp
or tune sps and other DTS packages.
I am using JOB which execute 10 DTS once daily...
ldf file growing many times bigger than mdf is fine...
But if you want to limit the ldf's from growing beyond, you need to
take regular transaction log backups.
If you want to shrink ldf file and release the space back to OS, then
you can even do SHRINKFILE
|||Vikram wrote:
> How to check for LDF file size I.e. my database ldf size is 10 times the
> size of mdf. is it ok. For having smaller ldf do i need to check for some sp
> or tune sps and other DTS packages.
> I am using JOB which execute 10 DTS once daily...
>
It's not very likely that your logfile needs to be 10 times your
datafile, so you might have to backup your logfile.
If your database are in FULL recovery mode, you should make sure that
you run logfile backups regularly so the logfile get truncated and you
can reuse the space in the file.
If your database are in Simple recovery mode, you can't backup the log
and it will be truncated automatically, but then it might be that you
simply need such a big logfile.
Executing 10 DTS jobs daily doesn't tell a lot since you don't tell what
they actually do.
If you find that you have a lot of unused space in your logfile (e.g. if
you've never ran a logfile backup and then starts to do it) you can
shrink the file to a more appropriate size by running DBCC SHRINKFILE.
You can check how much space is used in the logfile by running DBCC
SQLPERF ( LOGSPACE ) or by using Enterprise Manager.

Regards
Steen