Re: How to manage the transaction log

#1

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 (AT) discussions (DOT) microsoft.com> wrote

Quote:

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.

#2

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...soft (DOT) com, "Accesshelp"
<Accesshelp (AT) discussions (DOT) microsoft.com> wrote:

Quote:

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 (AT) discussions (DOT) microsoft.com> wrote in message
news:179C5290-4BC6-4B19-BD44-BB37653C92A4 (AT) microsoft (DOT) 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.

#3

Quote:

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?

#4

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-21CC9A25BA1F...soft (DOT) com, "Accesshelp"
<Accesshelp (AT) discussions (DOT) microsoft.com> wrote:

Quote:

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?

#5

Good morning Erland,

For both database and log file, I sent them to 10% file growth and
unrestricted file growth. As I mentioned in my previous message, it stops
letting me do anything at 2.53 GB on database.

Thanks.

"Erland Sommarskog" wrote:

Quote:

Accesshelp (Accesshelp (AT) discussions (DOT) 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, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

#6

Accesshelp (Accesshelp (AT) discussions (DOT) microsoft.com) writes:

Quote:

Good morning Erland,

For both database and log file, I sent them to 10% file growth and
unrestricted file growth. As I mentioned in my previous message, it stops
letting me do anything at 2.53 GB on database.

Just so that we have it all summarised, can you please post:

1) The error message you get.
2) The output from "sp_helpdb yourdb"
3) exec xp_cmdshell 'dir "yourdb.mdf"'
4) exec xp_cmdshell 'dir "yourdb.ldf"'

Get the full path for the dir commands from the output from sp_helpdb.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: How to manage the transaction log

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools

Re: How to manage the transaction log - 06-03-2009 , 06:24 PM

Re: How to manage the transaction log - 06-03-2009 , 09:51 PM

Re: How to manage the transaction log - 06-04-2009 , 01:41 PM

Re: How to manage the transaction log - 06-04-2009 , 09:28 PM

Re: How to manage the transaction log - 06-08-2009 , 11:01 AM

Re: How to manage the transaction log - 06-08-2009 , 05:23 PM