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...
>