LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:

> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt
.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.

>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> > Hi,
> > The log of the TEMPDB database is growing and growing. What should i do ?
> > What did i arrive to this situation?
> > For information we are using SSIS and OLAP, maybe it will help to answer.
> > Thanks.
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>> Hi,
>> The log of the TEMPDB database is growing and growing. What should i do ?
>> What did i arrive to this situation?
>> For information we are using SSIS and OLAP, maybe it will help to answer.
>> Thanks.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.
Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.
|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:

> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>
|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.

>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
sql

'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.

I have been developing a large SSIS project and it uses scripts extensively.

During development, i encountered no problems.

However, when i tried executing them on a server (windows server 2003), i was given this error for all the scripts:

Here is a brief log:

PackageStart,servername,network\login,PkgName,{A6778813-1F3A-4133-A00C-6F02AC8CC8B1},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:26 PM,3/10/2006 5:24:26 PM,0,0x,Beginning of package execution.

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Variables' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 26 Columns 51-63
Line Text: Dim DaysToKeepErrorFile As Integer = CInt(Dts.Variables("vDaysToKeepErrorFiles").Value.ToString)

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 49 Columns 25-31
Line Text: Dts.Log(MaintenanceMsg, 0, ZeroByte)

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 55 Columns 17-23
Line Text: Dts.Log("Script Error:" + ex.Message, 0, ZeroByte)

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,5,0x,The script files failed to load.
PackageEnd,servername,network\login,PkgName,{A6778813-1F3A-4133-A00C-6F02AC8CC8B1},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,1,0x,End of package execution.

I've been using the same scripts for months in development and testing. Both dev and test server has SQL 2005 and SSIS installed.

What causes this?

Seems very strange, as obvious Log is a method of the ScriptObjectModel class.

Can you logon to the server anbd built a test package on the server and try running it there an then in BIDS, obviously using a script task a Log.

Has this ever worked on the problem server, or is it a recent failure? Are the good and bad machines running the same service pack? I am not clear on what machines work and fail and if failures relate to some or all machines.

|||

If you find it strange, imagine me! haha...

Do i need VS2005 installed on the server to open the VBA window to edit the script? I got this error when trying :

--

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The operation could not be completed. (Microsoft.VisualBasic.Vsa.DT)

1. I've checked the assembly versions of Microsoft.SqlServer.ManagedDTS.dll and microsoft.sqlserver.scripttask.dll on the server and my machine, they're the same. 9.0.242.0

2. This is the first time we're trying to deploy on the server. It has worked fine on four other machines : 2 desktops and 2 laptops.

3. The server is running SP1 the good machines aren't. I figured this shouldn't be a problem as SP1 should be 'backward compatible'. I'll try to get SP1 on the good machines just to eliminate the problem, but it'll take some time, which i don't have.

Any other ideas?

|||

Things have gotten more interesting.

My package has multiple scripts and i discovered that some work, and some dont!

What makes it interesting is that each of these scripts are identitical (i copied and pasted the original script i wrote), except for name and variable values.

If the script's content is identical, and both use dts.log and dts.variables object, why is it that one works and one doesnt?

What on earth could be causing this?

Another question:

where is the reference 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel' referenced from? its not part of the references i see in the VSA ide but i see it in the package xml. I couldn't find it in the windows\assembly folder either. Anybody knows ANYTHING about this issue?

|||

Well, i've got it solved.

I copied a working script, deleted the bad ones and pasted them in again. I think the important step is after pasting the copied script task, i opened the script in the editor and closed it. Somehow, this seemed to work. Although it still doesn't solve the mystery of why it worked on some machines but failed on others. The only difference is the server was running Windows Server 2003 and the development was winxp.

So to anyone whos reusing scripts, i suggest opening the 'design script' window and closing it after pasting, just to be safe and make sure SSIS precompiles everything properly.

Log File path in Configuration file does not work.

Hi,

I cannot get the log file path read from the configuration.

If the path in the Connection Manager is invalid, package throws an error "SSIS logging provider has failed to open the log" instead of reading it from the config.file. What am I doing wrong?

Here is the portion of the config file. Everthing else is read from the config file correctly.

- <Configuration ConfiguredType="Property" Path="\Package.Connections[SalesforceConnectLog].Properties[ConnectionString]" ValueType="String"> ConfiguredValue>D:\APPS\BACIAR\SalesforceConnect\SalesforceConnect.log</ConfiguredValue>

</Configuration>

Appreciate a help.

Gulden

I'm not sure about this but it COULD be that the log fails validation before the config gets applied. Try setting it in the package to c:\temp\SalesforceConnect.log or something similar and see what happens.

-Jamie

|||

Jamie,

It worked..

I gather from here then, the path in the Connection Manager should be valid for the target environment. Only then, the real path is being read from the config file.

Thanks a lot....

Gulden

|||

Yeah, sort of. Its an unfortunate consequence of the order in which things occur when the package spins up. It seems the log file location needs to be a valid "place" even though its never actually used - that's why I suggested something as arbitrary as "c:\temp".

-Jamie

|||

What is interesting is, I have Excel file connections in the same package with paths invalid for the target environment. Those work fine... Valid paths are read from the config.

Thanks again..

Gulden

|||

Gulden wrote:

What is interesting is, I have Excel file connections in the same package with paths invalid for the target environment. Those work fine... Valid paths are read from the config.

Thanks again..

Gulden

Yeah but that's not really comparing apples with apples. A connection manager used by a logfile is used differently (i.e. alot more) than others. Also its a different type of connection manager.

I haven't given a very good answer to this thread because frankly, I don't know what the proper answer is. At least it works now anyway.

-Jamie

Log Errors but Continue to Load Data?

Hello,

In my SSIS package I am trying to import a .csv flat file with about 170,000 rows of data with 75 columns (I know this is rather large). I would like to create a SSIS package that loads ALL of the rows of data into a table. This table has it's fields defined (such as money, float, varchar, datetime, etc). I want the data to load to this table even if there is a conversion error converting any field. When the data is loaded to this table, any fields that couldn't be converted should be set to null. Of the 75 columns of data, there are MANY columns that could be invalid.

For example, if the flat file contains the value "00/00/00" for my "PaidDate" field, I would want all of the other fields to be populated and the value for this particular record's "PaidDate" field to be null.

It would also be nice if I could trap any of the fields that caused a problem and log them along with the row.

I know that SSIS supports the "Redirect Row" method for handling data, but in my case, I don't want to redirect it. I want to continue to load it, just with a null value.

Is there an easy way of doing this (i.e. perhaps by using the Advanced Editor for the Flat File Source and setting something in the Input/Output columns)? I really don't want to create a Derived Column or Data Conversion transformation for every field that needs to be converted (b/c there are 75 columns to maintain this for).

I know if I import this file to an Access 2003 database, it imports the data fine and logs any conversion errors to an "_ConversionErrors" table. I am basically looking for this same behavior in SSIS, without having to maintain 75 columns.

TIA

There is one other way I can think of. If you change the output columns of your source to be the same data type as your destination and select "ignore" for all errors then it will just cause that one column of the row to not be loaded into the data flow. Of course you have no way of determining which rows these are and you also have to manage the metadata by hand so I doubt you'd be much better off than a data conversion transformation.|||

Thanks Brent!

I actually started going down this path (convert from the flat file source) right after I posted my initial question. It stinks that I can't identify the rows. I tried to do make the conversion/truncation for each "redirect" to another route and then join them all back together using the UNION ALL, but it turns out you only get three values back from the ErrorOutput columns...A stream reader, an error code, and an error column. This isn't enough information to get back to the row that caused the error without some nasty parsing logic I assume.

Would be nice if there was a way to funnel that stream and re-create a row from it!

Thanks again!

Log Data wrongly "redirected" To Master.dbo.sysdtslog90

I have been using the SSIS Log Provider for SQL Server quite a lot this year with little to no problems. Recently however as I edit or create packages the logging seems to keep going to the master.dbo.sysdtslog90 table as opposed to the [db].dbo.sysdtslog90 as setup in the package. I tried totally deleting the connection from the package and recreating and still had the problem.

Is there some setting on the SQL server itself that can cause this? Our "dba" likes to make changes without letting anyone know about them.

Any ideas?

I've been doing some testing and I went ahead and tried to send the logging to a different db (used existing connection from another project) and lo and behold it works fine - went to existing db from another project. So my question is why would logging go to the master db on a connection that performs everything fine (on the correct db) for all tasks in the package? Smells like a bug to me but I can't put my finger on exactly why it's behaving that way.