can I shrink the database include log file, when database is a Transaction (Subscription) database

Question

Post reply

can I shrink the database include log file, when database is a Transaction (Subscription) database

MAK-1128556

SSCarpal Tunnel

Points: 4594
More actions
June 17, 2010 at 8:56 am

#233920

I am user 2005 with SP3, and try to shrink the database, db is in simple mode. even I stop the distributor agent job but db is not shrink including log file.

Viewing 15 posts - 1 through 15 (of 15 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

steveb.

SSC-Forever

Points: 46733

June 17, 2010 at 8:59 am

#1181622

how much free space is in the database file?

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 2

still I have 35GB free space, but the log file increasing very fast. I already add two files

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Sounds like you have a runaway process.

If there is that much transaction occurring in your database, you should consider implementing Full Backups.

Also, you need to find the process causing such growth.

Here is an article to help find the cause of your growth.

http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 4

I do not mind the growth, but want to shrink the log file, that is not happening

steveb. SSC-Forever Points: 46733 More actions · Answer 5

you wont be able to shrink it if there are open transactions.

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 6

I want to shrink the log file, I try to stop the distributor agent job, so tran going on, but when I run this

select name,log_reuse_wait, log_reuse_wait_desc from sys.databases

I can see db is use for replication

DB_Replicated 6REPLICATION

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

MAK-1128556 (6/17/2010)
I do not mind the growth, but want to shrink the log file, that is not happening

If you shrink it, it will just grow again. That can be extremely detrimental to performance. You need to be careful of vlfs and io that can cause poor performance when the log grows.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Is this the publisher? Is the log reader running? If not, the log will not be cleared and will grow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 9

this db is subscription db, i stop the log reader job and then run the shrink the db but it is not shrinking the log file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Don't stop the log reader. It has to be running (and running without error) to mark log space as reusable.

Are you sure (100% sure) that this is the subscriber, not the publisher? A subscriber shouldn't have a log reader. What does the following return?

DBCC OPENTRAN

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 11

I am 110% sure this db is a subscriber

Transaction information for database 'DB_Replicated '.

Oldest active transaction:

Replicated Transaction Information:

Oldest distributed LSN : (318309:27942:217)

Oldest non-distributed LSN : (318310:16285:1)

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

ALSO when I ran this

select name,log_reuse_wait, log_reuse_wait_desc from sys.databases

CRS5_OLTP_Replicated 6REPLICATION

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

MAK-1128556 (6/17/2010)
I am 110% sure this db is a subscriber
Replicated Transaction Information:
Oldest distributed LSN : (318309:27942:217)
Oldest non-distributed LSN : (318310:16285:1)

There is no way that this database is not published. It's configured as a transactional replication publisher and there have been transactions distributed in the past. It may be a subscriber, but it's also a publisher. Check that the log reader is running correctly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 13

Thanks Gail,

somehow(by mistake) this db is also use as publisher too, but I am not replication any data for that database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

Then drop the publication. By having it there (and I assume not having the log reader running) the log cannot be reused because it's marked for replication.

Once you've dropped the publication(s), run DBCC OPENTRAN again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass