If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Shrink Log File

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: Pinoy in NJ
Posts: 103
Shrink Log File

My DB's recover model is SIMPLE. Is it OK to schedule a SHRINK FILE only on the log files regularly? Any GOOD vs BAD about my plan? I want to do this because the log files keeps on increasing.

Right now, the log file s on ENABLE AUTOGROWTH, FILE GROWTH = 10%, RESTICTED FILE GROWTH = 2,097,152.

TIA
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,459
The log file keeps growing, because there is some data load, update or purge that requires that much space as a single transaction. If no one is going to do anything about the large load/update/whatever, you may as well accept that the log file will grow to that size, and allow it to stabilize.
Reply With Quote
  #3 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
As MCrowly pointed out, your log file will likely quickly grow to the same size again, if there is a process running on your database that requires it.
Expanding the log file eats up resources and will make your queries run more slowly, so you don't want to shrink it on any regular basis.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,634
There is nothing good, just bad. DML operations are queued/suspended when the growth occurs, and with percentage-based setting each consequtive growth will be longer than the previous one.
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: Pinoy in NJ
Posts: 103
Quote:
Originally Posted by MCrowley View Post
The log file keeps growing, because there is some data load, update or purge that requires that much space as a single transaction. If no one is going to do anything about the large load/update/whatever, you may as well accept that the log file will grow to that size, and allow it to stabilize.
We use the server mainly as a staging DB for our BI. I have SSIS packages that runs weekly that imports data from our outside vendors and our AS400. I prep the data and transfer it to another SQL server. The main reason I want to shrink the log id because it is 35% the size of my data file which is approx 50GB.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2008
Posts: 138
As earlier mentioned if it's a regular thing - from a performance perspective it's better to leave the expanded size.
Do you need the space for any other transactions on other databases that may have the logs on the same drive?
__________________
---------------------------------
www.sqlserver-dba.com
www.dba-db2.com
Reply With Quote
  #7 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,791
Instead of scheduling a shrink, just drop the database and recreate it. That way you don't hit any of the problems, and can efficiently set the log file to whatever size you want.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #8 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Yoikes!
Drop and recreate the database Pat?
Have to take issue with you on this.
Assuming this is a production database...
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #9 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,791
If the database is simply a scratch space for BI where data is loaded, manipulated, then copied to another server then there is nothing of long term value except for the schema. It would be simpler and more efficient to drop the database and rebuild the schema than it is to truncate the tables (or heaven forbid to delete the rows) then shrink the log on a regular basis.

If there was some reason to keep the database around, then I question whether a scheduled truncation of the log makes any sense. Shrinking the log is a really poor choice with little or nothing that could justify doing it more often than annually or quarterly in my opinion. If you do something between one and four times per year that causes the log file to bloom in size by a factor of five or more, then there might be a good reason that would justify shrinking the log.

I'm pretty sure that you and I are on the same page. If there is data in the database that needs to be kept over time and the data is worth working to ensure that queries perform well, then shrink is probably a poor choice. If the data is volatile meaning you can refresh it at will without losing anything of value, then there is no need to keep the database and a drop/rebuild of the schema is a good fit. If performance is of no concern, then a shrink won't hurt anything (but I still wouldn't do a shrink).

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On