Solved

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

Posted on 2009-07-01
28
502 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
 
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
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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now