Solved

Sharepoint Transaction Logs are too large

Posted on 2011-09-30
5
461 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

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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