Solved

Sharepoint Transaction Logs are too large

Posted on 2011-09-30
5
462 Views
Last Modified: 2012-06-21
When reviewing our MSSQL Data folder, we noticed that our SQL Server Database Transaction Logs have grown too large in size. 1 file is 100 gigs and another is 85 gigs. Any recommendations on shrinking the logs to a reasonable level or maintenance that we should implement?  
0
Comment
Question by:Emi975
[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
  • 3
5 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 200 total points
ID: 36892957
You must backup the T-logs first before you can shrink the files. Please see more at:

backup t-log
http://msdn.microsoft.com/en-us/library/ms191284.aspx

shrink t-log
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

general backup info
http://msdn.microsoft.com/en-us/library/ms186865.aspx
0
 
LVL 5

Assisted Solution

by:MrNetic
MrNetic earned 50 total points
ID: 36892962
Hi, if you don't run transaction log backups, and are not interested in point in time recovery, you can alter database recovery model to simple and shrink the log to 1024MB (do in this order).
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 200 total points
ID: 36892996
Simple or Full recovery mode you still need to backup before truncate - is just that in Simple recovery mode you have less logging.

BTW the truncate_only or no_log options are removed in next SQL 2008 version.
0
 

Author Comment

by:Emi975
ID: 36893521
Dumb Question and thank you for your responses above. I will do backups of all the transaction logs  and databases before anything with Management Studio.

After my backup, I would just like the transaction log changes to be incorporated into the database and have a new transaction log started. How does a new transaction log begin or how do I make all previous transactions permanent. I think I am not asking this the proper way but my goal is:
Database file: mdf.
Transaction log: new file, that starts accounting for transactions starting today.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 200 total points
ID: 36893631
I don't think you can control that because SQL does it's own implicit "CHECKPOINT" that COMMITs all uncommited transactions from t-log to data(mdf) file.
As far as adding and running with 2 phisycal t-log files for a database this is not a good idea unless mandatory because yo are running out of space on the t-log drive.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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