June 23, 2012 at 10:40 pm
Hello All,
I have been assigned the dba role for SQL server 2008 R2. I have very little experience, its been only three weeks, so, needless to say, the learning curve is very steep 🙂
I have learned that tempDB can grow, and it can only be cleared if the SQL services are restarted. Given that, I've been researching, and found a code/query to run that will clear the contents of tempDB, if older than 12 hours (this parameter can be changed)
Question: Has anyone used this procedure, and were you successful. I've tried copying the pasted code in the query manager but I get syntax errors. It is quite possible it was written for an earlier version.
In any case, I'm looking for a solution to clear the tempDB without bringing down the SQL services.
any help is greatly appreciated.
erpdba
June 24, 2012 at 12:36 am
How about you post that code so we can determine if we have used the code you are talking about?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 24, 2012 at 8:10 am
yes, certainly. Here it is.....
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
June 24, 2012 at 8:43 am
That won't work.
Only the connection that creates a temp table can drop it. That script will just give you a whole lot of errors.
Why do you want to clear TempDB anyway?
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 24, 2012 at 10:20 am
We run an ERP system. On an average, end users and automated scheduled processes submit close to 3500 reports which accesses the database non stop. Some reports are inquiry only, where as some reports will update the tables. We recently migrated from AS/400 db2/400 to MS SQL 2008 R2.
Some of these reports, most likely were written inefficiently which in turn makes the tempDB to grow. Unfortunately, we cannot identify them all at once so we are addressing them as we go. Meanwhile, the tempDB grows quite often where we are having to take frequent production outages to clear the tempDB.
I thought, as an interim solution, if we can somehow clear the tempDB periodically, without having the need to recycle SQL services, that would be great, and that's when I ran into that code, which brought hope 🙂
June 24, 2012 at 10:47 am
The solution is not to try and shrink TempDB, but to size it correctly for the usage that you get. SQL will automatically drop temp tables as soon as they go out of scope.
Monitor how big TempDB gets due to the usage, then ensure that there's adequate space on the drive and size the DB so that it starts at the necessary size, doesn't have to grow there after every restart.
Once that's done. work on optimising those reports and gradually get the TempDB abuse down.
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 24, 2012 at 12:30 pm
GilaMonster (6/24/2012)
SQL will automatically drop temp tables as soon as they go out of scope...
I totally agree with Gail (who doesn't), but the assumption is that they, the temp tables go out of scope. Now do they? If you monitor the number of connections to SQL, does it stay relatively constant, or does it keep going up?
June 24, 2012 at 5:20 pm
the problem is that with an ERP system, we are dealing with close to 4000 tables for production. Some of these tables are quite large, for example, the tables on financial side, one particular one is in excess of 225 million records. We originally sized the tempDB to 200GB, then turned out that wasn't enough so we had doubled it. Even at 400GB, it is very close to its ceiling.
In our situation, it is very difficult to even guesstimate the appropriate size of the temp table so we are shooting in the dark. It is impossible to predict which user will run a process that will make the tempDB to grow. What if a different user will submit another report against the same table...so, you get the idea
I have set alerts when the tempDB storage drive starts to get 75% of its size. As soon as I see the alerts, when possible, I quickly make a note of the running process to help determine if that one indeed is causing the tempDB to grow. We have managed to isolate several running process that causes the database to grow but we have a long way to go.
Your suggestions provide an interim solution to allocate as much space as possible to tempDB till we track down all the culprits from growing it.
June 24, 2012 at 5:33 pm
Have a read through this, there's a section on monitoring TempDB, you can put a job in place to track the abusers of TempDB without having to sit and watch for drive space alerts
http://technet.microsoft.com/library/Cc966545
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 25, 2012 at 1:47 pm
GilaMonster (6/24/2012)
Have a read through this, there's a section on monitoring TempDB, you can put a job in place to track the abusers of TempDB without having to sit and watch for drive space alerts
http://technet.microsoft.com/library/Cc966545
I like that article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 25, 2012 at 10:24 pm
fm2xm (6/24/2012)
the problem is that with an ERP system, we are dealing with close to 4000 tables for production. Some of these tables are quite large, for example, the tables on financial side, one particular one is in excess of 225 million records. We originally sized the tempDB to 200GB, then turned out that wasn't enough so we had doubled it. Even at 400GB, it is very close to its ceiling.In our situation, it is very difficult to even guesstimate the appropriate size of the temp table so we are shooting in the dark. It is impossible to predict which user will run a process that will make the tempDB to grow. What if a different user will submit another report against the same table...so, you get the idea
I have set alerts when the tempDB storage drive starts to get 75% of its size. As soon as I see the alerts, when possible, I quickly make a note of the running process to help determine if that one indeed is causing the tempDB to grow. We have managed to isolate several running process that causes the database to grow but we have a long way to go.
Your suggestions provide an interim solution to allocate as much space as possible to tempDB till we track down all the culprits from growing it.
Gosh... why are you storing these tables in TempDB? They belong in their own database.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply