Database & t-log file size

I have a database that is about 60GB only half of which is used space -
the transaction log is 31GB with only 100MB of used space. Is it safe
to shrink these as both are on separate drives and need the free space.My recommendatiion is to keep the MDF (data) file as it is because this will
ensure that you have enough space in data file and will help you not to
autogrow by itself. But you could shrink the LDF file using DBCC SHRINKFILE
.
If the database is in development you could change the recovery model to
SIMPLE and truncate the log using BACKUP LOG <DBNAME> with Truncate_only and
then shrink the file. If it is production then have the recovery model as
FULL and then schedule a transaction log backup in frequent intervals. This
will help to keep the LDF file not to grow as well as help you to do point i
n
time recovery if needed.
Thanks
Hari
"J1C" wrote:

> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
>|||J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
>
What recovery model are you using for this database? If Full or
Bulk-Logged, are you running transaction log backups at regular
intervals? Log file growth occurs when:
- database is in Simple mode, the log file will grow large enough to
hold any transaction that is executed. Committed transactions are
automatically purged from the log, but the physical file size is not
decreased.
- database is in Full or Bulk-Logged mode, transactions are purged when
a transaction log backup occurs. Committed transactions that are not
flagged for replication are purged. The physical file size is not
decreased.
The log file is a "journal", recording all of the changes made in your
database. If a large transaction is submitted that affects 30GB of
data, the transaction log will grow to 30GB in order to hold that
transaction. Once the transaction is committed, it will be flushed out
of the log (automatically if in Simple mode, upon t-log backup
otherwise). The log file will remain 30GB in size however. Use DBCC
SHRINKFILE to reclaim that space. Consider the cause of the growth,
however, there's no point shrinking if it's just going to grow again.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
Wrong question. The right question is how much space do you need to run
your database? We can't possibly tell you that. Get some estimates from
the DBA or from whoever developed or supports the system.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--