Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sharepoint Transaction Logs are too large

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 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