Unable to shrink a log in "simple mode" db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Unable to shrink a log in "simple mode" db

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-03 : 14:24:01

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

38200 Posts

Posted - 2011-10-03 : 14:45:21

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

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-03 : 15:59:04

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

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-03 : 16:00:57

oops, I forgot to add, there is no replication on this server.

Regards
Paresh Motiwala
Boston, USA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-03 : 16:04:10

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

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-03 : 16:09:58

thanks....

Regards
Paresh Motiwala
Boston, USA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-03 : 16:42:35

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

--
Gail Shaw
SQL Server MVP

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-10-05 : 11:34:41

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-05 : 11:54:33

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources