Operating system error 112(There is not enough space on the disk.)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paanis - 24 Jul 2008 16:07 GMT

hi,

I'm trying to migrate some date from MSDE to SQL Express 2005. Its a very
large transacation and its failed because of this error message. I've set the
database recovery model to Simple.

The transaction log for database 'XXXX' is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases. The value for this column is NOTHING.

I've free space of 300 GB. I don't think space is a issue.

Wondering what is the reason it failed.

TIA

Reply to this Message

Olaf Pietsch - 24 Jul 2008 16:34 GMT

> I'm trying to migrate some date from MSDE to SQL Express 2005. Its a very
> large transacation and its failed because of this error message. I've set
[quoted text clipped - 6 lines]
>
> I've free space of 300 GB. I don't think space is a issue.

we have done all our database migrations via backup / restore or detach /
attach. We have never seen such an error.
Are you doing the same?

Signature

Gruß Olaf
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)
Blog (http://www.sqlpass.de/PASSUserBlogs/tabid/178/Default.aspx?BlogID=3)
Regionalgruppe Köln/Bonn/Düsseldorf
(http://www.sqlpass.de/Regionalgruppen/KoelnBonnDuesseldorf/tabid/81/Default.aspx)

Reply to this Message

paanis - 24 Jul 2008 17:30 GMT

No. I'm not doing any backup /restore database. I'm doing through the .net
code.

> > I'm trying to migrate some date from MSDE to SQL Express 2005. Its a very
> > large transacation and its failed because of this error message. I've set
[quoted text clipped - 10 lines]
> attach. We have never seen such an error.
> Are you doing the same?

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 24 Jul 2008 17:31 GMT

Are you sure that:

(a) autogrow is enabled for database 'XXXX'?
(b) autogrow is not some ridiculous amount, like 3200% or 6400%?

On 7/24/08 11:07 AM, in article
FC8C50E3-CEFB-43BF-9A96-F556493470F3@microsoft.com, "paanis"

> hi,
>
[quoted text clipped - 11 lines]
>
> TIA

Reply to this Message

paanis - 24 Jul 2008 17:45 GMT

The autogrowth for MDF file is by 1 MB, unrestricted file growth

For LDF is by 10 percent and Restricted File Growth (MB) is set to a value of

2,097,152 (2TB) by default.

> Are you sure that:
>
[quoted text clipped - 19 lines]
> >
> > TIA

Reply to this Message

paanis - 24 Jul 2008 18:32 GMT

Is there any way I can track whats going on with out using SQL Profiler.

> The autogrowth for MDF file is by 1 MB, unrestricted file growth
>
[quoted text clipped - 25 lines]
> > >
> > > TIA

Reply to this Message

Russell Fields - 24 Jul 2008 19:15 GMT

paanis,

Depending on how fast the data is coming in, the speed of your input can
overrun the speed of your autogrow. If that happens, you used to get the
same error as if the disk was full. This was pretty easy to do in SQL
Server 6.5, which is when I ran into it.

SQL Server 2005 initializes almost instantly, so it seems like the window
for this problem is smaller. But, since I have not tried to duplicate your
symptom, I do not know for sure of the error you would get in SQL Server
2005.

Try changing your autogrow to some fixed amount, appropriate to the volume
of data you are importing, and try again.

RLF

> The autogrowth for MDF file is by 1 MB, unrestricted file growth
>
[quoted text clipped - 29 lines]
>> >
>> > TIA

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 24 Jul 2008 19:31 GMT

> For LDF is by 10 percent

Why a percent? How big is the file now? I agree with Russell, that this
should be configured as a fixed, appropriate, and predictable amount.
Percentage is for the birds; what it really means is, "I want each
successive growth to take longer than the last." I also agree that it may
be because your transaction is too large and too fast for the log's
filegrowth to accommodate. Try migrating your data in smaller chunks, or at
least putting Gos in between large batches, so that it isn't one ginormous
transaction. This is likely what is causing your log file to grow so
much/fast... being in simple mode doesn't help you here, because even in
simple mode the active transaction(s) need to be able to be rolled
back/recovered.

(As an aside, instant file initialization is not automatic; there are some
restrictions. For example, the service account must be an administrator,
belong t the Volume Maintenance Tasks policy, or be explicitly granted
SE_MANAGE_VOLUME_NAME rights. It is also not possible on Windows 2000 --
XP/2003+.)

Reply to this Message

paanis - 24 Jul 2008 19:41 GMT

The LDF size is 3.65 GB, after reaching this size it is giving me this error.
You mean to un-check the option of Enable Autogrowth property for LDF file?
And set Initial size to some Number?? How do I set the size as Fixed?

> > For LDF is by 10 percent
>
[quoted text clipped - 15 lines]
> SE_MANAGE_VOLUME_NAME rights. It is also not possible on Windows 2000 --
> XP/2003+.)

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 24 Jul 2008 19:45 GMT

> The LDF size is 3.65 GB, after reaching this size it is giving me this error.
> You mean to un-check the option of Enable Autogrowth property for LDF file?
> And set Initial size to some Number?? How do I set the size as Fixed?

NO. If you disable autogrowth on the log file, you will kill the script at
least as early if not earlier. Set the GROWTH to a fixed number instead of
a percentage. But anyway, obviously your transaction requires much larger
log file than 3.65 GB. Even increasing by 10 percent or 1 GB now is not
going to help anything because if your transaction requires 80 GB of log,
well, I don't even want to do the math on how many 10% growths it will take
to get there, but clearly it is too many.

Unless you want to manually expand the file to 200Gb or something, and hope
that the log file won't need more room than that, the answer is going to be
to change your script so it does not migrate all of your data in one single
statement / transaction. Can't really give any recommendations here because
all you said was "I am migrating data."

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 24 Jul 2008 19:36 GMT

>> see the log_reuse_wait_desc column in
>> sys.databases. The value for this column is NOTHING.

Did you try looking at this column while the transaction was occurring,
instead of after the error? If the transaction has failed and this specific
connection has given up and has stopped doing anything, this value may have
been changed by activity from another connection (or by the system) in the
meantime.

Reply to this Message

paanis - 24 Jul 2008 20:00 GMT

This is a tool that we have developed to migrate data from MSDE to SQL Server
2005 database. We wrote code to migrate because of the underlying database
table/schema changes.

We run this migration in one transaction and have to roll back if any error
occurs. I was able to migrate some of the datasets from the customers
successfully. For only with this dataset causing failure giving the above
said error.

There wil be no other applications running to access the database when I run
this TOOL.

Correct me if I am wrong, You mean to say, set File Growth in MEGABYTES
instead of IN PERCENT?

> >> see the log_reuse_wait_desc column in
> >> sys.databases. The value for this column is NOTHING.
[quoted text clipped - 4 lines]
> been changed by activity from another connection (or by the system) in the
> meantime.

Reply to this Message