Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sharepoint Transaction Logs are too large

Posted on 2011-09-30
5
Medium Priority
?
464 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 800 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 200 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 800 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 800 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

688 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