September 5, 2012 at 10:43 am
Hi All,
i am trying to clean up my temp table sin tempdb database that are older than 24 hours.
during my research the following link gave me the information that was needed.
http://potomac9499.wordpress.com/2007/12/31/cleaning-up-tempdb/
the script create a Sp and a Table. when executing the stored procedure it will delete all the temporary tables older than 24hours.
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N’[dbo].[TempTableToKeep]‘) AND OBJECTPROPERTY(id, N’IsUserTable’) = 1)
DROP TABLE [dbo].[TempTableToKeep]
GO
CREATE TABLE [dbo].[TempTableToKeep] (
[TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateToDelete] [datetime] NOT NULL
) ON [PRIMARY]
GO
IF EXISTS (SELECT name FROM sysobjects WHERE name = N’sp_DropTempTables’ AND type = ‘P’)
DROP PROCEDURE sp_DropTempTables
GO
CREATE PROCEDURE sp_DropTempTables
AS
DECLARE @Cursor AS CURSOR
DECLARE @Name AS VARCHAR(100)
DECLARE @TableName AS SYSNAME
DECLARE @Owner AS VARCHAR(100)
DECLARE @sql AS NVARCHAR(200)
SET @Cursor = CURSOR SCROLL FOR
SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
FROM TempTableToKeep
RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
WHERE ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete < GETDATE())) OR
((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = ‘U’) AND (TempTableToKeep.DateToDelete IS NULL))
OPEN @Cursor
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
WHILE (@@FETCH_STATUS = 0)
BEGIN
If (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name = @Name AND type = ‘U’)
BEGIN
SET @sql = ‘DROP TABLE tempdb..’ + @Name
–PRINT @sql
EXECUTE sp_executesql @sql
END
FETCH NEXT FROM @Cursor
INTO @Name
END
END
CLOSE @Cursor
DEALLOCATE @Cursor
GO
...............................................................................................
now my problem is when i execute the script it creates the table and Sp but when i execute the SP it gives the following error.
Msg 16924, Level 16, State 1, Procedure sp_DropTempTables, Line 15
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
(1 row(s) affected)
kindly help
Thanks in advance
September 5, 2012 at 10:55 am
Well, I'm no fan of cursors, but the error says it all: The number of variables in the INTO clause of your FETCH commands must match the number of columns returned by your CURSOR's SELECT command.
Since you've got a wildcard on the SELECT, you've got a lot of unspecified columns there (10 to 20 or maybe even more). You two FETCH commands however have one and two variables in their INTO clauses. So you should probably change the SELECT command to return the two columns that you actually want and then change your second FETCH command to also receive two columns.
Or just do the whole thing without a Cursor or While loop (which is what I would do).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2012 at 11:02 am
Thanks Barry,
it makes sense. can you please let me know where to make changes in the code, sorry I am kind a new at this and for the past month or so a "accidental DBA". a bit details as to where/what to make changes will be very helpful .
Thanks in advance.
September 5, 2012 at 11:08 am
qur7 (9/5/2012)
Thanks Barry,it makes sense. can you please let me know where to make changes in the code, sorry I am kind a new at this and for the past month or so a "accidental DBA". a bit details as to where/what to make changes will be very helpful .
Thanks in advance.
I'm skipping over the part that you should not be deleting temp objects, and let teh system handle them instead.
the core issue is the definition of the cursor query:
SELECT tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*....
that's the NAME column plus 79 additional columns which come in under the asterisk.
if you look at your code, it looks like you only use the @NAME variable for a single column in that result set.
so remove the tempdb.dbo.sysobjects.*.... part and you'll be pretty close.
Lowell
September 5, 2012 at 11:17 am
Thanks Lowell,
I grayed out the part " SELECT tempdb.dbo.sysobjects.name --tempdb.dbo.sysobjects.*"
but it gave the same error when executing the SP
"Msg 16924, Level 16, State 1, Procedure sp_DropTempTables, Line 15
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns."
kindly help
With Regards,
September 5, 2012 at 11:29 am
yeah, i had mentioned "get you closer", because i saw you'll need to modify it some, and wanted you to understand that part.
looking at your cursor, do you notice that it's looking for two values, @Name and @Owner?
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
the cursor definition needs to have the columns for those two values, in the exact same order.
right now, your only selecting the @Name, i think, if you commented out the * asterisk part.
Lowell
September 5, 2012 at 11:38 am
Thanks for the input.
i took out the owner from the cursor the SP executes successfully but does do any thing, meaning none of the temp tables get deleted.
can Lowell or any one tell me what changes i need to made for this SP to make it work.
Thanks in advance for every one.
September 5, 2012 at 12:14 pm
Hmm, I just noticed what this is trying to do (thanks, Lowell!).
Why on earth would you even want to do this? If a #temp still exists, it's because someone is using it.
I doubt that SQL Server will let you drop it, and if it does, it still seems like a really, really bad idea.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 5, 2012 at 12:17 pm
i understand , but in this box.. because of the bad application code lot of temporary tables are not getting drop... from last month of so. i want to drop them.
i just had this issue which i cant seem to able to fix it.. really appreciate if this can be fixed...
kindly help me so i can d what is needed.
Thanks
September 5, 2012 at 12:27 pm
Guys,
i really need help in this, i know people have explained the issue but i cant seems to be able to make it work.
in my environment i cant delete the temp tables that why it works for me.
can any one please make the code work. i really appreciate the help in this matter.
September 5, 2012 at 1:14 pm
qur7 (9/5/2012)
i understand , but in this box.. because of the bad application code lot of temporary tables are not getting drop... from last month of so. i want to drop them.i just had this issue which i cant seem to able to fix it.. really appreciate if this can be fixed...
kindly help me so i can d what is needed.
Thanks
That means you have had database connections stay active for a month? Those temp tables will destroy themselves when that connection is closed. If you start removing temp tables belonging to other connections (which I am not sure you can do) you are potentially causing applications using those other connections to have all sorts of unknown issues.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2012 at 1:21 pm
RBarryYoung (9/5/2012)
Hmm, I just noticed what this is trying to do (thanks, Lowell!).Why on earth would you even want to do this? If a #temp still exists, it's because someone is using it.
I doubt that SQL Server will let you drop it, and if it does, it still seems like a really, really bad idea.
Pretty sure you can't. If it is a temp table then no other connections have visibility of the table. You can of course see it listed in tempdb but you can't select anything from it, let alone drop it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2012 at 1:28 pm
You do know that tempdb is recreated when SQL Server is restarted, correct? That will clear all your orphaned temp tables for you. Of course you need a maintenance window in order to do a SQL Server restart. it isn't something you just want to do in a production environment any old time.
September 5, 2012 at 1:49 pm
also take a look at this detailed 5 page thread on trying to delete all temp tables from tempdb.
http://www.sqlservercentral.com/Forums/Topic627590-146-1.aspx
a quick recap: you cannot drop tables from other connections that you did not create.
the better question is why do you think those tables are evil and must be destroyed? what is it you are trying to accomplish?
Lowell
September 5, 2012 at 6:01 pm
Sean Lange (9/5/2012)
RBarryYoung (9/5/2012)
Hmm, I just noticed what this is trying to do (thanks, Lowell!).Why on earth would you even want to do this? If a #temp still exists, it's because someone is using it.
I doubt that SQL Server will let you drop it, and if it does, it still seems like a really, really bad idea.
Pretty sure you can't. If it is a temp table then no other connections have visibility of the table. You can of course see it listed in tempdb but you can't select anything from it, let alone drop it.
That's my recollection also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply