Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

SQL 2000 database not shrinking despite using DBCC SHRINKDATABASE and DBCC SHRINKFILE

Hello:

I have a database that is extraordinarily large and have been trying to shrink it to the size it should be with just the data.

sp_spaceused returns:
name            size              unallocated
UltBPMtest      12860.50 MB      -249.74 MB

reserved                            data               index               unused
13400056 KB      1567864 KB      358840 KB      11473352 KB

Therefore, I want to remove the unused 11 GB and just leave the data at 1.5 GM. This should be possible, but it doesn't seem to work. Any ideas?
Thanks,
Joseph

0
CEGE
Asked:
CEGE
  • 12
  • 10
  • 3
  • +2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you have any tables without a clustered index?
if yes, create a clustered index on those (resp change an existing index into a clustered index), and check again.
0
 
CEGEAuthor Commented:
I don't know what a clustered index is...sorry.
0
 
RiteshShahCommented:
can you try backing up transaction log once and than shrink your file?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I don't know what a clustered index is...sorry.
RTFM, aka starting on this page:
http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please update statistics and run the shrinkfile again...Just a wild guess...
0
 
CEGEAuthor Commented:
I don't have logs, I have it in SIMPLE mode. How do you update statistics?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<How do you update statistics?>>

USE yourdbname
GO

EXEC sp_updatestats
GO
0
 
CEGEAuthor Commented:
I just the updatestats and shrunk the DB...
DBCC SHRINKFILE ('Ultimus BPM_Data', 7,truncateonly)
but the spaceused come back the same.

13400120 KB      1567888 KB      358840 KB      11473392 KB

hmmm???
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
TRy shrinking the file to a size that is more realistic...

DBCC SHRINKFILE ('Ultimus BPM_Data', 3072)
0
 
CEGEAuthor Commented:
Thanks Racimo:

I just did
DBCC SHRINKFILE ('Ultimus BPM_Data', 3072) and
DBCC SHRINKDATABASE (UltBPMtest, TRUNCATEONLY);

and these are the results....
id                fid                       size              min size      usedpages    estpages
12      1      1640784      128      1640752      1640752

but why then is it still so big? if it is saying this?
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Looks like the shrink has succeeded.  Can you confirm the file size on the disk.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Can you post also the sp_spaceused again...
0
 
CEGEAuthor Commented:
13113888 KB      1672888 KB      189512 KB      11251488 KB

the physical file size is still 13 GB...I don't understand it...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<the physical file size is still 13 GB...I don't understand it...>>
Is the database ONLINE.  shrinkfile may fail when there is too much activity to allow the physical reorganization.  

You can either:

> put the filegroup in READONLY then try again during a maintainance period.  During that time, users will be able to access the data but they won't be able to insert.
> if you can't do the above, identify the top size tables then create a new filegroup and migrate these tables on the new filegroup.  Then shrink the old file again.  That's a way to *force* the shrink...
0
 
CEGEAuthor Commented:
this database is a copy of the real one...it's online, but just I am using it.

In read-only I can't execute any DBCC commands...IS there another way to force the SHRINK? It's getting on my nerves a bit now.

thanks.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
What you could try is also to create a new 3gb file in the same filegroup and empty the old file by running

DBCC SHRINKFILE (oldfilename, EMPTYFILE);
GO

hth
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
How much RAM are you using...Just curious...
0
 
Eugene ZCommented:
run   DBCC UPDATEUSAGE
Because DBCC UPDATEUSAGE can take some time to run on large tables or databases, it should not be used only unless you suspect incorrect values are being returned by sp_spaceused.  (it  maybe  your case)
 http://msdn.microsoft.com/en-us/library/ms188414.aspx
and check what are real DB files sizes on the server drive (select * from yourdb.dbo.sysfiles   -- to see location)
0
 
Eugene ZCommented:
BTW: can you run and post result for
 select @@version
or check if you have at least  service pack 3a for sql server 2000
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Also please attempt a simple resize of the file...

alter database yourdb modify file (name='filename', size=3072MB)
0
 
CEGEAuthor Commented:
hello:

I am sorry I don't know what you mean about creating a new file. How can I do this?

4 GB ram, real 3,5 GB
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<How can I do this?>>
PLease attempt the two follwonig actions:

1) Attempt reducing the file size by running

alter database yourdb modify file (name='oldfilename', size=3072MB)

If the above does not work try action 2

2)  
create a newfile named filename2 in the same filegroup than the file you can't shrink...

alter database yourdb add file (name='filename2', size=3072MB, filename='path of the new file', growth=0)
go

DBCC SHRINKFILE (oldfilename, EMPTYFILE);
GO

alter database yourdb modify file (name='filename', size=3072MB)

HTH
0
 
Eugene ZCommented:
can you run
DBCC UPDATEUSAGE
--and then
exec  sp_spaceused    
-----------------------
then post result
--
and check database files sizes on your server drive - > windows explorer- >etc
/see my post above/
0
 
CEGEAuthor Commented:
1) It wouldn't let me.

2) I just finished and now I have two data files one 5 GM and another 12GB.
sp_spaceused
12768688 KB      1590176 KB      189792 KB      10988720 KB

3) UPDATEUSAGE:
I just ran it and it changed some values for soem of the tables.

4) select @@version :

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
1) Is there an error message when trying to resize?
2) Did you try to empty the space.
0
 
CEGEAuthor Commented:
After the UpdateUsage:

13240344 KB      11929424 KB      257944 KB      1052976 KB

I seem to have more data ocupied and less unused...what are the correct values???
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<what are the correct values???>>
Well, I'd say that's your real size...Eugene hit right on the spot...
0
 
CEGEAuthor Commented:
I think that was it..that it was giving me the wrong info, it was shrinking, but only to where it could, becasue of the data in it. I appreciate the help offered. Cheers.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 12
  • 10
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now