Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Transaction log of 96 gig

Author  Topic 

deanfp
Starting Member

26 Posts

Posted - 2012-06-25 : 06:47:59
Hi

Our transaction log is 96 gig in size. We are running an e-commerce site. I've run maintenance on the SQL database and all tables are a good size.

However I'm not that knowledgeable on log files. How can I reduce the size of this log file without losing or compromising data?

In SQL Management Studio I have right cliked on the database and shrunk the database but this log file is now nearly 100 gig.

Any ideas would be welcome.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-06-25 : 07:06:35
I'm assuming you're in FULL RECOVERY mode. Maintain the size via a) sizing exercise b) regular LOG BACKUPS.
Shrink is OK for a one-off scenario - but as a regular way to maintain the size - I'd avoid.
There are a number of different ways of estimating size required for log files - this will depend on activity.
One useful method - is to identify the largest index and multiply by 1.25 - on the basis you're issuing a REBUILD

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-06-25 : 08:40:04
You should read this article:
Managing Transaction Logs
http://www.sqlservercentral.com/articles/Administration/64582/





CODO ERGO SUM
Go to Top of Page

prett
Posting Yak Master

212 Posts

Posted - 2012-06-26 : 01:27:24
Log file size get grow due to many reasons like Uncommitted transactions, Extremely large transactions, Unreplicated transactions. To resolve this issue, you first find out the main cause of big log file size. After that use solutions that is provided in this article: http://www.recoverdatabase.blogspot.in/2012/04/what-to-do-if-sql-server-log-file.html
Go to Top of Page

deanfp
Starting Member

26 Posts

Posted - 2012-06-26 : 04:16:32
Thanks will use these great links!
Go to Top of Page
   

- Advertisement -