?
Solved

turn off logging in mssql 2005

Posted on 2006-06-16
3
Medium Priority
?
714 Views
Last Modified: 2010-05-18
I want to turn off or turn off as much logging as possible for one of my db's running in 2005.
what commands should I run?  
0
Comment
Question by:gotaquestion
[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
  • 2
3 Comments
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16923832
Assuming you mean transaction logging you can set your database to simple recovery mode.
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 500 total points
ID: 16924797
In SSMS, right click on your database go to properties and change your recovery model to "SIMPLE"... this means that you will NOT be able to recover to a specific point of time.. So if you do a nightly Full Database backup at 11:59pm and something happens to your server at 3:30PM causing catastrophic failure you will only be able to recover your database to the state the night before.

If this is acceptable, then you can do that..

The questions I have are:

1.) What do you mean by logging? If it is transaction logging than do above.. (You will still be logging activity to a transaction log but it will be truncated on regular intervals.. This is the way SQL works to protect you in the event of a suddent stop your server or a desire to rollback a transaction... you can't stop that but you can stop how long it is kept and to what degree it is logged by changing to SIMPLE recovery mode)

2.) What is the reason? If it is performance, you need to look at a bigger picture to improve this.
0
 
LVL 1

Author Comment

by:gotaquestion
ID: 16925564
i have a giant 300GB db that grows 2 gigs a day from iis logs.  if it crashes I can just re import the iis log itself.  So I don't need the extra transactional logging - should lower my i/o's
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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 ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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

650 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