Dropping temporary tables on restore
-
We have a database which continues to grow steadly with each import and export creating a table. It contains maps which are quite hungry on space. However if you view the database through EM (table info) only some of the tables are shown i.e. none of the import and export tables are shown.
Is there a restore mechanism which would drop the temporary tables? I could then see if the mapping database was damaged by this.
The supplying company designed the database to work in Oracle (where they do cleared down the temporary tables) but have no mechanism to do this in SQL Server.
Madame Artois
-
Are you restoring a database from a backup or are you importing data using DTS or some other ETL-like tool. Are the import and export tables like staging tables?
Greg
Greg
-
If they are true temporary tables they reside in the 'tempdb' database. Every SQL Server restart autmatically drops 'tempdb' and recreates it.
Hence all true temporary tables or objects in 'tempdb' will disappear at every SQL Server restart.
Space usage of 'tempdb' will go back to normal after a restart because the actual DB files are 'recreated'
If in Enterprise Manager you can't see a table - the table does not exist (or in the tempdb special case, is a true temporary table. Bevare that Temporary Tables (as well as big Table variables) are ALWAYS created in 'tempdb' an nowhere else even if you were in another DB when creating it).
Regards,
Hanslindgren
-
I will doing a test restore from a backup just to see if these 'temp' tables disappear. I have sent mail to the company to ask about clearing these tables down as I thought that they were not true temporary tables (as they weren't in tempdb).
The odd thing about seeing these tables in EM is that if you double click the database you can see and open these tables. If you click on the database and choose Taskpad then table info, you cannot.
Madame Artois
-
Okay, if they are not in tempdb they are not true temporary tables. Taskpad is a MS tool that relies on the db statistics and might have some errors if you are not patched enough.
A test restore will probably not make these tables you talk about disappear...
Try to run
DBCC UPDATEUSAGE(0)
when the DB is not overly used (since DBCC are quite powerful and performance consuming commands).
When it has finished run this script:
(which will list all your tables and how much space is associated with very table)
-----------------------------------------------------------------------------------------------------
-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_spacedecimal(15,2),
index_spacedecimal(15,2),
total_sizedecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
-----------------------------------------------------------------------------------------------------
Cheers,
Hanslindgren
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply