Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
--
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>> Ok, I am seeing something weird.
>> I have a production database that according to EM and DBCC SHOWFILESTATS
>> has 140GB of space allocated and is currently using 138GB. However,
>> according to the OS, the data file is ~ 157GB. I also perform log
>> shipping with this DB and the size of the data file on the standby copy
>> is
>> correct @. 140GB. This tells me that the number OS seen size on the
>> production server is definitely incorrect. How can this be? What could
>> cause this sort of discrepancy?
>> The only thing that makes even a bit of sense.... the last mod date on
>> the data file is last week around the time I had a stupid vendor kick off
>> a select * into from a table with 35 million records. It is possible that
>> this caused an auto grow. The transaction never committed as I caught it
>> and rolled it back. Could the growth on the OS side have "stuck". The
>> numbers jive somewhat with what the auto growth rate is set at.
>> Regardless I am not ready to give up that much additional space right
>> now,
>> especially not knowing if the OS/SQL are chatting correctly, therefore
>> making what the OS sees actually available to SQL.
>> Any input would be greatly appreciated.
>> Thanks!
>> Paulsql
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Thursday, March 22, 2012
Database Size - Allocated v. Used - Strange Problem
Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
PaulWell I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
>
Database Size - Allocated v. Used - Strange Problem
Ok, I am seeing something weird.
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
Paul
Well I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>
|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
I have a production database that according to EM and DBCC SHOWFILESTATS
has 140GB of space allocated and is currently using 138GB. However,
according to the OS, the data file is ~ 157GB. I also perform log
shipping with this DB and the size of the data file on the standby copy is
correct @. 140GB. This tells me that the number OS seen size on the
production server is definitely incorrect. How can this be? What could
cause this sort of discrepancy?
The only thing that makes even a bit of sense.... the last mod date on
the data file is last week around the time I had a stupid vendor kick off
a select * into from a table with 35 million records. It is possible that
this caused an auto grow. The transaction never committed as I caught it
and rolled it back. Could the growth on the OS side have "stuck". The
numbers jive somewhat with what the auto growth rate is set at.
Regardless I am not ready to give up that much additional space right now,
especially not knowing if the OS/SQL are chatting correctly, therefore
making what the OS sees actually available to SQL.
Any input would be greatly appreciated.
Thanks!
Paul
Well I think I fixed it.
I grew the data file by 1MB through EM, and the file size value in the OS
seemed to "reset" itself.
Strange.
Paul
"Paul Timmerman" wrote:
> Ok, I am seeing something weird.
> I have a production database that according to EM and DBCC SHOWFILESTATS
> has 140GB of space allocated and is currently using 138GB. However,
> according to the OS, the data file is ~ 157GB. I also perform log
> shipping with this DB and the size of the data file on the standby copy is
> correct @. 140GB. This tells me that the number OS seen size on the
> production server is definitely incorrect. How can this be? What could
> cause this sort of discrepancy?
> The only thing that makes even a bit of sense.... the last mod date on
> the data file is last week around the time I had a stupid vendor kick off
> a select * into from a table with 35 million records. It is possible that
> this caused an auto grow. The transaction never committed as I caught it
> and rolled it back. Could the growth on the OS side have "stuck". The
> numbers jive somewhat with what the auto growth rate is set at.
> Regardless I am not ready to give up that much additional space right now,
> especially not knowing if the OS/SQL are chatting correctly, therefore
> making what the OS sees actually available to SQL.
> Any input would be greatly appreciated.
> Thanks!
> Paul
>
|||Try DBCC UPDATEUSAGE next time and see if that helps.
Andrew J. Kelly SQL MVP
"Paul Timmerman" <PaulTimmerman@.discussions.microsoft.com> wrote in message
news:75C0B5A4-A4EF-48F0-B4C2-BB4F22C9F0E1@.microsoft.com...[vbcol=seagreen]
> Well I think I fixed it.
> I grew the data file by 1MB through EM, and the file size value in the OS
> seemed to "reset" itself.
> Strange.
> Paul
> "Paul Timmerman" wrote:
Sunday, March 11, 2012
Database retains size
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribió en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribió en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> > What recovery model is the database in?
> >
> > You can set up a trace to see when the database is growing. There are
> > database and log file growth events.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Eduardo Sicouret" <esicouret> wrote in message
> > news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have this 10 GB size database and unfortunately i'm running out of sp=ace
> > in my HDD. I'm using SQL Server 2000.
> >
> > before i use dbcc shrink file on the database data file, the result of
> > sp_spaceused are the following:
> >
> > db_name database_size unallocated space
> > abc 10881.70 MB -2526.48 MB
> >
> > After I use: DBCC SHRINKFILE (abc_data, 10000)
> >
> > I reduce the data file to aprox 10000, that is 800 Megs.
> >
> > That all works fine, but next day as I check my DB the database size
> > returns
> > to it's original size.
> >
> > There's no way that in one night 800 megs of data is inserted in the
> > database.
> >
> > I execute DBCC Reindex on 2 tables at night and do some backups..
> >
> > Do you have any idea of what's maybe happening?
> > Any tip for regain some space?
> >
> > Thanks
> >
> > Eduardo Sicouret
> >
> >
> >|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> > What growth rate do you have specified in the database properties?
> >|||You shrink the file, then reindex which need a lot of working space which is why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is that if you want best
performance, you need this working space. Some extra info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size returns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:
> You shrink the file, then reindex which need a lot of working space which is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is that if you want best
> performance, you need this working space. Some extra info here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have this 10 GB size database and unfortunately i'm running out of space in my HDD. I'm using
> > SQL Server 2000.
> >
> > before i use dbcc shrink file on the database data file, the result of sp_spaceused are the
> > following:
> >
> > db_name database_size unallocated space
> > abc 10881.70 MB -2526.48 MB
> >
> > After I use: DBCC SHRINKFILE (abc_data, 10000)
> >
> > I reduce the data file to aprox 10000, that is 800 Megs.
> >
> > That all works fine, but next day as I check my DB the database size returns to it's original
> > size.
> >
> > There's no way that in one night 800 megs of data is inserted in the database.
> >
> > I execute DBCC Reindex on 2 tables at night and do some backups..
> >
> > Do you have any idea of what's maybe happening?
> > Any tip for regain some space?
> >
> > Thanks
> >
> > Eduardo Sicouret
> >|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.|||See whether you can shrink your logfile.. too..
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribió en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribió en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> > What recovery model is the database in?
> >
> > You can set up a trace to see when the database is growing. There are
> > database and log file growth events.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Eduardo Sicouret" <esicouret> wrote in message
> > news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have this 10 GB size database and unfortunately i'm running out of sp=ace
> > in my HDD. I'm using SQL Server 2000.
> >
> > before i use dbcc shrink file on the database data file, the result of
> > sp_spaceused are the following:
> >
> > db_name database_size unallocated space
> > abc 10881.70 MB -2526.48 MB
> >
> > After I use: DBCC SHRINKFILE (abc_data, 10000)
> >
> > I reduce the data file to aprox 10000, that is 800 Megs.
> >
> > That all works fine, but next day as I check my DB the database size
> > returns
> > to it's original size.
> >
> > There's no way that in one night 800 megs of data is inserted in the
> > database.
> >
> > I execute DBCC Reindex on 2 tables at night and do some backups..
> >
> > Do you have any idea of what's maybe happening?
> > Any tip for regain some space?
> >
> > Thanks
> >
> > Eduardo Sicouret
> >
> >
> >|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> > What growth rate do you have specified in the database properties?
> >|||You shrink the file, then reindex which need a lot of working space which is why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is that if you want best
performance, you need this working space. Some extra info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size returns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:
> You shrink the file, then reindex which need a lot of working space which is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is that if you want best
> performance, you need this working space. Some extra info here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> > Hello,
> >
> > I have this 10 GB size database and unfortunately i'm running out of space in my HDD. I'm using
> > SQL Server 2000.
> >
> > before i use dbcc shrink file on the database data file, the result of sp_spaceused are the
> > following:
> >
> > db_name database_size unallocated space
> > abc 10881.70 MB -2526.48 MB
> >
> > After I use: DBCC SHRINKFILE (abc_data, 10000)
> >
> > I reduce the data file to aprox 10000, that is 800 Megs.
> >
> > That all works fine, but next day as I check my DB the database size returns to it's original
> > size.
> >
> > There's no way that in one night 800 megs of data is inserted in the database.
> >
> > I execute DBCC Reindex on 2 tables at night and do some backups..
> >
> > Do you have any idea of what's maybe happening?
> > Any tip for regain some space?
> >
> > Thanks
> >
> > Eduardo Sicouret
> >|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.|||See whether you can shrink your logfile.. too..
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
Database retains size
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribi en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:[vbcol=seagreen]
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
ace[vbcol=seagreen]|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:[vbcol=seagreen]
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...|||You shrink the file, then reindex which need a lot of working space which is
why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is t
hat if you want best
performance, you need this working space. Some extra info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl..
.
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_
spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size retur
ns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the datab
ase.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:[vbcol=seagreen]
> You shrink the file, then reindex which need a lot of working space which
is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is
that if you want best
> performance, you need this working space. Some extra info here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2M
SFTNGP04.phx.gbl...|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo SicouretWhat growth rate do you have specified in the database properties?|||What recovery model is the database in?
You can set up a trace to see when the database is growing. There are
database and log file growth events.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Eduardo Sicouret" <esicouret> wrote in message
news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
Hello,
I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using SQL Server 2000.
before i use dbcc shrink file on the database data file, the result of
sp_spaceused are the following:
db_name database_size unallocated space
abc 10881.70 MB -2526.48 MB
After I use: DBCC SHRINKFILE (abc_data, 10000)
I reduce the data file to aprox 10000, that is 800 Megs.
That all works fine, but next day as I check my DB the database size returns
to it's original size.
There's no way that in one night 800 megs of data is inserted in the
database.
I execute DBCC Reindex on 2 tables at night and do some backups..
Do you have any idea of what's maybe happening?
Any tip for regain some space?
Thanks
Eduardo Sicouret|||File growth is automatically 64 megs at a time.
ESB
"Tracy McKibben" <tracy.mckibben@.gmail.com> escribi en el mensaje
news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...
> What growth rate do you have specified in the database properties?
>|||Recovery model is FULL and Autoshrink, Auto update statistics, auto create
statistics are checked
ESB
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi en el mensaje
news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
> What recovery model is the database in?
> You can set up a trace to see when the database is growing. There are
> database and log file growth events.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message
> news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
> in my HDD. I'm using SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of
> sp_spaceused are the following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size
> returns
> to it's original size.
> There's no way that in one night 800 megs of data is inserted in the
> database.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>
>|||I bet the reindex job is leaving unused space in your db. Run DBCC
UPDATEUSAGE after rebuilding the indexes and see if that helps. Ken.
Eduardo Sicouret wrote:[vbcol=seagreen]
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
> ESB
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> escribi=F3 en el mensaje
> news:%23zoKn2wiGHA.4776@.TK2MSFTNGP05.phx.gbl...
ace[vbcol=seagreen]|||How big are the tables you're reindexing, and are there clustered
indexes on them? If these are large tables with clustered indexes, the
reindex operation is likely causing the database to grow.
Eduardo Sicouret wrote:[vbcol=seagreen]
> File growth is automatically 64 megs at a time.
> ESB
> "Tracy McKibben" <tracy.mckibben@.gmail.com> escribi=F3 en el mensaje
> news:1149779303.783251.118980@.j55g2000cwa.googlegroups.com...|||You shrink the file, then reindex which need a lot of working space which is
why the size blows up.
If you shrink again, the reindex is basically undone again. Bottom line is t
hat if you want best
performance, you need this working space. Some extra info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2MSFTNGP04.phx.gbl..
.
> Hello,
> I have this 10 GB size database and unfortunately i'm running out of space
in my HDD. I'm using
> SQL Server 2000.
> before i use dbcc shrink file on the database data file, the result of sp_
spaceused are the
> following:
> db_name database_size unallocated space
> abc 10881.70 MB -2526.48 MB
> After I use: DBCC SHRINKFILE (abc_data, 10000)
> I reduce the data file to aprox 10000, that is 800 Megs.
> That all works fine, but next day as I check my DB the database size retur
ns to it's original
> size.
> There's no way that in one night 800 megs of data is inserted in the datab
ase.
> I execute DBCC Reindex on 2 tables at night and do some backups..
> Do you have any idea of what's maybe happening?
> Any tip for regain some space?
> Thanks
> Eduardo Sicouret
>|||Should also probably point out that all this
shrinking/growing/shrinking/growing is going to severly fragment the
database file, eventually hurting performance.
Tibor Karaszi wrote:[vbcol=seagreen]
> You shrink the file, then reindex which need a lot of working space which
is why the size blows up.
> If you shrink again, the reindex is basically undone again. Bottom line is
that if you want best
> performance, you need this working space. Some extra info here:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Eduardo Sicouret" <esicouret> wrote in message news:esf4dxwiGHA.3848@.TK2M
SFTNGP04.phx.gbl...|||"Eduardo Sicouret" <esicouret> wrote in message
news:%234bwfGxiGHA.4372@.TK2MSFTNGP03.phx.gbl...
> Recovery model is FULL and Autoshrink, Auto update statistics, auto create
> statistics are checked
>
I'd disable Autoshrink btw.
Growing and shrinking your DB can lead to disk level fragmentation.
Saturday, February 25, 2012
database repair without data loss?
Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.
Subscribe to:
Posts (Atom)