Re: How to manage the transaction log

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Aaron,

Thanks for your input. I don't think I have an issue with my hard drive
space. I have 50 GB (to 100 GB if needed) on my hard drive.

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.

I am in the situation that I can not set either limited or unlimited, and
either one is not working for me. I know I must be missing something since I
am still new to SQL Server.

How do I shrink the file?

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-4F1085E652F3@xxxxxxxxxxxxx, "Accesshelp"
<Accesshelp@xxxxxxxxxxxxxxxxxxxxxxxxx> 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" <Accesshelp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:179C5290-4BC6-4B19-BD44-BB37653C92A4@xxxxxxxxxxxxxxxx
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.





.

Relevant Pages

  • Re: PerfMon recording to SQL 2005
    ... seconds or even every ne second) from a busy server to a database on ... On the Perfmon counter Log Files tab, SQL Database has been set, End File ... Configuring the log file, choose the tested DSN as the System DSN ... The user account I created in SQL is an owner of the ...
    (microsoft.public.sqlserver.server)
  • Re: How to manage the transaction log
    ... I created the database with "Simple" Recover Model. ... Just remember to backup the database before the BCP ... I am using the SQL Server 2005 Express. ... I set the Autogrowth of log file unrestricted. ...
    (microsoft.public.sqlserver.tools)
  • Re: Detached database cant be attached back
    ... Are you saying that you deleted one of the log files, and then copied the second log file to the ... Tibor Karaszi, SQL Server MVP ... Original database had 2 datafiles and 2 log files. ... Could not open new darabase 'crn'. ...
    (microsoft.public.sqlserver.server)
  • Re: How to manage the transaction log
    ... Just remember to backup the database before the BCP ... I am using the SQL Server 2005 Express. ... I set the Autogrowth of log file unrestricted. ...
    (microsoft.public.sqlserver.tools)
  • Re: PEAR DB - Useful?
    ... SQL, all I have to do is change the connect string and I can use it to ... work with more or less any database out there. ... PHP5's exceptions and stuff, ... debugging information in a log file. ...
    (comp.lang.php)