Hello,
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regards
Did you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>
|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Sunday, February 26, 2012
Free unused space in database
Hello,
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regardsDid you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regardsDid you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx
.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Free unused space in database
Hello,
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regardsDid you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
I need to free unused space in one database, but neither
the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
commands shows the desired results.
Inside my database i've two tables:
t1 - is the original table
t2 - was created with a select into...from t1
when i use the sp_spaceused in these tables:
t1 unused space = 1822968 KB
t2 unused space = 39992 KB
for me its obvious that when the table t2 was created the
unused space(physical space) its not allocated in the new
table.
The question is that i want to free unused space but cant
see results w/ dbcc shrink...
Best regardsDid you try
DBCC SHRINKDATABASE(DBName, 'TruncateOnly')
However, be very aware that if your database grew to this size once, it will
likely do so again... so don't expect this to be permanent disk space
relief.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||try to update statistics. Look in BOL.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps the sp_spaceused results are inaccurate? Try running DBCC
UPDATEUSAGE to get the latest stats.
Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Perhaps some index is occupying space - try reindexing (DBCC DBREINDEX).
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1a5d201c44e43$fd195e10$a101280a@.phx.gbl...
> Hello,
> I need to free unused space in one database, but neither
> the "dbcc shrinkdatabase" nor the "dbcc shrinkfile"
> commands shows the desired results.
> Inside my database i've two tables:
> t1 - is the original table
> t2 - was created with a select into...from t1
> when i use the sp_spaceused in these tables:
> t1 unused space = 1822968 KB
> t2 unused space = 39992 KB
> for me its obvious that when the table t2 was created the
> unused space(physical space) its not allocated in the new
> table.
> The question is that i want to free unused space but cant
> see results w/ dbcc shrink...
> Best regards
>|||Or do a showcontig to get more information on any kind of fragmentation.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Friday, February 24, 2012
Free and Used Allocated Space with Shrinkfile
SQL 7.0 NT 2000 server
Before doing anything:
Used: Free: Total
20GB 20GB 40GB
After
dbcc shrinkfile(1,notruncate)
followed by
dbcc shrinkfile(1,truncateonly)
the view from EM is:
Used: Free: Total
20GB 1.6MB 21GB
Within 24 hours
Used: Free: Total
20GB 12GB 32GB
The db and trans log are set to grow automatically by 10%
so how can the MDF file suddenly go to 32GB? Shouldn't
SQL expand the 1.6MB by 10% when needed, and then 10%
after that, etc?
There have been no deletions in the DB only insertions.
Thanks for your help.
DonThe growth is most likely due to a reindexing job (probably a maintenance
plan) at night. The growth is 10% of the entire file and not the 1.6MB
free. So the first growth would be 2GB and it just gets larger from there.
When you have a db over a few GB it is a good idea to change the growth
increment to a fixed amount and not a percentage so it always grows in a
controllable fashion. But never shrink an active database down to anywhere
near the size of the data. The db needs lots of free space to work
properly.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||In addition to Andrew's comments, please read:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||Yes, there was an indexing maintenance job that ran.
As you say the first growth would be 2GB, but wouldn't
SQL use that space and then increase it by 10%?
Don
>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>
>.
>|||If you went from 20 GB to 32 GB, then my guess is it had to autogrow
MULTIPLE times.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
> >--Original Message--
> >The growth is most likely due to a reindexing job
> (probably a maintenance
> >plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
> >free. So the first growth would be 2GB and it just gets
> larger from there.
> >When you have a db over a few GB it is a good idea to
> change the growth
> >increment to a fixed amount and not a percentage so it
> always grows in a
> >controllable fashion. But never shrink an active
> database down to anywhere
> >near the size of the data. The db needs lots of free
> space to work
> >properly.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> >> SQL 7.0 NT 2000 server
> >>
> >> Before doing anything:
> >>
> >> Used: Free: Total
> >> 20GB 20GB 40GB
> >>
> >> After
> >> dbcc shrinkfile(1,notruncate)
> >> followed by
> >> dbcc shrinkfile(1,truncateonly)
> >> the view from EM is:
> >>
> >> Used: Free: Total
> >> 20GB 1.6MB 21GB
> >>
> >> Within 24 hours
> >>
> >> Used: Free: Total
> >> 20GB 12GB 32GB
> >>
> >> The db and trans log are set to grow automatically by
> 10%
> >> so how can the MDF file suddenly go to 32GB? Shouldn't
> >> SQL expand the 1.6MB by 10% when needed, and then 10%
> >> after that, etc?
> >> There have been no deletions in the DB only insertions.
> >>
> >> Thanks for your help.
> >>
> >> Don
> >>
> >>
> >
> >
> >.
> >|||Yes as Aaron stated it had to have grown multiple times. Since a DBREINDEX
needs 1.2 (or more) times the size of the index in free space to rebuild it
this is not uncommon. If your db has 20 GB of actual data and indexes I see
no problem with have 10 or 20GB of free space in the data file. There is no
penalty for too much free space but a big one for too little.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
>>--Original Message--
>>The growth is most likely due to a reindexing job
> (probably a maintenance
>>plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
>>free. So the first growth would be 2GB and it just gets
> larger from there.
>>When you have a db over a few GB it is a good idea to
> change the growth
>>increment to a fixed amount and not a percentage so it
> always grows in a
>>controllable fashion. But never shrink an active
> database down to anywhere
>>near the size of the data. The db needs lots of free
> space to work
>>properly.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
> 10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>>
>>.|||I seem to be missing the concept...
if I shrink a db down to the size of the data, and SQL
needs lots of free space to work with, won't SQL either
expand by a fixed amount or by percentage as needed?
Don
>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>
>.
>|||But, that's my question...
why would it expand MULTIPLE times? It's only supposed to
expand as needed...unless I'm missing something entirely.
>--Original Message--
>If you went from 20 GB to 32 GB, then my guess is it had
to autogrow
>MULTIPLE times.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:214101c5023c$620efd50$a401280a@.phx.gbl...
>> Yes, there was an indexing maintenance job that ran.
>> As you say the first growth would be 2GB, but wouldn't
>> SQL use that space and then increase it by 10%?
>> Don
>> >--Original Message--
>> >The growth is most likely due to a reindexing job
>> (probably a maintenance
>> >plan) at night. The growth is 10% of the entire file
>> and not the 1.6MB
>> >free. So the first growth would be 2GB and it just
gets
>> larger from there.
>> >When you have a db over a few GB it is a good idea to
>> change the growth
>> >increment to a fixed amount and not a percentage so it
>> always grows in a
>> >controllable fashion. But never shrink an active
>> database down to anywhere
>> >near the size of the data. The db needs lots of free
>> space to work
>> >properly.
>> >
>> >--
>> >Andrew J. Kelly SQL MVP
>> >
>> >
>> >"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> >> SQL 7.0 NT 2000 server
>> >>
>> >> Before doing anything:
>> >>
>> >> Used: Free: Total
>> >> 20GB 20GB 40GB
>> >>
>> >> After
>> >> dbcc shrinkfile(1,notruncate)
>> >> followed by
>> >> dbcc shrinkfile(1,truncateonly)
>> >> the view from EM is:
>> >>
>> >> Used: Free: Total
>> >> 20GB 1.6MB 21GB
>> >>
>> >> Within 24 hours
>> >>
>> >> Used: Free: Total
>> >> 20GB 12GB 32GB
>> >>
>> >> The db and trans log are set to grow automatically
by
>> 10%
>> >> so how can the MDF file suddenly go to 32GB?
Shouldn't
>> >> SQL expand the 1.6MB by 10% when needed, and then
10%
>> >> after that, etc?
>> >> There have been no deletions in the DB only
insertions.
>> >>
>> >> Thanks for your help.
>> >>
>> >> Don
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||> why would it expand MULTIPLE times? It's only supposed to
> expand as needed...unless I'm missing something entirely.
Who knows what your maintenance plan did, and what other activity is going
on in the box?
As for how many times it will expand, if you set it to 10%, and it needs a
GB, then another GB, then another GB, then another GB, it is going to grow
multiple times. Since we have ABSOLUTELY no idea what was going on in your
system, I think it's impossible for us to tell you how many times the file
needed to grow and why.
A|||Seems to me that the LOG file is supposed to be used for
these types of things.
>--Original Message--
>> why would it expand MULTIPLE times? It's only
supposed to
>> expand as needed...unless I'm missing something
entirely.
>Who knows what your maintenance plan did, and what other
activity is going
>on in the box?
>As for how many times it will expand, if you set it to
10%, and it needs a
>GB, then another GB, then another GB, then another GB,
it is going to grow
>multiple times. Since we have ABSOLUTELY no idea what
was going on in your
>system, I think it's impossible for us to tell you how
many times the file
>needed to grow and why.
>A
>
>.
>|||> Seems to me that the LOG file is supposed to be used for
> these types of things.
Seems you should get a better grasp on what you are doing in your
maintenance plan. For example, if you reindex a table, it needs to allocate
data to other areas within the DATA file. Why would you think this should
happen exclusively in the LOG file?
If you don't have the ability or motivation to understand why you need more
disk space in this case, buy a bigger drive and stop shrinking your database
for no reason (and without understanding the causes/consequences).|||What a jerk...
>--Original Message--
>> Seems to me that the LOG file is supposed to be used
for
>> these types of things.
>Seems you should get a better grasp on what you are
doing in your
>maintenance plan. For example, if you reindex a table,
it needs to allocate
>data to other areas within the DATA file. Why would you
think this should
>happen exclusively in the LOG file?
>If you don't have the ability or motivation to
understand why you need more
>disk space in this case, buy a bigger drive and stop
shrinking your database
>for no reason (and without understanding the
causes/consequences).
>
>.
>|||This is the kind of immature crap that makes me wonder why I bother to help
anyone at all.
"Oh geez, you're right, but I don't like your tone. So instead of thanking
you and taking your advice, I'm going to call you names!"
*plonk*
> What a jerk...|||Yes Don it will but you obviously needed more space than one growth block
gave you. Once it used up that amount it needed to grow again. In your
case it must have grown several times. You can trace the Autogrow event if
you want to see when it grows and how many times. This is very common with
database maintenance activities.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:21b901c50243$66ce07d0$a401280a@.phx.gbl...
>I seem to be missing the concept...
> if I shrink a db down to the size of the data, and SQL
> needs lots of free space to work with, won't SQL either
> expand by a fixed amount or by percentage as needed?
> Don
>>--Original Message--
>>The growth is most likely due to a reindexing job
> (probably a maintenance
>>plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
>>free. So the first growth would be 2GB and it just gets
> larger from there.
>>When you have a db over a few GB it is a good idea to
> change the growth
>>increment to a fixed amount and not a percentage so it
> always grows in a
>>controllable fashion. But never shrink an active
> database down to anywhere
>>near the size of the data. The db needs lots of free
> space to work
>>properly.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
> 10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>>
>>.
Before doing anything:
Used: Free: Total
20GB 20GB 40GB
After
dbcc shrinkfile(1,notruncate)
followed by
dbcc shrinkfile(1,truncateonly)
the view from EM is:
Used: Free: Total
20GB 1.6MB 21GB
Within 24 hours
Used: Free: Total
20GB 12GB 32GB
The db and trans log are set to grow automatically by 10%
so how can the MDF file suddenly go to 32GB? Shouldn't
SQL expand the 1.6MB by 10% when needed, and then 10%
after that, etc?
There have been no deletions in the DB only insertions.
Thanks for your help.
DonThe growth is most likely due to a reindexing job (probably a maintenance
plan) at night. The growth is 10% of the entire file and not the 1.6MB
free. So the first growth would be 2GB and it just gets larger from there.
When you have a db over a few GB it is a good idea to change the growth
increment to a fixed amount and not a percentage so it always grows in a
controllable fashion. But never shrink an active database down to anywhere
near the size of the data. The db needs lots of free space to work
properly.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||In addition to Andrew's comments, please read:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||Yes, there was an indexing maintenance job that ran.
As you say the first growth would be 2GB, but wouldn't
SQL use that space and then increase it by 10%?
Don
>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>
>.
>|||If you went from 20 GB to 32 GB, then my guess is it had to autogrow
MULTIPLE times.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
> >--Original Message--
> >The growth is most likely due to a reindexing job
> (probably a maintenance
> >plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
> >free. So the first growth would be 2GB and it just gets
> larger from there.
> >When you have a db over a few GB it is a good idea to
> change the growth
> >increment to a fixed amount and not a percentage so it
> always grows in a
> >controllable fashion. But never shrink an active
> database down to anywhere
> >near the size of the data. The db needs lots of free
> space to work
> >properly.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> >> SQL 7.0 NT 2000 server
> >>
> >> Before doing anything:
> >>
> >> Used: Free: Total
> >> 20GB 20GB 40GB
> >>
> >> After
> >> dbcc shrinkfile(1,notruncate)
> >> followed by
> >> dbcc shrinkfile(1,truncateonly)
> >> the view from EM is:
> >>
> >> Used: Free: Total
> >> 20GB 1.6MB 21GB
> >>
> >> Within 24 hours
> >>
> >> Used: Free: Total
> >> 20GB 12GB 32GB
> >>
> >> The db and trans log are set to grow automatically by
> 10%
> >> so how can the MDF file suddenly go to 32GB? Shouldn't
> >> SQL expand the 1.6MB by 10% when needed, and then 10%
> >> after that, etc?
> >> There have been no deletions in the DB only insertions.
> >>
> >> Thanks for your help.
> >>
> >> Don
> >>
> >>
> >
> >
> >.
> >|||Yes as Aaron stated it had to have grown multiple times. Since a DBREINDEX
needs 1.2 (or more) times the size of the index in free space to rebuild it
this is not uncommon. If your db has 20 GB of actual data and indexes I see
no problem with have 10 or 20GB of free space in the data file. There is no
penalty for too much free space but a big one for too little.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
>>--Original Message--
>>The growth is most likely due to a reindexing job
> (probably a maintenance
>>plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
>>free. So the first growth would be 2GB and it just gets
> larger from there.
>>When you have a db over a few GB it is a good idea to
> change the growth
>>increment to a fixed amount and not a percentage so it
> always grows in a
>>controllable fashion. But never shrink an active
> database down to anywhere
>>near the size of the data. The db needs lots of free
> space to work
>>properly.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
> 10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>>
>>.|||I seem to be missing the concept...
if I shrink a db down to the size of the data, and SQL
needs lots of free space to work with, won't SQL either
expand by a fixed amount or by percentage as needed?
Don
>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>
>.
>|||But, that's my question...
why would it expand MULTIPLE times? It's only supposed to
expand as needed...unless I'm missing something entirely.
>--Original Message--
>If you went from 20 GB to 32 GB, then my guess is it had
to autogrow
>MULTIPLE times.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:214101c5023c$620efd50$a401280a@.phx.gbl...
>> Yes, there was an indexing maintenance job that ran.
>> As you say the first growth would be 2GB, but wouldn't
>> SQL use that space and then increase it by 10%?
>> Don
>> >--Original Message--
>> >The growth is most likely due to a reindexing job
>> (probably a maintenance
>> >plan) at night. The growth is 10% of the entire file
>> and not the 1.6MB
>> >free. So the first growth would be 2GB and it just
gets
>> larger from there.
>> >When you have a db over a few GB it is a good idea to
>> change the growth
>> >increment to a fixed amount and not a percentage so it
>> always grows in a
>> >controllable fashion. But never shrink an active
>> database down to anywhere
>> >near the size of the data. The db needs lots of free
>> space to work
>> >properly.
>> >
>> >--
>> >Andrew J. Kelly SQL MVP
>> >
>> >
>> >"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> >> SQL 7.0 NT 2000 server
>> >>
>> >> Before doing anything:
>> >>
>> >> Used: Free: Total
>> >> 20GB 20GB 40GB
>> >>
>> >> After
>> >> dbcc shrinkfile(1,notruncate)
>> >> followed by
>> >> dbcc shrinkfile(1,truncateonly)
>> >> the view from EM is:
>> >>
>> >> Used: Free: Total
>> >> 20GB 1.6MB 21GB
>> >>
>> >> Within 24 hours
>> >>
>> >> Used: Free: Total
>> >> 20GB 12GB 32GB
>> >>
>> >> The db and trans log are set to grow automatically
by
>> 10%
>> >> so how can the MDF file suddenly go to 32GB?
Shouldn't
>> >> SQL expand the 1.6MB by 10% when needed, and then
10%
>> >> after that, etc?
>> >> There have been no deletions in the DB only
insertions.
>> >>
>> >> Thanks for your help.
>> >>
>> >> Don
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||> why would it expand MULTIPLE times? It's only supposed to
> expand as needed...unless I'm missing something entirely.
Who knows what your maintenance plan did, and what other activity is going
on in the box?
As for how many times it will expand, if you set it to 10%, and it needs a
GB, then another GB, then another GB, then another GB, it is going to grow
multiple times. Since we have ABSOLUTELY no idea what was going on in your
system, I think it's impossible for us to tell you how many times the file
needed to grow and why.
A|||Seems to me that the LOG file is supposed to be used for
these types of things.
>--Original Message--
>> why would it expand MULTIPLE times? It's only
supposed to
>> expand as needed...unless I'm missing something
entirely.
>Who knows what your maintenance plan did, and what other
activity is going
>on in the box?
>As for how many times it will expand, if you set it to
10%, and it needs a
>GB, then another GB, then another GB, then another GB,
it is going to grow
>multiple times. Since we have ABSOLUTELY no idea what
was going on in your
>system, I think it's impossible for us to tell you how
many times the file
>needed to grow and why.
>A
>
>.
>|||> Seems to me that the LOG file is supposed to be used for
> these types of things.
Seems you should get a better grasp on what you are doing in your
maintenance plan. For example, if you reindex a table, it needs to allocate
data to other areas within the DATA file. Why would you think this should
happen exclusively in the LOG file?
If you don't have the ability or motivation to understand why you need more
disk space in this case, buy a bigger drive and stop shrinking your database
for no reason (and without understanding the causes/consequences).|||What a jerk...
>--Original Message--
>> Seems to me that the LOG file is supposed to be used
for
>> these types of things.
>Seems you should get a better grasp on what you are
doing in your
>maintenance plan. For example, if you reindex a table,
it needs to allocate
>data to other areas within the DATA file. Why would you
think this should
>happen exclusively in the LOG file?
>If you don't have the ability or motivation to
understand why you need more
>disk space in this case, buy a bigger drive and stop
shrinking your database
>for no reason (and without understanding the
causes/consequences).
>
>.
>|||This is the kind of immature crap that makes me wonder why I bother to help
anyone at all.
"Oh geez, you're right, but I don't like your tone. So instead of thanking
you and taking your advice, I'm going to call you names!"
*plonk*
> What a jerk...|||Yes Don it will but you obviously needed more space than one growth block
gave you. Once it used up that amount it needed to grow again. In your
case it must have grown several times. You can trace the Autogrow event if
you want to see when it grows and how many times. This is very common with
database maintenance activities.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:21b901c50243$66ce07d0$a401280a@.phx.gbl...
>I seem to be missing the concept...
> if I shrink a db down to the size of the data, and SQL
> needs lots of free space to work with, won't SQL either
> expand by a fixed amount or by percentage as needed?
> Don
>>--Original Message--
>>The growth is most likely due to a reindexing job
> (probably a maintenance
>>plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
>>free. So the first growth would be 2GB and it just gets
> larger from there.
>>When you have a db over a few GB it is a good idea to
> change the growth
>>increment to a fixed amount and not a percentage so it
> always grows in a
>>controllable fashion. But never shrink an active
> database down to anywhere
>>near the size of the data. The db needs lots of free
> space to work
>>properly.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
> 10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>>
>>.
Sunday, February 19, 2012
Fragmented System Tables
I have found that a bunch of our tables were highly fragemented. (in some cases as much as 80%)
I used DBCC dBReindex to correct all the user tables, but how do I defrag the system table indexes?
Thanks--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)
-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'
-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'|||Originally posted by ispaleny
--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)
-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'
-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'
Thank you very much,
2 additional related questions
1) Can this be set to run weekly via a scheduled Job?
2) Can this be done with users still connected
(we have users connected 24/7 via internet)
Thanks again.|||Q1) Can this be set to run weekly via a scheduled Job?
A1) Yes. But you must add a script for disconnecting users.
Q2) Can this be done with users still connected
A2) No. The database must be in a single user mode.
I must be a funny job to maintain a living database :)
Let me know how you solved it.
Good luck !|||If you have set up your web site to have one place where the connections are made AND if the database is not THAT huge, you could replicate it, run the corrections, point the connections to the "new" database, replace the old one and go on.
That should be something that could certainly be automated. Might be more work than its worth tho...
I used DBCC dBReindex to correct all the user tables, but how do I defrag the system table indexes?
Thanks--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)
-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'
-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'|||Originally posted by ispaleny
--all except clustered indexes on sysobjects and sysindexes
dbcc dbrepair(@.dbname, repairindex, @.tabname, @.indid)
-- And an example
exec sp_dboption 'YourDatabase','single user','true'
dbcc dbrepair('YourDatabase', repairindex, 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'
-- And better encapsulated in undocumented MS SP
exec sp_dboption 'YourDatabase','single user','true'
exec sp_fixindex 'YourDatabase', 'syscolumns', 1)
exec sp_dboption 'YourDatabase','single user','false'
Thank you very much,
2 additional related questions
1) Can this be set to run weekly via a scheduled Job?
2) Can this be done with users still connected
(we have users connected 24/7 via internet)
Thanks again.|||Q1) Can this be set to run weekly via a scheduled Job?
A1) Yes. But you must add a script for disconnecting users.
Q2) Can this be done with users still connected
A2) No. The database must be in a single user mode.
I must be a funny job to maintain a living database :)
Let me know how you solved it.
Good luck !|||If you have set up your web site to have one place where the connections are made AND if the database is not THAT huge, you could replicate it, run the corrections, point the connections to the "new" database, replace the old one and go on.
That should be something that could certainly be automated. Might be more work than its worth tho...
Fragmentation of Table Differs Between 2000 and 2005 Client on 200
I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
=============
VB .NET Developer
http://www.rocketscience.uk.com
Exactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
=============
VB .NET Developer
http://www.rocketscience.uk.com
Exactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
Fragmentation of Table Differs Between 2000 and 2005 Client on 200
I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
=============
VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in mes
sage
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So whi
ch
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
=============
VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in mes
sage
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So whi
ch
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com
Fragmentation of Table Differs Between 2000 and 2005 Client on 200
I'm examining the logical fragmentation for a table running on SQL Server
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
============= VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com
2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
one should I believe?
--
============= VB .NET Developer
http://www.rocketscience.uk.comExactly which fragmentation figure are you referring to?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in message
news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com|||The algorithm for calculating fragmentation is different in SQL Server 2005.
The new algorithm is more precise, so the fragmentation values appear higher
in SQL Server 2005 than in SQL Server 2000. For example, in SQL Server 2000,
if a table has page 11 and page 13 in the same extent, it isn't counted as
fragmented. However these two pages will cause two physical IOs , so in SQL
Server 2005, this is counted as fragmentation.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:BABBC08C-B9B4-487F-BC56-5CDFF5F9D1AB@.microsoft.com...
> I'm examining the logical fragmentation for a table running on SQL Server
> 2000. If i run dbcc showcontig the fragmentation appears as 0.21% however
> if
> i examine the same index in SQL Management Studio 2005 it's 20.64%. So
> which
> one should I believe?
> --
> =============> VB .NET Developer
> http://www.rocketscience.uk.com
Subscribe to:
Posts (Atom)