• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

easy way to shrink a sql log file in windows 2003 server OS

easy way to shrink a sql log file in windows 2003 server OS
0
aawesome
Asked:
aawesome
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hi,

  transaction log has 2 possible configuration:
  * SIMPLE
     means that any transaction get's written to the transaction log, but once it is committed, it is overwritable in the log.
     you cannot perform transaction log backups
     you cannot perform restore to a point of time
     you should let the transaction log file size as it is, because if you shrink it (regulary) to a small size, it has to grow again.

  * FULL
     means that any transaction get's written to the transaction log, but only once the transaction log backup has been performed, it is overwritable in the log
     you have to (should) perform transaction log backups
     you can perform restore to a point of time
     you should let the transaction log file size as it is, because if you shrink it (regulary) to a small size, it has to grow again.

note: if you had a database in full recovery mode, but did not do any transaction log backups, the log file will indeed grow endlessy until it fills the hard disk(s) completely.
if that happened, you should change to SIMPLE, and try several times the DBCC SHRINKFILE ( log_name, <some size here) until the file shrinks (as the log file is internally a circular buffer, and in case the pointer is at the end of the file, the file won't shrink, hence the retries)

0
 
twoboatsCommented:
First off, backup the log to remove the inactive part.

Then

DBCC SHRINKFILE

To reduce the filesize
0
 
KCTSCommented:
I think the secret here is to backup on a regular basis and purge the log file see http://databases.about.com/od/sqlserver/a/disaster_3.htm
0
 
moondistCommented:
If you just need trunc the transaction log quickly run the following. It will mark all transactions as backed up and allow the SHRINKFILE command to compress to 2MB. Be CAREFUL with this command though as it removes any recovery option using the transaction log. The best method is regular backups as KCTS suggests. You can set those up in Enterprise Manager, Management, DB Maintenance Plans and schedule as needed (make sure the SQL Agent is running or the job will never start). I have used this succesfully at client locations when the log was allowed to grow unchecked and filled their harddrive and a full backup was not possible.

-----
Backup Log DBNAMEHERE with truncate_only

DBCC SHRINKFILE ('DBLOGICALNAMEHERE_log', 2)
-----
0
 
moondistCommented:
Oops. looking back I wasn't clear. This command can be run in "Query Analyzer". Copy, paste, edit dbnames and click "Run" or hit F5.
--------
Backup Log DBNAMEHERE with truncate_only

DBCC SHRINKFILE ('DBLOGICALNAMEHERE_log', 2)
---------
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now