Tlog file
-
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
-
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
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
No my plan is completely to delete the Transaction log file can u body plz give very good soultion plz
Thx
shiv
-
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? -
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
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
what is the command to truncate the Transaction log file or any another procedure is there plz tel me
Thx
shiv
-
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
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
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? -
Hi Matt,
I'm not absolutely convinced by "WITH TRUNCATE". "TRUNCATE_ONLY" seems to work however, it is the same as "NO_LOG".
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
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? -
Indeed the dbname is missing. I've forgotten to escape the <
Many thanksAndras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
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
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply