D: drive is full and transaction log is full

Question

Post reply

D: drive is full and transaction log is full

Viewing 15 posts - 61 through 75 (of 86 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Brandie Tarvin (9/17/2010)

You can't shrink other databases while the disk is full. SQL Server requires space in which to do the shrinking, in which to add markers or throw data into tempdb or whatever it's doing.

Shrink is a logged operation. Hence there must be available space in the transaction log in order to shrink.

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

GilaMonster (9/17/2010)

Brandie Tarvin (9/17/2010)

You can't shrink other databases while the disk is full. SQL Server requires space in which to do the shrinking, in which to add markers or throw data into tempdb or whatever it's doing.

Shrink is a logged operation. Hence there must be available space in the transaction log in order to shrink.

Talk about your catch 22... especially since 90% of the shrink question seems to happen when you're out of space in the file / drive :w00t:.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Ninja's_RGR'us (9/17/2010)

Talk about your catch 22... especially since 90% of the shrink question seems to happen when you're out of space in the file / drive :w00t:.

My personal favourite is people trying to shrink a full transaction log. I want to shout at them sometimes 'It's full! There's no free space in it! You need to make it bigger, not smaller!'

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

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 4

GilaMonster (9/17/2010)

Brandie Tarvin (9/17/2010)

You can't shrink other databases while the disk is full. SQL Server requires space in which to do the shrinking, in which to add markers or throw data into tempdb or whatever it's doing.

Shrink is a logged operation. Hence there must be available space in the transaction log in order to shrink.

Thank you for the clarification, Gail. It helps.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Leonard Rutkowski SSCrazy Points: 2666 More actions · Answer 5

Leonard Rutkowski

SSCrazy

Points: 2666

September 17, 2010 at 7:28 am

#1222974

I must be spacing out. read through the posts, and didn't see the previous suggestion. mea culpa. 🙂

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 6

GilaMonster (9/17/2010)

Ninja's_RGR'us (9/17/2010)

Talk about your catch 22... especially since 90% of the shrink question seems to happen when you're out of space in the file / drive :w00t:.

My personal favourite is people trying to shrink a full transaction log. I want to shout at them sometimes 'It's full! There's no free space in it! You need to make it bigger, not smaller!'

Gail, if I happen to be one of those people, you have my permission to thwack me soundly upside the back of the head with a cricket bat. Please, correct me if you catch me saying something stupid.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Brandie Tarvin (9/17/2010)

GilaMonster (9/17/2010)

Brandie Tarvin (9/17/2010)

You can't shrink other databases while the disk is full. SQL Server requires space in which to do the shrinking, in which to add markers or throw data into tempdb or whatever it's doing.

Shrink is a logged operation. Hence there must be available space in the transaction log in order to shrink.

Thank you for the clarification, Gail. It helps.

No need to be sorry to help out... and on the plus side you're going to be better at it so I fail to see a downside here!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Brandie Tarvin (9/17/2010)

GilaMonster (9/17/2010)

Ninja's_RGR'us (9/17/2010)

Talk about your catch 22... especially since 90% of the shrink question seems to happen when you're out of space in the file / drive :w00t:.

My personal favourite is people trying to shrink a full transaction log. I want to shout at them sometimes 'It's full! There's no free space in it! You need to make it bigger, not smaller!'

Gail, if I happen to be one of those people, you have my permission to thwack me soundly upside the back of the head with a cricket bat. Please, correct me if you catch me saying something stupid.

Well you just did... you're not THAT dumb (uninformed might be a better choice of words).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

GilaMonster (9/17/2010)

Ninja's_RGR'us (9/17/2010)

Talk about your catch 22... especially since 90% of the shrink question seems to happen when you're out of space in the file / drive :w00t:.

My personal favourite is people trying to shrink a full transaction log. I want to shout at them sometimes 'It's full! There's no free space in it! You need to make it bigger, not smaller!'

Hmm, makes you wonder if we need to make another article about dealing with severe problemes at work under pressure. And by we I mean you :hehe:.

hallhome SSC Veteran Points: 245 More actions · Answer 10

Update: I took Gail's advice and moved tempdb to another drive and recycled SQL Server. The database in question was able to recover and I was able to shrink its transaction log. I reclaimed 250 GB of space on the drive via these actions. Going forward I am going to implement a revcovery scheme that is more in tune with the usage of this database.

While I am an experienced DBA (Oracle, DB2, Teradata, Sybase) this is my first foray into MS SQL Server and the Windows OS. Each DBMS has its own nuances when it comes to backup/recovery, and I turned to your experience so that I would not put myself in a worse postion that I already was. I greatly appreciate your helpful (and sometimes spirited!) suggestions and I'm sure I will return in the future with issues.

Kathryn

WayneS SSC Guru Points: 95405 More actions · Answer 11

hallhome (9/17/2010)

Update: I took Gail's advice and moved tempdb to another drive and recycled SQL Server. The database in question was able to recover and I was able to shrink its transaction log. I reclaimed 250 GB of space on the drive via these actions. Going forward I am going to implement a revcovery scheme that is more in tune with the usage of this database.

While I am an experienced DBA (Oracle, DB2, Teradata, Sybase) this is my first foray into MS SQL Server and the Windows OS. Each DBMS has its own nuances when it comes to backup/recovery, and I turned to your experience so that I would not put myself in a worse postion that I already was. I greatly appreciate your helpful (and sometimes spirited!) suggestions and I'm sure I will return in the future with issues.

Kathryn

Kathryn,

First of all, I'm glad these actions worked for you.

Secondly, Excellent idea on determining what the recovery scheme should be. In order to prevent unnecessary growth until you do that, you ought to consider either start performing transaction log backups, or setting the recovery model to simple.

And finally, I'm glad that you realized you could make it worse, and that you turned to us.

Looking forward to seeing more of you in the future (but, hopefully, without database down issues!)

Edit: Oh - thank you for reporting back for how you handled it, and then all is better.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 12

Steve Jones - SSC Editor

SSC Guru

Points: 728159

September 17, 2010 at 10:29 am

#1223163

Great advice from Wayne, and if you search for Gail, she has a great article on managing transaction logs on this site.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

Ninja's_RGR'us (9/17/2010)

Hmm, makes you wonder if we need to make another article about dealing with severe problemes at work under pressure. And by we I mean you :hehe:.

Thank you for volunteering!

Now, should that come before or after the rewrite of 'Managing transaction logs', before or after the article on recovering from a suspect database annd before or after the editorial on asking good questions?

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

hallhome (9/17/2010)

Update: I took Gail's advice and moved tempdb to another drive and recycled SQL Server. The database in question was able to recover and I was able to shrink its transaction log.

Oh good. Always nice to have a positive outcome on these kinda questions.

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

WayneS SSC Guru Points: 95405 More actions · Answer 15

GilaMonster (9/17/2010)

Ninja's_RGR'us (9/17/2010)

Hmm, makes you wonder if we need to make another article about dealing with severe problemes at work under pressure. And by we I mean you :hehe:.

Thank you for volunteering!

Now, should that come before or after the rewrite of 'Managing transaction logs', before or after the article on recovering from a suspect database annd before or after the editorial on asking good questions?

Before, before, before, but after the one on the woes of being abused on the forums. 😀

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2