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.
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.
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!
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.
Hello 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.
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.
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
Hi,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
Hello,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.
We 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?
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!
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(); }
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.
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
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?
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"
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?
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
I 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?
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.
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?
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
I'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
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?
I am getting real stressed out because while trying to import an excel file into a temporary table i get a truncation error...
The max lenght used in the origin column is about 800 characters. So how the hell can i get the column to load? Jesus... this should be easy task... :P
Warning 1 Validation warning. Data Flow Task: Destination - TransItem [25]: Truncation may occur due to inserting data from data flow column "Info" with a length of 128 to database column "Column 4" with a length of 50. Package3.dtsx 0 0
Hi! The product description table contains a field, long_desc varchar(8000). Is there any way to get the entire description? The query, 'select long_desc from product_desc' always truncates characters more than 255 characters long. Even exporting the data to an Excel spreadsheet using DTS does the same thing.
