Tempdb getting full

Question

Tempdb getting full

binu john

SSCommitted

Points: 1987
More actions
June 2, 2005 at 4:58 am

#109850

I want to clear one scenario, that is what can I do if my tempdb getting full.I dont want to restart the server since that is a production server.Shall I create a new data file for tempdb?Any help would be appreciated...
Rgds
Binu John

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Milner SSCrazy Points: 2425 More actions · Answer 1

Create this in the master database and run it in the offending database. Works in tempdb as well. This proc (sp_trx_log_shrink is for shrinking the log).

EXEC sp_trx_log_shrink 2, 50

Then, run this from withing the TempDB database:

---------------------------------------

sp_helpdb tempdb -- show the size before

GO

DBCC SHRINKFILE (1,50) -- assuming the fileID of the Tempdb data file is 1

GO

sp_helpdb tempdb -- show size after

GO

---------------------------------------

Here's that proc for your master DB:

------------------------------ BEGIN Procedure --------------------------

Create Procedure sp_trx_log_shrink (@MaxMinutes INT,

@NewSize INT )

/*=========================================================================

Title: Shrink SQL Server Transaction Log Stored procedure

Script sp_SQL_Server_Trx_Log_Shrink.sql

Purpose: system proc based on

INF: How to Shrink the SQL Server 7.0 Transaction Log

Force shrink transaction log of current database to

specific size.

Params: @MaxMinutes = Max number minutes to run before stoppint

(recommend 2 at least)

@NewSize = New size in MBs of the log file after shrinking

(recommend at least 10 MB in most DBs)

Create/Update History:

3/9/2005 3:33:44 PM - GMilner: Converted to procedure.

5/13/2004 11:07:58 AM - G Milner: Created;

Notes:

Assumes only 2 physical database files and that _Data file

is file id 1 in sysfiles table and that log file is file id 2.

Original Source:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q256650

Microsoft Knowledge Base Article - 256650 ;

=========================================================================*/

AS

SET NOCOUNT ON

DECLARE @err int

DECLARE @LogicalFileName sysname

--DECLARE @SSQL as VARCHAR(255)

DECLARE @DBN as nVarchar(50)

-- Setup / initialize

DECLARE @OriginalSize int

set @DBN = (select db_name())

PRINT 'Database: ' + @DBN

--SET @SSQL = N'SELECT [name] FROM ' + CAST(@DBN AS nVarchar(50)) + '.dbo.sysfiles where filid = 2'

SET @LogicalFileName = (SELECT FILE_NAME (2))

PRINT 'Log logical filename: ' + @LogicalFileName

PRINT ''

EXEC sp_helpdb @DBN

SELECT @OriginalSize = size -- in 8K pages

FROM sysfiles

WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

-- Wrap log and truncate it.

DECLARE @Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR(255)

SELECT @StartTime = GETDATE(),

@TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'

-- Try an initial shrink.

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk

AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.

BEGIN -- Outer loop.

SELECT @Counter = 0

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

DELETE DummyTrans

SELECT @Counter = @Counter + 1

END -- update

EXEC (@TruncLog) -- See if a trunc of the log shrinks it.

END -- outer loop

SELECT 'Final Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),size) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

DROP TABLE DummyTrans

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

IF @err 0

BEGIN

RETURN(@err)

PRINT '*** SHRINK FAILED!!! ***'

END

IF @err = 0

BEGIN

PRINT '*** Perform a full database backup ***'

END

------------------------ End Procedure -------------------------

G. Milner

Greg Milner SSCrazy Points: 2425 More actions · Answer 2

PS:

Sorry all the indents and formatting got wasted in the post here.

G. Milner

binu john SSCommitted Points: 1987 More actions · Answer 3

Dear Milner,

Thanks for your help,and also want to find is there any sigle step/command to tackle this kind of situation?

Rgds

Binu

Greg Milner SSCrazy Points: 2425 More actions · Answer 4

Sure, after you create the stored procedure, put put the below in a SQL job and schedule it to run a couple of times a week (on off hours of course):

---------------------------------------

EXEC sp_trx_log_shrink 2, 50

DBCC SHRINKFILE (1,50)

G. Milner

binu john SSCommitted Points: 1987 More actions · Answer 5

Urgent help needed...

I want to know T-Sql statement that how to update a login with password and enable 'always prompt for login name and password ' option in the EM.

Any help would be appreciated.

Regards

Binu John