Solved

turn off logging in mssql 2005

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

695 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