SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Unable to shrink a log in "simple mode" db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 10/03/2011 :  14:24:01  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
Folks I ahve a database in "Simple" recovery mode. It is not being used at all and is there only for reference. The database is 5GB and the log file is 80GB, I cannot shrink it. Any ideas?
Regards
Paresh Motiwala

Regards
Paresh Motiwala
Boston, USA

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 10/03/2011 :  14:45:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
You may not be using it, but SQL sure is. SIMPLE recovery model just affects what happens after the transaction completes. It still uses the transaction log.

1. How are you performing the shrink? Did you get an error? Have you tried DBCC SHRINKFILE?
2. Show us the output of this query: select log_reuse_wait_desc from sys.databases where name = 'DbNameGoesHere'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 10/03/2011 :  15:59:04  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
Hi Tara
1. Yes, I used shrinkfile, shrinkdatabase and GUI. it comes out of the "shrink" operation in under a couple of seconds.

2. The output to the query is REPLICATION
STrange, this is not what it was doing earlier.
....
Thanks in advance for your help.
Paresh
quote:
Originally posted by tkizer

You may not be using it, but SQL sure is. SIMPLE recovery model just affects what happens after the transaction completes. It still uses the transaction log.

1. How are you performing the shrink? Did you get an error? Have you tried DBCC SHRINKFILE?
2. Show us the output of this query: select log_reuse_wait_desc from sys.databases where name = 'DbNameGoesHere'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Regards
Paresh Motiwala
Boston, USA
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 10/03/2011 :  16:00:57  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
oops, I forgot to add, there is no replication on this server.

Regards
Paresh Motiwala
Boston, USA
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 10/03/2011 :  16:04:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
You do have replication in place if the query returned that information. So you'll now need to investigate removing replication.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 10/03/2011 :  16:09:58  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
thanks....

Regards
Paresh Motiwala
Boston, USA
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/03/2011 :  16:42:35  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
http://www.sqlservercentral.com/articles/Transaction+Log/72488/

--
Gail Shaw
SQL Server MVP
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 10/05/2011 :  11:34:41  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
Thank you to all those who responded. The key word was replication. It seems this orphan database was taken from some other server where it was a publisher. IT was never used again. I had to remove the replication on this database by running sp_removedbreplication. I was able to shrink the database back from 90GB to 6GB.

Regards
Paresh Motiwala
Boston, USA
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 10/05/2011 :  11:54:33  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000