DBfile usage: showing

Showing posts with label showing. Show all posts

Tuesday, March 27, 2012

DBCC Shrinkdatabase /shrinkfile not shrinking my database

I have 137GB of database (mdf). it is showing on database properties
that i still have about 63Gb available. If i go to EM-shrink file, i
can have a minimum of 65GB which i guess is my actual data. But when i
shrink it from there or from Query Analyzer, it still remains 137Gb.
I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
work.
can anybody help pls.
TIA
JorizJoriz,
Just to clarify, this is the .mdf file and not the .ldf file that you're
trying to shrink correct? What are you specifying for the targetpercent in
the DBCC SHRINKFILE statement? Do you have exclusive access to the database
right now?
HTH
Jerry
<google@.joriz.is-a-geek.net> wrote in message
news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>
> I have 137GB of database (mdf). it is showing on database properties
> that i still have about 63Gb available. If i go to EM-shrink file, i
> can have a minimum of 65GB which i guess is my actual data. But when i
> shrink it from there or from Query Analyzer, it still remains 137Gb.
> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> work.
> can anybody help pls.
> TIA
> Joriz
>|||Hi Joriz,
If u want to shrink the mdf file then ui have to run srink database
command.
But in most cases that doesnt work.
The process u are following works in ldf files.
If u want to optimise ur logfile on disk.
u have to run this command
backup log database name with truncate_only
dbcc shrinkfile(logical name , size)
HTH
from
Doller
Jerry Spivey wrote:
> Joriz,
> Just to clarify, this is the .mdf file and not the .ldf file that you're
> trying to shrink correct? What are you specifying for the targetpercent in
> the DBCC SHRINKFILE statement? Do you have exclusive access to the database
> right now?
> HTH
> Jerry
> <google@.joriz.is-a-geek.net> wrote in message
> news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
> >
> >
> > I have 137GB of database (mdf). it is showing on database properties
> > that i still have about 63Gb available. If i go to EM-shrink file, i
> > can have a minimum of 65GB which i guess is my actual data. But when i
> > shrink it from there or from Query Analyzer, it still remains 137Gb.
> > I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
> > work.
> >
> > can anybody help pls.
> >
> > TIA
> >
> > Joriz
> >|||doller wrote:
> Hi Joriz,
> If u want to shrink the mdf file then ui have to run srink database
> command.
> But in most cases that doesnt work.
> The process u are following works in ldf files.
> If u want to optimise ur logfile on disk.
> u have to run this command
> backup log database name with truncate_only
> dbcc shrinkfile(logical name , size)
> HTH
> from
> Doller
>
> Jerry Spivey wrote:
>> Joriz,
>> Just to clarify, this is the .mdf file and not the .ldf file that you're
>> trying to shrink correct? What are you specifying for the targetpercent in
>> the DBCC SHRINKFILE statement? Do you have exclusive access to the database
>> right now?
>> HTH
>> Jerry
>> <google@.joriz.is-a-geek.net> wrote in message
>> news:1129585565.049054.249070@.g44g2000cwa.googlegroups.com...
>> I have 137GB of database (mdf). it is showing on database properties
>> that i still have about 63Gb available. If i go to EM-shrink file, i
>> can have a minimum of 65GB which i guess is my actual data. But when i
>> shrink it from there or from Query Analyzer, it still remains 137Gb.
>> I even tried doing a dbcc shrinkfile to 110GB, but still it didn't
>> work.
>> can anybody help pls.
>> TIA
>> Joriz
>
Shrinking the database file doesn't require a SHRINK DATABASE - that can
be done with the DBCC SHRINKFILE command as well.
When you want to shrink your database file (.mdf) you can use the syntax
below :
USE YourDatabaseName
DBCC SHRINKFILE (LogicalDataFileName, TargetSize)
You can look up the command in Books On Line. If you need to get the
logical file names of your database, you can use sp_helpdb.
HTH
Regards
Steen

DBCC Shrinkdatabase /shrinkfile not shrinking my database

Sunday, March 25, 2012

DBCC SHOWCONTIG incorrect?

Hi. I have a table that has 58 million rows and is about 24 GB. I ran a DBCC
DBREINDEX which appeared to run fine - new fillfactor showing etc. However,
showcontig still shows the indexes as being badly fragmented. I've run an
update stats with fullscan and update usage which has corrected incorrect
space stats but that's all. I run this process every month and normally
everything works as expected. Any ideas why this time it didn't please?Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
--
Keith Kratochvil
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Thanks. I've already done that and it didn't.
"Keith Kratochvil" wrote:
> Perhaps DBCC UPDATEUSAGE will fix the incorrect stats.
> --
> Keith Kratochvil
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Hi Shirley
Can you show us the details from DBCC SHOWCONTIG?
Also, how many indexes are there? Are they all fragmented, clustered and
nonclustered?
How much free space is in the database?
--
HTH
Kalen Delaney, SQL Server MVP
"Shirley" <Shirley@.discussions.microsoft.com> wrote in message
news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> DBCC
> DBREINDEX which appeared to run fine - new fillfactor showing etc.
> However,
> showcontig still shows the indexes as being badly fragmented. I've run an
> update stats with fullscan and update usage which has corrected incorrect
> space stats but that's all. I run this process every month and normally
> everything works as expected. Any ideas why this time it didn't please?|||Hi Kalen, thanks for replying.
There is one clustered IX and 3 NC IX. These are the results
- Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
- Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
- Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
- Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
There is about 30 GB free space in the DB and it can autogrow.
"Kalen Delaney" wrote:
> Hi Shirley
> Can you show us the details from DBCC SHOWCONTIG?
> Also, how many indexes are there? Are they all fragmented, clustered and
> nonclustered?
> How much free space is in the database?
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > DBCC
> > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > However,
> > showcontig still shows the indexes as being badly fragmented. I've run an
> > update stats with fullscan and update usage which has corrected incorrect
> > space stats but that's all. I run this process every month and normally
> > everything works as expected. Any ideas why this time it didn't please?
>
>|||Problem fixed. I ran an UPDATE STATISTICS again but with the FULLSCAN option,
then re-ran the reorg and all looks fine now. Don't know why this just
started to happen but will do the updatestats before the reorg every time now.
"Shirley" wrote:
> Hi Kalen, thanks for replying.
> There is one clustered IX and 3 NC IX. These are the results
> - Scan Density [Best Count:Actual Count]......: 98.18% [292775:298197] CL
> - Scan Density [Best Count:Actual Count]......: 54.10% [22661:41886] NC
> - Scan Density [Best Count:Actual Count]......: 52.88% [30754:58162] NC
> - Scan Density [Best Count:Actual Count]......: 54.74% [27210:49709] NC
> There is about 30 GB free space in the DB and it can autogrow.
>
> "Kalen Delaney" wrote:
> > Hi Shirley
> >
> > Can you show us the details from DBCC SHOWCONTIG?
> > Also, how many indexes are there? Are they all fragmented, clustered and
> > nonclustered?
> > How much free space is in the database?
> >
> > --
> > HTH
> > Kalen Delaney, SQL Server MVP
> >
> >
> > "Shirley" <Shirley@.discussions.microsoft.com> wrote in message
> > news:B05EB837-51D3-40E1-B517-E758862A412A@.microsoft.com...
> > > Hi. I have a table that has 58 million rows and is about 24 GB. I ran a
> > > DBCC
> > > DBREINDEX which appeared to run fine - new fillfactor showing etc.
> > > However,
> > > showcontig still shows the indexes as being badly fragmented. I've run an
> > > update stats with fullscan and update usage which has corrected incorrect
> > > space stats but that's all. I run this process every month and normally
> > > everything works as expected. Any ideas why this time it didn't please?
> >
> >
> >

Friday, February 17, 2012

DBCC CHECKDB error resolved with dbreindex - safe?

We had a hard drive failure in one of our RAID arrays. We've resolved the
hardware issue; however, DBCC CHECKDB is showing errors in at least some of
our databases. The way we structure our databases is with two data files,
the second containing all of our table indexes. The RAID array that failed
contained these "index-only" data files.
Since DBCC CHECKDB reported that the minimum repair option would be
'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database from
our last full plus differential backups. It's approximately 45 databases,
and I was hoping to avoid this.
Since CHECKDB seems to be reporting an error on only a single table in each
database AND the hardware failure was on the array that contains only
indexes, I thought simply reindexing the table reporting an error might be
an option. So I took one of the databases showing an error and reindexed
the offending table with DBCC DBREINDEX. It completed successfully, so I
reran the DBCC CHECKDB and it reported no errors.
I realize restoring from backups might be the safest route to go, but if I
only reindex a table and the dbcc checkdb comes up clean, am I at risk for
any data loss?
I appreciate your insight on this situation.
Thanks
-joeIf the error was in the index then rebuilding the index will in most cases
fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
from scratch. So as long as there are no hardware issues to prevent a clean
rebuild you should be OK. The problem comes into play more when it is data
corruption than indexes.
--
Andrew J. Kelly SQL MVP
"Joe Ross" <joeross7@.hotmail.com> wrote in message
news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
> We had a hard drive failure in one of our RAID arrays. We've resolved the
> hardware issue; however, DBCC CHECKDB is showing errors in at least some
> of our databases. The way we structure our databases is with two data
> files, the second containing all of our table indexes. The RAID array
> that failed contained these "index-only" data files.
> Since DBCC CHECKDB reported that the minimum repair option would be
> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
> from our last full plus differential backups. It's approximately 45
> databases, and I was hoping to avoid this.
> Since CHECKDB seems to be reporting an error on only a single table in
> each database AND the hardware failure was on the array that contains only
> indexes, I thought simply reindexing the table reporting an error might be
> an option. So I took one of the databases showing an error and reindexed
> the offending table with DBCC DBREINDEX. It completed successfully, so I
> reran the DBCC CHECKDB and it reported no errors.
> I realize restoring from backups might be the safest route to go, but if I
> only reindex a table and the dbcc checkdb comes up clean, am I at risk for
> any data loss?
> I appreciate your insight on this situation.
> Thanks
> -joe
>|||Thank you. This is how I understood it, but it's reassuring to get
confirmation from an expert.
Much appreciated,
-joe
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ej%23s64F7FHA.2484@.TK2MSFTNGP10.phx.gbl...
> If the error was in the index then rebuilding the index will in most cases
> fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
> from scratch. So as long as there are no hardware issues to prevent a
> clean rebuild you should be OK. The problem comes into play more when it
> is data corruption than indexes.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe Ross" <joeross7@.hotmail.com> wrote in message
> news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
>> We had a hard drive failure in one of our RAID arrays. We've resolved
>> the hardware issue; however, DBCC CHECKDB is showing errors in at least
>> some of our databases. The way we structure our databases is with two
>> data files, the second containing all of our table indexes. The RAID
>> array that failed contained these "index-only" data files.
>> Since DBCC CHECKDB reported that the minimum repair option would be
>> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
>> from our last full plus differential backups. It's approximately 45
>> databases, and I was hoping to avoid this.
>> Since CHECKDB seems to be reporting an error on only a single table in
>> each database AND the hardware failure was on the array that contains
>> only indexes, I thought simply reindexing the table reporting an error
>> might be an option. So I took one of the databases showing an error and
>> reindexed the offending table with DBCC DBREINDEX. It completed
>> successfully, so I reran the DBCC CHECKDB and it reported no errors.
>> I realize restoring from backups might be the safest route to go, but if
>> I only reindex a table and the dbcc checkdb comes up clean, am I at risk
>> for any data loss?
>> I appreciate your insight on this situation.
>> Thanks
>> -joe
>|||Andrew, There's another SQL 2005 launch event December 19th in Waltham if
you'd care to post it on NESQL.
Please email me if you'd like more details.
--
burt_king@.yahoo.com
"Joe Ross" wrote:
> Thank you. This is how I understood it, but it's reassuring to get
> confirmation from an expert.
> Much appreciated,
> -joe
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ej%23s64F7FHA.2484@.TK2MSFTNGP10.phx.gbl...
> > If the error was in the index then rebuilding the index will in most cases
> > fix the problem. If you do a DBCC DBREINDEX you are rebuilding the index
> > from scratch. So as long as there are no hardware issues to prevent a
> > clean rebuild you should be OK. The problem comes into play more when it
> > is data corruption than indexes.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe Ross" <joeross7@.hotmail.com> wrote in message
> > news:O25cjrE7FHA.744@.TK2MSFTNGP10.phx.gbl...
> >> We had a hard drive failure in one of our RAID arrays. We've resolved
> >> the hardware issue; however, DBCC CHECKDB is showing errors in at least
> >> some of our databases. The way we structure our databases is with two
> >> data files, the second containing all of our table indexes. The RAID
> >> array that failed contained these "index-only" data files.
> >>
> >> Since DBCC CHECKDB reported that the minimum repair option would be
> >> 'REPAIR_ALLOW_DATA_LOSS', I thought I would be restoring each database
> >> from our last full plus differential backups. It's approximately 45
> >> databases, and I was hoping to avoid this.
> >>
> >> Since CHECKDB seems to be reporting an error on only a single table in
> >> each database AND the hardware failure was on the array that contains
> >> only indexes, I thought simply reindexing the table reporting an error
> >> might be an option. So I took one of the databases showing an error and
> >> reindexed the offending table with DBCC DBREINDEX. It completed
> >> successfully, so I reran the DBCC CHECKDB and it reported no errors.
> >>
> >> I realize restoring from backups might be the safest route to go, but if
> >> I only reindex a table and the dbcc checkdb comes up clean, am I at risk
> >> for any data loss?
> >>
> >> I appreciate your insight on this situation.
> >>
> >> Thanks
> >> -joe
> >>
> >
> >
>
>

Tuesday, March 27, 2012

DBCC Shrinkdatabase /shrinkfile not shrinking my database

DBCC Shrinkdatabase /shrinkfile not shrinking my database

DBCC Shrinkdatabase /shrinkfile not shrinking my database

Sunday, March 25, 2012

DBCC SHOWCONTIG incorrect?

DBCC SHOWCONTIG incorrect?

Friday, February 17, 2012

DBCC CHECKDB error resolved with dbreindex - safe?

DBCC CHECKDB error resolved with dbreindex - safe?

DBfile usage

Blog Archive

About Me