Recovery of database on a mount point

When restoring a database where the data file(s) are located under a mount point, seems to be a problem with reporting free space available.

DB Size - Say 30GB (25 Data, 5 Log)

E:\ Drive 20GB with 15GB Free

Restoring database file to

E:\SQLMountPoint\

- This points to separate disk with more than enough free space

SQL prevents the restore by stating there is not enough free space.

A long work around is

assign a drive letter to the large disk

Restore database using temp drive letter - F:\SQLDATA\MyDB_Data.mdf

Update sysfiles - changing drive & path - E:\SQLMountPoint\SQLDATA\MyDB_Data.mdf

Stop & restart database...

Is there a better way?

Wishlist - Restore only warns about free space - but allows continue...

Regards

Which OS version and SQL Server version are you using?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Thanks for the reply.

OS: Windows2003 x64 Datacenter R2

SQL: Now running 9.00.3175 - seems to have resolved the problem?

- unable to find mention in fixlists

More testing required just to be sure.

LateNightDBA...

|||

You will typically not find every fix in the published fix list.

I'm glad it works now.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Recovering unused space on Sql 7.0 tables

Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evanDepends on how the database files were created. SS 7.0 is kind of PITA
regarding file management... It's one of the main reasons to upgrade to
SS2K.
Anyway, execute the following from within the database you are discribing to
get an accurate representation:
EXECUTE sp_spaceused @.updateusage = 'true'
EXECUTE sp_helpdb 'MyDatabase'
Now, use the DBCC SHRINKFILE command to reclaim the space you desire on the
data files. SS 7.0 space reclaimation on the transaction logs is lousy.
More often than not, you have to detach the database, move the transaction
log somewhere else, then use the sp_attach_single_file_db to have the
database brought back online with a newly created, and hopefully smaller,
transaction log.
Hope this helps.
Sincerely,
Anthony Thomas
"evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
news:OGlnIj52EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evan

Recovering unused space on Sql 7.0 tables

Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evan
Depends on how the database files were created. SS 7.0 is kind of PITA
regarding file management... It's one of the main reasons to upgrade to
SS2K.
Anyway, execute the following from within the database you are discribing to
get an accurate representation:
EXECUTE sp_spaceused @.updateusage = 'true'
EXECUTE sp_helpdb 'MyDatabase'
Now, use the DBCC SHRINKFILE command to reclaim the space you desire on the
data files. SS 7.0 space reclaimation on the transaction logs is lousy.
More often than not, you have to detach the database, move the transaction
log somewhere else, then use the sp_attach_single_file_db to have the
database brought back online with a newly created, and hopefully smaller,
transaction log.
Hope this helps.
Sincerely,
Anthony Thomas

"evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
news:OGlnIj52EHA.1404@.TK2MSFTNGP11.phx.gbl...
Hi List,
I have 4 tables with 20+million rows (3rd party app) anyway these tables are
reporting their space used in the 50+gigabytes but the space they are
claiming is about 70-90 gb is there anyway to force them to shrink to
recover this space.
thanks
evan

Recovering space in a heavily fragmented table

I have a large (240GB) table which contains a very large
amount of character data in text datatype columns. During
recent maintenance a good deal of this data has been
nulled to save space resulting in the table reporting
about 80GB of unused space within the allocated extents.
What is the best way to defrag this table and recover the
space?
The table has NO clustered index on it.
Thanks to anyone who thinks they can help!!!
JonathanHi Jonathan,
You can't recover empty extents that have been used by text directly in SQL
Server 2000, but you have two options to work around it:
1) you can copy the data to a new table, drop the old table and then rename
the new table with sp_rename.
2) copy the data in the text column to a new table, drop the text column
from the original table, run DBCC CLEANTABLE on the original table, recreate
the column and copy the data back in and finally drop the new table.
Jacco Schalkwijk
SQL Server MVP
"Jonathan Smith" <jonathan.smith@.moneysupermarket.com> wrote in message
news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...

quote:

---

> I have a large (240GB) table which contains a very large
> amount of character data in text datatype columns. During
> recent maintenance a good deal of this data has been
> nulled to save space resulting in the table reporting
> about 80GB of unused space within the allocated extents.
> What is the best way to defrag this table and recover the
> space?
> The table has NO clustered index on it.
> Thanks to anyone who thinks they can help!!!
> Jonathan

---

|||Ah. And I thought I was being really stupid and there
would be a simple answer. Thanks for your help. I believe
a long night may be in store for me...
Jonathan Smith

quote:

---

>--Original Message--
>Hi Jonathan,
>You can't recover empty extents that have been used by

---

text directly in SQL

quote:

---

>Server 2000, but you have two options to work around it:
>1) you can copy the data to a new table, drop the old

---

table and then rename

quote:

---

>the new table with sp_rename.
>2) copy the data in the text column to a new table, drop

---

the text column

quote:

---

>from the original table, run DBCC CLEANTABLE on the

---

original table, recreate

quote:

---

>the column and copy the data back in and finally drop the

---

new table.

quote:

---

>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jonathan Smith" <jonathan.smith@.moneysupermarket.com>

---

wrote in message

quote:

---

>news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...
During[QUOTE]
the[QUOTE]
>
>.
>

---

|||fyi - this functional shortfall has been fixed in Yukon. Both shrink and
defrag will compact LOB extents.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Smith" <jonathan.smith@.moneysupermarket.com> wrote in message
news:473d01c3e413$c7619ac0$a501280a@.phx.gbl...[QUOTE]
> Ah. And I thought I was being really stupid and there
> would be a simple answer. Thanks for your help. I believe
> a long night may be in store for me...
> Jonathan Smith
>
> text directly in SQL
> table and then rename
> the text column
> original table, recreate
> new table.
> wrote in message
> During
> the

Recovering space in a heavily fragmented table

I have a large (240GB) table which contains a very large
amount of character data in text datatype columns. During
recent maintenance a good deal of this data has been
nulled to save space resulting in the table reporting
about 80GB of unused space within the allocated extents.
What is the best way to defrag this table and recover the
space?
The table has NO clustered index on it.
Thanks to anyone who thinks they can help!!!
JonathanHi Jonathan,
You can't recover empty extents that have been used by text directly in SQL
Server 2000, but you have two options to work around it:
1) you can copy the data to a new table, drop the old table and then rename
the new table with sp_rename.
2) copy the data in the text column to a new table, drop the text column
from the original table, run DBCC CLEANTABLE on the original table, recreate
the column and copy the data back in and finally drop the new table.
--
Jacco Schalkwijk
SQL Server MVP
"Jonathan Smith" <jonathan.smith@.moneysupermarket.com> wrote in message
news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...
> I have a large (240GB) table which contains a very large
> amount of character data in text datatype columns. During
> recent maintenance a good deal of this data has been
> nulled to save space resulting in the table reporting
> about 80GB of unused space within the allocated extents.
> What is the best way to defrag this table and recover the
> space?
> The table has NO clustered index on it.
> Thanks to anyone who thinks they can help!!!
> Jonathan|||Ah. And I thought I was being really stupid and there
would be a simple answer. Thanks for your help. I believe
a long night may be in store for me...
Jonathan Smith
>--Original Message--
>Hi Jonathan,
>You can't recover empty extents that have been used by
text directly in SQL
>Server 2000, but you have two options to work around it:
>1) you can copy the data to a new table, drop the old
table and then rename
>the new table with sp_rename.
>2) copy the data in the text column to a new table, drop
the text column
>from the original table, run DBCC CLEANTABLE on the
original table, recreate
>the column and copy the data back in and finally drop the
new table.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jonathan Smith" <jonathan.smith@.moneysupermarket.com>
wrote in message
>news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...
>> I have a large (240GB) table which contains a very large
>> amount of character data in text datatype columns.
During
>> recent maintenance a good deal of this data has been
>> nulled to save space resulting in the table reporting
>> about 80GB of unused space within the allocated extents.
>> What is the best way to defrag this table and recover
the
>> space?
>> The table has NO clustered index on it.
>> Thanks to anyone who thinks they can help!!!
>> Jonathan
>
>.
>|||fyi - this functional shortfall has been fixed in Yukon. Both shrink and
defrag will compact LOB extents.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Smith" <jonathan.smith@.moneysupermarket.com> wrote in message
news:473d01c3e413$c7619ac0$a501280a@.phx.gbl...
> Ah. And I thought I was being really stupid and there
> would be a simple answer. Thanks for your help. I believe
> a long night may be in store for me...
> Jonathan Smith
> >--Original Message--
> >Hi Jonathan,
> >
> >You can't recover empty extents that have been used by
> text directly in SQL
> >Server 2000, but you have two options to work around it:
> >
> >1) you can copy the data to a new table, drop the old
> table and then rename
> >the new table with sp_rename.
> >2) copy the data in the text column to a new table, drop
> the text column
> >from the original table, run DBCC CLEANTABLE on the
> original table, recreate
> >the column and copy the data back in and finally drop the
> new table.
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Jonathan Smith" <jonathan.smith@.moneysupermarket.com>
> wrote in message
> >news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...
> >> I have a large (240GB) table which contains a very large
> >> amount of character data in text datatype columns.
> During
> >> recent maintenance a good deal of this data has been
> >> nulled to save space resulting in the table reporting
> >> about 80GB of unused space within the allocated extents.
> >> What is the best way to defrag this table and recover
> the
> >> space?
> >> The table has NO clustered index on it.
> >> Thanks to anyone who thinks they can help!!!
> >>
> >> Jonathan
> >
> >
> >.
> >sql

Recover Lost data files?

Hi
When we issue a DBCC SHRINKFILE with empty option, the data file
is removed and the space is alloted back to the OS.
Same way when we issue a an ALTER DATABASE to remove a database,
all the data files and transaction log files are dropped.
But can these files be recovered again by using any of the disk
recovery tools?

AchillesDBCC SHRINKFILE doesn't remove a data file it just removes unused space in a
file. ALTER DATABASE doesn't remove databases, although it can be used to
remove files (but only when they are empty of data). Since neither of these
statements actually removes any data from your database there isn't really
anything to recover. I doubt it would be feasible to restore a database to a
prior state using disk recovery tools and I definitely wouldn't recommend
you try as the likely result would be a corrupt database.

If you think you have lost some data for some reason then restoring from a
backup is your best course of action. But as I said, neither of the
statements you have mentioned should cause you to lose data even if you use
them in error.

Hope this helps.

--
David Portas
SQL Server MVP
--