Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 06-06-2009
Member
 
Join Date: Jan 2009
Posts: 721
Cannot shrink transaction log file in in sql

There is an sql server running on windows 2003. The disk space is around 1 tetrabyte. There are 2 database on the system. The first is for production and the second one is used for test environment. The transaction log file is growing unexpectedly. It has grown to an extent that only some mb's are left out it. I tried to shrink to get some place but I failed. Any suggestions really appreciated.
Reply With Quote
  #2  
Old 06-06-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Cannot shrink transaction log file in in sql

The expansion of log file can make you database unstable. It can grow upto an un acceptable limit. Each database has one data file and log transaction file. Normally your server physically stores data in the data file. All the modifications and transaction performed for each database is store in transaction log file. The transaction log files grows in units of virtual log files. Some time they grow to extent that you can lose your disk space. To reduce the size of transaction log you will need to truncate the interactive transaction in your transaction log and shrink it.
Reply With Quote
  #3  
Old 06-06-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Cannot shrink transaction log file in in sql

You can prevent transaction log file to grow unexpectedly and eat up your disk space. In order to do this you will need to perform some actions listed below. To avoid automatic expansion of the transaction log file, set the size of the log file so that it cannot increase to large value. Configure the automatic expansion by using memory units.
Reply With Quote
  #4  
Old 06-06-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Cannot shrink transaction log file in in sql

It is necessary shrink the log file other wise it will effect your database. You shrink the transaction file in your sever to remove unused pages. Is is very important to backup your database before shrinking the transaction log file. You can shrink it manually and get more disk space. First to backup the file run Transact -SQl statement similar to below : -
  • BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'

After that to shrink file run the below statement : -
  • DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Cannot shrink transaction log file in in sql"
Thread Thread Starter Forum Replies Last Post
Impossible to complete the transaction, the file opens in Windows Explorer Abbyss Operating Systems 4 16-12-2010 07:29 PM
How to shrink .avi file Maranello Windows Software 3 08-08-2009 02:03 PM
How create iso file with Dvd shrink Damodar Windows Software 3 31-07-2009 03:08 PM
Compact or Shrink down excel file Visala Windows Software 3 28-07-2009 05:21 PM
How to shrink SQL server db log file Elijah Software Development 3 05-06-2009 06:16 PM


All times are GMT +5.5. The time now is 01:12 PM.