Log file auto shrink question

Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.

I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...ThanksThe error mentioned does not refer to transaction log, but rather to data device of your database. Usually it's caused by either BULK INSERT/BCP...IN or an INSERT/UPDATE where the amount of resulting data exceeds the growth capacity of the database. These operations also affect the transaction log but the error would be different if that was the case. There are multiple sources on the net with similar approach. You can check here (http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp) for a fancy SQL-DMO version of it. For a known technique to handle shrinking of transaction log files using T-SQL go to this (http://dbforums.com/t515230.html) post.

log file

How can I delete the transaction logs? Every time I try it errors saying the
file isn't empty and I don't know how to empty it! I've tried everything and
the log file is over 200 mb so far!
Thanks,
ToddHave you tried BACKUP LOG your_db_name WITH TRUNCATE_ONLY?
How did you try to 'delete' the xaction log?
The other thing you might think about is put the DB
recovery option to SIMPLE (equiv to trunc. log on chkpt).
That way, you won't have to manual delete the xaction log.
hth
-a
>--Original Message--
>How can I delete the transaction logs? Every time I try
it errors saying the
>file isn't empty and I don't know how to empty it! I've
tried everything and
>the log file is over 200 mb so far!
>Thanks,
>Todd
>
>.
>|||If you have multiple log files for this database and you want to 'delete'
(i.e. remove one of them), you can try DBCC SHRINKFILE with the EMPTYFILE
option. This do NOT work with the primary transaction log file, though.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Todd Ellington" <todd@.vtserve.com> wrote in message
news:u7zuupjiDHA.2824@.tk2msftngp13.phx.gbl...
> How can I delete the transaction logs? Every time I try it errors saying
the
> file isn't empty and I don't know how to empty it! I've tried everything
and
> the log file is over 200 mb so far!
> Thanks,
> Todd
>

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 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 copy job using robocopy reports errors

Hi,
We are using a sql job to copy log files to a standby server using
robocopy. Often times the sql job status reports a failure however
robocopy itself reports the file copy was successful.
Has anyone run into this before. I would like to find out why these jobs
are reporting failures as they a sending out an aweful lot of notifications.
Thanks
DilanI assume you are using Agent and CmdExec jobsteps? Agent uses the return cod
e from the process you
start to determine whether the execution was successful or not. If the proce
ss (RoboCopy) returns
anything different from 0, you will get failure of that job step. So you nee
d to determine why
RoboCopy returns such a return code. I presume that RoboCopy returns proper
error messages, so just
define an output file for the job step and check there for the error message
s from RoboCopy.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dilan A" <dilan.a@.youtelus.net> wrote in message news:GnXTf.8832$A4.8081@.clgrps12...seagreen">
> Hi,
> We are using a sql job to copy log files to a standby server using robocop
y. Often times the sql
> job status reports a failure however robocopy itself reports the file copy
was successful.
> Has anyone run into this before. I would like to find out why these jobs a
re reporting failures as
> they a sending out an aweful lot of notifications.
> Thanks
> Dilan

Log copy job using robocopy reports errors

Hi,
We are using a sql job to copy log files to a standby server using
robocopy. Often times the sql job status reports a failure however
robocopy itself reports the file copy was successful.
Has anyone run into this before. I would like to find out why these jobs
are reporting failures as they a sending out an aweful lot of notifications.
Thanks
Dilan
I assume you are using Agent and CmdExec jobsteps? Agent uses the return code from the process you
start to determine whether the execution was successful or not. If the process (RoboCopy) returns
anything different from 0, you will get failure of that job step. So you need to determine why
RoboCopy returns such a return code. I presume that RoboCopy returns proper error messages, so just
define an output file for the job step and check there for the error messages from RoboCopy.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dilan A" <dilan.a@.youtelus.net> wrote in message news:GnXTf.8832$A4.8081@.clgrps12...
> Hi,
> We are using a sql job to copy log files to a standby server using robocopy. Often times the sql
> job status reports a failure however robocopy itself reports the file copy was successful.
> Has anyone run into this before. I would like to find out why these jobs are reporting failures as
> they a sending out an aweful lot of notifications.
> Thanks
> Dilan

Log copy job using robocopy reports errors

Hi,
We are using a sql job to copy log files to a standby server using
robocopy. Often times the sql job status reports a failure however
robocopy itself reports the file copy was successful.
Has anyone run into this before. I would like to find out why these jobs
are reporting failures as they a sending out an aweful lot of notifications.
Thanks
DilanI assume you are using Agent and CmdExec jobsteps? Agent uses the return code from the process you
start to determine whether the execution was successful or not. If the process (RoboCopy) returns
anything different from 0, you will get failure of that job step. So you need to determine why
RoboCopy returns such a return code. I presume that RoboCopy returns proper error messages, so just
define an output file for the job step and check there for the error messages from RoboCopy.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dilan A" <dilan.a@.youtelus.net> wrote in message news:GnXTf.8832$A4.8081@.clgrps12...
> Hi,
> We are using a sql job to copy log files to a standby server using robocopy. Often times the sql
> job status reports a failure however robocopy itself reports the file copy was successful.
> Has anyone run into this before. I would like to find out why these jobs are reporting failures as
> they a sending out an aweful lot of notifications.
> Thanks
> Dilan

locks caused by Sch-S

I have SQL Server 2005 EE build 3186. And on the server several times
occurred errors caused by queries timeouts. When I look at locks, I see what
the timeouts are caused by reindexation job, which rebuilding indices. The
job is blocked by other connection, which hold Sch-S lock on one of tables.
The other connection belongs to one application which call one of stored
procedures. The stored procedure only do select from several tables, not
modify any data and not start any transactions.
sp_who2 shows what that application send last batch about 7 hours before
start of reindexation job. So the lock was bheld for 7 hours.
May someone tell, is it known problem? How it may be fixed or prevented?
Hi
Do not run reindex during the work hours. Moreover, examime only heavy
fragmented tables/indexs abd defragment only them.
Use alter index ... with option ONLINE (for more details see BOL)
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:EE853477-3366-4D83-B999-D86A7DB7C563@.microsoft.com...
>I have SQL Server 2005 EE build 3186. And on the server several times
> occurred errors caused by queries timeouts. When I look at locks, I see
> what
> the timeouts are caused by reindexation job, which rebuilding indices. The
> job is blocked by other connection, which hold Sch-S lock on one of
> tables.
> The other connection belongs to one application which call one of stored
> procedures. The stored procedure only do select from several tables, not
> modify any data and not start any transactions.
> sp_who2 shows what that application send last batch about 7 hours before
> start of reindexation job. So the lock was bheld for 7 hours.
> May someone tell, is it known problem? How it may be fixed or prevented?
|||Reindex run not during work hours - between time when that query which caused
Sch-S lock was executed and reindexation was started, was 7 hours - query
place Sch-S lock at 9:30, and reindexation started at 16:40. And the
resulting locks was found several hours lately. Alter index, even with online
option, still is not fully online - it need to get Sch-M lock for some short
period of time. Because Sch-M is not compatible with Sch-S, reindexation was
locked by Sch-S. Next, all other queries to that table was also locked - by
Sch-M, because of algorithm which SQL Server use for processing queries when
encountering locks (described in Kalen Delaney's books).
Looking at Sch-S description, I can not understand why the lock, which
should be held for milliseconds, lasted for more than 7 hours.
"Uri Dimant" wrote:

> Hi
> Do not run reindex during the work hours. Moreover, examime only heavy
> fragmented tables/indexs abd defragment only them.
> Use alter index ... with option ONLINE (for more details see BOL)
>
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:EE853477-3366-4D83-B999-D86A7DB7C563@.microsoft.com...
>
>

locks caused by Sch-S

I have SQL Server 2005 EE build 3186. And on the server several times
occurred errors caused by queries timeouts. When I look at locks, I see what
the timeouts are caused by reindexation job, which rebuilding indices. The
job is blocked by other connection, which hold Sch-S lock on one of tables.
The other connection belongs to one application which call one of stored
procedures. The stored procedure only do select from several tables, not
modify any data and not start any transactions.
sp_who2 shows what that application send last batch about 7 hours before
start of reindexation job. So the lock was bheld for 7 hours.
May someone tell, is it known problem? How it may be fixed or prevented?Hi
Do not run reindex during the work hours. Moreover, examime only heavy
fragmented tables/indexs abd defragment only them.
Use alter index ... with option ONLINE (for more details see BOL)
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:EE853477-3366-4D83-B999-D86A7DB7C563@.microsoft.com...
>I have SQL Server 2005 EE build 3186. And on the server several times
> occurred errors caused by queries timeouts. When I look at locks, I see
> what
> the timeouts are caused by reindexation job, which rebuilding indices. The
> job is blocked by other connection, which hold Sch-S lock on one of
> tables.
> The other connection belongs to one application which call one of stored
> procedures. The stored procedure only do select from several tables, not
> modify any data and not start any transactions.
> sp_who2 shows what that application send last batch about 7 hours before
> start of reindexation job. So the lock was bheld for 7 hours.
> May someone tell, is it known problem? How it may be fixed or prevented?|||Reindex run not during work hours - between time when that query which caused
Sch-S lock was executed and reindexation was started, was 7 hours - query
place Sch-S lock at 9:30, and reindexation started at 16:40. And the
resulting locks was found several hours lately. Alter index, even with online
option, still is not fully online - it need to get Sch-M lock for some short
period of time. Because Sch-M is not compatible with Sch-S, reindexation was
locked by Sch-S. Next, all other queries to that table was also locked - by
Sch-M, because of algorithm which SQL Server use for processing queries when
encountering locks (described in Kalen Delaney's books).
Looking at Sch-S description, I can not understand why the lock, which
should be held for milliseconds, lasted for more than 7 hours.
"Uri Dimant" wrote:
> Hi
> Do not run reindex during the work hours. Moreover, examime only heavy
> fragmented tables/indexs abd defragment only them.
> Use alter index ... with option ONLINE (for more details see BOL)
>
>
> "andsm" <andsm@.discussions.microsoft.com> wrote in message
> news:EE853477-3366-4D83-B999-D86A7DB7C563@.microsoft.com...
> >I have SQL Server 2005 EE build 3186. And on the server several times
> > occurred errors caused by queries timeouts. When I look at locks, I see
> > what
> > the timeouts are caused by reindexation job, which rebuilding indices. The
> > job is blocked by other connection, which hold Sch-S lock on one of
> > tables.
> > The other connection belongs to one application which call one of stored
> > procedures. The stored procedure only do select from several tables, not
> > modify any data and not start any transactions.
> > sp_who2 shows what that application send last batch about 7 hours before
> > start of reindexation job. So the lock was bheld for 7 hours.
> > May someone tell, is it known problem? How it may be fixed or prevented?
>
>