Join devRant
Do all the things like
++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatar
Sign Up
Pipeless API
From the creators of devRant, Pipeless lets you power real-time personalized recommendations and activity feeds using a simple API
Learn More
-
I'm no expert in MSSQL.
But from experience with Postgres/SQLite/MySQL, everything that doesn't fit into the predefined buffers and/or contains blob/binary data, will be written to a temporary file.
It seems in MSSQL this ist handled by a system resource space:
https://docs.microsoft.com/en-us/... -
@IntrusionCM hm strange then, guess will need to check the server configs but behavior worse seems like tmp data is getting written to the main file and then getting deleted but the file isn't shrunk.
-
@billgates did you check the link?
What you describe is what I would expect from the autogrow feature.
In databases it is very common that file space is not reclaimed and just soft deleted / marked as deleted...
Based on that assumption, I found the following link:
https://support.microsoft.com/en-us... -
@IntrusionCM yes it said tempdb so tempdb should be a seperate file on the DB drive right? So there's like db1.dat and tempdb.dat?
The issue I see is that db1 somehow grew by 80Gb but it's all unallocated now.
Whereas I'm thinking that 80gb should be part of a file that houses tempdb, sorta like windows virtual memory or I guess the Linux swap file? -
A few things will cause you to use disk space invisibly:
1. Swap
2. Functions backed by filestreams
Most inserts will use disk space, with a few notable exceptions.
If you want to avoid using disk space, have a look at designing memory-optimized tables: https://docs.microsoft.com/en-us/...
If you want to go totally off the reservation, you can also use memory mapped files in embedded code to write table backing interfaces to operations. That's a bit excessive though. -
@SortOfTested but the only thing I did was select * from (select ... From ... Where ...)) And seems this blew up the DB file by 80gb but now all of that is unallocated.
To me it looks like a tmp DB though not sure if it is.
In mongo I know you have to tell the driver explicitly to use hd as a buffer but even then I don't think it blows up the actual DB file?
In MSSQL, can SELECT JOIN (SELECT ...) queries actually end up using disk space rather just RAM?
I ran one of those last week and now am wondering if that's the root cause of a huge DB file with a lot of unallocated space.
Seems it may just use an existing DB file and expand it (without cleaning it up afterwards) but not sure...
Any ideas on how so this works and well show to make it so disk space used for queries get cleaned up properly or easier to just shrink or remove without touching the DB file there's actually used for persistence?
question
no space left
mssql