Log space 98% free but drive has no space

Question

Post reply

Log space 98% free but drive has no space

dopydb

SSCertifiable

Points: 6687
More actions
October 9, 2020 at 11:12 am

#3795417

hi folks
just after some advice
if the Log space has 98% reported free space but the physical drive has virtually no free space left
will trans logs for the db post ok?
cheers
m

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

Yes, the free Log space is within the allocation of the log file(s).

😎

Obviously, you should monitor the free space within the files and turn off any autogrowth for the files.

dopydb SSCertifiable Points: 6687 More actions · Answer 2

dopydb

SSCertifiable

Points: 6687

October 9, 2020 at 11:54 am

#3795431

cheers Eirkur! just testing some theory !

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Be careful of autogrowth here. Also, it's a good idea on any drive with auto-growth enabled, create an file called 'deletemeinemergencies' or similar. Make it big enough, say 20 or 50 mb, so that you can use it to easily reclaim a little space when the drive filled by accident. It makes recovery much easier.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

dopydb wrote:

hi folks
just after some advice
if the Log space has 98% reported free space but the physical drive has virtually no free space left
will trans logs for the db post ok?
cheers
m

I'd also suggest that you need to figure out what caused your log file to grow to the extent that it has and if it's still occurring. Whatever it is, it needs to be found and fixed.

Typically, it's one of two things...

A poorly written query with one or more accidental many-to-many joins (and they typically have a DISTINCT or even a GROUP BY in them to "get rid of the duplicate rows" they return).
Index Maintenance, especially if you're using supposed "Best Practice" methods of using REORGANIZE between 5 and 30% logical fragmentation. REORGANIZE isn't the tame, little, resource efficient kitty that a lot of people would have you believe that it is. It's REALLY tough on the log file especially on large indexes and can come close to producing log file entries almost twice the size as the data in the index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 5

Grant Fritchey wrote:

Be careful of autogrowth here. Also, it's a good idea on any drive with auto-growth enabled, create an file called 'deletemeinemergencies' or similar. Make it big enough, say 20 or 50 mb, so that you can use it to easily reclaim a little space when the drive filled by accident. It makes recovery much easier.

OOOOH! I like that idea! Why didn't I think of that! 🙂

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

For saving space, https://voiceofthedba.com/2014/12/01/creating-placeholder-files/

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

david.edwards 76768 wrote:

Grant Fritchey wrote:
Be careful of autogrowth here. Also, it's a good idea on any drive with auto-growth enabled, create an file called 'deletemeinemergencies' or similar. Make it big enough, say 20 or 50 mb, so that you can use it to easily reclaim a little space when the drive filled by accident. It makes recovery much easier.
OOOOH! I like that idea! Why didn't I think of that! 🙂

For the log file, it's real handy if (for example, you have a 100GB drive just for the log file), to allocate only 10 or 20GB and then setup an alert for when it grows. That way, you can have some time to find out what is causing the log file to grow. You could even make it so the alert runs code to see what's running to help narrow down the candidates or scare up some code that will tell you what everything that's currently running is using for logfile space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Steve Jones - SSC Editor wrote:

For saving space, https://voiceofthedba.com/2014/12/01/creating-placeholder-files/

Those are handy to help trick the network folks into thinking that you're using more space than you are so you have time to make a request for more space but that goes against my nature (Absolute honesty, etc). It also won't help code and won't help you find the problem.

For log files, I'd do like I stated above with alerts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)