SQL Server - Error 9002-----The transaction log for database 'tempdb' is full.

Hi...
this is prathap i am beginner on sql server as DBA

every day i got this error :
error 9002-------The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

i am configuring system databases in c:\ drive.....C:\ drive having 18GB free space.its more than enough free space........and My all server backup's running @2:00 AM every day and Transaction logs also running parallely every 30 min....because my databases are critical.

But i got the error every day in morning 8:00 AM to 8.30 AM........how to find out which database logs are written on templog file.......and how to stop this error.......

Please give me the permanent solution.

hi

Solution: 1

-- First check Tempdb Tran log file size using  dbcc sqlperf(logspace)

-- tempdb 999.9922 99.99628 0

USE MASTER
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME='templog', SIZE=1500MB)

Solution:2 

ALTER DATABASE Tempdb 
ADD LOG FILE 
( NAME = tempdblog2,
FILENAME = 'E:\MSSQL.1\MSSQL\DATA\tempdblog2.ldf',
SIZE = 10MB,
MAXSIZE = 100MB,
FILEGROWTH = 10MB)

The root cause may be:

1 - Have a poorly optimized query (lots of sorts or hash joins can cause this)

2 - Are overusing #temp tables in your query

3 - Have a drive space issue

You can use DBCC SHRINKFILE to truncate your transaction log,

DBCC SHRINKFILE(<filename>,<desired_shrink_size>)
DBCC SHRINKFILE('Transaction.log',10)

Thanks

Thanks ........

I am giving the auto growth for tempdb and unrestricted growth......some times logs writtens upto 18GB space.
That time
i am using DBCC SQLPERF(logspace)
then DBCC shrinkfile(templog,1)..........shrink that database then release the space to OS...........Why it writtens that much of logs in logfile........where is the problem........

Please.......

To resolve the same I go around multiliple ways will explain few workaround here.

1.If database is not setup for Logshipping,Replication,Mirroring and CDC.

1. set the database into simple recovery mode

2. Set the database back to full recovery mode.

3. Run the shrinkfile command.

DBCC SHRINKFILE (2,1)

or

DBCC SHRINKFILE (2)

or

DBCC SHRINKFILE (2,truncate_only)

(Mirosoft do not recommend to use truncate statement in production)

2.In case the database is setup for Replication (any topology) and transaction log file is growing tremendously and soon will be full. Transaction Log file can’t be shrinked as there are transaction which are not marked as replicated in the transaction log file.

Or

If transaction log is full and transaction written on the log file are not marked as replicated the logreader agent will keep trying to scan the log and mark the transaction as replicated but it will not succeed as there is no space to write transaction in the transaction (Mark the transaction as replicated).As the transaction are not marked as replicated DBA’s cant shrink the Log file.

To resolve the issue we can execute the below steps.

1. Lets say the trasaction log file is on drive D:\ of size 200 GB.

2. The transaction log file has grown by size 200 GB.

3. Create a new log file to some other drive.Let’s say on drive F:\ at the publisher database.

USE [master]

GO

ALTER DATABASE [xzcvxz] ADD LOG FILE ( NAME = N’xyz_log’, FILENAME = N’F:\xyz_01.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

4. As new log file is added to the database, SQL Server will start using it.the new transaction will be written to the newly added transaction log files.The Log reader agent will start scaning the old log file and will mark the transaction as distributed once sp_repcmd will replicate the commands or transaction to the distributor database.

5. Alter the Database in simple recovery mode.

ALTER DATABASE [xzcvxz] SET RECOVERY SIMPLE WITH NO_WAIT

6. Alter the Database again in full recovery mode.

ALTER DATABASE [xzcvxz] SET RECOVERY FULL WITH NO_WAIT

7. Now Shrink the Log file.

USE [xzcvxz]

DBCC SHRINKFILE (2)

thanks.........

If the database is in Logshipping Configuration........can i shrink the log file ? if shrink is possible .please give me the process.....

thanks advance.....

here the steps:

1.DBCC SHRINKDATABASE ('tempdb', 20,TRUNCATEONLY)

--Here after I did not get any compliant from application

2.CHECKPOINT

3.BACKUP LOG tempdb WITH TRUNCATE_ONLY

Before doing just have a look at here :
http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

is it possible to shrink the log files where database configured for LOGSHIPPING?

Please anyone help me.