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
Showing posts with label amount. Show all posts
Showing posts with label amount. Show all posts
Friday, March 23, 2012
Recovering Reports
I had an unfortunate situation where a downed server resulted in a significant amount of lost work (the rdl files of several reports I had been working on).
I had previously uploaded them to the reporting services server and had hoped that I could recover them from there - but I can't find them anywhere.
Is there a mechnaism for recovering rdl files (or at least the sql behind them?) from the reporting services server?
That's all.
I hope it helps you.
VectorR3|||Excellent - thank you...sql
Recovering Reports
I had an unfortunate situation where a downed server resulted in a
significant amount of lost work (the rdl files of several reports I had been
working on).
I had previously uploaded them to the reporting services server and had
hoped that I could recover them from there - but I can't find them anywhere.
Is there a mechnaism for recovering rdl files (or at least the sql behind
them?) from the reporting services server?I beleive it is stored in an image field named Content in the Catalog table
of your reporting server database
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Kwebster" <Kwebster@.discussions.microsoft.com> wrote in message
news:4DBD3671-64E8-4159-8887-D26B59C18D89@.microsoft.com...
> I had an unfortunate situation where a downed server resulted in a
> significant amount of lost work (the rdl files of several reports I had
been
> working on).
> I had previously uploaded them to the reporting services server and had
> hoped that I could recover them from there - but I can't find them
anywhere.
> Is there a mechnaism for recovering rdl files (or at least the sql behind
> them?) from the reporting services server?|||Are the reports still showing up when you go to the report manager site? If
so, you can go to the properties -> general page of each report and click on
the edit link under report definition and save as.
"Kwebster" wrote:
> I had an unfortunate situation where a downed server resulted in a
> significant amount of lost work (the rdl files of several reports I had been
> working on).
> I had previously uploaded them to the reporting services server and had
> hoped that I could recover them from there - but I can't find them anywhere.
> Is there a mechnaism for recovering rdl files (or at least the sql behind
> them?) from the reporting services server?|||Hello Daivd. A thousand thank you's - worked perfectly.
"David Siebert" wrote:
> Are the reports still showing up when you go to the report manager site? If
> so, you can go to the properties -> general page of each report and click on
> the edit link under report definition and save as.
> "Kwebster" wrote:
> > I had an unfortunate situation where a downed server resulted in a
> > significant amount of lost work (the rdl files of several reports I had been
> > working on).
> >
> > I had previously uploaded them to the reporting services server and had
> > hoped that I could recover them from there - but I can't find them anywhere.
> >
> > Is there a mechnaism for recovering rdl files (or at least the sql behind
> > them?) from the reporting services server?|||Hrllo Wayne
Thank you - learn something new everyday!
"Wayne Snyder" wrote:
> I beleive it is stored in an image field named Content in the Catalog table
> of your reporting server database
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Kwebster" <Kwebster@.discussions.microsoft.com> wrote in message
> news:4DBD3671-64E8-4159-8887-D26B59C18D89@.microsoft.com...
> > I had an unfortunate situation where a downed server resulted in a
> > significant amount of lost work (the rdl files of several reports I had
> been
> > working on).
> >
> > I had previously uploaded them to the reporting services server and had
> > hoped that I could recover them from there - but I can't find them
> anywhere.
> >
> > Is there a mechnaism for recovering rdl files (or at least the sql behind
> > them?) from the reporting services server?
>
>
significant amount of lost work (the rdl files of several reports I had been
working on).
I had previously uploaded them to the reporting services server and had
hoped that I could recover them from there - but I can't find them anywhere.
Is there a mechnaism for recovering rdl files (or at least the sql behind
them?) from the reporting services server?I beleive it is stored in an image field named Content in the Catalog table
of your reporting server database
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Kwebster" <Kwebster@.discussions.microsoft.com> wrote in message
news:4DBD3671-64E8-4159-8887-D26B59C18D89@.microsoft.com...
> I had an unfortunate situation where a downed server resulted in a
> significant amount of lost work (the rdl files of several reports I had
been
> working on).
> I had previously uploaded them to the reporting services server and had
> hoped that I could recover them from there - but I can't find them
anywhere.
> Is there a mechnaism for recovering rdl files (or at least the sql behind
> them?) from the reporting services server?|||Are the reports still showing up when you go to the report manager site? If
so, you can go to the properties -> general page of each report and click on
the edit link under report definition and save as.
"Kwebster" wrote:
> I had an unfortunate situation where a downed server resulted in a
> significant amount of lost work (the rdl files of several reports I had been
> working on).
> I had previously uploaded them to the reporting services server and had
> hoped that I could recover them from there - but I can't find them anywhere.
> Is there a mechnaism for recovering rdl files (or at least the sql behind
> them?) from the reporting services server?|||Hello Daivd. A thousand thank you's - worked perfectly.
"David Siebert" wrote:
> Are the reports still showing up when you go to the report manager site? If
> so, you can go to the properties -> general page of each report and click on
> the edit link under report definition and save as.
> "Kwebster" wrote:
> > I had an unfortunate situation where a downed server resulted in a
> > significant amount of lost work (the rdl files of several reports I had been
> > working on).
> >
> > I had previously uploaded them to the reporting services server and had
> > hoped that I could recover them from there - but I can't find them anywhere.
> >
> > Is there a mechnaism for recovering rdl files (or at least the sql behind
> > them?) from the reporting services server?|||Hrllo Wayne
Thank you - learn something new everyday!
"Wayne Snyder" wrote:
> I beleive it is stored in an image field named Content in the Catalog table
> of your reporting server database
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Kwebster" <Kwebster@.discussions.microsoft.com> wrote in message
> news:4DBD3671-64E8-4159-8887-D26B59C18D89@.microsoft.com...
> > I had an unfortunate situation where a downed server resulted in a
> > significant amount of lost work (the rdl files of several reports I had
> been
> > working on).
> >
> > I had previously uploaded them to the reporting services server and had
> > hoped that I could recover them from there - but I can't find them
> anywhere.
> >
> > Is there a mechnaism for recovering rdl files (or at least the sql behind
> > them?) from the reporting services server?
>
>
Subscribe to:
Posts (Atom)