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

Hi!

While backing a SQL database onto another system after mapping the drive I get this error saying "Operating system error 112(There is not enough space on the disk.)..". There's a lot of space on the Destination server.

But when I back it up onto the same machine the backup's successfull.

I'm not even able to copy and paste the backed up file onto another machine. can someone help me out on this

The size of the DB is 5GB. Is it the problem with the size??

Thankyou For answering Satya and Loach...

I'm logged in with Sa user on the server and also logged on in administrative mode on both the machines (source and destination servers)

I think I've not communicated clearly I'll try and put in my query more eloborately.

1. We have a server which hosts the SQL server and the Database X installed on it. The size of the database when backedup is around 5 GB.

2. We have a backup server which has all the backed up files.

3. I've scheduled the backup using the SQL server agent (Scheduled in Sa user mode).

4. I've created a folder by name DB backup on the backup server and I've assigned access rights to it for administrator user.

5. I've mapped the DB backup folder as a network drive to the Server.

6. Now since DB backup folder acts as a drive in the actual server I scheduled the backup into that folder.

This Process worked successfully earlier, but then the size of the database was relatively smaller actually very small. That's one of the reason why I asked you earlier whether it was a problem with the size.

One thing is for sure In this process which I'm following I've logged on everywhere with administrative privilages.

Thanks for your co-operation once again.What account is used for SQL Server services (MSSQLSErver & SQLAgent)?
Use Domain account with ADMIN Privileges and try backup.|||Satya is correct.

Make sure the account has admin privelges on your SQL Server and the permissions necessary to write to the destination folder. It is not necessary, however, for the account to be a domain admin.|||You may also want to make sure there is no Quotas set up across your network or on the machine you are trying to access. Your Network Admin may be blocking your transfer just because of the size. If Windows is set yo stop the transfer over a certain size or bandwidth then SQL Server will report out of space even when you aren't

openxml question

Hello,
I read a document from MSDN saying that:
Avoid OPENXML over large XML documents.
Avoid large numbers of concurrent OPENXML statements over XML
documents.
But my question is: how to define large? Is there some suggestion
saying for example that a 100kb xml file will be too big? Or 100
concurrent openxml will be to match for the system?
The document on msdn can be found at:
http://msdn2.microsoft.com/en-us/library/ms998577.aspx
The document was written in 2004. Are the arguments valid for both sql
2000 and 2995?
Many Thanks
JerryYes the arguments are pretty much the same. Other than adding XML data type
support, I'm not aware of any changes to the inner workings of OPENXML. If
you're using SQL 2005, you might try using the nodes() method of the XML
data type to shred your XML data instead.
<DAXU@.hotmail.com> wrote in message
news:7da00930-9135-4ea6-9438-b2276eccdc1e@.h11g2000prf.googlegroups.com...
> Hello,
> I read a document from MSDN saying that:
> Avoid OPENXML over large XML documents.
> Avoid large numbers of concurrent OPENXML statements over XML
> documents.
>
> But my question is: how to define large? Is there some suggestion
> saying for example that a 100kb xml file will be too big? Or 100
> concurrent openxml will be to match for the system?
> The document on msdn can be found at:
> http://msdn2.microsoft.com/en-us/library/ms998577.aspx
> The document was written in 2004. Are the arguments valid for both sql
> 2000 and 2995?
> Many Thanks
> Jerry
>|||On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:

>Hello,
>I read a document from MSDN saying that:
>Avoid OPENXML over large XML documents.
>Avoid large numbers of concurrent OPENXML statements over XML
>documents.
>
>But my question is: how to define large? Is there some suggestion
>saying for example that a 100kb xml file will be too big? Or 100
>concurrent openxml will be to match for the system?
>The document on msdn can be found at:
>http://msdn2.microsoft.com/en-us/library/ms998577.aspx
>The document was written in 2004. Are the arguments valid for both sql
>2000 and 2995?
No, I think not.
On SQL2005 (!), I've used openxml to process two megabyte files on a
tiny, overloaded laptop, I run five or ten nodes queries to shred it
in two seconds, and that's pretty darned good performance in my book!
Maybe 20mb would be a problem, or 200mb. Assume it does keep
everything in RAM, but tokenized. Also even small systems today do
have gigabytes of RAM, which may be wasn't so true even four years
ago.
J.|||> On SQL2005 (!), I've used openxml to process two megabyte files
Simply out of curiosity, did this out-perform using the xml datatype
to do the inital shred?
Marc|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:knbrn3lgsftdtev6ofsnt3sv9fosai8oh5@.
4ax.com...
> On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>
> No, I think not.
> On SQL2005 (!), I've used openxml to process two megabyte files on a
> tiny, overloaded laptop, I run five or ten nodes queries to shred it
> in two seconds, and that's pretty darned good performance in my book!
> Maybe 20mb would be a problem, or 200mb. Assume it does keep
> everything in RAM, but tokenized. Also even small systems today do
> have gigabytes of RAM, which may be wasn't so true even four years
> ago.
One of the problems with the legacy methods of shredding XML using the
COM-based stored procs and OPENXML is that it automatically assigned 1/8th
of your SQL Server's total memory to the XML cache. Unfortunately this is a
limitation of the MSXML parser used by SQL Server to fulfill OPENXML
requests. According to this article you could potentially run out of memory
if you prepare too many XML documents at once without releasing some.|||FYI, here's the SQL 2005 article detailing the 1/8th memory issue:
http://msdn2.microsoft.com/en-us/library/ms187367.aspx. This issue affects
SQL Server 2005 as well as SQL Server 2000. This means that if your SQL
Server has 2 GB assigned to it, that 2 MB XML file you're processing just
got 250 MB assigned to it until you destroy the COM object with
sp_xml_removedocument.
That's quite a hefty price to pay to process a 2 MB file.|||"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz). On
a simple 1 MB XML document I came up with the following performance:
- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds
OPENXML appears to be slightly faster for simple XML documents, although it
reserves 1/8th of the SQL Server memory regardless of the size of the XML
document. I didn't test it with more complex documents and paths, like
documents with multiple namespaces, and several levels of nesting, etc.
When you are shredding XML documents stored in a column, the nodes() method
with a primary XML index is considerably faster than either of the other two
methods.|||Typo, 100 KB XML document, not 1 MB.
"Mike C#" <xyz@.xyz.com> wrote in message
news:eLL1CpKUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "Marc Gravell" <marc.gravell@.gmail.com> wrote in message
> news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
> On a simple 1 MB XML document I came up with the following performance:
> - shredding an XML type variable with nodes() method 1,000 times took 77
> seconds
> - shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
> - shredding an XML type column with nodes() method and primary XML index
> 1,000 times took 42 seconds
> OPENXML appears to be slightly faster for simple XML documents, although
> it reserves 1/8th of the SQL Server memory regardless of the size of the
> XML document. I didn't test it with more complex documents and paths,
> like documents with multiple namespaces, and several levels of nesting,
> etc. When you are shredding XML documents stored in a column, the nodes()
> method with a primary XML index is considerably faster than either of the
> other two methods.
>|||My pardon, I was using openrowset('bulk ...'), and I guess that is using the
XML datatype for shredding.
Josh
"Marc Gravell" wrote:

> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
>|||"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:4F59EC99-46F2-4F48-99CF-F453DCD947C1@.microsoft.com...
> My pardon, I was using openrowset('bulk ...'), and I guess that is using
> the
> XML datatype for shredding.
?
Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type variable
or column? I ask because casting character/binary data to XML and shredding
it to relational format are two completely different tasks.

openxml question

Hello,
I read a document from MSDN saying that:
Avoid OPENXML over large XML documents.
Avoid large numbers of concurrent OPENXML statements over XML
documents.
But my question is: how to define large? Is there some suggestion
saying for example that a 100kb xml file will be too big? Or 100
concurrent openxml will be to match for the system?
The document on msdn can be found at:
http://msdn2.microsoft.com/en-us/library/ms998577.aspx
The document was written in 2004. Are the arguments valid for both sql
2000 and 2995?
Many Thanks
Jerry
Yes the arguments are pretty much the same. Other than adding XML data type
support, I'm not aware of any changes to the inner workings of OPENXML. If
you're using SQL 2005, you might try using the nodes() method of the XML
data type to shred your XML data instead.
<DAXU@.hotmail.com> wrote in message
news:7da00930-9135-4ea6-9438-b2276eccdc1e@.h11g2000prf.googlegroups.com...
> Hello,
> I read a document from MSDN saying that:
> Avoid OPENXML over large XML documents.
> Avoid large numbers of concurrent OPENXML statements over XML
> documents.
>
> But my question is: how to define large? Is there some suggestion
> saying for example that a 100kb xml file will be too big? Or 100
> concurrent openxml will be to match for the system?
> The document on msdn can be found at:
> http://msdn2.microsoft.com/en-us/library/ms998577.aspx
> The document was written in 2004. Are the arguments valid for both sql
> 2000 and 2995?
> Many Thanks
> Jerry
>
|||On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:

>Hello,
>I read a document from MSDN saying that:
>Avoid OPENXML over large XML documents.
>Avoid large numbers of concurrent OPENXML statements over XML
>documents.
>
>But my question is: how to define large? Is there some suggestion
>saying for example that a 100kb xml file will be too big? Or 100
>concurrent openxml will be to match for the system?
>The document on msdn can be found at:
>http://msdn2.microsoft.com/en-us/library/ms998577.aspx
>The document was written in 2004. Are the arguments valid for both sql
>2000 and 2995?
No, I think not.
On SQL2005 (!), I've used openxml to process two megabyte files on a
tiny, overloaded laptop, I run five or ten nodes queries to shred it
in two seconds, and that's pretty darned good performance in my book!
Maybe 20mb would be a problem, or 200mb. Assume it does keep
everything in RAM, but tokenized. Also even small systems today do
have gigabytes of RAM, which may be wasn't so true even four years
ago.
J.
|||"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:knbrn3lgsftdtev6ofsnt3sv9fosai8oh5@.4ax.com...
> On Wed, 2 Jan 2008 05:15:40 -0800 (PST), DAXU@.hotmail.com wrote:
>
> No, I think not.
> On SQL2005 (!), I've used openxml to process two megabyte files on a
> tiny, overloaded laptop, I run five or ten nodes queries to shred it
> in two seconds, and that's pretty darned good performance in my book!
> Maybe 20mb would be a problem, or 200mb. Assume it does keep
> everything in RAM, but tokenized. Also even small systems today do
> have gigabytes of RAM, which may be wasn't so true even four years
> ago.
One of the problems with the legacy methods of shredding XML using the
COM-based stored procs and OPENXML is that it automatically assigned 1/8th
of your SQL Server's total memory to the XML cache. Unfortunately this is a
limitation of the MSXML parser used by SQL Server to fulfill OPENXML
requests. According to this article you could potentially run out of memory
if you prepare too many XML documents at once without releasing some.
|||FYI, here's the SQL 2005 article detailing the 1/8th memory issue:
http://msdn2.microsoft.com/en-us/library/ms187367.aspx. This issue affects
SQL Server 2005 as well as SQL Server 2000. This means that if your SQL
Server has 2 GB assigned to it, that 2 MB XML file you're processing just
got 250 MB assigned to it until you destroy the COM object with
sp_xml_removedocument.
That's quite a hefty price to pay to process a 2 MB file.
|||"Marc Gravell" <marc.gravell@.gmail.com> wrote in message
news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz). On
a simple 1 MB XML document I came up with the following performance:
- shredding an XML type variable with nodes() method 1,000 times took 77
seconds
- shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
- shredding an XML type column with nodes() method and primary XML index
1,000 times took 42 seconds
OPENXML appears to be slightly faster for simple XML documents, although it
reserves 1/8th of the SQL Server memory regardless of the size of the XML
document. I didn't test it with more complex documents and paths, like
documents with multiple namespaces, and several levels of nesting, etc.
When you are shredding XML documents stored in a column, the nodes() method
with a primary XML index is considerably faster than either of the other two
methods.
|||Typo, 100 KB XML document, not 1 MB.
"Mike C#" <xyz@.xyz.com> wrote in message
news:eLL1CpKUIHA.3400@.TK2MSFTNGP03.phx.gbl...
> "Marc Gravell" <marc.gravell@.gmail.com> wrote in message
> news:a6c9a102-6257-4947-b086-73c555aafe01@.l32g2000hse.googlegroups.com...
> I just ran a couple of tests on my server (SQL 2005, 2 GB RAM, 2.2 GHz).
> On a simple 1 MB XML document I came up with the following performance:
> - shredding an XML type variable with nodes() method 1,000 times took 77
> seconds
> - shredding a VARCHAR variable with OPENXML 1,000 times took 76 seconds
> - shredding an XML type column with nodes() method and primary XML index
> 1,000 times took 42 seconds
> OPENXML appears to be slightly faster for simple XML documents, although
> it reserves 1/8th of the SQL Server memory regardless of the size of the
> XML document. I didn't test it with more complex documents and paths,
> like documents with multiple namespaces, and several levels of nesting,
> etc. When you are shredding XML documents stored in a column, the nodes()
> method with a primary XML index is considerably faster than either of the
> other two methods.
>
|||My pardon, I was using openrowset('bulk ...'), and I guess that is using the
XML datatype for shredding.
Josh
"Marc Gravell" wrote:

> Simply out of curiosity, did this out-perform using the xml datatype
> to do the inital shred?
> Marc
>
|||"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:4F59EC99-46F2-4F48-99CF-F453DCD947C1@.microsoft.com...
> My pardon, I was using openrowset('bulk ...'), and I guess that is using
> the
> XML datatype for shredding.
?
Are you actually shredding the XML data after you load it using
openrowset(...) or are you actually just storing it an an XML type variable
or column? I ask because casting character/binary data to XML and shredding
it to relational format are two completely different tasks.
|||On Mon, 7 Jan 2008 21:32:31 -0500, "Mike C#" <xyz@.xyz.com> wrote:

>Are you actually shredding the XML data after you load it using
>openrowset(...) or are you actually just storing it an an XML type variable
>or column? I ask because casting character/binary data to XML and shredding
>it to relational format are two completely different tasks.
Cast it to XML variable with schema to validate via XSD.
Then pass it to SP as XML with no schema, shred it there with four to
six queries mostly cross applies.
Only reason I use the unschema'd XML in the SP is so I can change the
XSD without first dropping the SP. I timed it both ways, seems to
make very little difference. The shredding is indecently fast. Takes
two seconds to shred into table vars, about ten seconds to write rows
to database.
openrowset used like this doesn't do that 1/8 of RAM, does it?
Thanks.
J.