log files

I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
order to optimize for speed, I set the database recovery mode to Simple, both
on my user DB and tempdb in order to avoid huge log files.
(1) why does my tempdb log file still grow large (10 GB) ?
(2) why can't I specify "unrestricted growth" on additional log files for
tempdb? (every time I specify a location with ample HD space for an
additional tempdb log file in the DB Properties, the server sets Autogrowth
to a restricted growth and my changes don't take effect)Can you elaborate on exactly how you are doing these mass insertions?
--
Andrew J. Kelly SQL MVP
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> I'm using SQL Server 2005 Developer Edition and doing mass data insertion.
> In
> order to optimize for speed, I set the database recovery mode to Simple,
> both
> on my user DB and tempdb in order to avoid huge log files.
> (1) why does my tempdb log file still grow large (10 GB) ?
> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets
> Autogrowth
> to a restricted growth and my changes don't take effect)|||> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets Autogrowth
> to a restricted growth and my changes don't take effect)
What max size are the files set to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
> order to optimize for speed, I set the database recovery mode to Simple, both
> on my user DB and tempdb in order to avoid huge log files.
> (1) why does my tempdb log file still grow large (10 GB) ?
> (2) why can't I specify "unrestricted growth" on additional log files for
> tempdb? (every time I specify a location with ample HD space for an
> additional tempdb log file in the DB Properties, the server sets Autogrowth
> to a restricted growth and my changes don't take effect)|||The raw data is in text files. I created a SSIS package in Management Studio
using the DTS-like import/export tool that reads the file and fills a table
in tempdb. Every time I read in a new table, the SSIS package drops and
re-creates the existing table in tempdb.
"Andrew J. Kelly" wrote:
> Can you elaborate on exactly how you are doing these mass insertions?
> --
> Andrew J. Kelly SQL MVP
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion.
> > In
> > order to optimize for speed, I set the database recovery mode to Simple,
> > both
> > on my user DB and tempdb in order to avoid huge log files.
> >
> > (1) why does my tempdb log file still grow large (10 GB) ?
> > (2) why can't I specify "unrestricted growth" on additional log files for
> > tempdb? (every time I specify a location with ample HD space for an
> > additional tempdb log file in the DB Properties, the server sets
> > Autogrowth
> > to a restricted growth and my changes don't take effect)
>
>|||The default for log files was 100MB, I think... But since I could not set it
to "unrestricted growth", I simply changed the max size to 20GB to make sure
I never run out of space.
"Tibor Karaszi" wrote:
> > (2) why can't I specify "unrestricted growth" on additional log files for
> > tempdb? (every time I specify a location with ample HD space for an
> > additional tempdb log file in the DB Properties, the server sets Autogrowth
> > to a restricted growth and my changes don't take effect)
> What max size are the files set to?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
> > order to optimize for speed, I set the database recovery mode to Simple, both
> > on my user DB and tempdb in order to avoid huge log files.
> >
> > (1) why does my tempdb log file still grow large (10 GB) ?
> > (2) why can't I specify "unrestricted growth" on additional log files for
> > tempdb? (every time I specify a location with ample HD space for an
> > additional tempdb log file in the DB Properties, the server sets Autogrowth
> > to a restricted growth and my changes don't take effect)
>|||Reason I asked is that when you set to unlimited, SQL Server will actually store and show the
maximum size for a log file (which is 2TB). This is, of course the same as unlimited (as any
database file cannot grow beyond the max specification) but the presentation might confuse a bit. So
if you set to maxsize and it show 2TB, all is fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
> The default for log files was 100MB, I think... But since I could not set it
> to "unrestricted growth", I simply changed the max size to 20GB to make sure
> I never run out of space.
> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
>> > order to optimize for speed, I set the database recovery mode to Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>|||Will SQL delete the table in TEMPDB when it get restarted? Would it be
easier just to create a blank database and store data there?
James
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Reason I asked is that when you set to unlimited, SQL Server will actually
> store and show the maximum size for a log file (which is 2TB). This is, of
> course the same as unlimited (as any database file cannot grow beyond the
> max specification) but the presentation might confuse a bit. So if you set
> to maxsize and it show 2TB, all is fine.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>> The default for log files was 100MB, I think... But since I could not set
>> it
>> to "unrestricted growth", I simply changed the max size to 20GB to make
>> sure
>> I never run out of space.
>> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files
>> > for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets
>> > Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data
>> > insertion. In
>> > order to optimize for speed, I set the database recovery mode to
>> > Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files
>> > for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets
>> > Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>
>|||Sorry, but I didn't follow the other threads of this discussion, so I don't know that table you are
referring to...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James" <hushdontspamme@.hotmail.com> wrote in message
news:%236HH2kQIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Will SQL delete the table in TEMPDB when it get restarted? Would it be easier just to create a
> blank database and store data there?
> James
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>> Reason I asked is that when you set to unlimited, SQL Server will actually store and show the
>> maximum size for a log file (which is 2TB). This is, of course the same as unlimited (as any
>> database file cannot grow beyond the max specification) but the presentation might confuse a bit.
>> So if you set to maxsize and it show 2TB, all is fine.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>> The default for log files was 100MB, I think... But since I could not set it
>> to "unrestricted growth", I simply changed the max size to 20GB to make sure
>> I never run out of space.
>> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
>> > order to optimize for speed, I set the database recovery mode to Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>
>|||Perhaps I should add: Tempdb is re-created every time SQL Server starts.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James" <hushdontspamme@.hotmail.com> wrote in message
news:%236HH2kQIGHA.1192@.TK2MSFTNGP11.phx.gbl...
> Will SQL delete the table in TEMPDB when it get restarted? Would it be easier just to create a
> blank database and store data there?
> James
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%239%232RJQIGHA.2628@.TK2MSFTNGP15.phx.gbl...
>> Reason I asked is that when you set to unlimited, SQL Server will actually store and show the
>> maximum size for a log file (which is 2TB). This is, of course the same as unlimited (as any
>> database file cannot grow beyond the max specification) but the presentation might confuse a bit.
>> So if you set to maxsize and it show 2TB, all is fine.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:43C1914A-DFA4-4B92-B1E2-FF1E4F30DA11@.microsoft.com...
>> The default for log files was 100MB, I think... But since I could not set it
>> to "unrestricted growth", I simply changed the max size to 20GB to make sure
>> I never run out of space.
>> "Tibor Karaszi" wrote:
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>> What max size are the files set to?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ken Abe" <KenAbe@.discussions.microsoft.com> wrote in message
>> news:F538B5DA-328E-489C-AF6A-FA14658010EC@.microsoft.com...
>> > I'm using SQL Server 2005 Developer Edition and doing mass data insertion. In
>> > order to optimize for speed, I set the database recovery mode to Simple, both
>> > on my user DB and tempdb in order to avoid huge log files.
>> >
>> > (1) why does my tempdb log file still grow large (10 GB) ?
>> > (2) why can't I specify "unrestricted growth" on additional log files for
>> > tempdb? (every time I specify a location with ample HD space for an
>> > additional tempdb log file in the DB Properties, the server sets Autogrowth
>> > to a restricted growth and my changes don't take effect)
>>
>sql

Log Errors, but Continue Processing Data

Do you have to set the Error Output on DF components to "Fail Component" in order to get the errors?

What I would LIKE to do is a combination of "Ignore Failure" and "Fail Component". You see, I am using the Logging feature in my package that creates the sysdtslog90 table in the SQL database. The errors that I am logging make sense and have enough information for my purposes.

The problem is that I would like to continue processing the data and not have it stop when a data error occurs. I REALLY do not want to Redirect Rows unless it is necessary for me to do what I am asking.

Using Ignore Failure on both the source text file and destination SQL table allows the "good" data to be inserted, but I cannot get any info on the columns in error. Conversely, if I choose to Fail component, I get the info on the columns in error, but only the data that was inserted before the error was encountered is inserted into the table.

Suggestions?

You can't "ignore failure" and expect to obtain error information on columns. After all, you're ignoring the error.|||

ronemac wrote:

The problem is that I would like to continue processing the data and not have it stop when a data error occurs. I REALLY do not want to Redirect Rows unless it is necessary for me to do what I am asking.

Redirect Rows is the only way to capture error information about the rows, and still be able to send them to a destination.

|||

And that makes complete sense.. What I am looking for is an option to fail component, but insert the "good data". I don't care that the component fails - just as long as the "good" data makes it. I can then use the errors to let the data creator know that there were issues with some of the data - and give them an idea of what the bad data is.

I am guessing there is no such option since you did not reply with a suggestion for me?

|||

That being said....

I am trying to use the Fast Load option, and I noticed that you cannot redirect rows with Fast Load. All of the things I read pointed to using Fast Load to avoid the SQL logging - using Fast Load is similar to Bulk Insert from what I gather. True statement? Other suggestions?

Thanks

|||

ronemac wrote:

That being said....

I am trying to use the Fast Load option, and I noticed that you cannot redirect rows with Fast Load. All of the things I read pointed to using Fast Load to avoid the SQL logging - using Fast Load is similar to Bulk Insert from what I gather. True statement? Other suggestions?

Thanks

True. However, you can redirect rows -- sort of. Using the fast load option and redirect error rows, if an error is encountered, then the whole batch (as determined by MaxInsertCommitSize) is redirected. From this point, you could hook the error flow up to another OLE DB destination, this time not using fast load. Coming out of here, you can capture the error row. This will help to speed up processing, depending on the size of the batch that gets redirected.

Or, you build in error handling upstream. For instance, if you errors are mostly referential integrity errors, you can use a lookup upstream to see if the incoming record exists in the destination or not. If it doesn't, send it to the OLE DB Destination. If it does, either update it or throw it away.|||

Okay, just one more question... Is it the Source (text file) or Destination (SQL table) where I want to redriect the rows?

The errors I have run into so far (and probably will in the futre) are some of the data types FROM THE SOURCE do not match or are not valid based on the SQL data type.

Thanks again.

|||You may want to redirect both. It's up to you on which errors you want to capture. Errors that happen when the record is attempting to be inserted should be caught with an error redirect on the OLE DB destination, for instance.|||In general, I think the earlier you can eliminate or redirect rows, the better. So my first thought would be eliminate it at the source. Why do any more processing of a row that's in error than you absolutely have to?|||I agree. Thanks|||

jwelch wrote:

In general, I think the earlier you can eliminate or redirect rows, the better. So my first thought would be eliminate it at the source. Why do any more processing of a row that's in error than you absolutely have to?

Except that it may not be in error at the source.|||

Well, I thought I was done here... This is a pretty simple quesiton that you may know the answer to.

These are 2 of the errors I took out of the Progress list after the package has run and I have chosen to Fail Component (Instead of Redirect Rows) on the SQL destination DF component.

[Destination - Student [49]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[Destination - Student [49]] Error: There was an error with input column "STU_FREE_RDUC_LNCH" (910) on input "Destination Input" (62). The column status returned was: "The value could not be converted because of a potential loss of data.".

So, if I redirect the rows (instead of Fail Component) and throw in a script task that sets some outputs for me to log - to include the error description - it works great and I do log the error description that is the same as sown in the first error above.

Can I not capture that second error from the 2 errors above when I redirect rows? Isn't there something that will tell me WHAT caused the row to fail?

Life would be SO MUCH easier if I could just get that column name when I redirect the rows.

Thanks

|||

The reason for this thread has to do with me using the built in SQL logging (to sysdtslog90) and then querying this table to get "meaningful" information.

I found a sort-of-workaround for this issue.

Basically, what I am doing is setting the Error Output on the source file to Ignore Failure so that the "good" data loads and on the OLE DB Destination I am setting the Error Output to Redirect Row to a second OLE DB Destination that is mapped to the same table (knowing the rows will fail). I am then setting the Error Output to Fail Component on the second OLE DB Destination. This is where I get the column error message

Doing this will allow me to capture the number of rows processed in the source, the number wrote to the destination, and most importantly it will give me the column name that caused the error...

Kind of ugly, but it gives me what I want.

log all queries

Hi!

In order to debug a program written for us I'd like to log all
queries to a database/a specific table in MS SQL Server 2000.

Are there any options to get a logging like this and how do I
enable it?

--
Marco Dieckhoff
icq# 22243433
GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keysNot exactly. One clumsy option, is to turn on the profiler to track the
SELECT statements & dumb them into an ASCII file. On large databases with
heavy transactions, this may not be worth it though.

--
- Anith
( Please reply to newsgroups only )|||"Marco Dieckhoff" <dieck@.gmx.de> wrote in message
news:bj9m97$nes$2@.rzcomm2.rz.tu-bs.de...
> Hi!
> In order to debug a program written for us I'd like to log all
> queries to a database/a specific table in MS SQL Server 2000.
> Are there any options to get a logging like this and how do I
> enable it?
> --
> Marco Dieckhoff
> icq# 22243433
> GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys

You can use Profiler, and set a filter for the tables/databases that you're
interested in. You can either run the trace from the Profiler GUI, or by
creating a server-side trace with the sp_trace% procs. There is plenty of
information on this in Books Online.

Simon|||Hello,

How about writing a trigger attached to the table in question for
select/update/delete operations that will record data to some audit table?

TK

"Marco Dieckhoff" <dieck@.gmx.de> wrote in message
news:bj9m97$nes$2@.rzcomm2.rz.tu-bs.de...
> Hi!
> In order to debug a program written for us I'd like to log all
> queries to a database/a specific table in MS SQL Server 2000.
> Are there any options to get a logging like this and how do I
> enable it?
> --
> Marco Dieckhoff
> icq# 22243433
> GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys|||Select triggers are not in SQL 2000!!

If you access your tables by stored procedure then you can implement an
auditing function.

John

"Tom Kitta" <tom@.energyshop.com> wrote in message
news:B727b.28098$mk1.12186@.news02.bloor.is.net.cab le.rogers.com...
> Hello,
> How about writing a trigger attached to the table in question for
> select/update/delete operations that will record data to some audit table?
> TK
>
> "Marco Dieckhoff" <dieck@.gmx.de> wrote in message
> news:bj9m97$nes$2@.rzcomm2.rz.tu-bs.de...
> > Hi!
> > In order to debug a program written for us I'd like to log all
> > queries to a database/a specific table in MS SQL Server 2000.
> > Are there any options to get a logging like this and how do I
> > enable it?
> > --
> > Marco Dieckhoff
> > icq# 22243433
> > GPG Key 0x1A6C95BA -- http://www.frankonia-brunonia.de/keys