I have always assumed that when you backup a SQL Server database the transaction log is automatically truncated so that there is no need to explictly truncate it. It makes sense to me, you would not normally need logs from before the most recent backup. BOL, with all its talk about check points etc, seems to hint at this but I can't find an explicit statement to this effect.
Anyone?
Dick Campbell
If we choose "truncate log on checkpoint", then we cannot backup the transaction. May someone tell me what's the difference between them and the advantage over the the other.
Thanks.
yatie
HI,
What I have set out to do is to try and truncate the transaction logs on my server instances after a nightly backup. I went ahead and used the shrink database option in the maintenance plan wizard. Unfortunately I have found this will only truncate the logs for databases using the simple database recovery model, and I wish to truncate the whole lot to save space...
I went ahead and put together an SSIS package that does the following:
1. First thing I do is build a fresh copy of a database listing in a temp table using the following SQL statement
USE Master;
Go
SELECT name, DATABASEPROPERTYEX(name, 'Recovery') AS RecoveryModel, DATABASEPROPERTYEX(name, 'Status') AS DBStatus INTO TempTables.dbo.Recovery FROM sysdatabases ORDER BY name
GO
2. Next I read this dataset back into memory using a SQL statement as follows:
USE TempTables
GO
Select name from dbo.recovery Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb'
I map the results to a results set called User::TableName (which is of an object data type)
3. Next the package goes to a ForEach Loop container and I use the Foreach ADO Enumerator Enumerator to read the data I just selected into the look. I select the User::TableName variable and use the enumeration mode 'Rows in first table'. I have used a variable mapping of Variable: User::TableName with an Index 0.
4. I then use the following SQL statement (which sits in the For Each Loop) to try and alter the recovery model:
ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
GO
This is where the package falls over with the following error message
SSIS package "mnt_TransLog.dtsx" starting.
Error: 0x0 at Simple Mode: Incorrect syntax near '@TableName'.
Error: 0xC002F210 at Simple Mode, Execute SQL Task: Executing the query "ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
" failed with the following error: "'RECOVERY' is not a recognized SET option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Simple Mode
Warning: 0x80019002 at For Each Table Set to Simple: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at mnt_TransLog: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "mnt_TransLog.dtsx" finished: Failure.
I think the problem here is either I am not passing the variable across correctly (i.e. not declaring it or something..!) or I am passing the wrong type of variable across.
Once the recovery model is changed I can truncate the transaction logs successfully but I can€™t seem to until this is done! Any help in finding a solution to this problem or a better way of approaching this problem would be appreciated!
Thanks
Marek Kluczynski
Management Information Officer
Investors in People UK
7-10 Chandos Street
London
W1G 9DQ
Tel: 020 7467 1956
Hi,
I have a data file that has numeric data that looks like:
1.123456
And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr.
As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component").
Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point.
You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it!
Thanks
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
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.
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?!
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.
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.
If you are running in Full Recovery Mode and do a full backup every night but never do a backup of the log during the day does the log file ever truncate? From what I read this should be in Simple Recovery Mode but I'm wondering what happens in the case that I mention in the first sentence. Thanks.
View 1 Replies View RelatedHello Everyone and thanks for your help in advance. I am working on importing a flat text file into SQL Server 2005 and am having problems. The flat file is a CSV text file with " being used as a text qualifier. Each line is broken by a CrLf combination. When I try importing this file into a SQL Server 2000 table using the same datatypes and sizes for each column, it works perfectly fine with the data importing as expected. However, in SQL Server 2005, again using the identical column datatypes and sizes, the import fails giving me warnings such as:
* Warning 0x802092a7: Data Flow Task: Truncation may occur due to inserting data from data flow column "Column 0" with a length of 50 to database column "MLS_ID" with a length of 10. (SQL Server Import and Export Wizard)
Virtually every columns gives this type of warning, yet I don't understand why since the columns are all variable in length (every message says a column length of 50) and all are delimited rather than fixed size. Then later in the import, errors occur something like:
* Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 15" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) * Error 0xc020902a: Data Flow Task: The "output column "Column 15" (70)" failed because truncation occurred, and the truncation row disposition on "output column "Column 15" (70)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)
I haven't got a clue as to why this is happening. For the record, on the flat file source screen, I have ensured that delimited has been selected rather than fixed width. Any help on this issue owuld be greatly appreciated. Thanks.
Can anyone help with this error I am receiving when I perform the following
piece of code???:
INSERT INTO MEMBELIG
SELECT MEMBELIG_temp.*
FROM MEMBELIG_temp
WHERE (LOB = 'PPO')
I am receiving the following error:
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Any help would be greatly appreciated, I am new to this stuff.
Thanks,
Rey
Hi,
Could someone please let me know what are the exact steps to follow to truncate the transaction log files? As these log files grow very fast and there seems to be no space in the drive.
Currently am using the below steps to truncate the log file:
Step1:Use the below syntax:
backup log <database name> with no_log
Step2:shrink the log file. Right click the correct database and choose shrink file ->chosse the log -> ok
I would be grateful if someone can give me a proper solution.
Thanks.
Hello All,I am attempting a bulk load of fixed position flat file data via bcpand I have noticed that I get a Right Truncation error when trying toload a row where the last column value is NULL.For example:Flat file row:0000016MFMT file:7.031 SQLCHAR 0 7 "" 1 RECORD_KEY2 SQLCHAR 0 1 "" 2 SEX3 SQLCHAR 0 1 "
" 3 HEIGHTIn this row, the height info is null and I get a right truncationerror. The row below, with height info goes in fine:Flat file row:0000016M510Let me know what I am doing wrong!Thanks in advance
How is it possible to avoid truncation errors in MS SQL? For example,if I run the followingdeclare @a as decimal(38,8)declare @b as decimal(38,8)declare @c as decimal(38,8)set @a = 30.0set @b = 350.0set @c = @a/@bselect @cset @c = @c*@bselect @cI get 29.99990000 instead of 30.0. Is there a way around this?ThanksBruno
View 6 Replies View RelatedHi,I'm trying to upload a large number of log entries currently stored astext files into a database table using bcp. For a few rows I get a"right truncation" error and the offending rows are not uploaded to thetable.I don't want to increase the size of the table varchar fields becauseit's only about a dozen out of almost million rows that have thisproblem ... I want to provide an override - i.e. if a row will resultin truncated data, truncate but still bulk copy the offending row. Isthat possible?I couldn't find such an option in the documentation.Any help is greatly appreciated.Thanks,Mudassir Latif
View 2 Replies View RelatedHello,I am attempting to write a stored procedure that builds and executes adynamic SQL statement which can be up to 8000 characters long.Therefore, I have declared a variable of type varchar(8000) which,according to the documentation, is the maximum acceptable length ofsuch a variable. Unfortunately, however, SQL Server seems allowvarchars to only be half this size: the resulting string keepingsgetting truncated to 4000 characters as reported by the len function.Is there setting somewhere that would fix this behavior or somework-around that I can employ that would allow me to execute a dynamicsql statement that is longer than 4000 characters?(note: I am not using the sp_executesql proc as it maxes out at 4000; Iam simply calling EXEC which, according to the docs, should be fine)Thank You.
View 1 Replies View RelatedWe use SQL Server 2005 x64 Enterprise and I have created a SSIS routine to replace a legacy DTS routine that reads from a Data Reader Source and writes to a SQL Server 2005 database. The field I am receiving the truncation error on is "Description" and it is set as nvarchar(50), which it always has been, and the old DTS routine works fine on it. I checked the contents of description and the maximum number of characters in any row is 28. I have tried changing it to nvarchar(max), nvarchar(4000) and ntext but it still fails with a truncation error. Any leads on how I may solve this issue?
All,
I am having a strange problem that I have been looking at for a day now, and my head is straing to hurt as I have banged my head on the desk so many times. I have written an extensible set of classes that allow me to build SSIS packages dynamically via a web front end. I am finding this code is working OK, but I have this silly bug.
The code is trying to generate an SSIS package that does somethign very simple, and transfer data from a 10 coumn table with a mix of data types, move through another component that adds a couple of extra columns on basic on some variables, then map it onto a OLD DB destination. This code works fine, until I start using strings of various lengths.
When the package runs, it fails validation with errors saying that truncation may occur as I am trying to put a 100 character string, into a 50 character string. The error is logical as you wouldn';t want to do that, but this is not what I am doing. I am actually transfering data from a 50 character string into a 100 character string. When I try it with a table where the strings are the same length at both ends, or no strings are involved, everything works fine, and the data goes from the source to the destination.
I must be setting something slightly wrong which only triggers this problem when the sizes don't match, but the data flow direction is fine, and the data types match. I have included the code from the piece of code that 'writes' the output part of the package. If anyone has any idea what might be going wrong, I would be forever in their debt!
Code Block
private void ConfigureInputColumns(Package package, MainPipe pipe, IDTSOutput90 SourceColumnCollection)
{
_InstanceOfDestination.AcquireConnections(null);
_InstanceOfDestination.ReinitializeMetaData();
IDTSPath90 path = pipe.PathCollection.New();
path.AttachPathAndPropagateNotifications(SourceColumnCollection,_Destination.InputCollection[0]);
IDTSInput90 input = _Destination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
int iIndex = 0;
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the destination
// to add each available virtual input column as an input column.
_InstanceOfDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
}
IDTSExternalMetadataColumn90 exInputColumn;
foreach (IDTSInputColumn90 inColumn in _Destination.InputCollection[0].InputColumnCollection)
{
// create the MAP
// What we need to do here, is say what source column, goes to what destination column.
// we read by index, and we need to map the specifics, we could control just 3 of 20 columns
// to whatever column we wanted in the destination......
// We know the name of the inColumn - It is called inColumn.Name, we need to find the column
// we want to map with by finding its name.
if (inColumn.Name == "BatchID" || inColumn.Name == "ValidationStatus")
{
exInputColumn = _Destination.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
// map it
_InstanceOfDestination.MapInputColumn(_Destination.InputCollection[0].ID, inColumn.ID, exInputColumn.ID);
}
else if (Mapping.Map.ContainsKey(inColumn.Name))
{
exInputColumn = _Destination.InputCollection[0].ExternalMetadataColumnCollection[Mapping.Map[inColumn.Name]]; // inColumn.Name
// map it
_InstanceOfDestination.MapInputColumn(_Destination.InputCollection[0].ID, inColumn.ID, exInputColumn.ID);
}
}
_InstanceOfDestination.ReleaseConnections();
}
Hello Guys I am using XML files and dumping data to sql server 2005 , i have field called as rate which is having money as datatype and i am getting following error
LoadDataXML to XML Source -- LoadDataXML [907]: The value was too large to fit in the output column "RATE" (95245).
please help me out with the solution of this ...the data which is coming from xml file is unsigned itneger single bit and my database is having money .so should i use a conversion task in between if any body can give idea about this that would be great , if you want more information tell me ...
thanks
krish
Hello,
I'm having a problem with one of my packages due to a truncation warning that I can't get rid of. It's not the end of the world, because the package still works. It's just extremely frustrating.
The problem arises in a derived column item in a data flow task. There is a postcode field in the data flow which has space for 20 characters. I create a derived column from this which simply removes any spaces:
Derived Column Name: Postcode
Derived Column: Replace 'Postcode'
Expression: REPLACE(" ",Postcode,"")
Data Type: string [DT_STR]
Length: 20
Code Page: 1252 (ANSI - Latin I)
However when I use this expression, or anything else which uses the replace function, I end up with the warning message:
Warning 1 Validation warning. Create Staging Tables: Derived Column [20555]: The result string for expression "REPLACE(" ",Postcode,"")" may be truncated if it exceeds the maximum length of 4000 characters. The expression could have a result value that exceeds the maximum size of a DT_WSTR.
I have tried everything I can think of to get rid of the warning. Is there some way I can use the replace function, but not have the system convinced that I'm about to go over the maximum size limit?
Many thanks
I am trying to write a SSIS package to move data from an access database table to a SQL db table.
I have a field which has data that too long for NVARCHAR(255), so, I end upin this error:
"A truncation error occurred on the specified object of the specified component"
Any work around?????
i define a clumn like this:
clumn name: Body
data type: ntext
then i run a store procedure which is defined as :
create insert_artcle as
(
@body ntext
)
insert into article{body}
values{@body}
but the data get into the database is truncated to 8 charactors
what is the problem? Is it has something to do with the ntext size 16?
Scenario:
I have a database in SQL Server 2000 with 90gb of data. It is growing every day with a some millions records. In order to get superior performance I rebuild the indexes every night. The job takes about 1.5 hours, and sometimes causes to the log file to grow to 30-40 gigs. I do a backup of the log files every 20 minutes, and this causes an log truncation to happen automatically (according to the MS documention).
Problem:
The log files makes the disk goes full. I cannot insert new disks at this point.
Question:
When rebuilding the indexes takes 1.5 hours, and I do a log backup (which also does a truncation) every 20 minutes, will the log backup ever get to do something with the transaction log while its still working on a transaction (doing the rebuild of index)?
Observation:
It seems like the 20 minute log backup is of no help because I see the log files grows so big.
BR
Henrik
Norway
I had to convert "2005-05-17 16:35:49.000" date to string usingCAST(a.ps_ActImplDate AS varchar(20)), 'Not Assigned'), since I had to assign "not assigned" to all null datesProblem here is when I am displaying date in string format it is only displaying like "May 17 2005 4:35PM" missing seconds part. I need everything because I need to query using same date on other table. How to convert datetime to string without any truncationFollowing a snapshot of how it each datetime looks before and after conversion2005-05-17 16:35:49.000 May 17 2005 4:35PM2005-05-17 16:27:03.000 May 17 2005 4:27PM2005-05-17 16:27:03.000 May 17 2005 4:27PM2005-05-17 16:35:48.000 May 17 2005 4:35PM
View 2 Replies View RelatedI have a problem while importing data from Excel to SQL Server.The leading zeros in data get truncated.Even if I try and change the excel data column as 'Text' and copy paste the data back into the Text column, the problem persists.Does any one have any thoughts about this problem?
View 14 Replies View RelatedHow can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.
Gurus,
Everyweek I am truncating the logfiles. I want to automate the truncate logfiles on every sunday. Anyone can help me to automate this process..
Thanks
-- Krishna
We are in the process of testing our software against sql 2005 as we are about to migrate up from sql 2000. One thing I have noticed is that when we insert data using our import process(.net app) if the string is too long it is truncated but still inserted into the table. but when we run the same against sql 2005 it falls over with error message about string being too long. Is there a setting in sql 2005 etc that needs to be set to allow truncation.
View 2 Replies View RelatedHi,
I need to delete entries from a couple of tables in a database, because these tables are quite big, the transaction log is continuously growing and therefore fills up the hard disk. I need to truncate the transaction log at intervals. A sample of my script below:
USE Mediation
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
COMMIT
BEGIN TRANSACTION
DELETE tblAlarmLog
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO
DELETE tblBackPlanes
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO
DELETE tblCPU
WHERE CreateDT <= dateadd(yy,-1,getdate())
GO
Delete tblClients
WHERE pbxid IN (Select c.pbxid from tblClients AS c left join tblpbx AS p on c.pbxid = p.pbxid
WHERE p.LastDialupDT <= dateadd(yy,-1,getdate()))
GO
COMMIT TRANSACTION
BEGIN TRAN
BACKUP LOG Mediation
WITH TRUNCATE_ONLY
GO
COMMIT TRAN
BEGIN TRAN
DBCC SHRINKFILE (Mediation_Log, 100)
GO
COMMIT TRAN
This is just the first part of my script, more tables are queried after the COMMIT.
I am using the Simple database model so the log should be automatically truncated on checkpoint. Is a COMMIT like a checkpoint?
Anyway, I did some research and found the way to truncate the log is by backing up the LOG. And then using DBCC SHRINKFILE to shrink the physical file on disk.
I receive the errors below during execution:
Server: Msg 3021, Level 16, State 1, Line 4
Cannot perform a backup or restore operation within a transaction.
Server: Msg 3013, Level 16, State 1, Line 4
BACKUP LOG is terminating abnormally.
Server: Msg 8920, Level 16, State 2, Line 4
Cannot perform a ShrinkFile operation inside a user transaction. Terminate the transaction and reissue the statement.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This is a long running query which will run over night, is there any way to prevent the transaction log from growing too large?
Thanking you...
Using SQL 7 with an Access 2000 MDB front end with ODBC linked tables.Have a DateModified field which previously was smalldatetime. Changed overthe weekend to datetime. Field is updated with a trigger that sets value toGetDate().One record in the table now cannot be updated. In Access, get message:[microsoft][odbc sql server driver]fractional truncation (#0)[microsoft][odbc sql server driver]timeout expired (#0)In QA (using Update), no message; just hangs.Note that many records in the table have been modified since the change, andthere doesn't seem to be a problem. It just seems to be this one record(possible others that we haven't come across yet) that will not allowupdates.Any ideas how to resolve this?Thanks!Neil
View 4 Replies View RelatedI'm running SQL Profiler to analyze the queries being run against mySQL server. The problem is, that in the Profiler results, theTextData column, which contains the SQL statements executed, istruncated so I don't get to see the entire SQL statements. I've gonethrough all the documentation and menu options but I can't seem tofind a way to make it show me the entire SQL statement regardless oflength. Can anybody help?Thanks,Huey
View 1 Replies View Related
Ok, this is starting to drive me nuts, I've been trying to get this to work for 2 days now. I have a .csv file that I'm reading and importing the data into my table. I defined Derived columns for the type and the lengths as well. I have one column that keeps kicking out this error: The column in the db has a length of 50, the column in my derived column is set to 50, now there are some strings in the colum from the csv file that contain more then 50 characters so I have this in my derived column section: to get the first 50 characters.
(DT_WSTR,50)NAME
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "NAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
so to some it up. My field (name) in my .csv file can contain 2 to 80 characters but I only want the first 50 and if the field contains greater then 50 characters I'm getting the error above.
Has anyone else run into something like this? If so how did you get it working?