Solved

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

Posted on 2009-07-01
28
551 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 3
  • +2
28 Comments
 
LVL 143

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 143

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 43

Accepted Solution

by:
Eugene Z 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 43

Expert Comment

by:Eugene Z
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 43

Expert Comment

by:Eugene Z
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

615 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