Solved

turn off logging in mssql 2005

Posted on 2006-06-16
3
685 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
  • 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 125 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL - format decimal in a string 5 38
SQL Query 2 31
AD and SQL Server 2016 2 25
MS SQL query to show nearest date 6 32
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

809 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