Ldf file size is more then mdf file size -Publisher DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vinay - 25 Mar 2008 14:56 GMT

Morning All,

At production on publisher Database, on friday evening our log reader job
failed, I checked on sunday morning, till that time it occupies all the disk
space, and what I found is
backup size becomes 4times more then actual. and ldf size becomes 2/3 times
more( it used to be less then mdf size),
After restarting the logreader agent- job, the backup file size becomes
normal, but my ldf file size is still high.... which is a my concern... this
is occuping most of my disk space.

Please suggest me how to handle this, as replication is going on, and i
think its not good to truncate the log.

thinks are like this:
generally:
mdf: 4GB
ldf: 3.8GB
bak: 4GB

now
ldf file size is : 8.8GB

Please suggest. Its critical for me.

Signature

Thank you.
Regards,
Vinay Thakur

Reply to this Message

Vinay - 25 Mar 2008 15:07 GMT

can I shrink the ldf? ... would it impact on my replication?

*** IT IS A PRODUCTION, PUBLISHER DATABASE

Signature

Thank you.
Regards,
Vinay Thakur

> Morning All,
>
[quoted text clipped - 20 lines]
>
> Please suggest. Its critical for me.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 25 Mar 2008 15:12 GMT

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

> can I shrink the ldf? ... would it impact on my replication?

Reply to this Message

Vinay - 25 Mar 2008 16:05 GMT

I could see this in the link:
Unreplicated transactions
The transaction log size of the publisher database can expand if you are
using replication. Transactions that affect the objects that are replicated
are marked as "For Replication." These transactions, such as uncommitted
transactions, are not deleted after checkpoint or after you back up the
transaction log until the log-reader task copies the transactions to the
distribution database and unmarks them. If an issue with the log-reader task
prevents it from reading these transactions in the publisher database, the
size of the transaction log may continue to expand as the number of
non-replicated transactions increases.

So I think I could shrink the log file, just want to confirm will it affects
replication?

Thank you all for your valuable suggestions.

Signature

Thank you.
Regards,
Vinay Thakur

> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> > can I shrink the ldf? ... would it impact on my replication?

Reply to this Message

Andrew J. Kelly - 25 Mar 2008 15:16 GMT

Use DBCC SHRINKFILE to shrink the log file. This can be done in production
and should not normally be done except in situations like this.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> can I shrink the ldf? ... would it impact on my replication?
>
[quoted text clipped - 27 lines]
>>
>> Please suggest. Its critical for me.

Reply to this Message

Vinay - 25 Mar 2008 15:39 GMT

Hi Andrew,
some more inputs, DB compatibility level is 6.5 and I am having sql server
2000 standard edition - 8.0.818,

So can i shrink the log file....

what is the good time to do so?
till what size shall i shrink?

my previous DBA told me to do all the activity from entrprice manager.

Signature

Thank you.
Regards,
Vinay Thakur

> Use DBCC SHRINKFILE to shrink the log file. This can be done in production
> and should not normally be done except in situations like this.
[quoted text clipped - 30 lines]
> >>
> >> Please suggest. Its critical for me.

Reply to this Message

Andrew J. Kelly - 25 Mar 2008 20:21 GMT

I have never run a db in 6.5 compatibility mode so I could not say for sure.
I find it hard to digest that people still use 6.5 or remnants of these
days. If your previous DBA said to always use EM then he wasn't a very good
DBA so maybe it is a good thing he is gone:). You can shrink from EM but
make sure to shrink only the file not the database.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Hi Andrew,
> some more inputs, DB compatibility level is 6.5 and I am having sql server
[quoted text clipped - 47 lines]
>> >>
>> >> Please suggest. Its critical for me.

Reply to this Message

Vinay - 25 Mar 2008 20:38 GMT

Thank you very much Andrew,

yeah I will shrink only log file and that to I will do this on non
production hour(evening time) before full backup starts,
before runnint the shrink file i will check
dbcc opentran(databasenm) and make sure that there should not be any open
transaction present on this db.

Also Andrew I abserved that the output of
dbcc loginfo('DBname')
shows status of '0' for last some LSN's so i think there should not be any
problem to shink file, we are not doing any truncation on log so it should be
safe.

Hope I am clear and going on right direction.

Thanks again.
Vinay

Signature

Thank you.
Regards,
Vinay Thakur

> I have never run a db in 6.5 compatibility mode so I could not say for sure.
> I find it hard to digest that people still use 6.5 or remnants of these
[quoted text clipped - 53 lines]
> >> >>
> >> >> Please suggest. Its critical for me.

Reply to this Message

Andrew J. Kelly - 26 Mar 2008 00:38 GMT

Yes it sounds like you should be fine. Good luck.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Thank you very much Andrew,
>
[quoted text clipped - 79 lines]
>> >> >>
>> >> >> Please suggest. Its critical for me.

Reply to this Message