?
Solved

SQL 2000:  cannot Shrink Transaction Log File

Posted on 2008-10-13
4
Medium Priority
?
1,192 Views
Last Modified: 2012-05-05
Greetings, Experts.  I am in need to shrink my stubborn transaction log file.  A lot of space is being eaten up by my DB's transaction log and it needs to go down in size on my server.  For the last month, I've seen "a lot of growth" (for our company anyway) with the transaction log.  The MDF file is 234MB and the LDF is 5.1GB.

I am familiar with SQL Server, but not to the degree that will allow me to shrink this log and not bust something in the process.  This is a production database and as a result I am a nervous newbie to the idea of shrinking the log file.

So far, I've tried the following in Query Analyzer:  DBCC SHRINKFILE (databasename_Log, 0)

The above action took down my transaction file about a 1/2 GB.  

I seek dialog and solutions related to first taking my transaction log down in size.  Once at a lower level, I'd like to see how to keep it down.

In closing, I'm not highly versed in the intricate nature of the transaction log, although I do understand its purpose.  Can you assist me?  I thank you kindly.
0
Comment
Question by:todjklki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:todjklki
ID: 22707426
Update:  I just ran the DBCC SHRINKFILE (databasename_Log, 0) again, and noticed that the file size went to 1MB.  I'm not sure just what happened, but it would appear that I have lost my transaction log?  
Can anyone help me decipher what just happened?


0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 22707519
Well you have file size set to 0 there, so it reduced to the minimum.  If you wanted to keep a certain size then specify in the DBCC SHRINKFILE.  

I usually find I have to run DBCC SHRINKFILE twice to get it to work anyway, so now that you ran on the second time it just worked and did what it was told and reduce transaction log.  To maintain the size of the log to manageable level you can do frequent backups of transaction logs.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22707522
0
 
LVL 41

Assisted Solution

by:graye
graye earned 1000 total points
ID: 22713823
If you're up to a little bit of reading... take a look at this article that explains how log backups are used to curb the growth of the log file.   You'll need to start doing periodic log backups to prevent this from happening again.
http://home.hot.rr.com/graye/Articles/SQL_LogMaintenance.htm 
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question