"Accesshelp" <Accessh***@discussions.microsoft.com> wrote in message
news:179C5290-4BC6-4B19-BD44-BB37653C92A4@microsoft.com...
> Hello all,
>
> How do I manage the transaction log file (ldf) so that it would not keep
> growing and so that I can import the data into the database?
>
> I am using the SQL Server 2005 Express.  All my data are in csv files, and
> I
> import them into SQL via bcp.
>
> Initially, I set the Autogrowth of log file unrestricted.  Then it grew up
> to 8 GB.  Because of that I was not able to run the queries in SQL.
>
> Then I created another database by setting the log file to 25 MB max with
> restricted growth.  Now I am no longer able to import the data from csv
> via
> bcp because the SQL is not able to write to the log.
>
> I will end up importing 4 GB of transactions into SQL from csv files.  Is
> there a way I can turn off the recording to the log during the import and
> turn it back on after the import?
>
> Thanks.

"bass_player" wrote:

> You can switch to SIMPLE recovery model before the BCP task and switch back
> to FULL recovery model.  I doubt that this is a highly critical database
> since it is running on SQL Server Express so I'd recommend sticking with
> SIMPLE recovery model. Just remember to backup the database before the BCP
> task just to be sure
>
> "Accesshelp" <Accessh***@discussions.microsoft.com> wrote in message
> news:179C5290-4BC6-4B19-BD44-BB37653C92A4@microsoft.com...
> > Hello all,
> >
> > How do I manage the transaction log file (ldf) so that it would not keep
> > growing and so that I can import the data into the database?
> >
> > I am using the SQL Server 2005 Express.  All my data are in csv files, and
> > I
> > import them into SQL via bcp.
> >
> > Initially, I set the Autogrowth of log file unrestricted.  Then it grew up
> > to 8 GB.  Because of that I was not able to run the queries in SQL.
> >
> > Then I created another database by setting the log file to 25 MB max with
> > restricted growth.  Now I am no longer able to import the data from csv
> > via
> > bcp because the SQL is not able to write to the log.
> >
> > I will end up importing 4 GB of transactions into SQL from csv files.  Is
> > there a way I can turn off the recording to the log during the import and
> > turn it back on after the import?
> >
> > Thanks.
>
>
>

> Hi bass_player,
>
> Thanks for your input.  I created the database with "Simple" Recover Model.
> Therefore, it has been Simple before or after bcp.
>
> What other ways would you suggest?
>
> Thanks.
>
> "bass_player" wrote:
>
>> You can switch to SIMPLE recovery model before the BCP task and switch back
>> to FULL recovery model.  I doubt that this is a highly critical database
>> since it is running on SQL Server Express so I'd recommend sticking with
>> SIMPLE recovery model. Just remember to backup the database before the BCP
>> task just to be sure
>>
>> "Accesshelp" <Accessh***@discussions.microsoft.com> wrote in message
>> news:179C5290-4BC6-4B19-BD44-BB37653C92A4@microsoft.com...
>>> Hello all,
>>>
>>> How do I manage the transaction log file (ldf) so that it would not keep
>>> growing and so that I can import the data into the database?
>>>
>>> I am using the SQL Server 2005 Express.  All my data are in csv files, and
>>> I
>>> import them into SQL via bcp.
>>>
>>> Initially, I set the Autogrowth of log file unrestricted.  Then it grew up
>>> to 8 GB.  Because of that I was not able to run the queries in SQL.
>>>
>>> Then I created another database by setting the log file to 25 MB max with
>>> restricted growth.  Now I am no longer able to import the data from csv
>>> via
>>> bcp because the SQL is not able to write to the log.
>>>
>>> I will end up importing 4 GB of transactions into SQL from csv files.  Is
>>> there a way I can turn off the recording to the log during the import and
>>> turn it back on after the import?
>>>
>>> Thanks.
>>
>>
>>

"Aaron Bertrand [SQL Server MVP]" wrote:

> Size the log file large enough so that it will accommodate your
> transactions.  Then back up the log (or checkpoint) and shrink it.  Better
> yet, leave it that size in case you do more imports like this later.  If
> that means you need to put it on a bigger drive, do so.  Hard disks are
> cheaper than the time and effort you'll spend trying to manage a teeny tiny
> log file.
>
>
>
>
> On 6/3/09 7:40 PM, in article
> 77A3BF41-E288-4588-9B2D-4F1085E65***@microsoft.com, "Accesshelp"
> <Accessh***@discussions.microsoft.com> wrote:
>
> > Hi bass_player,
> >
> > Thanks for your input.  I created the database with "Simple" Recover Model.
> > Therefore, it has been Simple before or after bcp.
> >
> > What other ways would you suggest?
> >
> > Thanks.
> >
> > "bass_player" wrote:
> >
> >> You can switch to SIMPLE recovery model before the BCP task and switch back
> >> to FULL recovery model.  I doubt that this is a highly critical database
> >> since it is running on SQL Server Express so I'd recommend sticking with
> >> SIMPLE recovery model. Just remember to backup the database before the BCP
> >> task just to be sure
> >>
> >> "Accesshelp" <Accessh***@discussions.microsoft.com> wrote in message
> >> news:179C5290-4BC6-4B19-BD44-BB37653C92A4@microsoft.com...
> >>> Hello all,
> >>>
> >>> How do I manage the transaction log file (ldf) so that it would not keep
> >>> growing and so that I can import the data into the database?
> >>>
> >>> I am using the SQL Server 2005 Express.  All my data are in csv files, and
> >>> I
> >>> import them into SQL via bcp.
> >>>
> >>> Initially, I set the Autogrowth of log file unrestricted.  Then it grew up
> >>> to 8 GB.  Because of that I was not able to run the queries in SQL.
> >>>
> >>> Then I created another database by setting the log file to 25 MB max with
> >>> restricted growth.  Now I am no longer able to import the data from csv
> >>> via
> >>> bcp because the SQL is not able to write to the log.
> >>>
> >>> I will end up importing 4 GB of transactions into SQL from csv files.  Is
> >>> there a way I can turn off the recording to the log during the import and
> >>> turn it back on after the import?
> >>>
> >>> Thanks.
> >>
> >>
> >>
>
>

"Aaron Bertrand [SQL Server MVP]" wrote:

> > As I mentioned in my previous post, the issue is even if I set the log file
> > to be unlimited, I was not able to run the append queries when the log file
> > hit 8 GB. 
>
> What does "not able to run" mean?  Do you get an error message?  If so,
> could you be specific?
>
>

> Aaron,
>
> Below is the message that I got when I tried to run the delete query.  My
> log file is 4.5 GB when I got this error.
>
> Msg 9002, Level 17, State 4, Line 1
> The transaction log for database 'SnfMCRData' is full. To find out why space
> in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
>
> Thanks.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>>> As I mentioned in my previous post, the issue is even if I set the log file
>>> to be unlimited, I was not able to run the append queries when the log file
>>> hit 8 GB. 
>>
>> What does "not able to run" mean?  Do you get an error message?  If so,
>> could you be specific?
>>
>>

"Aaron Bertrand [SQL Server MVP]" wrote:

> That sounds like you ran out of space on your disk, sorry.  You said before
> you have 50 GB free, have you verified that?  Is your file system compressed
> or maybe there is disk quota in place, which would prevent the log file from
> growing?  Are you sure the log file is in the location you think it is?
>
>
> On 6/4/09 4:59 PM, in article
> DEE03559-21AA-4C70-8B43-21CC9A25B***@microsoft.com, "Accesshelp"
> <Accessh***@discussions.microsoft.com> wrote:
>
> > Aaron,
> >
> > Below is the message that I got when I tried to run the delete query.  My
> > log file is 4.5 GB when I got this error.
> >
> > Msg 9002, Level 17, State 4, Line 1
> > The transaction log for database 'SnfMCRData' is full. To find out why space
> > in the log cannot be reused, see the log_reuse_wait_desc column in
> > sys.databases
> >
> > Thanks.
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >>> As I mentioned in my previous post, the issue is even if I set the log file
> >>> to be unlimited, I was not able to run the append queries when the log file
> >>> hit 8 GB. 
> >>
> >> What does "not able to run" mean?  Do you get an error message?  If so,
> >> could you be specific?
> >>
> >>
>
>

"Erland Sommarskog" wrote:

> Accesshelp (Accessh***@discussions.microsoft.com) writes:
> > My hard disk has 100 GB, but the company quotas to 50 GB.  Of the 50 GB
> > quota, I have 38 GB left at this point.  I believe space on my hard
> > drive is not an issue. 
> >
> > I believe the actual issue is the limit on the SQL database size.  From
> > what I observed from yesterday, when the SQL database size hits 2.53 GB,
> > I can not import data nor running any queries (append, update, delete,
> > etc.).  The reason I am saying that is I was able to do anything
> > regardless of the size of transaction log; however, once I hit 2.53 GB
> > on database, I could not do anything. 
> >
> > I thought the SQL database size is 4 GB.  Do you know why it would stop me
> > from doing anything once it hits 2.53 GB?
> 
> What have you set autogrow to?
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

"Erland Sommarskog" wrote:

> Accesshelp (Accessh***@discussions.microsoft.com) writes:
> > Thanks for your input.  I created the database with "Simple" Recover
> > Model.  Therefore, it has been Simple before or after bcp.
> >
> > What other ways would you suggest?
> 
> Run DBCC OPENTRAN()in that database. If there is an open transaction,
> simple recovery mode does not help, because the log does not get truncated
> beyond the oldest active transaction.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>