A Truncation Occurred During Evaluation Of The Expression
Aug 22, 2007
First of all, I get the following error message for one of my packages which uses user variables:
SSIS package "UsageAnalysis.dtsx" starting.
Information: 0x4004300A at Perform xmlState Shredding, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Update Analysis Table, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001700E at UsageAnalysis: A truncation occurred during evaluation of the expression.
Error: 0xC0019004 at UsageAnalysis: The expression for variable "GetAnalysisData" failed evaluation. There was an error in the expression.
Error: 0xC02020E9 at Update Analysis Table, UsageAnalysis Source [1]: Accessing variable "User::GetAnalysisData" failed with error code 0xC001700E.
Error: 0xC0024107 at Update Analysis Table: There were errors during task validation.
Warning: 0x80019002 at Usage Analysis Process: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (5) 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 "UsageAnalysis.dtsx" finished: Failure.
Now my package has the following variables:
GetMaxUsageID: scope package level, type string, statement SELECT MAX(UsageID) AS MaxUsageID FROM XX.XXX
MaxUsageID: scope package level, type int32, default value 0, value get assigned from the following statement executed from sql task that runs GetMaxUsageID variable as above
GetAnalysisData: scope package level, type string, Evaluate as Expression
"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > " + (DT_STR, 8, 1252) @[User::MaxUsageID]
The package has worked fine until MaxUsageID value reached to 10,00,000 and since then I have been getting above mentioned error message. The problematic step is related to Data Flow task where I use GetAnalysisData. I have tried replacing user variable with literal as follows
"SELECT * FROM dbo.UsageAnalysis WHERE UsageID > 1000000"
the error message stays the same. Please note that package has worked fine before and it still works ok if I don't use user variables. Obviously, some of you would see eliminating user variables as workaround but I would appreciate if cause of that error message could be investigated.
Thanks,
Asaf
View 7 Replies
ADVERTISEMENT
Apr 22, 2015
I'm migration SSIS 2005 packages to 2012. It works fine when i run in 2005 but in 2012 it gives an error about truncate ?
(((DT_DATE)@ArchiveStartTime < (DT_DATE)@ArchiveEndTime) ? ((getdate() < (DT_DATE)((DT_WSTR,12)(DT_DATE)(DT_DBDATE)getdate() + " " + @ArchiveEndTime)) && ((DT_DBTIME)getdate() > (DT_DBTIME)@ArchiveStartTime)) : ((getdate() < (DT_DATE)((DT_WSTR,12)(DT_DATE)(DT_DBDATE)getdate() + " " + @ArchiveEndTime)) || ((DT_DBTIME)getdate() > (DT_DBTIME)@ArchiveStartTime))) && (@ChangeLogsRemoved > 0)
User variables:
ArchiveEndTime Sting 14:56
ArchiveStartTime Sting 11:00
View 1 Replies
View Related
Jan 27, 2005
Hi I am trying to do something like the following:
DECLARE @Operator varchar(1)
DECLARE @Rate float
DECLARE @Quantity float
DECLARE @Converted float
SET @Quantity = 6
SET @Operator = '/'
SET @Rate = 2
SET @Converted = 0
@Converted = (@Quantity substituteTheValueOfThis(@Operator) @Rate)
PRINT @Converted
so that the output would be 3
The reason I need to do it like this is that @Operator will change at runtime...
Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.
View 5 Replies
View Related
Jun 23, 2006
I think I know the answer to this but thought I'd ask anyway.
I have a conditional split to check a column for null values or empty string values. It looks like this:
(!ISNULL(Ballot)) || (LEN(TRIM(Ballot)) > 0)
My question is: Are both sides of the expression evaluated? My testing says yes, because a Null value causes an error. Is there a way to short circuit the evaluation like the || operator in C# or the (less than elegant, and seemingly threatening) OrElse operator in VB? Whats the best alternative:
A slightly more complex expression that turns a null value into an empty string
A script component
Two conditional splits
Two paths out of one condtional split
I went with the first option, here is the expression I came up with:
LEN(ISNULL(Ballot) ? "" : TRIM(Ballot)) > 0
View 3 Replies
View Related
Aug 22, 2007
Hi guys,
I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.
The details are as following:
There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.
Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.
After series of consequent executions I have got following log file:
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦
As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.
Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.
Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.
View 5 Replies
View Related
Oct 19, 2007
Hello
When I call ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) I am getting the following error when the data type is Numeric(18, 4):
Expression evaluation caused an overflow. [ Name of function (if known) = ]
The numbers involved are not that big and work fine when ExecuteReader() or ExecuteResultSet(SqlServerCe.ResultSetOptions.None) are called on the same SQL.
Any ideas? Thanks in advance!
Cheers,
Dave
Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim errorDescription As String = String.Empty
Dim numericNumber As String = String.Empty
Try
Using sqlCE As New System.Data.SqlServerCe.SqlCeConnection("Data Source = '" & My.Application.Info.DirectoryPath & "MyDatabase.sdf';")
sqlCE.Open()
Dim sqlCECommand As SqlServerCe.SqlCeCommand = sqlCE.CreateCommand()
sqlCECommand.CommandText = "SELECT SUM(MT.TPM_Measure1) AS CurrentAmount FROM BUS_Table MT"
System.Diagnostics.Debug.WriteLine(sqlCECommand.CommandText)
Dim reader As System.Data.IDataReader = Nothing
If RadioButton1.Checked Then
reader = sqlCECommand.ExecuteReader() 'Works fine
ElseIf RadioButton2.Checked Then
reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.None) 'Works fine
Else
reader = sqlCECommand.ExecuteResultSet(SqlServerCe.ResultSetOptions.Scrollable) 'Causes the error!
End If
If reader.Read() Then
numericNumber = reader(0).ToString()
End If
reader.Close()
reader.Dispose()
End Using
Catch ex As Exception
errorDescription = ex.Message
Finally
Me.lblError.Text = errorDescription
Me.lblNumeric.Text = numericNumber
End Try
End Sub
TPM_Measure1 datatype is Numeric(18,4)
When the above query works the value is: 4053723.6300
View 18 Replies
View Related
Jun 27, 2007
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
View 5 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Nov 26, 2007
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 Related
Aug 30, 2007
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.
View 5 Replies
View Related
Jan 11, 2001
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
View 1 Replies
View Related
May 9, 2008
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.
View 1 Replies
View Related
Jul 23, 2005
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
View 1 Replies
View Related
Jul 23, 2005
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 Related
Mar 16, 2006
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
View 2 Replies
View Related
Jan 26, 2007
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.
View 1 Replies
View Related
Jun 15, 2007
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?
View 7 Replies
View Related
Jan 3, 2008
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();
}
View 7 Replies
View Related
Sep 6, 2007
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
View 5 Replies
View Related
Jan 14, 2008
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
View 5 Replies
View Related
May 24, 2007
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
View 5 Replies
View Related
Feb 14, 2008
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?????
View 21 Replies
View Related
Jul 23, 2005
/* goal: dynamic evaluation of table rowplatform: sql 2000*/use northwind;declare @tbl sysnameset @tbl = 'customers'EXEC('select count(*) from ' +@tbl)/*Why the following dynamic evaluation would failIF EXEC('select count(*) from ' +@tbl) = 0PRINT 'no rows'Its variant,IF exists (EXEC('select count(*) from ' +@tbl)) = 0PRINT 'no rows'*/Do I know more sleep :)TIA.
View 31 Replies
View Related
Aug 21, 2006
We're going to set up a demo of an Analysis and Reporting server. Does the Evaluation Edition of 2005 come with things like the BI studio, Management Studio and other client-side tools?
Also, does anyone know if the Eval Analysis Services is the full product (e.g. semi-additive measures, ...?)
Thanks a bunch
View 1 Replies
View Related
Nov 17, 2006
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?
View 2 Replies
View Related
Mar 22, 2005
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
View 2 Replies
View Related
Jun 15, 2005
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 Related
Jun 7, 2004
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?
View 14 Replies
View Related
Jun 13, 2001
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
View 3 Replies
View Related
Mar 24, 2004
How 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.
View 2 Replies
View Related
Apr 10, 2008
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
View 15 Replies
View Related
Apr 24, 2008
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 Related