The transaction log for database tempdb is full

By - 


If you get the error message for Tempdb Transaction log is full.

Msg 9002, Level 17, State 4, Procedure sp_helpdb, Line 19

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


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)






































































Comments











Post a Comment






























Popular posts from this blog









Index Clean-Up Scripts







By







-















  dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this.        Un-Used Indexes Script       Declare @dbid int       , @dbName varchar(100);       Select @dbid = DB_ID()       , @dbName = DB_Name();       With partitionCTE (object_id, index_id, row_count, partition_count)    As   (       Select [object_id]           , index_id           , Sum([rows]) As 'row_count'           , Count(partition_id) As 'partition_count'       From sys.partitions       Group By [object_id]           , index_id   )        Select Object_Name(i.[object_id]) as objectName           , i.name           , Case                When i.is_unique = 1            ...








Read more










forgot sa password and no logins are added







By







-














Image







 Stop the SQL services  Edit the properties of the SQL Service  Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters       Start the SQL services. These are now running in Single User Mode.  Start CMD on tthe SQL server   Start the SQLCMD command.         Now we create a new user. Enter following commands   CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy   Go   Now this user is created       Now we grant the user a SYSADMIN roles using the same SQLCMD window.    sp_addsrvrolemember ‘recovery’, ‘sysadmin’  go            








Read more










The SQL Server DBA’s Guide to Teradata







By







-















      Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database .   Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named.   Release History:   2012-08-17 – Added UPDATE examples; reformatted  2012-08-12 – Added date examples  2012-08-08 – Initial Release    SELECT Statements   Simple SELECT   SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...








Read more