Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Shrinking the log file does not reduce size Expand / Collapse
Author
Message
Posted Friday, May 3, 2013 1:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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.
Post #1449073
Posted Friday, May 3, 2013 3:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1449099
Posted Friday, May 3, 2013 4:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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?
Post #1449115
Posted Friday, May 3, 2013 5:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1449130
Posted Friday, May 3, 2013 5:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:04 AM
Points: 265, Visits: 1,718
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.
Post #1449155
Posted Friday, May 3, 2013 7:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
What version (edition) of SQL Server ar you currently using?



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)
Post #1449189
Posted Friday, May 3, 2013 9:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1449266
Posted Friday, May 3, 2013 9:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 9:44 AM
Points: 42,822, Visits: 35,953
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 2008, MVP
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

Post #1449267
Posted Friday, May 3, 2013 11:03 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
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.



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)
Post #1449305
Posted Friday, May 3, 2013 11:10 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 23,299, Visits: 32,039
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.



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)
Post #1449307
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse