Solved

turn off logging in mssql 2005

Posted on 2006-06-16
3
676 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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