1

Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed due to this error and the server needed to be restarted. Can this be related to adding the indexes or it could be something totally else?

Client's server version:

  • Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Testing server version:

  • Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Developer Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Improve this question
3
  • Yes, it can be. Add details about servers versions into your question.
    – Denis Rubashkin
    Nov 13, 2018 at 13:24
  • 1
    jindrich, check how how to merge accounts as it appears you have 2 different ones you're attempting to use.
    – LowlyDBA - John M
    Nov 13, 2018 at 14:28
  • You can run DBCC OPENTRAN and see what's going on.
    – Terry Carmen
    Nov 13, 2018 at 18:18

1 Answer 1

Reset to default
5

An index create on a temp table will use space in the tempdb transaction log while the index is being built. So yes, the index could have caused the issue in the general sense that it added enough transactions to the log file that it ran out of space.

Transaction Log Disk Space for Index Operations

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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