Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
New Member

Q about SQL Transaction log best practice on CCM servers

Just come on board to a new position and taking a look over the CMCS servers I'm now supporting and had a question about the SQL Transaction logs on the servers and what's considered best practice out in the field.

I've found that the Transaction Logs for both the CCM0302 and CDR databases are growing unchecked, and don't appear to be truncated by BARS during backup.

Given I'm running BARS nightly, and any restore would be from a BARS restore, am I best off switching the DB's to simple logging instead of Full Logging? I realise I loose the ability to do a point in time recovery of the DB, but given I'm not ever likely to do that, instead just doing a full BARS restore, is there any issues in this change?

Haven't found anything in net searches regarding this topic so would be interested in hearing if anyone else has changed their logging settings or are running a seperate process to truncate the logs after a BARS Backup.

Cheers

2 REPLIES
New Member

Re: Q about SQL Transaction log best practice on CCM servers

Okay, so apart from managing to post my Q twice (damn those browser delays + refresh), I think I have the answer.

When BARS runs, it actually does a truncate of the CDR DB Tlog. It doesn't shrink it though. So if for whatever reason, the tlog has grown to a large size, it will remain that way unless you go into SQL EM and manually shrink it.

Should be no need for simple recovery to be set.

Re: Q about SQL Transaction log best practice on CCM servers

I think that for you is better to follow this procedure (you can do it also for art logs, just change cdr for art)

The following procedure is used to shrink these files:

##################################################################

Open a command prompt and type:

C:\>osql -E

1> use cdr

2> go

1> backup log cdr with no_log

2> go

1> dbcc shrinkdatabase (cdr)

2> go

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

13 1 368 96 360 360

13 2 96 63 96 56

(2 rows affected)

DBCC execution completed. If DBCC printed error messages, contact your

system administrator.

1> create table Test (test int DEFAULT NULL NULL)

2> go

1> insert into Test (test) VALUES (1)

2> go

(1 row affected)

1> declare @t integer

2> set @t = 0

3> use cdr

4> while (@t <= 100000)

5> BEGIN

6> update Test set test=1 where test=1

7> select @t = @t + 1

8> if ((@t % 500) = 0)

9> BEGIN

10> select @t

11> END

12> END

13> go

// you will see a lot of "(1 row affected)"

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

-----------

100000

(1 row affected)

(1 row affected)

1> drop table test

2> go

1> checkpoint

2> go

1> dbcc shrinkdatabase (cdr)

2> go

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

13 1 368 96 360 360

13 2 96 63 96 56

(1 row affected) DBCC execution completed. If DBCC printed error messages,

contact your system administrator.

#####################################################

233
Views
0
Helpful
2
Replies
CreatePlease to create content