1

I'm having problems with TempDb being full. Are there any system views I can see to diagnose what's using up TempDb and/or how much space is still available on it?

It would be awesome to also see the TempDb transaction size limit too if possible.

0

So far, I've found the below, using these pages:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-file-space-usage-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017

sys.dm_pdw_nodes_db_file_space_usage doesn't appear to hold any information about tempDb, so therefore we can only use sys.dm_pdw_nodes_io_virtual_file_stats.

The example in Microsoft's documentation doesn't explain what each file_id means, although the example they give filters on it. Until I can find any more information, I'll sum all files in TempDb as this appears to give the most sensible result (but it's a complete guess!).

        select sum(num_of_bytes_written) from sys.dm_pdw_nodes_io_virtual_file_stats
where database_name = 'tempdb'

I cannot find any documentation of transaction size limits for Azure DW Gen2.

This only applies to gen 1 - https://docs.microsoft.com/en-gb/azure/sql-data-warehouse/sql-data-warehouse-develop-transactions#transaction-size

Edit:

after further experimentation, it appears this query doesn't work either, the storage use always increases, so it appears to be more like a write counter. the size_on_disk_bytes never changes, so I'm not sure this would do it either.

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.