Turn of logging on SQL maintenance plans

Justin

I am running a SQL maintenance job on a 40 GB database which performs
optimizations by re-orginizing data and indexes pages. After the job
is finished, a separate job peforming a SQL transaction log backup is
run on the same database, which produces a 30 GB transaction log backup
file. Is there any way to turn off logging during the maintenance
plan, so that when the transaction log backup occurrs it will not
produce a large backup file?

Jul 10 '06 #1

Subscribe Reply

6263

Hugo Kornelis

On 10 Jul 2006 12:32:46 -0700, Justin wrote:

>I am running a SQL maintenance job on a 40 GB database which performs
optimization s by re-orginizing data and indexes pages. After the job
is finished, a separate job peforming a SQL transaction log backup is
run on the same database, which produces a 30 GB transaction log backup
file. Is there any way to turn off logging during the maintenance
plan, so that when the transaction log backup occurrs it will not
produce a large backup file?

Hi Justin,

You can't turn off logging completely, but you can tell SQL Server to
overwrite parts of the transaction log that are not needed for restore
or repair in case of emergency. You do this by setting the recovery
model to simple.

BEWARE: By setting the recovery model to simple, you forfeit the ability
to restore your database to a point in time. To minimize potential for
data loss, you should switch back to full recovery and take a full
database backup (in that order!!) as soon as the maintenance job is
done.

--
Hugo Kornelis, SQL Server MVP

Jul 10 '06 #2

Erland Sommarskog

Justin (ju************ *@hotmail.com) writes:

I am running a SQL maintenance job on a 40 GB database which performs
optimizations by re-orginizing data and indexes pages. After the job
is finished, a separate job peforming a SQL transaction log backup is
run on the same database, which produces a 30 GB transaction log backup
file. Is there any way to turn off logging during the maintenance
plan, so that when the transaction log backup occurrs it will not
produce a large backup file?

That depends. If the database goes bad in the middle of the day, because
of hardware error, or a serious human error, what is your requirement for
recover from the disaster? Are you content with restoring from the
the most recent full backup? Or do you want to be able to recover to a
point as close to the disaster to minimise data loss?

If the full backup is OK for you, then the answer is simple. Put the
database into simple recovery and forget all about the transaction log.

If you want up-to-the-point recovery, you want to avoid turning off
the transaction log, because if you do you break the log chain, and
until you have run a new full backup, you don't have the option to
recover to point in time.

What you could try to keep down the transaction log in size during
maintenance is to switch to bulk-logged recovery. This is a variant
of full recovery that logs less for some bulk operations like index
rebuilds.

You could also consider setting up a more intelligent job for reindexing.
There is little need to reindex a table with low fragmentation. DBCC
SHOWCONTIG can be used to examine the fragementation for a table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jul 11 '06 #3

Similar topics

4328

Order of Database Maintenance Plans (SQL 7/2000)

by: trotter | last post by:

I want to know if there is a "best-practice" for setting up Database Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want to know the order in which I complete the tasks. Do I complete optimization first, then integrity checks, then translog backup, then full backup??? OR is there a better order which should be used? Should I ALWAYS backup the transaction Log before I complete a full database backup, and if so, why?? ...

Microsoft SQL Server

1978

Alert forwarding - does it apply to Maintenance Plan reports?

by: Alexey Aksyonenko | last post by:

I know that SQL Servers can be set up to forward alerts to a single SQL Server. I am wondering if same would be true for the Maintenance Plan Reports. Basically, I want to have only one SQL Server on the network that has the SQL Agent Mail profile set up, and to use that server for all e-mail and/or pager notifications for all alerts and events raised by any of the SQL Servers on the network, including Maintenance Plan reports. Is this...

Microsoft SQL Server

1759

Maintenance job fails

by: Jim Andersen | last post by:

Hi there, Used the wizard to create a maintenance plan for a db. But it fails most of the time. I checked the log-files. At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It succeeds always. At 1.05 AM, for 1 second, it runs "integrity check", but most of the time it fails, and says that it couldn't switch to single-user because other users

Microsoft SQL Server

8754

Bizarre maintenance plan failure (db backup)

by: nielsonj1976 | last post by:

I am getting a failure on the db backup job of one of my maintenance plans. It is coming back with the generic error message of, "sqlmaint.exe failed. (Error 22029). The step failed." I then checked the Database Maintenance Plan History page, but this shows all the steps having run successfully. If I check the drives for the actual backup files, they exist and look healthy too!

Microsoft SQL Server

1380

Where are the maintenance plans stored in SQL Server 2005?

by: Larry Maturo | last post by:

I noticed that in SQL Server Management Studio, under Management, there is a Legacy folder with maintenace plans created by adding entries to the system tables dbo.sysmaintplan_* tables (i.e. all the maintenace plans created in sql server 2000). Where are the new maintenance plans kept? Is there any API interface to it? -- Larry Maturo

Microsoft SQL Server

2526

Cannot turn on logging for tables with names > 18 chars

by: RdR | last post by:

Hi, I am using Q Replication, I need to set to logging to capture changes on a table but the table has more than 18 characters for the name, I looked at the docs, it mentioned that table names with more than 18 characters are allowed in DB2 V8 but when you turn on logging, it will not turn on logging. Is there a work around to this? Thanks

DB2 Database

255

Logging!!?

by: Netsharp Ninja | last post by:

What is the best of loggin in .NET I am used to use logging into a file with Java but I see many in .NET use event logging (Well I have not seen other techniques) From my old days(Client-Server) I am also aware of using Database for this purpose. Can you tell me if anybody cares about logging and what is the strategy adopted for a Enterprise application.

C# / C Sharp

1462

Modifying SQL Server 2000 Maintenance Plans through Scripting

by: pk | last post by:

I have a problem that I would like to resolve through scripting, but I'm not sure how to go about it (or if it's even possible.) We're trying to work around a bug ( http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k ) that requires us to split up our maintenance plan into two parts. In the first, we backup "All User Databases". This runs fine. The second maintenance plan backs up the transaction logs and then...

Microsoft SQL Server

1245

Deleting a maintenance plan

by: Neil | last post by:

Using SQL 7, going into EM, and clicking on one of the databases, I see two maintenance plans listed. One of them is active, and the other has no options checked and nothing schueduled. How can I delete the unused maintenance plan? Thanks! Neil

Microsoft SQL Server

9671

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10433

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10161

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

10000

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

9035

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7538

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6777

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5560

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4112

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp