Truncating The Transaction Log

Jun 22, 2001

hello,
Daily, after a database backup done in the evening, around 3am we have a lot of flat files to integrate in tables and then process.

The question is : We want to free the space used by the transation log.
Then, we use "backup log with no_log" and/or "backup log with truncate_only"
We look at the size of the transaction log with "dbcc sqlperf( logspace )" but the 'Log space used %' stays the same.

Could you give us some informations or tips on this.

Thank you from Paris
Patrick

View 4 Replies


ADVERTISEMENT

Truncating The Transaction Log

Mar 19, 2001

Hi all,
This is a database on which there are continuous inserts and updates. Data is loaded into this database in bulk at regular intervals. I do not back up the transaction log. For these reasons, I have set the options, 'select into/bulk copy' and 'truncate log on checkpoint'. When the 'truncate log on checkpoint' option was not set, the log file would grow very huge and fill up the entire disk space(file growth is unlimited). But, even after the 'truncate log on checkpoint' option has been set, the log file does grow at times to fill up the entire disk. I assumed that since the 'truncate log on checkpoint' option was set, the inactive portion of the log would be truncated every 1 minute(ie, during every checkpoint). Could someone please explain the reason for this behaviour?

Thanks in advance,
Praveena

View 6 Replies View Related

SQL 6.5 Truncating Transaction Log

Jan 28, 2004

Recently I start working with SQL 6.5 server on NT4 (sp6)

Before me someone make abnormal reinstallation of this SQL server.
after that server need to be restarted every day

On the database edit window I have seen that Avaluable Log Space is 0
and in the course of working with device have seen that device definition
is erroneous. the path to file is different from the name of file,
i.e. in device defined (D:aaa03_log) and actually present file
(D:aaalog_03)

and as result every operation on device get the error.
I have change the name of the file to match the sql device definitions but
first this don't helped.
I have added new device and expanded the database log on it, but Avaluable Log Space remains 0.
After a number of restarts I noticed that old erroneous device is now
become visible and I can work with it, for instance expand it. But Avaluable Log Space remains 0 and in the sql monitor I also see that log occupied 100% of avaluable space.

May be transaction log located with data and I just can't see that ?

There is option on database edit window: Truncate transaction Log
and I want run it. The organisation don't interested in log. main thing is that the data will be safe.

Is it the right thing to do?

Tanks to everyone who can help

View 1 Replies View Related

Truncating A Transaction Log

Feb 4, 2008

2 Questions?

1. When I do a full backup, does that truncate my transaction log? Or does only a backup of transaction log will truncate a transaction log?

View 6 Replies View Related

Truncating Transaction Logs.

Jun 7, 2001

I am having trouble Truncating a Transaction Log. I`ve tried everything in Book Online.
I`ve backed up the database, I`ve tried DBCC SHRINKFILE, DBCC SHRINKDATABASE, BACKUP LOG TRUNCATE_ONLY ...etc, but it will not shrink. Any suggestions ? Thanks.

View 1 Replies View Related

Truncating Transaction Logs

Mar 1, 2007

Hi

I just wanted to know can you truncate transaction logs in SqlServer 2000 and if so how is this done?

Thanks

View 1 Replies View Related

Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

May 31, 2008

Hi All

I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.

If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.

I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.

set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN


It's got me stumped, so any ideas gratefully received.Thx

View 1 Replies View Related

SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 22, 2007

I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.

for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.

I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.

if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

and

Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.

Please help me it's very urgent.

View 3 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Feb 6, 2007

I am getting this error  :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!

View 1 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.

Dec 22, 2006

i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.

Message is like this:

Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

View 8 Replies View Related

Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction. (HELP)

Jan 8, 2008

Hi,

i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".

my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.



Thanks in advance.


View 2 Replies View Related

Help Truncating Log

Oct 18, 2001

I have a large database and I can only do a tape backup, and I have 10 GIG log file on sql2000. Is there a way to truncate it.

Thanks

View 3 Replies View Related

Truncating

Feb 19, 2006

Hi.I have a multiplier that multiplies 2 floating point numbers with 7bits exponent and 10 bits mantissa.so its output has 7 bits exponentand 20 bits mantissa.now its output must return to its input in order to compute anothermultiplication.on the other hand its output must be truncated to 10bits. how can I do this? please help me.thanks.

View 1 Replies View Related

Truncating

Feb 7, 2008

Does anyone have experience with truncating an expression like the Excel TRUNC?

For example in Excel, you might have something like =TRUNC(IF($AE11=0,1,X11/$AE11),5) which drops off a certain amount of the results after the decimal point. 87.5659321 becomes 87.565 instead of the result of a rounding.

View 9 Replies View Related

Truncating A Table

Nov 2, 2006

What is the best way to delete ALL data in a table without the transaction log filling up? I do not need to log the deletions.Truncate Table ReportSearchRecordSets with NO_LOG?Delete * from table with NO_LOG?Thanks SQL Server 2005 newbie

View 3 Replies View Related

Truncating The Log File

Dec 11, 2001

Can someone please tell me where in SQL2000 I can truncate the log file and
set the database to truncate the log at checkpoint?

Thanks,
Dianne

View 3 Replies View Related

Truncating All The Tables

Aug 3, 2000

How to truncate all the tables in the databases at once,if there are 200 tables?Any help is appreciated!
Thanks.

View 1 Replies View Related

Truncating A Field

Nov 2, 2000

I am trying to truncate one field within a table and replace it with data from another database. Creating the data and inserting it is no problem, does anyone have any way of completing the truncate could I use an update or a delete as an alternative method?

View 2 Replies View Related

Truncating The Log File

Dec 8, 2001

Hi everyone,

Although I truncate the log file and I have no pending transactions, its size does not shrink at all (it stays a 0.5 GB).

Does anyone know why or what can I do to solve this issue.

Thanks,
Vasilis

View 1 Replies View Related

Truncating Log File

Jan 30, 2004

Hi,

I am having a problem with growing transaction log size, it has grown to 10 gb and I need to truncate it. How can I do it without interfering the users since it's our production database with 24/7 operational service.

Thanks in advance!!!

View 4 Replies View Related

Truncating Log File

Sep 16, 2004

I have a Database of size 200 MB and my transactio log is 13GB(very high).So can I truncate the log file by taking a fresh full backup?

Thanks.

View 3 Replies View Related

Truncating Numbers

Feb 20, 2006

Hi!

I have field1 decimal(11,0) containing number 1234567 and
I must get the last six digits to int-field, eg. I want field2 int containing 234567 as result. How should I do that with functions? And it should work regardless of the length of the field1, eg field1 = 123456789 -> field2 = 456789

Makkaramestari

View 20 Replies View Related

Truncating A File

Feb 8, 2007

Hi,

I'm using sp_OAMethod to write to a text file, like this:

DECLARE @i INT, @File VARCHAR(1000), @FS INT, @RC INT, @FileID INT, @Date DATETIME

SET @File = 'E: extfile.txt'

EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

EXEC @RC = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @File, 8, 1

EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, @Date

This always appends to the file. I want to truncate the file and write to it afresh.

Please inform me how to do that.

View 3 Replies View Related

Checkpoint Not Being Set? Log Not Truncating?

Oct 26, 2007

Hi,

I've set up a number of jobs (not a maintenance plan) via a script in SQL 2005. These jobs do the following:

1) Full backup every sunday night
2) Differential backup every weeknight
3) Log backup every hour

The database is obviously in the full recovery model.

The backups all seem to be running, with one issue - the log file is still growing and is not being truncated. I was under the impression that a log backup should result in the log being truncated after each full backup. However, this does not seem to be the case.

Is there anything obvious I've missed that needs to be set up, or is there a way I can check that the full backup is actually setting the appropriate checkpoint and that the log backups are 'seeing' these checkpoints?

Thanks

View 2 Replies View Related

Truncate On Checkpoint Not Truncating

Dec 30, 1998

The log on one of my databases keeps filling up, even though I have it set to truncate on checkpoint. the only real difference between this database and the others on my server is that it is built from the dump of another database (on another server) where the tables are marked for replication.

I'm wondering if the fact it is built from a replicating database could be causing this. I've noticed I can't drop any of the table, even though my database isn't set to replicate (or publish).

two questions
1) Any ideas?
2) Is there anyway I can make my server realize I'm not replicating so it will let me drop those tables? (nothing in Enterprise manager indicates that my database is replicating or publishing).


Thanks,
Jim

View 2 Replies View Related

INSERT Into TEXT Is Truncating

Nov 3, 2005

I have an application that issues the following against a SQL server table called SQLTEST:

sqltest.TT_MEMO is a TEXT type field
mmvar has about 5k worth of character data

INSERT INTO SQLTEST (TT_MEMO) values (?mmvar)

PROBLEM: the mmvar is getting truncated to 1024 characters

What can I do to rid the truncation?

Thanks,

Peter

View 5 Replies View Related

Backup Without Truncating Logs?

Dec 20, 2007

I've got a Sql server 2000 box that currently backs up to tape in full every night. I also want to back the box up off site but not in full (as 30GB is a little too much to transfer every evening).

So my plan was to do a full backup to tape at 7pm then a differential at 8pm (to transfer off site).

The problem I am having is that after my differential has been done the logs get truncated so if I want to replay them for any reason I need to get that differential back to site.

Anyone have any suggestions please?

View 7 Replies View Related

Display Truncating On SQL Profiler

Jan 20, 2004

Greetings,

I am having a problem debugging an XML error we are getting in our production environment because I can't view the entire call to the stored procedure in Profiler. I have successfully traced the error, but when I go to the line with the call to the SP that caused the error, it doesn't show me the entire call. It only shows me the 'exec sproc_name and then the first 16 characters of the XML string parameter that is being passed to the proc. For some reason it's doing this to ONLY the stored procs that have XML parameters...on procs that use standard parameters, it displays the entire call correctly.

I have looked for some type of setting that controls this, but haven't been able to find it. I also have looked through many forums for this issue but to no avail. Does anyone know why this is happening? And, is there a workaround/fix?

Thanks in advance...

SB

View 1 Replies View Related

Truncating Log Files Daily ..

Apr 21, 2004

Hello -

How can I truncate log files of all Databases daily automatically.

Thanks

View 4 Replies View Related

Doubt In Truncating Decimals..

May 22, 2008

Hello

I need a help ..
I need to convert 45.4593251 to 45.46

How to achieve it.
Can any one help me please..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 3 Replies View Related

Does Database Need To Shrink After Truncating?

Mar 15, 2007

There is a table need truncate and then insert a huge data (more than million rows) every day. Does database grow up every day?

View 2 Replies View Related

Truncating Logs In Logshipping

Sep 20, 2007

How can you truncate database logs without disrupting log shipping configuration on that database

View 2 Replies View Related

Oracle LONG Truncating

Nov 21, 2007

I've noticed that when a dataflow task returns an Oracle LONG field. If the query involves one table, the LONG is returned normally. If the query involves any simple joins, the LONG will be truncated at the first 100 characters.

The Microsoft driver does work correctly.

Has anyone else experienced this with the Oracle driver and are there any known work-arounds?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved