Circular logging

Question

Circular logging

Ed7

SSCrazy Eights

Points: 8519
More actions
January 8, 2007 at 1:34 pm

#114994

Hi, Can anyone tell me how to turn of the SQL Server 2000 circular logging?
Pls help. Thank you.
Ed

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

Are you talking about the "black box" trace?

The only other thing I can think of is that transaction logs in SQL Server are sometimes referred to as "circular". Of course transaction logging is on by default and can't be turned off.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 2

Hi I am newbee DBA. I run into a problems that everytimes I run a setup replication from Server A to replicate Server B.

The Server B SQL Server 2000 database log is keep growing until the disk is full.

So, I consulted with my company Senior DBA and she told me as follow:

Mrs. DORI [2:04 PM]:

then the log files should not grow

Mrs. DORI [2:04 PM]:

because circular logging is on

Mrs. DORI [2:05 PM]:

it should be truncating it

So, my guest the SQL Server 2000 in Server B circular logging is on. I try to follow what she mentioned about it. Please helps.

Thanksssss!!!

DCPeterson SSCoach Points: 15266 More actions · Answer 3

Let me guess, Mrs. Dori is a DB2 DBA, that's the term that they use for this...

Anyway what she is talking about is setting the recovery model to "Simple". I hope that she (or someone) understands the implications behind that decsion.

The easiest way to do this is to open Query Analyzer and run the following command:

ALTER DATABASE databaseName

SET RECOVERY SIMPLE

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 4

The SQL Server 2000 sweetwater database is already set to RECOVERY SIMPLE mode.

Here are the way I confirmed the database model:

1. Open Enterprise Manager

2. Right click the sweetwater database

3. Click on the OPTION tab.

Thank you.

Ed

DCPeterson SSCoach Points: 15266 More actions · Answer 5

OK, that should do the trick, however there are still several things to keep in mind.

1. Setting the recovery to Simple does not shrink the existing file. If you need to shrink the file use dbcc shrinkfile.

2. Setting simple recovery does not absolutely prevent the transaction log from growing. If there are long running transactions (index maintenance is a common example), the log file will grow to accomodate them.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

MohammedU SSCoach Points: 19644 More actions · Answer 6

Un-replicated transactions will also fill the log in transactional replication because sql will not truncate the log until the transactions replicated...

MohammedU
Microsoft SQL Server MVP

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

SQL ORACLE

One Orange Chip

Points: 27807

January 9, 2007 at 3:40 pm

#682222

Did you try

BACKUP TRAN dbName WITH TRUNCATE_ONLY?

noeld SSC Guru Points: 96590 More actions · Answer 8

You can't truncate the Log of a database in simple recovery mode

You can run CHECKPOINT though to get the expected effect immediately

Cheers,

* Noel

DCPeterson SSCoach Points: 15266 More actions · Answer 9

Actually you can... (I know, it's weird) however running that command will have no effect since the database is already set to use the Simple recovery model.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/