Solved

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

Posted on 2009-07-01
28
525 Views
Last Modified: 2012-05-07
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
Comment
Question by:CEGE
  • 12
  • 10
  • 3
  • +2
28 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24752379
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
 
LVL 1

Author Comment

by:CEGE
ID: 24752381
I don't know what a clustered index is...sorry.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24752397
can you try backing up transaction log once and than shrink your file?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24752409
>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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24752426
Please update statistics and run the shrinkfile again...Just a wild guess...
0
 
LVL 1

Author Comment

by:CEGE
ID: 24752441
I don't have logs, I have it in SIMPLE mode. How do you update statistics?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24752468
<<How do you update statistics?>>

USE yourdbname
GO

EXEC sp_updatestats
GO
0
 
LVL 1

Author Comment

by:CEGE
ID: 24752505
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
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 200 total points
ID: 24752539
TRy shrinking the file to a size that is more realistic...

DBCC SHRINKFILE ('Ultimus BPM_Data', 3072)
0
 
LVL 1

Author Comment

by:CEGE
ID: 24752723
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24752739
Looks like the shrink has succeeded.  Can you confirm the file size on the disk.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24752744
Can you post also the sp_spaceused again...
0
 
LVL 1

Author Comment

by:CEGE
ID: 24752894
13113888 KB      1672888 KB      189512 KB      11251488 KB

the physical file size is still 13 GB...I don't understand it...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24752971
<<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
 
LVL 1

Author Comment

by:CEGE
ID: 24753033
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24753059
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24753061
How much RAM are you using...Just curious...
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 300 total points
ID: 24753468
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24753477
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24753505
Also please attempt a simple resize of the file...

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

Author Comment

by:CEGE
ID: 24753530
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24753590
<<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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24754348
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
 
LVL 1

Author Comment

by:CEGE
ID: 24754836
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24754897
1) Is there an error message when trying to resize?
2) Did you try to empty the space.
0
 
LVL 1

Author Comment

by:CEGE
ID: 24755789
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24755870
<<what are the correct values???>>
Well, I'd say that's your real size...Eugene hit right on the spot...
0
 
LVL 1

Author Closing Comment

by:CEGE
ID: 31598682
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question