September 11, 2007 at 8:11 am
Hi Friends,
My Transaction log file is incresing nearly 10gb know i want delete that Transaction log file.
It is very big prob to our another database files also can you please help me
Thx
siva
September 11, 2007 at 8:25 am
What recovery mode is your database in (full, simple, bulk)
select recovery_model_desc from sys.databases where database_id=db_id()
If it is in full, you will need to back up the log, then truncate it.
If it is in simple, try to truncate it, if this does not reduce the size, you will need to check why (long running transaction, etc.)
Regards,
Andras
September 12, 2007 at 6:43 am
No my plan is completely to delete the Transaction log file can u body plz give very good soultion plz
Thx
shiv
September 12, 2007 at 6:47 am
A SQL database relies on transaction logs to run. Delete it and your DB will in essence be "dead". I don't think it will mount without it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2007 at 6:55 am
Of course I would not like to stop you deleting the transaction log, but I hope you know that the transaction log is a rather critical file(s) for a database.
Regards,
Andras
September 12, 2007 at 8:17 am
what is the command to truncate the Transaction log file or any another procedure is there plz tel me
Thx
shiv
September 12, 2007 at 8:21 am
BACKUP log WITH TRUNCATE_ONLY
dbcc shrinkfile('myDatabaseLogName')
you need to fill in the database name and the logname
I strongly suggest you read http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=352434#bm352437 and make a full backup.
Regards,
Andras
September 12, 2007 at 8:26 am
Agree with Andras. There are pitfalls - make sure you understand them. Just one small clarification on syntax:
Backup LOG <databasename> WITH TRUNCATE
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2007 at 8:36 am
Hi Matt,
I'm not absolutely convinced by "WITH TRUNCATE". "TRUNCATE_ONLY" seems to work however, it is the same as "NO_LOG".
Andras
September 12, 2007 at 9:15 am
it'd be nice if I finished typing prior to sending I just wanted to clarify where the database name went in your initial post. it is TRUNCATE_ONLY.
So - Backup Log <databasename> with TRUNCATE_ONLY.
Nothing like making things more confusing when trying to clarify....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 12, 2007 at 9:23 am
Indeed the dbname is missing. I've forgotten to escape the < Many thanks
Andras
September 14, 2007 at 8:33 am
Just a short note about the alternative to this, it may be worth to get used to switching to simple recovery mode and then back to full. The BACKUP log WITH TRUNCATE_ONLY or NO_LOG will not be included in Katmai (SQL Server 2008).
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/c10eeaa5-3d3c-49b4-a4bd-5dc4fb190142.htm
Regards,
Andras
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply