[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 866
  • Last Modified:

How to shrink a SQL7 transaction log file?

Hi,
I have a SQL7 running in NT4. The database size is about 2.5GB. The .mdf file is about 2.5GB, but the .ldf file is 7.5GB. When I back up the transaction log in SQL Enterprise Manager, that backup file is only 20MB!
Q#1. Why there is a huge size difference in the database, .ldf file and the log file backup?
Q#2. How to configure the database so the  .ldf file does not 'hog' too much disk space?
Q#3. How to 'shrink' the current .ldf file? I tried cmd: dbcc shrinkdatabase (myDB, 50) does not seem to work.
Q#4. What considerations to take when deciding whether to commit the log to database or keep it in transaction log?
Thanks a lot.
0
richtree
Asked:
richtree
  • 5
  • 2
  • 2
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
 
Scott PletcherSenior DBACommented:
Q1.  Db and log backups include only active portions of the file; the original files contain inactive data as well, so they can be (much) larger.  The log file is *never* shrunk automatically, so once it has grown to a very large size, you have to explicitly shrink it to get it back down to a manageable size.

Q2.  You can restrict the log file to a maximum size (say 200M: you need an amount at least the size of the single largest transaction you will run).  This works great for dbs in Simple mode.
For non-simple dbs, you must make sure that you backup the log frequently enough so that whatever space you assign is sufficient to meet all logging requirements.

Q3.
BACKUP LOG databaseName WITH TRUNCATE_ONLY
DBCC SHRINKFILE (logicalLogFileName, 200)
    --* you can get the logical log file name using "exec databaseName.dbo.sp_helpfile" in Query Analyzer
--if it doesn't work, run the two commands again.
--When done with that, do a full backup of your db as you will have broken your tlog backup chain.

Q4.  I'm not sure specifically what you want to know.
0
 
richtreeAuthor Commented:
some comments from DG:
here is how to shrink the transaction log file in SQL7.
- in the database options, set the auto-shrink on for transaction log file
or
- run DBCC Shrinkfile (myDB_log, 500) , 500 means 500M, myDB_log should be the actually myDB's log file name.
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
If it is still doesn't work, you can use the alter database command, or restore the database into a new log file name.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
richtreeAuthor Commented:
I do not see SIMPLPLE or FULL mode, but I see the following options:
General - Database files - File properties: Automatically grow file -
File growth:
 In megabytes
 By percent: 10 (//current setting)
Maximum file size:
 Unrestricted filegrowth (//current setting)
 Restrict filegrowth (MB)
Similar with  Transaction Log setting as above.
Q#5. Based on the above choices, which option corresponds to SIMPLE mode, which to FULL mode?
Q#5. What impact should I be aware of before I 'shrink' the log file? Right now the log file take 7GB space while it actually is only 250MB. So if there is no negative impact, I certainly like to 'shrink' it for now though it will grow again.
Q#6. Any preparation/precaution should I take before I 'shrink' it?
Thanks a lot.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I do not see SIMPLE or FULL mode
check in the database general properties, not in the file properties of the database
0
 
richtreeAuthor Commented:
Will I lose any capability (eg restore to a point) if I manually 'shrink' the database?
What cautions should I take before 'shrink' it?
Thanks a lot.
0
 
Scott PletcherSenior DBACommented:
>> Will I lose any capability (eg restore to a point) if I manually 'shrink' the database? <<

If you shrink the log (either alone or as part of the whole db), yes, **from that point on** -- you could still do point-in-time recovery to before the shrink.  However, if you take a full backup of the db immediately after the shrink, then you will have a valid starting point for point-in-time recovery.
0
 
richtreeAuthor Commented:
thank you for your confirmation.
0
 
richtreeAuthor Commented:
Thank you all for your wonderful ideas.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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