Re: HELP!!

From: Hari (hari_prasad_k_at_hotmail.com)
Date: 04/15/04 

Date: Thu, 15 Apr 2004 09:34:29 +0530

Hi,

The best option is shrinking the transaction log and data file individually
using the DBCC SHRINKFILE command.

How to check the Transaction log file size and usage, execute the below
command

DBCC SQLPERF(LOGSPACE)

How to shrink the transaction log file if your database is FULL recovery
model.

1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')

How to shrink the transaction log file if your database is Simple recovery
model.

1. Truncate the Transaction Log using (Backup log dbname with triuncateonly
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncate_only')

How to shrink the data files

1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)

Note:

If your data is not that critical (Development Server) and if you do not
require a time based
recovery go for SIMPLE recovery Model for your database. This
require less monitoring of tranasction log usage.

Thanks
Hari
MCDBA

"DAVE" <anonymous@discussions.microsoft.com> wrote in message
news:64DAA4E3-173F-43B6-A12A-104336227E8A@microsoft.com...
> how do i reduce the size of an mdf and ldf file in sql server2000. they
have grown to the point of locking up my machine

Relevant Pages

  • Re: Question on shrinkingTransaction Log file
    ... After Transaction log backup the physical file will not shrink automatically ... Shrink the transaction log file. ...
    (microsoft.public.sqlserver.server)
  • Re: DBfile usage
    ... DBCC SQLPERF ... Based on the outcome you can shrink the MDF and LDF file seperately. ... Backup the transaction log ...
    (microsoft.public.sqlserver.server)
  • Re: MDF and LDF size
    ... > You need to shrink the transaction log file to reduce the size. ... Now shrink the transaction log file using DBCC SHRINK FILE from the ...
    (microsoft.public.sqlserver.server)
  • Re: SQL dB bloated?
    ... Using the DBCC SQLPERFyou can get log space allocated and used %. ... In this case you can shrink the transadction log file. ... Backup or truncate the transaction log depends on recovery model (if it ...
    (microsoft.public.sqlserver.setup)
  • Re: Question on shrinkingTransaction Log file
    ... up a transaction log that is 25GB and I've got only 1 GB ... >Auto shrink will take more I/O. ... Backup the transaction log (Use BACKUP Log dbname to ... Shrink the transaction log file. ...
    (microsoft.public.sqlserver.server)