RS First Dynamics NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

TempDb is full

TempDb is full
Come liberare lo spazio quando il TempDB è pieno.

Errrore windows registry

> Source: MSSQLSERVER
Event ID: 17052
Description: The log file for database 'tempdb' is full.
Back up the transaction log for the database to free up
some log space

TempDb

Verifica spazio occupato dalle tabelle temporanee

USE tempdb
GO
EXEC sp_spaceused
 
The following should give you some clues as to which table(s) consume most of the space
in the data file(s)
this will help you narrow down any transactions that are either taking a long time
or repeatedly being left in limbo:
 
USE tempdb
GO
 
SELECT name
    FROM tempdb..sysobjects
 
SELECT OBJECT_NAME(id), rowcnt
    FROM tempdb..sysindexes
    WHERE OBJECT_NAME(id) LIKE '#%'
    ORDER BY rowcnt DESC

Chiudere transazioni le aperte
Short-term fix
 
Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once, will likely cause it to grow again.
 
To shrink tempdb, you can consider using DBCC ShrinkDatabase, DBCC ShrinkFile (for the data or the log file), or ALTER DATABASE. See KB #256650, KB #272318 and KB #307487 for more information.
 
If you can't shrink the log, it might be due to an uncommitted transaction. See if you have any long-running transactions with the following command:
 

DBCC OPENTRAN or DBCC OPENTRAN('tempdb')
 
Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with 'SPID (Server Process ID) : <number>'). Use that <number> in the following: 
 
DBCC INPUTBUFFER(<n0umber>)
 
This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine if you want to end this process with: 
 
KILL <number>

 

To prevent tempdb log file growth, make sure tempdb is in simple recovery mode (this allows the log to be truncated automatically). To check if this is the case:
 
- SQL Server 7.0, should show 'trunc. log on chkpt.'
--.or 'recovery=SIMPLE' as part of status column:
 
EXEC sp_helpdb 'tempdb' 
 
- SQL Server 2000, should yield 'SIMPLE':
 
SELECT DATABASEPROPERTYEX('tempdb', 'recovery')
 

fonte
http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html

Categoria: Sql Server ALL
venerdì, 06 mag 2011 Ore. 09.30

Messaggi collegati


Statistiche
  • Views Home Page: 464.692
  • Views Posts: 881.811
  • Views Gallerie: 0
  • n° Posts: 343
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003