2

In our solution we are creating some physical tables in "tempDB" for an activity. But recently we are facing an issue where these physical tables are getting deleted automatically. We would like to know the possible reasons/scenario behind this issue.

edit: Yes, I get that creating physical tables in 'tempdb' is not advisable but here I am only looking for possible reasons why it is getting deleted.

Prakazz
  • 421
  • 1
  • 8
  • 21
  • 4
    Why are you creating it in `tempdb` ? `tempdb` is re-created everytime `SQL Server` started – Squirrel Jun 12 '19 at 06:48
  • like @Squirrel said, never make any tables/objects in tempdb database. – Yeou Jun 12 '19 at 06:57
  • 1
    You shouldn't want to create tables in `tempdb`. If, for some valid reason you do want tables in `tempdb`, you can create them in the `model` database. When the sql server service is restarted, `tempdb` is recreated based on `model`. – HoneyBadger Jun 12 '19 at 07:18
  • `but here I am only looking for possible reasons why it is getting deleted.` As highlighted in my earlier comment, the `tempdb` is *re-created* when `SQL Server` started. It means it will drop the tempdb and create a new tempdb without your `physical tables` – Squirrel Jun 12 '19 at 08:02

3 Answers3

0

Wow - that is a really interesting thing to do. I am curious why you implemented it like that.

I take it that originally this strategy worked for you but now it doesn't? SQL server will grow the tempDB to an optimal size and then delete data from it but not shrink it. The tempDB may be mostly empty at any given point in time.

Maybe your tempDB is now running at capacity and something has to give. Possibly some change in the load - type of queries being run etc means that your tables are being wiped. Try just giving it more size or creating a second tempDB on another disk.

Gerard
  • 301
  • 2
  • 9
0

There are two types of temporary tables in MS SQL - local and global.

The deletion policy is the following:

  • local temporary tables (prefixed with #): these tables are deleted after the user disconnects from the instance of SQL Server
  • global temporary tables (prefixed with ##): these are deleted when all users referencing the table disconnect from the instance of SQL Server

The tempDB database tables are cleared out on startup as well.

There are other types of tables stored in the tempDB. One of them is called table variables (prefixed with @) and the other is persisted temporary tables (created without using any prefix).

Persisted temporary tables are deleted only when the SQL service is restarted.

Dávid Molnár
  • 10,673
  • 7
  • 30
  • 55
  • I have just been doing some reading. There are 3 and the last one must be what Prak is doing https://www.brentozar.com/archive/2016/05/creating-tables-stored-procedures-tempdb-permanently/ Relevant code following...... – Gerard Jun 12 '19 at 07:40
  • What is the third one? – Dávid Molnár Jun 12 '19 at 07:41
  • /* This one is only available during my session: */ CREATE TABLE #myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* This one is global, meaning it's available to other sessions: */ CREATE TABLE ##myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO /* You can create both of those at the same time. They're different. */ /* This one is just like a user table, but in TempDB: */ CREATE TABLE dbo.myTempTable (ID INT IDENTITY(1,1), Stuffing VARCHAR(100)); GO – Gerard Jun 12 '19 at 07:42
  • Yes, I understand now, thanks. I updated my answer. I could not find any other reason why SQL server would delete persisted temporary tables, but the cleanup on startup. – Dávid Molnár Jun 12 '19 at 08:17
0

From the docs:

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

This means that not only physical tables but also other objects like triggers, permissions, views, etc. will also be gone after a service restart. This is why you shouldn't use tempdb for user objects.

You can create a schema in your own database and keep an SQL Agent Job that deletes all it's tables every once in a while, so you can mimic a "temporary" physical table space to work around.

EzLo
  • 13,780
  • 10
  • 33
  • 38