SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrinking the log file does not reduce size


Shrinking the log file does not reduce size

Author
Message
IT researcher
 IT researcher
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1798
After detaching and attaching the database when i run query nothing returns. So it is solved.
But while running dbcc opentran i am getting output like following.
Transaction information for database 'Reg_test'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (103448:85:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Also if i take the ldf backup and then try to shrink then ldf is not shrinking.I think it is due to the open transaction related to replication.
So how it can be solved?What does above message means?It's urgent plz help.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90927 Visits: 45284
Not an open transaction (and detaching and attaching would never solve that, detaching and attaching a database won't change anything within the database)

Either the database has had replication in the past (possibly snapshot replication with the bug mentioned in the article), or has been restored from a database that was replicated. SQL still thinks that everything in the log needs to be replicated but since there's no log reader that will never happen, hence the log is never truncated and will grow until it fills the disk

If you are absolutely sure that there's no replication, then you can do the following:

Create a transactional replication publication
Publish a single article
Stop the log reader
run sp_repldone
Drop the transactional replication publication that you created

Now run DBCC OPENTRAN again and there should be no reference to distributed and non-distributed LSNs

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


IT researcher
 IT researcher
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1798
It had replication set in it about 6 months back. But now it is removed.
As you said i can add replication once again and remove it.But it is a production server so i have to take care while doing it.
So is there any other method?
What if i change recovery model to simple and then shrink the log file and then once again change to full recovery model?

Also if i have to add replication again then i do have some doubts. As this is sql server express so it will act only as subscriber.Also is it ok if i add replication to one new table in that database?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90927 Visits: 45284
IT researcher (5/3/2013)
As you said i can add replication once again and remove it.But it is a production server so i have to take care while doing it.
So is there any other method?


None that works reliably. I suppose you could export the entire DB and recreate it, but that would be rather a large waste of time.

You just need to replicate one table, can be the smallest table in the DB, doesn't matter. It's just so that SQL will clean up the replication properly

What if i change recovery model to simple and then shrink the log file and then once again change to full recovery model?


No effect whatsoever, the log is not waiting for a log backup, it's waiting for replication. Changing the recovery model will leave you a DB in simple recovery waiting for replication to clear the log and shrinkfile will do nothing.

Please, read the article I references above, most of this is explained in there.

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


IT researcher
 IT researcher
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 1798
Whether any other tweaks available which can help me to remove replication related data from it? The problem is now i need sql server standard to add a publisher to that database.
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40992 Visits: 38567
What version (edition) of SQL Server ar you currently using?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90927 Visits: 45284
IT researcher (5/3/2013)
Whether any other tweaks available which can help me to remove replication related data from it?


It's not that there's replicated data, there's replication markers left in the log. There are no 'tweaks' that fix that. You need to persuade SQL to clear that out and since it thinks that the DB's not replicated about the only way is to create replication first.
Got a dev edition that you can move the DB to for fixing?

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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90927 Visits: 45284
Lynn Pettis (5/3/2013)
What version (edition) of SQL Server ar you currently using?


Express. Was stated earlier.

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


Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40992 Visits: 38567
GilaMonster (5/3/2013)
Lynn Pettis (5/3/2013)
What version (edition) of SQL Server ar you currently using?


Express. Was stated earlier.


Thank you. I looked earlier and just could not seem to find it. Just another snake in the grass.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40992 Visits: 38567
Your database isn't really that big compared to others. Another choice you could make if you have the maintenace window do it is this:

1. Script the database (all of it)
2. Create a new empty database using the scripts from above
3. Transfer the data from the old database to the new database
4. Drop the old database (or rename it)
5. Rename the new database to the old datbase name.


You can test this multiple times leaving off steps 4 and 5 until you feel confortable with what needs to be done.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search