0

I have only read permission to the database I'm querying in. The same query was working for since yesterday but today I am encountering the below error in sql server management studio.

The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Any thoughts or suggestions are much appreciated. Thanks in advance!!

Rob
  • 9
  • 1
  • 1
  • 3
  • The obvious option is to check the space first. If the transaction log has grown unchecked, then maybe consider - Is this a production database? Is it high volume that required transaction log backups? If not, then setting the database to simple would be a first option. – Anthony Horne Jun 17 '17 at 19:42
  • What do you get when you run `SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = N'tempdb'`? Are you using simple or full recovery method for tempdb? How large is the .ldf file for the tempdb database? Is the disk on which it resides full? Is autogrowth disabled? – Bacon Bits Jun 17 '17 at 20:05
  • When I run: SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = N'tempdb' ..............I get log_reuse_wait_desc = NOTHING – Rob Jun 17 '17 at 20:29
  • @BaconBits When I run: SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = N'tempdb' ..............I get the below...... name = tempdb log_reuse_wait_desc = NOTHING – Rob Jun 17 '17 at 21:02
  • @AnthonyHorne I checked the size by selecting properties of tempdb and it appears that total size is 55796.44mb and free space is 55763.09mb – Rob Jun 17 '17 at 21:34
  • What is the use-case for the database - high volume, development, light-weight, mission-critical? – Anthony Horne Jun 18 '17 at 14:19
  • @AnthonyHorne development – Rob Jun 18 '17 at 14:47
  • Then definitely change the database to SIMPLE (instead of FULL) AND shrink the Log Files and Log. Then re-run. – Anthony Horne Jun 18 '17 at 14:48
  • @AnthonyHorne it is already in simple. When I check the properties of tempdb, it has plenty of free space. However when I check the properties of the db I'm working on, it has just 270mb free space. Is that a probable cause that my query isn't finding enough space to run? – Rob Jun 18 '17 at 14:50
  • Can you try and back up the database? – Anthony Horne Jun 18 '17 at 14:53
  • Is there a specific query you are running that is causing this issue? – Anthony Horne Jun 18 '17 at 14:54
  • @AnthonyHorne I have only read access to this client database. I use it for getting data for my etl. So I'm trying to figure out what is the reason for my query failing with this error before I reach out to the client. – Rob Jun 18 '17 at 14:56
  • Yes, it a 2000 line query with 20 union all 's – Rob Jun 18 '17 at 14:58
  • Can the ETL be done as 20 individual queries? With much data, it needs to be staged somewhere, so I can understand the tempdb issue. – Anthony Horne Jun 18 '17 at 15:01
  • @AnthonyHorne considering I have only read access, and all this 20 unions will be then left joined 3 times to different set of queries., I doubt that. Also, when I run these queries each individually they are running fine. Also, Ive never had this issue earlier even when I did this for way more data .. I do this process everyday btw. – Rob Jun 18 '17 at 15:06
  • @Rob Sorry - out of ideas. – Anthony Horne Jun 18 '17 at 15:07
  • 1
    @AnthonyHorne hmm. Thanks much for your effort. If something triggers you later, do let me know. – Rob Jun 18 '17 at 15:09
  • @AnthonyHorne my one other question is .. for a query to run in db, does SQL server need the db also to have enough free space or tempdb free space is enough? – Rob Jun 18 '17 at 15:11
  • My gut feel says "No", i.e. tempdb is used for this - which supports the process of being read-only, but still being able to union (gotta go somewhere). Some interesting articles on this subject - https://stackoverflow.com/a/1595327/1662973 , https://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/ , https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx , https://social.msdn.microsoft.com/Forums/sqlserver/en-US/626cc456-9bb2-48e2-be5b-dda45765718a/ssms-query-causing-there-is-not-enough-space-on-the-disk-error?forum=transactsql – Anthony Horne Jun 18 '17 at 15:23
  • @AnthonyHorne sure, thanks much! – Rob Jun 18 '17 at 15:24
  • How much space is left on the drive where tempdb resides? – SchmitzIT Jun 19 '17 at 11:19

1 Answers1

1

tempdb support operations like sort or temporary tables.

If a big select with a huge sort operation happens, tempdb might increase unexpectedly.

First make sure that kind of operations are exceptionals, if not fix it or make sure users are sensitive about this subject.

Then restart the instance and shrink the tempdb data file to reduce it to a proper size.

Restarting the instance will produce a service stop, so make sure users are aware of the restart

Edit :

Also if the given ETL cannot or won't be changed, then this is considered as normal behavior and the server has to be properly configured/sized in order to support such transactions.

Hybris95
  • 2,286
  • 2
  • 16
  • 33
  • If the tempdb grows due to results of a query, restarting would only provide a temporary fix, as it'd happen again the next time the query is ran – SchmitzIT Jun 19 '17 at 11:20
  • Thats why he has first of everything to make sure these operations are exceptionals and to fix it before doing the correction (read the answer please) – Hybris95 Jun 19 '17 at 11:23
  • The answer is generic, it is not context-based. And yes I did – Hybris95 Jun 19 '17 at 11:25
  • This DBA has to point the difficulties he encounters with the given ETL to the developers or else the problem will come again. Maybe the OP should start making a question about optimizing his query if it helps – Hybris95 Jun 19 '17 at 11:27