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 Tuesday, April 30, 2013 5:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:58 PM
Points: 270, Visits: 1,745
I have a database which had mdf size of 350 MB and ldf size 4.9 GB
When i try to shrink the log file it's not shrinking. I know shrinking a database is not good and it should not be done. But still i am trying to do it for shrinking the log file. The recovery model is set to FULL.
Then i followed some steps:
When i run dbcc SQLPerf(logspace) i found that logsize is 4932 MB and Log space used is 98.76%
So large amount of (98%) of log is using now.
Then i run this command use <databasename> dbcc loginfo
Now almost all VLF has status 2 which means all are in use.
then I tried to take log backup. After log backup also shrinking didnt reduce the size. Then i changed recovery model to simple and then tried shrinking.But this also didn't help. Also i ran dbcc opentran (database)
and found that no transaction is open now. So what is making the database which does not allow me to shrink the log size.How can i solve this?
Post #1447960
Posted Tuesday, April 30, 2013 5:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
Please run this:
select log_reuse_wait_desc from sys.databases where name = 'DBName'

Is this tempdb or user db?
After log backup, what is the VSL status? 0 or 2?
Post #1447962
Posted Tuesday, April 30, 2013 5:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:58 PM
Points: 270, Visits: 1,745
It's not a tempdb , it is my own user database.
When i run the query
select log_reuse_wait_desc from sys.databases where name = 'DBName'

I got output like
log_reuse_wait_desc
REPLICATION

But i don't have replication set to that database.
Post #1447968
Posted Tuesday, April 30, 2013 6:03 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 2:53 AM
Points: 1,101, Visits: 5,290
May be it is backup of a replicated database restored.

To fix this issue, create a dummy replication and remove it.

or try this :
sp_removedbreplication
Post #1447975
Posted Tuesday, April 30, 2013 6:04 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: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/


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 #1447976
Posted Tuesday, April 30, 2013 6:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:58 PM
Points: 270, Visits: 1,745
I tried sp_removedbreplication but it didn't help me to remove it.
Also under subscription in SMS i cannot see any replication. So from where it came? How it can be removed?
Post #1447992
Posted Tuesday, April 30, 2013 6:46 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: Today @ 5:50 AM
Points: 40,209, Visits: 36,618
Did you read the article I referenced? Specifically the section on replication and if necessary the kb article it links to?


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 #1447995
Posted Tuesday, April 30, 2013 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:58 PM
Points: 270, Visits: 1,745
Hi Gail shaw
Yes I saw that article. Right now i don't have replication set to the database. I think i had it a year before then i dropped all replication in the server. Now if i check under subscription i dont have any replication. I also checked under database.No where i am not able to find replication. In the link u have mentioned they told to run DBCC OPENTRAN(13) but in my case it returns "No active open transactions."
Also there is no meaning in running sp_repldone null, null, 0,0,1 as repliaction is not setup. When i run thsi i am getting error like "Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication."
So now please help me.
Post #1447999
Posted Tuesday, April 30, 2013 7:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
I suggest just deattach and attach it, so replication entry may be removed.
Post #1448007
Posted Tuesday, April 30, 2013 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:58 PM
Points: 270, Visits: 1,745
Detaching the database worked fine and now replication related problem solved.
Thank you
Post #1448033
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse