Solved

Sharepoint Transaction Logs are too large

Posted on 2011-09-30
5
456 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
  • 3
5 Comments
 
LVL 39

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 39

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 39

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now