Error Restoring Database - SQLState: 42000 - insufficient free space

I am trying to move a database from one machine to another by taking a
backup from one and restoring it on another. Both machines are W2k sp3 with
SQL2k sp3. As far as I can tell they are both straightforward, standard
installations (OS and DB), except;
The target machine has been partitioned into three (system, apps, and data),
and the disk is mirrored.
I can take the backup and restore it to a number of machines, but on the
machine that I need to restore the database to I get;
Microsoft SQL-DMO(ODBC SQL State: 42000)
There is insufficient free space on disk volume e:\ to create the database.
The database requires 16519462912 additional free bytes, while only
6329229312 bytes are available.
RESTORE DATABASE is terminating abnormally.
The database backup file is only 146M, the data file is 143M and the log
file is 15M.
The data partition (e:) has 8G free space, although the system/boot
partition (c:) has about 2G
Does anyone have any ideas how this could be happening?
--
regards,
Paul DurdinHi.
The data partition (e:) need + - 16 GB. free,you only have
+ - 6 GB.
>--Original Message--
>I am trying to move a database from one machine to
another by taking a
>backup from one and restoring it on another. Both
machines are W2k sp3 with
>SQL2k sp3. As far as I can tell they are both
straightforward, standard
>installations (OS and DB), except;
>The target machine has been partitioned into three
(system, apps, and data),
>and the disk is mirrored.
>I can take the backup and restore it to a number of
machines, but on the
>machine that I need to restore the database to I get;
>Microsoft SQL-DMO(ODBC SQL State: 42000)
>There is insufficient free space on disk volume e:\ to
create the database.
>The database requires 16519462912 additional free bytes,
while only
>6329229312 bytes are available.
>RESTORE DATABASE is terminating abnormally.
>The database backup file is only 146M, the data file is
143M and the log
>file is 15M.
>The data partition (e:) has 8G free space, although the
system/boot
>partition (c:) has about 2G
>Does anyone have any ideas how this could be happening?
>--
>regards,
>Paul Durdin
>
>.
>|||That's indeed what the error message says - but surely you telling me that a
140M database needs 16G to restore it!
--
regards,
"Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> Hi.
> The data partition (e:) need + - 16 GB. free,you only have
> + - 6 GB.
> >--Original Message--
> >I am trying to move a database from one machine to
> another by taking a
> >backup from one and restoring it on another. Both
> machines are W2k sp3 with
> >SQL2k sp3. As far as I can tell they are both
> straightforward, standard
> >installations (OS and DB), except;
> >The target machine has been partitioned into three
> (system, apps, and data),
> >and the disk is mirrored.
> >
> >I can take the backup and restore it to a number of
> machines, but on the
> >machine that I need to restore the database to I get;
> >
> >Microsoft SQL-DMO(ODBC SQL State: 42000)
> >There is insufficient free space on disk volume e:\ to
> create the database.
> >The database requires 16519462912 additional free bytes,
> while only
> >6329229312 bytes are available.
> >RESTORE DATABASE is terminating abnormally.
> >
> >The database backup file is only 146M, the data file is
> 143M and the log
> >file is 15M.
> >The data partition (e:) has 8G free space, although the
> system/boot
> >partition (c:) has about 2G
> >
> >Does anyone have any ideas how this could be happening?
> >--
> >regards,
> >
> >Paul Durdin
> >
> >
> >.
> >|||When you restore a database, you need the same amount of space for the mdf,
ndf and ldf as the size those files had when you did the backup. If the
files were smaller when you did the backup and the restore claims you need
more space, you have hit a bug in SQL Server (I haven't heard of such a bug,
though). A couple of things:
Make sure that you restore the correct backup. One backup file can have more
than one backup. Check using RESTORE HEADERONLY.
Check the size needed according to the backup. Use RESTORE FILELISTONLY.
Make sure you specify the correct FILE = if the backup file has more then
one backup.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> That's indeed what the error message says - but surely you telling me that
a
> 140M database needs 16G to restore it!
> --
> regards,
>
> "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > Hi.
> > The data partition (e:) need + - 16 GB. free,you only have
> > + - 6 GB.
> >
> > >--Original Message--
> > >I am trying to move a database from one machine to
> > another by taking a
> > >backup from one and restoring it on another. Both
> > machines are W2k sp3 with
> > >SQL2k sp3. As far as I can tell they are both
> > straightforward, standard
> > >installations (OS and DB), except;
> > >The target machine has been partitioned into three
> > (system, apps, and data),
> > >and the disk is mirrored.
> > >
> > >I can take the backup and restore it to a number of
> > machines, but on the
> > >machine that I need to restore the database to I get;
> > >
> > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > >There is insufficient free space on disk volume e:\ to
> > create the database.
> > >The database requires 16519462912 additional free bytes,
> > while only
> > >6329229312 bytes are available.
> > >RESTORE DATABASE is terminating abnormally.
> > >
> > >The database backup file is only 146M, the data file is
> > 143M and the log
> > >file is 15M.
> > >The data partition (e:) has 8G free space, although the
> > system/boot
> > >partition (c:) has about 2G
> > >
> > >Does anyone have any ideas how this could be happening?
> > >--
> > >regards,
> > >
> > >Paul Durdin
> > >
> > >
> > >.
> > >
>|||Thanks for the suggestions.
I've checked the backup file using RESTORE FILELISTONLY FROM
FILE='backupfilename'
This reports the log file as 16377380864 (where have I seem this figure
before?) So I guess that explains why SQLSvr says it cant restore the
database.
I checked the log file size in EM to be 14,643M, but the physical log file
is actually 14,993,664k.
Being a newbie to SQL DBA stuff I am puzzled. How come the physical size is
different from the size recorded in EM?
I did a bit of reading, and I am starting to get a bit of an understanding,
so I did a...
DBCC SHRINKDATABASE (databasename)
followed by
BACKUP LOG databasename WITH TRUNCATE_ONLY
Then did another backup and examined the backup as above. I get much the
same figures as before.
How can I shrink the log file (having already dome the above).
--
regards,
Paul Durdin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> When you restore a database, you need the same amount of space for the
mdf,
> ndf and ldf as the size those files had when you did the backup. If the
> files were smaller when you did the backup and the restore claims you need
> more space, you have hit a bug in SQL Server (I haven't heard of such a
bug,
> though). A couple of things:
> Make sure that you restore the correct backup. One backup file can have
more
> than one backup. Check using RESTORE HEADERONLY.
> Check the size needed according to the backup. Use RESTORE FILELISTONLY.
> Make sure you specify the correct FILE = if the backup file has more then
> one backup.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > That's indeed what the error message says - but surely you telling me
that
> a
> > 140M database needs 16G to restore it!
> >
> > --
> > regards,
> >
> >
> > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > Hi.
> > > The data partition (e:) need + - 16 GB. free,you only have
> > > + - 6 GB.
> > >
> > > >--Original Message--
> > > >I am trying to move a database from one machine to
> > > another by taking a
> > > >backup from one and restoring it on another. Both
> > > machines are W2k sp3 with
> > > >SQL2k sp3. As far as I can tell they are both
> > > straightforward, standard
> > > >installations (OS and DB), except;
> > > >The target machine has been partitioned into three
> > > (system, apps, and data),
> > > >and the disk is mirrored.
> > > >
> > > >I can take the backup and restore it to a number of
> > > machines, but on the
> > > >machine that I need to restore the database to I get;
> > > >
> > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > >There is insufficient free space on disk volume e:\ to
> > > create the database.
> > > >The database requires 16519462912 additional free bytes,
> > > while only
> > > >6329229312 bytes are available.
> > > >RESTORE DATABASE is terminating abnormally.
> > > >
> > > >The database backup file is only 146M, the data file is
> > > 143M and the log
> > > >file is 15M.
> > > >The data partition (e:) has 8G free space, although the
> > > system/boot
> > > >partition (c:) has about 2G
> > > >
> > > >Does anyone have any ideas how this could be happening?
> > > >--
> > > >regards,
> > > >
> > > >Paul Durdin
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Seems like EM reports the size incorrectly... Where in EM do you see that
space reported? In the taskpad or right-click the database, properties?
To shrink log, Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> Thanks for the suggestions.
> I've checked the backup file using RESTORE FILELISTONLY FROM
> FILE='backupfilename'
> This reports the log file as 16377380864 (where have I seem this figure
> before?) So I guess that explains why SQLSvr says it cant restore the
> database.
> I checked the log file size in EM to be 14,643M, but the physical log file
> is actually 14,993,664k.
> Being a newbie to SQL DBA stuff I am puzzled. How come the physical size
is
> different from the size recorded in EM?
> I did a bit of reading, and I am starting to get a bit of an
understanding,
> so I did a...
> DBCC SHRINKDATABASE (databasename)
> followed by
> BACKUP LOG databasename WITH TRUNCATE_ONLY
> Then did another backup and examined the backup as above. I get much the
> same figures as before.
> How can I shrink the log file (having already dome the above).
> --
> regards,
> Paul Durdin
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > When you restore a database, you need the same amount of space for the
> mdf,
> > ndf and ldf as the size those files had when you did the backup. If the
> > files were smaller when you did the backup and the restore claims you
need
> > more space, you have hit a bug in SQL Server (I haven't heard of such a
> bug,
> > though). A couple of things:
> >
> > Make sure that you restore the correct backup. One backup file can have
> more
> > than one backup. Check using RESTORE HEADERONLY.
> > Check the size needed according to the backup. Use RESTORE FILELISTONLY.
> > Make sure you specify the correct FILE = if the backup file has more
then
> > one backup.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > That's indeed what the error message says - but surely you telling me
> that
> > a
> > > 140M database needs 16G to restore it!
> > >
> > > --
> > > regards,
> > >
> > >
> > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > Hi.
> > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > + - 6 GB.
> > > >
> > > > >--Original Message--
> > > > >I am trying to move a database from one machine to
> > > > another by taking a
> > > > >backup from one and restoring it on another. Both
> > > > machines are W2k sp3 with
> > > > >SQL2k sp3. As far as I can tell they are both
> > > > straightforward, standard
> > > > >installations (OS and DB), except;
> > > > >The target machine has been partitioned into three
> > > > (system, apps, and data),
> > > > >and the disk is mirrored.
> > > > >
> > > > >I can take the backup and restore it to a number of
> > > > machines, but on the
> > > > >machine that I need to restore the database to I get;
> > > > >
> > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > >There is insufficient free space on disk volume e:\ to
> > > > create the database.
> > > > >The database requires 16519462912 additional free bytes,
> > > > while only
> > > > >6329229312 bytes are available.
> > > > >RESTORE DATABASE is terminating abnormally.
> > > > >
> > > > >The database backup file is only 146M, the data file is
> > > > 143M and the log
> > > > >file is 15M.
> > > > >The data partition (e:) has 8G free space, although the
> > > > system/boot
> > > > >partition (c:) has about 2G
> > > > >
> > > > >Does anyone have any ideas how this could be happening?
> > > > >--
> > > > >regards,
> > > > >
> > > > >Paul Durdin
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||Sorted!!
Article 272318 says it all. Now have the log file shrunk and the backup
reflects a smaller size. I'll have to try restoring the backup on the target
machine on Monday, but this looks like it will have fixed the problem.
Thanks for your help,
--
regards,
Paul Durdin
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> Thanks for the suggestions.
> I've checked the backup file using RESTORE FILELISTONLY FROM
> FILE='backupfilename'
> This reports the log file as 16377380864 (where have I seem this figure
> before?) So I guess that explains why SQLSvr says it cant restore the
> database.
> I checked the log file size in EM to be 14,643M, but the physical log file
> is actually 14,993,664k.
> Being a newbie to SQL DBA stuff I am puzzled. How come the physical size
is
> different from the size recorded in EM?
> I did a bit of reading, and I am starting to get a bit of an
understanding,
> so I did a...
> DBCC SHRINKDATABASE (databasename)
> followed by
> BACKUP LOG databasename WITH TRUNCATE_ONLY
> Then did another backup and examined the backup as above. I get much the
> same figures as before.
> How can I shrink the log file (having already dome the above).
> --
> regards,
> Paul Durdin
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > When you restore a database, you need the same amount of space for the
> mdf,
> > ndf and ldf as the size those files had when you did the backup. If the
> > files were smaller when you did the backup and the restore claims you
need
> > more space, you have hit a bug in SQL Server (I haven't heard of such a
> bug,
> > though). A couple of things:
> >
> > Make sure that you restore the correct backup. One backup file can have
> more
> > than one backup. Check using RESTORE HEADERONLY.
> > Check the size needed according to the backup. Use RESTORE FILELISTONLY.
> > Make sure you specify the correct FILE = if the backup file has more
then
> > one backup.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > That's indeed what the error message says - but surely you telling me
> that
> > a
> > > 140M database needs 16G to restore it!
> > >
> > > --
> > > regards,
> > >
> > >
> > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > Hi.
> > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > + - 6 GB.
> > > >
> > > > >--Original Message--
> > > > >I am trying to move a database from one machine to
> > > > another by taking a
> > > > >backup from one and restoring it on another. Both
> > > > machines are W2k sp3 with
> > > > >SQL2k sp3. As far as I can tell they are both
> > > > straightforward, standard
> > > > >installations (OS and DB), except;
> > > > >The target machine has been partitioned into three
> > > > (system, apps, and data),
> > > > >and the disk is mirrored.
> > > > >
> > > > >I can take the backup and restore it to a number of
> > > > machines, but on the
> > > > >machine that I need to restore the database to I get;
> > > > >
> > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > >There is insufficient free space on disk volume e:\ to
> > > > create the database.
> > > > >The database requires 16519462912 additional free bytes,
> > > > while only
> > > > >6329229312 bytes are available.
> > > > >RESTORE DATABASE is terminating abnormally.
> > > > >
> > > > >The database backup file is only 146M, the data file is
> > > > 143M and the log
> > > > >file is 15M.
> > > > >The data partition (e:) has 8G free space, although the
> > > > system/boot
> > > > >partition (c:) has about 2G
> > > > >
> > > > >Does anyone have any ideas how this could be happening?
> > > > >--
> > > > >regards,
> > > > >
> > > > >Paul Durdin
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||I was looking in the database properties under Transaction Log->Space
allocated.
Doh! I think it was me - I took 15,xxx to be 15M but now the figure is 11(M)
so EM was reporting 15G (correctly).
It was me! It was me! I shall have to go on a re-education course / take up
a new career, or maybe pack up for the day and get ready to watch the rugby
over the weekend (its very weird watching rugby in the morning - it should
be an evening thing with plenty of beer).
--
regards,
Paul Durdin
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eoivagsqDHA.2776@.tk2msftngp13.phx.gbl...
> Seems like EM reports the size incorrectly... Where in EM do you see that
> space reported? In the taskpad or right-click the database, properties?
> To shrink log, Check out below KB articles:
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> > Thanks for the suggestions.
> >
> > I've checked the backup file using RESTORE FILELISTONLY FROM
> > FILE='backupfilename'
> >
> > This reports the log file as 16377380864 (where have I seem this figure
> > before?) So I guess that explains why SQLSvr says it cant restore the
> > database.
> >
> > I checked the log file size in EM to be 14,643M, but the physical log
file
> > is actually 14,993,664k.
> >
> > Being a newbie to SQL DBA stuff I am puzzled. How come the physical size
> is
> > different from the size recorded in EM?
> >
> > I did a bit of reading, and I am starting to get a bit of an
> understanding,
> > so I did a...
> >
> > DBCC SHRINKDATABASE (databasename)
> > followed by
> > BACKUP LOG databasename WITH TRUNCATE_ONLY
> >
> > Then did another backup and examined the backup as above. I get much the
> > same figures as before.
> >
> > How can I shrink the log file (having already dome the above).
> >
> > --
> > regards,
> >
> > Paul Durdin
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > > When you restore a database, you need the same amount of space for the
> > mdf,
> > > ndf and ldf as the size those files had when you did the backup. If
the
> > > files were smaller when you did the backup and the restore claims you
> need
> > > more space, you have hit a bug in SQL Server (I haven't heard of such
a
> > bug,
> > > though). A couple of things:
> > >
> > > Make sure that you restore the correct backup. One backup file can
have
> > more
> > > than one backup. Check using RESTORE HEADERONLY.
> > > Check the size needed according to the backup. Use RESTORE
FILELISTONLY.
> > > Make sure you specify the correct FILE = if the backup file has more
> then
> > > one backup.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > > That's indeed what the error message says - but surely you telling
me
> > that
> > > a
> > > > 140M database needs 16G to restore it!
> > > >
> > > > --
> > > > regards,
> > > >
> > > >
> > > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > > Hi.
> > > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > > + - 6 GB.
> > > > >
> > > > > >--Original Message--
> > > > > >I am trying to move a database from one machine to
> > > > > another by taking a
> > > > > >backup from one and restoring it on another. Both
> > > > > machines are W2k sp3 with
> > > > > >SQL2k sp3. As far as I can tell they are both
> > > > > straightforward, standard
> > > > > >installations (OS and DB), except;
> > > > > >The target machine has been partitioned into three
> > > > > (system, apps, and data),
> > > > > >and the disk is mirrored.
> > > > > >
> > > > > >I can take the backup and restore it to a number of
> > > > > machines, but on the
> > > > > >machine that I need to restore the database to I get;
> > > > > >
> > > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > > >There is insufficient free space on disk volume e:\ to
> > > > > create the database.
> > > > > >The database requires 16519462912 additional free bytes,
> > > > > while only
> > > > > >6329229312 bytes are available.
> > > > > >RESTORE DATABASE is terminating abnormally.
> > > > > >
> > > > > >The database backup file is only 146M, the data file is
> > > > > 143M and the log
> > > > > >file is 15M.
> > > > > >The data partition (e:) has 8G free space, although the
> > > > > system/boot
> > > > > >partition (c:) has about 2G
> > > > > >
> > > > > >Does anyone have any ideas how this could be happening?
> > > > > >--
> > > > > >regards,
> > > > > >
> > > > > >Paul Durdin
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It was me! It was me! I shall have to go on a re-education course / take
up
> a new career, or maybe pack up for the day and get ready to watch the
rugby
> over the weekend (its very weird watching rugby in the morning - it should
> be an evening thing with plenty of beer).
LOL! :-) We all do this from time to time. One tip: IF contradictions, trust
the engine, as the tools might have bugs in them. This wasn't such a case,
but could have been .
Well, here, we have to go to a sportsbar to watch the rugby. In the
morning...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
news:%2336PHpsqDHA.1960@.TK2MSFTNGP12.phx.gbl...
> I was looking in the database properties under Transaction Log->Space
> allocated.
> Doh! I think it was me - I took 15,xxx to be 15M but now the figure is
11(M)
> so EM was reporting 15G (correctly).
> It was me! It was me! I shall have to go on a re-education course / take
up
> a new career, or maybe pack up for the day and get ready to watch the
rugby
> over the weekend (its very weird watching rugby in the morning - it should
> be an evening thing with plenty of beer).
> --
> regards,
> Paul Durdin
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eoivagsqDHA.2776@.tk2msftngp13.phx.gbl...
> > Seems like EM reports the size incorrectly... Where in EM do you see
that
> > space reported? In the taskpad or right-click the database, properties?
> >
> > To shrink log, Check out below KB articles:
> >
> > INF: How to Shrink the SQL Server 7.0 Transaction Log
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> >
> > INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC
SHRINKFILE
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> >
> > Log File Grows too big
> > http://www.support.microsoft.com/?id=317375
> >
> > Log file filling up
> > http://www.support.microsoft.com/?id=110139
> >
> > Considerations for Autogrow and AutoShrink
> > http://www.support.microsoft.com/?id=315512
> >
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > news:OBrU7YsqDHA.3844@.tk2msftngp13.phx.gbl...
> > > Thanks for the suggestions.
> > >
> > > I've checked the backup file using RESTORE FILELISTONLY FROM
> > > FILE='backupfilename'
> > >
> > > This reports the log file as 16377380864 (where have I seem this
figure
> > > before?) So I guess that explains why SQLSvr says it cant restore the
> > > database.
> > >
> > > I checked the log file size in EM to be 14,643M, but the physical log
> file
> > > is actually 14,993,664k.
> > >
> > > Being a newbie to SQL DBA stuff I am puzzled. How come the physical
size
> > is
> > > different from the size recorded in EM?
> > >
> > > I did a bit of reading, and I am starting to get a bit of an
> > understanding,
> > > so I did a...
> > >
> > > DBCC SHRINKDATABASE (databasename)
> > > followed by
> > > BACKUP LOG databasename WITH TRUNCATE_ONLY
> > >
> > > Then did another backup and examined the backup as above. I get much
the
> > > same figures as before.
> > >
> > > How can I shrink the log file (having already dome the above).
> > >
> > > --
> > > regards,
> > >
> > > Paul Durdin
> > >
> > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > > wrote in message news:e1HtntqqDHA.2588@.tk2msftngp13.phx.gbl...
> > > > When you restore a database, you need the same amount of space for
the
> > > mdf,
> > > > ndf and ldf as the size those files had when you did the backup. If
> the
> > > > files were smaller when you did the backup and the restore claims
you
> > need
> > > > more space, you have hit a bug in SQL Server (I haven't heard of
such
> a
> > > bug,
> > > > though). A couple of things:
> > > >
> > > > Make sure that you restore the correct backup. One backup file can
> have
> > > more
> > > > than one backup. Check using RESTORE HEADERONLY.
> > > > Check the size needed according to the backup. Use RESTORE
> FILELISTONLY.
> > > > Make sure you specify the correct FILE = if the backup file has
more
> > then
> > > > one backup.
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > > >
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Paul Durdin" <pdurdin@.nospam_hotmail.com> wrote in message
> > > > news:%23Vrx9mqqDHA.2644@.TK2MSFTNGP09.phx.gbl...
> > > > > That's indeed what the error message says - but surely you telling
> me
> > > that
> > > > a
> > > > > 140M database needs 16G to restore it!
> > > > >
> > > > > --
> > > > > regards,
> > > > >
> > > > >
> > > > > "Jorge Lavado" <jorge.Lavado@.cgey.com_NO_SPAM> wrote in message
> > > > > news:0aab01c3aa9b$1255c6d0$a101280a@.phx.gbl...
> > > > > > Hi.
> > > > > > The data partition (e:) need + - 16 GB. free,you only have
> > > > > > + - 6 GB.
> > > > > >
> > > > > > >--Original Message--
> > > > > > >I am trying to move a database from one machine to
> > > > > > another by taking a
> > > > > > >backup from one and restoring it on another. Both
> > > > > > machines are W2k sp3 with
> > > > > > >SQL2k sp3. As far as I can tell they are both
> > > > > > straightforward, standard
> > > > > > >installations (OS and DB), except;
> > > > > > >The target machine has been partitioned into three
> > > > > > (system, apps, and data),
> > > > > > >and the disk is mirrored.
> > > > > > >
> > > > > > >I can take the backup and restore it to a number of
> > > > > > machines, but on the
> > > > > > >machine that I need to restore the database to I get;
> > > > > > >
> > > > > > >Microsoft SQL-DMO(ODBC SQL State: 42000)
> > > > > > >There is insufficient free space on disk volume e:\ to
> > > > > > create the database.
> > > > > > >The database requires 16519462912 additional free bytes,
> > > > > > while only
> > > > > > >6329229312 bytes are available.
> > > > > > >RESTORE DATABASE is terminating abnormally.
> > > > > > >
> > > > > > >The database backup file is only 146M, the data file is
> > > > > > 143M and the log
> > > > > > >file is 15M.
> > > > > > >The data partition (e:) has 8G free space, although the
> > > > > > system/boot
> > > > > > >partition (c:) has about 2G
> > > > > > >
> > > > > > >Does anyone have any ideas how this could be happening?
> > > > > > >--
> > > > > > >regards,
> > > > > > >
> > > > > > >Paul Durdin
> > > > > > >
> > > > > > >
> > > > > > >.
> > > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>