SQL Server
 

  How to Truncate Transaction Log in SQL2000 usin SQLDMO

Asked By Noman Nasir
20-Dec-06 02:40 AM
Earn up to 0 extra points for answering this tough question.
Reply
My Data Base log became bigger enough to 20GB I want to trancate the log using SQLDMO
I write the following code. Code is executed without error but log file size remains same.
Plz Help

Dim mServer As New SQLDMO.SQLServer2
mServer.Connect "ServerName", "UID", "PWD"
mServer.Databases.Item("DBName").TransactionLog.Truncate

  Very easy way to truncate Transaction log

K Pravin Kumar Reddy replied to Noman Nasir
20-Dec-06 04:17 AM
Reply

in SQL Server 2000

backup log [database name] with truncate_only
(run this in query analyzer)

(open enterprise manager and expand the server tree)
--right click on the database
--all task
--shrink database
--bottom button (on right side of form) for files
--select sql log file (default is data file so make sure and change it)
--click ok

the log size of my main database was 67 gigs. I ran this and it shrinked my database size from 67 gigs to 50 megs (49 of which is the data file)

reference

http://forums.databasejournal.com/archive/index.php/t-40824.html

Truncate SQL Server Transaction Log

http://www.codeproject.com/cs/database/ClearTransactionLog.asp

  Thanx

Noman Nasir replied to K Pravin Kumar Reddy
20-Dec-06 11:51 PM
Reply
Thanx a lot!

  use DBCC

Saurabh Kaushik replied to Noman Nasir
29-Dec-06 10:17 AM
Reply

DBCC statememnts act as Database Console commands. You can use the below listed queries, sprcifically the DBCC ones to shrink your transaction log.

-- To GET the name of the log and data files
select name from dbo.sysfiles

-- SEE the active transactions as well as the File ID
dbcc loginfo('db_Name')

-- Mark transactions inactive so that they can be truncated later
-- Suppose file ID is 2

DBCC SHRINKFILE (2)
-- Backup the log file and truncate the inactive entries
BACKUP LOG [db_name] WITH TRUNCATE_ONLY
-- Run Shrinkfile again for maintainence.
DBCC SHRINKFILE (2)

You can create a stored procedure out of the last 3 statements and schedule it as a job on your server than can run overnight and ensure that the transaction log file size does not get out of hand.

Hope this helps.

  re: Very easy way to truncate Transaction log
Asha replied to K Pravin Kumar Reddy
04-May-10 02:16 PM
Reply
I tried to shrink database and selected the Transaction log file and saw when the process got done, the Transaction Log remained the same size.  Do you have any suggestions?
Thank you
  re: Very easy way to truncate Transaction log
K Pravin Kumar Reddy replied to Asha
05-May-10 01:27 AM
Reply

Hi Asha,


I'm not sure about SQL SERVER edition you are using, but you can try following steps..

Shrink SQL Server Log File

 These instructions detail how to shrink a SQL Server Log File.

I.  Shrink the log file size at the right time

 Immediately after using the SSIS package or Import the data to the database ( highlight the database->Tasks->Import data … ), or Export the data from the database ( highlight the database->Tasks->Export data … ),  shrink the log file to the desired size, for example, 1MB.  That is, highlight the database->Tasks->Shrink->Files

 

 set the file size, say, 1MB.

Then, click OK and you are done.


Eliminate SQL Server Log File

These steps detail how to eliminate a SQL Server Log File entirely.

 

II. Eliminate the log file completely

Sometimes we just do not need the big log file.  The logic is

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

 

Let’s say, the database name is testDev. In the SQL Server Management Studio,

  1. Highlight the database-> Tasks->Detach..-> Click OK
  2. Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf,
  3. Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf
  4. After this is done, you can verify the contents of the attached database and then delete the log file.




  re: How to Truncate Transaction Log in SQL2000 usin SQLDMO
Fati replied to Noman Nasir
09-May-11 09:02 AM
Reply
Thak you very much. I got the answer very satisfactory.
Create New Account