I have an issue, and hopefully I can explain this fully enough. We
have a 3 server environment, all running SQL Server 2000 SP4 on
Windows 2000 Server. Server A, Server B, and Server C. Server A
replicates to Server B, Server B then replicates to Server C.
Server B has a database that two data files and two log files on two
separate drives. So drive D has 1 datafile and 1 log file, and drive
E has the same. Drive E is running out of disk space and drive D had
some to spare. In Enterprise Manager, I went to the Shrink Database
menu for that database, and I choosed 'Empty the file (data will
migrate to other files in the file group) option. That finished
successfully. But now I see that only 2 mb is being used, but the
database size is still 22358 mb. I have tried the other shrink
options with no luck.
So I have tried:
-Compress Pages and then truncate free space from the file
-Truncate free space from the other end of file
-Shrink file to "min size"
-I have also tried checking the 'Move pages to beginning of file
before shrinking, then retrying the steps above.
Also, we have tried shrinking the transaction table using a stored
proceedure called sp_forceshrink_log as well as a few other things we
have seen online.
After all this, the file size is still 20+ gb. My question is this: Is
there anything else we can try to free up the close to 20 gigs of
space? Also, this database is in the middle of a replicated chain,
what effect would there be in deleting the empty file in the
properties of the database. Is that even possible with replication
enabled?
Thanks for any help you can provide.
JonIf you want to shrink a log file, you should do it immediately after
you backup the transaction log. If you do it later, you will get a
warning like "Cannot shrink log file 2 (LogFile) because all logical
log files are in use." and the file will not be shrinked.
If you get a warning saying "The log was not truncated because records
at the beginning of the log are pending replication. Ensure the Log
Reader Agent is running or use sp_repldone to mark transactions as
distributed", you should see this thread, for example:
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
read/23ca52b9df791e59/
In any case, you should not delete the transaction log file (especially
if the database is in a transactional replication chain).
--
Razvan Socol
SQL Server MVP|||Thanks for your response, but the issue is not with the log file, but
the data file. I emptied the contents of one data file to another
data file on another drive. So I have 2 mb used in the file, but the
file size is 20+ gb on the physical drive. I am unable to shrink the
file size using the options found in the "Shrink Database" window. So
I am looking for alternatives to getting the data file size down.
Jon
On Feb 3, 2:42 am, "Razvan Socol" <rso...@.gmail.com> wrote:
> If you want to shrink a log file, you should do it immediately after
> you backup the transaction log. If you do it later, you will get a
> warning like "Cannot shrink log file 2 (LogFile) because all logical
> log files are in use." and the file will not be shrinked.
> If you get a warning saying "The log was not truncated because records
> at the beginning of the log are pending replication. Ensure the Log
> Reader Agent is running or use sp_repldone to mark transactions as
> distributed", you should see this thread, for example:http://groups.google.com/group/comp.databases.ms-sqlserver/browse_frm/th
> read/23ca52b9df791e59/
> In any case, you should not delete the transaction log file (especially
> if the database is in a transactional replication chain).
> --
> Razvan Socol
> SQL Server MVP
Showing posts with label shrink. Show all posts
Showing posts with label shrink. Show all posts
Thursday, March 29, 2012
Data File shrink
I have a data file in which there is a table containing 5,000,000 rows. I
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize 8591936
minimumsize 128
usedpages 8206784
estimatedpages 8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
--
Message posted via http://www.sqlmonster.comThe space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||When you say truncate, is that the same as:
delete [tablename]?
--
Message posted via http://www.sqlmonster.com|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQLMonster.com...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.sqlmonster.com|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
--
Message posted via http://www.sqlmonster.com|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
--
Message posted via http://www.sqlmonster.com|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQLMonster.com...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.sqlmonster.com|||This is an archive table, that has essentially served its purpose. We are
no longer archiving to it, but instead only deletes will be performed
against it in the future. No further growth anticipated, just the opposite,
a gradual reduction in size.
If I remove the indexes before performing the import, and given that I
allow enough space in the shrunken data file to recreate the indexes, does
this process of recreating the indexes after the import (having a clustered
primary key identity index) cause any fragmentation issues, versus leaving
the indexes in place during the import?
--
Message posted via http://www.sqlmonster.com|||You will need at least 1.2 times the size of the table (in the case of the
clustered index) and for each non-clustered index in free space for this.
If you only have a small amount above that free the chances are the indexes
will not be contiguous in the file but will still be built. The more free
space you have the better chance that the indexes can be placed in a
contiguous area in the file when rebuilt. A little bit of fragmentation
won't hurt.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fb453ef7c78943ce9b5af4e75cfb224e@.SQLMonster.com...
> This is an archive table, that has essentially served its purpose. We are
> no longer archiving to it, but instead only deletes will be performed
> against it in the future. No further growth anticipated, just the
> opposite,
> a gradual reduction in size.
> If I remove the indexes before performing the import, and given that I
> allow enough space in the shrunken data file to recreate the indexes, does
> this process of recreating the indexes after the import (having a
> clustered
> primary key identity index) cause any fragmentation issues, versus leaving
> the indexes in place during the import?
> --
> Message posted via http://www.sqlmonster.comsql
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize 8591936
minimumsize 128
usedpages 8206784
estimatedpages 8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
--
Message posted via http://www.sqlmonster.comThe space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||When you say truncate, is that the same as:
delete [tablename]?
--
Message posted via http://www.sqlmonster.com|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQLMonster.com...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.sqlmonster.com|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
--
Message posted via http://www.sqlmonster.com|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
--
Message posted via http://www.sqlmonster.com|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQLMonster.com...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.sqlmonster.com|||This is an archive table, that has essentially served its purpose. We are
no longer archiving to it, but instead only deletes will be performed
against it in the future. No further growth anticipated, just the opposite,
a gradual reduction in size.
If I remove the indexes before performing the import, and given that I
allow enough space in the shrunken data file to recreate the indexes, does
this process of recreating the indexes after the import (having a clustered
primary key identity index) cause any fragmentation issues, versus leaving
the indexes in place during the import?
--
Message posted via http://www.sqlmonster.com|||You will need at least 1.2 times the size of the table (in the case of the
clustered index) and for each non-clustered index in free space for this.
If you only have a small amount above that free the chances are the indexes
will not be contiguous in the file but will still be built. The more free
space you have the better chance that the indexes can be placed in a
contiguous area in the file when rebuilt. A little bit of fragmentation
won't hurt.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fb453ef7c78943ce9b5af4e75cfb224e@.SQLMonster.com...
> This is an archive table, that has essentially served its purpose. We are
> no longer archiving to it, but instead only deletes will be performed
> against it in the future. No further growth anticipated, just the
> opposite,
> a gradual reduction in size.
> If I remove the indexes before performing the import, and given that I
> allow enough space in the shrunken data file to recreate the indexes, does
> this process of recreating the indexes after the import (having a
> clustered
> primary key identity index) cause any fragmentation issues, versus leaving
> the indexes in place during the import?
> --
> Message posted via http://www.sqlmonster.comsql
Data File shrink
I have a data file in which there is a table containing 5,000,000 rows. I
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize8591936
minimumsize128
usedpages8206784
estimatedpages8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
Message posted via http://www.sqlmonster.com
The space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.c om...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com
|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.c om...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com
|||When you say truncate, is that the same as:
delete [tablename]?
Message posted via http://www.sqlmonster.com
|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQLMonster.c om...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.sqlmonster.com
|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
Message posted via http://www.sqlmonster.com
|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.c om...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com
|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.c om...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com
|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
Message posted via http://www.sqlmonster.com
|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQLMonster.c om...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.sqlmonster.com
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize8591936
minimumsize128
usedpages8206784
estimatedpages8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
Message posted via http://www.sqlmonster.com
The space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.c om...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com
|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.c om...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com
|||When you say truncate, is that the same as:
delete [tablename]?
Message posted via http://www.sqlmonster.com
|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQLMonster.c om...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.sqlmonster.com
|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
Message posted via http://www.sqlmonster.com
|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.c om...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com
|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.c om...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com
|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
Message posted via http://www.sqlmonster.com
|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQLMonster.c om...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.sqlmonster.com
Data File shrink
I have a data file in which there is a table containing 5,000,000 rows. I
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize 8591936
minimumsize 128
usedpages 8206784
estimatedpages 8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
Message posted via http://www.droptable.comThe space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQ
droptable.com...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.droptable.com|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQ
droptable.com...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.droptable.com|||When you say truncate, is that the same as:
delete [tablename]?
Message posted via http://www.droptable.com|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQ
droptable.com...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.droptable.com|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
Message posted via http://www.droptable.com|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQ
droptable.com...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.droptable.com|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQ
droptable.com...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.droptable.com|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
Message posted via http://www.droptable.com|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQ
droptable.com...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.droptable.com
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize 8591936
minimumsize 128
usedpages 8206784
estimatedpages 8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
Message posted via http://www.droptable.comThe space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQ
droptable.com...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.droptable.com|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQ
droptable.com...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.droptable.com|||When you say truncate, is that the same as:
delete [tablename]?
Message posted via http://www.droptable.com|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQ
droptable.com...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.droptable.com|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
Message posted via http://www.droptable.com|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQ
droptable.com...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.droptable.com|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQ
droptable.com...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.droptable.com|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
Message posted via http://www.droptable.com|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQ
droptable.com...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.droptable.com
Tuesday, March 27, 2012
Data File Does Not Shrink! (SQL2K)
Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> > have you tried shrinking the files individually? To do this go to
> > shrink database, then click on the files... button and select the data
> > file you want to shrink.
> >
> > Leila wrote:
> >> Hi,
> >> I have a database that its data file is almost 15GB. When I go to shrink
> >> file page (EM), the page displays that the minimum file size can be 8GB
> >> but
> >> after shrink, the size doesn't change at all! I tried both EM and DBCC
> >> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> >> might help if I say that the complete backup is about 9GB, but after
> >> restore
> >> it's 15GB.
> >> Any help would be greatly appreciated.
> >> Leila
> >|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Condeça
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condeça" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condeça
> "Leila" wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but after shrink, the size doesn't change at all! I tried both EM and
>> DBCC SHRINKFILE and the result is the same(the process takes 10 minutes)!
>> It might help if I say that the complete backup is about 9GB, but after
>> restore it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>sql
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> > have you tried shrinking the files individually? To do this go to
> > shrink database, then click on the files... button and select the data
> > file you want to shrink.
> >
> > Leila wrote:
> >> Hi,
> >> I have a database that its data file is almost 15GB. When I go to shrink
> >> file page (EM), the page displays that the minimum file size can be 8GB
> >> but
> >> after shrink, the size doesn't change at all! I tried both EM and DBCC
> >> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> >> might help if I say that the complete backup is about 9GB, but after
> >> restore
> >> it's 15GB.
> >> Any help would be greatly appreciated.
> >> Leila
> >|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Condeça
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condeça" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condeça
> "Leila" wrote:
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but
>> after shrink, the size doesn't change at all! I tried both EM and DBCC
>> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
>> might help if I say that the complete backup is about 9GB, but after
>> restore
>> it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I have a database that its data file is almost 15GB. When I go to shrink
>> file page (EM), the page displays that the minimum file size can be 8GB
>> but after shrink, the size doesn't change at all! I tried both EM and
>> DBCC SHRINKFILE and the result is the same(the process takes 10 minutes)!
>> It might help if I say that the complete backup is about 9GB, but after
>> restore it's 15GB.
>> Any help would be greatly appreciated.
>> Leila
>sql
Data File Does Not Shrink! (SQL2K)
Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leila
have you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>
|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leila
have you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>
|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegr oups.com...
|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB but
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after restore
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>
|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>
Data File Does Not Shrink! (SQL2K)
Hi,
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log
.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>
I have a database that its data file is almost 15GB. When I go to shrink
file page (EM), the page displays that the minimum file size can be 8GB but
after shrink, the size doesn't change at all! I tried both EM and DBCC
SHRINKFILE and the result is the same(the process takes 10 minutes)! It
might help if I say that the complete backup is about 9GB, but after restore
it's 15GB.
Any help would be greatly appreciated.
Leilahave you tried shrinking the files individually? To do this go to
shrink database, then click on the files... button and select the data
file you want to shrink.
Leila wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila|||Thanks,
I explained it exactly in my previous post!
"Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...
> have you tried shrinking the files individually? To do this go to
> shrink database, then click on the files... button and select the data
> file you want to shrink.
> Leila wrote:
>|||you were less exact with the line "any help would be greatly
appreciated"
anyway, I was just trying to check the basics, people often confuse the
shrink database option and shrink file option.
things you could try (I don't know if they make a difference, but I
tend to clutch at straws when this happens to me and it usually works
in the end) would be clearing all connections to your db (not always an
option), checking to see what locks are held, if it's an option you
could even try detaching and re-attaching.
I believe the backup will restore with the file sizes it originally was
backed up with, so if you are restoring from a backup that had a lot of
free space when backed up, it will claim that space again upon restore.
Leila wrote:[vbcol=seagreen]
> Thanks,
> I explained it exactly in my previous post!
>
> "Someone Else" <someone_else_of_course@.hotmail.co.uk> wrote in message
> news:1163509708.111473.245080@.b28g2000cwb.googlegroups.com...|||Try:
dbcc shrinkfile(1,9000)
... explicitly specify the size (in MB)
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila,
The situation you are reporting, is relacionated with the DB Transaction Log
.
If you are not doing any log shipping, or transaction log backups, run the
Following:
Backup Log DatabaseName with truncate_only
This will truncate the inactive part of the Trn Log.
Best Regards,
Paulo Conde?a
"Leila" wrote:
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB bu
t
> after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after resto
re
> it's 15GB.
> Any help would be greatly appreciated.
> Leila
>
>|||Leila
Let's start with classic question , why would you want to shrink the file at
all?
Have you read this article?
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
DBCC SHRINKFILE does not shrink a file past the size needed to store the
data. If you have 50 percent of the pagaes in a 10 MB data file are used ,a
DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only
5GB/MB not 6GB/MB
"Leila" <Leilas@.hotpop.com> wrote in message
news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that its data file is almost 15GB. When I go to shrink
> file page (EM), the page displays that the minimum file size can be 8GB
> but after shrink, the size doesn't change at all! I tried both EM and DBCC
> SHRINKFILE and the result is the same(the process takes 10 minutes)! It
> might help if I say that the complete backup is about 9GB, but after
> restore it's 15GB.
> Any help would be greatly appreciated.
> Leila
>|||Thanks,
I exactly tried it, but no change occured!
"F_clef" <Fclef@.discussions.microsoft.com> wrote in message
news:A2C58C00-D254-496F-ABFC-B1C7DB76EBDF@.microsoft.com...[vbcol=seagreen]
> Try:
> dbcc shrinkfile(1,9000)
> ... explicitly specify the size (in MB)
> "Leila" wrote:
>|||Thanks,
The log file almost always is 30MB! (Simple recovery model is being used)
"Paulo Condea" <PauloCondea@.discussions.microsoft.com> wrote in message
news:B2BF958E-CE71-4DD6-8570-DA5EB3EF47F0@.microsoft.com...[vbcol=seagreen]
> Leila,
> The situation you are reporting, is relacionated with the DB Transaction
> Log.
> If you are not doing any log shipping, or transaction log backups, run the
> Following:
> Backup Log DatabaseName with truncate_only
> This will truncate the inactive part of the Trn Log.
> Best Regards,
> Paulo Condea
> "Leila" wrote:
>|||Thanks Uri,
Actually there was a big table in db (about 5GB). At the end of year, the
data of this table was moved to other db (kind of archive). The admin
dropped and recreated this table in first db. After some time, this table
became large again. sp_SpaceUsed shows that this table has 3GB reserved
space, 500MB is data and almost 2.5GB is unused! I'm curious to free this
2.5GB space.
I must mention that DBCC CHECKDB returns error for 4 pages near eachother.
But the ObjectID the it shows does not exists in sysobjects and Object_Name
function returns NULL for it!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:edCoPQ$BHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Leila
> Let's start with classic question , why would you want to shrink the file
> at all?
> Have you read this article?
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
> DBCC SHRINKFILE does not shrink a file past the size needed to store the
> data. If you have 50 percent of the pagaes in a 10 MB data file are used
> ,a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to
> only 5GB/MB not 6GB/MB
>
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uYimey%23BHHA.2328@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 7, 2012
Data Base Shrink
I have SQL Server 2000 on WIN2k Advanced Server And I have
a Database Consists of:-
1-Single Database File .
2-single transaction log file(initial size 2 MB).
I have Observed that the transaction log file'Capacity
reaches 23 Giga Byte so I Made a Backup for the whole
database and then I tried to shrink the log file using
enterprise manager shrink database wizard.then I
discovered that the physical file capacity was not
reduced, although enterprise manager gave me a message
that the file has been shrinked.
I tried More And More But No result.
Help will be so much appreciated
Best Regards:-
Ahmed NourGood shrink article can be found :-
http://www.mssqlserver.com/faq/logs-shrinklog.asp
--
HTH
Ryan Waight, MCDBA, MCSE
"Ahmed Nour" <a_m_nour@.hotmail.com> wrote in message
news:0d7201c393e7$706c2590$a401280a@.phx.gbl...
> I have SQL Server 2000 on WIN2k Advanced Server And I have
> a Database Consists of:-
> 1-Single Database File .
> 2-single transaction log file(initial size 2 MB).
> I have Observed that the transaction log file'Capacity
> reaches 23 Giga Byte so I Made a Backup for the whole
> database and then I tried to shrink the log file using
> enterprise manager shrink database wizard.then I
> discovered that the physical file capacity was not
> reduced, although enterprise manager gave me a message
> that the file has been shrinked.
> I tried More And More But No result.
> Help will be so much appreciated
> Best Regards:-
> Ahmed Nour|||Ahmed ,
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
--
- Vishal
a Database Consists of:-
1-Single Database File .
2-single transaction log file(initial size 2 MB).
I have Observed that the transaction log file'Capacity
reaches 23 Giga Byte so I Made a Backup for the whole
database and then I tried to shrink the log file using
enterprise manager shrink database wizard.then I
discovered that the physical file capacity was not
reduced, although enterprise manager gave me a message
that the file has been shrinked.
I tried More And More But No result.
Help will be so much appreciated
Best Regards:-
Ahmed NourGood shrink article can be found :-
http://www.mssqlserver.com/faq/logs-shrinklog.asp
--
HTH
Ryan Waight, MCDBA, MCSE
"Ahmed Nour" <a_m_nour@.hotmail.com> wrote in message
news:0d7201c393e7$706c2590$a401280a@.phx.gbl...
> I have SQL Server 2000 on WIN2k Advanced Server And I have
> a Database Consists of:-
> 1-Single Database File .
> 2-single transaction log file(initial size 2 MB).
> I have Observed that the transaction log file'Capacity
> reaches 23 Giga Byte so I Made a Backup for the whole
> database and then I tried to shrink the log file using
> enterprise manager shrink database wizard.then I
> discovered that the physical file capacity was not
> reduced, although enterprise manager gave me a message
> that the file has been shrinked.
> I tried More And More But No result.
> Help will be so much appreciated
> Best Regards:-
> Ahmed Nour|||Ahmed ,
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink
--
- Vishal
Subscribe to:
Posts (Atom)