June 1, 2009 at 4:24 pm
I'm jumping into this (closed?) discussion because I have a similar problem: Big tempDB, some old temp tables.
When I run:
USE tempdb
SELECT id, OBJECT_NAME(id), rowcnt, o.create_date, o.modify_date
FROM tempdb..sysindexes i
JOIN sys.objects o ON i.id=o.object_ID
WHERE OBJECT_NAME(id) LIKE '#%'
I see some old big tables that won't drop:
DROP TABLE #T1_________________________________________________________________________________________________________________0000000548F2
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#T1_________________________________________________________________________________________________________________0000000548F2', because it does not exist or you do not have permission.
Does anybody know why I can't drop this table? It's not about permission because I logged as the administrator. I think it's about finding the table, because a SELECT won't work either.
I know the table is big (rowcnt) and old (modify_date).
Is there a way to drop it without having to restart SQL-Server?
Thanks a lot,
Luiz.
June 2, 2009 at 1:49 am
It is about permission. Only the connection that created a temp table can drop it.
See if you can run sp_spaceused on that table and see how much space it's taking up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 8:34 am
When I try to run sp_spaceused it says that the table does not exist in database 'tempdb' or is invalid for this operation.
Now, some of the tables are from Sep-2008. I don't think the connection that created them is still active.
Is there a way to drop them from another connection?
Thanks,
Luiz.
June 2, 2009 at 8:54 am
Only the connection that created them can drop them or query them.
See if you can query sys.dm_db_index_physical_stats for all the tables in TempDB. That might give you the page size.
The only time that a temp table persists after the connection that created it closes is when SQL caches it to avoid the cost of recreating. When it does that, it caches only one data and one index page and discards the rest. The row count may well be inaccurate.
Run sp_spaceused on the database (without any parameters), see how much of the space in the DB is actually in use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2009 at 9:28 am
You may have larger problems than tables lingering about TempDB if there are tables out there from 2008. Since TempDB is completely recreated every time SQL restarts I am betting this server is a bit behind on patches both at the SQL and at the OS level.
...all servers have to be down sometimes, but everyone is happier when the outage is planned.
June 2, 2009 at 9:59 am
dm_db_index_physical_stats shows page_count of 411689, 70779, 0 and other values.
sp_spaceused gives the following results:
database_size = 16813.50 MB
unallocated space = 4393.73 MB
reserved = 12615696 KB
data = 12597272 KB
index_size = 3976 KB
unused = 14448 KB
What do you think?
June 2, 2009 at 10:10 am
David,
Here is more information:
select create_date from sys.databases where name = 'tempdb'
2008-08-19 15:12:27.390
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
9.00.3042.00 SP2 Standard Edition (64-bit)
What do you think?
June 2, 2009 at 8:37 pm
I would suggest getting to at least SP3 for SQL 2005 and making sure the OS is fully patched too. It would probably be best to get the OS fully patched in all of your environments before starting patching of SQL in your dev environment. Consider applying the CUs that come after SP3 as well. One of the CUs fixes a bug with database snapshots causing SQL Server to go into an infinite loop, locking up the server when it goes over 64 GB in size.
June 3, 2009 at 2:45 am
i know eat up my free space and they are not in used(orphaned).
How do you know the objects in tempdb are not in use?
If you can prove they are not in use, and that they are not cached objects that are available for reuse, then this may be a bug in SQL Server.
So if you think can prove they are not in use raise a ticket with Microsoft. The PSS team will help you identify if they really are unused, and if so get a bug raised for the SQL authors to deal with.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
July 1, 2010 at 11:05 am
for me, it is still a mystery even after all the suggestions.
I have 3 SQL 2005 Servers, one with SP2, 2nd and 3rd with SP3 and I see that table named dbo.##0712802 along with few others, under my tempdb>Temorary Tables and at all 3 places it is not going away. I have restarted one of my Server many many times as it is my development server but that temp table it not going away.
When I run one my application, it gives me the error, " [Microsoft] [SQL native Client] [SQL Server] Cannot insert the value NULL into column "XXXXX' , table " tembdb.doo.##0712802"; column does not allow nulls. insert fails.
Same message on all 3 servers, no matter what I do.
SQL Service was used to stop on of my Server which I had to restart few time until I changed to automatic, so I have tried that as well.
Now what you say ?
July 1, 2010 at 11:12 am
Please post new questions in a new thread and give as much in the way of details as you can.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 11:35 am
well it is the same question, same issue and I have just added that I have tried all of the above still no success.
I can still post new but then someone will say, it is already there, don't overload and duplicate the same issue.
We are never settling species
July 1, 2010 at 12:53 pm
zeeaay (7/1/2010)
well it is the same question, same issue and I have just added that I have tried all of the above still no success.I can still post new but then someone will say, it is already there, don't overload and duplicate the same issue.
Nope, it's a new question (you're not the OP), it's a year later than the latest activity on this and the only people likely to read this are those subscribed to this thread. In addition, it does not sound, to me anyway, like the same issue the OP in this thread had.
You'll stand a much better chance of getting answers in a new thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2010 at 8:06 am
David Levy mentioned in previous post "Yes, freeing the proccache will bring back the free space in tempdb temporarily. It will not shrink the file for you, but constantly growing and shrinking the file could result in volume fragmentation if you are stuck in a situation where tempdb is not on a dedicated volume. ".
I do not undertsand how "proccache" in MEMORY could dramically affect the tempdb size? Pls donot argue with me that since there is more memory avaible it could reduce the spill of temp working table space to tempdb. Do you think of the other nagative impacts of flushprocindb?
Pei
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply