0

I have a very complex SQL query that I execute in SQL Server Management Studio. As the query is running, I notice that my harddisk is filling up, until the point it's completely full (I'm talking a 10+GB increase in data).

I tried finding where this data is located, but can't find any large files and trying to find it by modified data through Explorer does not gives me thousands of folders and files. I went through a lot of them, but have a hard time identifying what I'm looking for.

So where does SQL Server typically hold all this data when executing a query?

ps. I know I need to do something about the query itself, but for now I need to know where data is stored.

Improve this question
2
  • 1
    In memory if it can, but typically spills over to TempDB, especially for large/complex queries. If your query updates/inserts/deletes then the log file will also be potential place. You can check this by right clicking on the database in question, then reports, then Standard Reports then Disk Usage. There should be a section (you need to expand it) that has data file growth events. You can use this to confirm which database.
    – Jonathan Fite
    Commented Jun 17, 2016 at 15:50
  • 1
    What is the your database recovery model ? Is the query a select only query (you are reading data) or a insert/update/delete (you are modifying the data) query ?
    – Kin Shah
    Commented Jun 17, 2016 at 15:52

3 Answers 3

Reset to default
1

Three suspects

  • tempdb
    A big query may use tempdb
    It may use it for spool, hash match, and spool
    Can cap the size of tempdb
    Can find this in SSMS

  • log
    If you are in full recovery mode then a select may use the transaction log.
    You can cap the size the transaction log but then the query just does not run.
    Can find this in SSMS
    Can change the recovery model in SSMS

  • swap file
    As SQL asks for memory and the server is out of physical memory then the OS will use the swap file. Not optimal. Cap the amount of memory available to SQL at like physical memory minus 1-4 gb.
    Can cap memory in SSMS
    Can cap page file at the OS
    I think it is in Control Panel

Improve this answer
0

Is your database in Recovery Model Full and do you run anything that changes data?

Then it's probably the Transaction log files, you can find their Location by right clicking on the database Name in the object Explorer, select Properties and on the Files page you can see the Path for the File Type "LOG".

Improve this answer
0

Is this SQL Server local to the machine in which you are running SSMS on or remote server? The page file could be causing issues.... but usually that is a set size, maybe you could shrink it... but yes typically you need to locate where the data file is and the log file is for the database you are working with or tempdb's and look at those...

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.