Solved

How to shrink a SQL 2005 database !!!

Posted on 2008-10-02
18
689 Views
Last Modified: 2012-05-05
Hi All,

I have a 72GB database. I want to shrink this database as much as possible.

Under SQL Managment studio when I see the property of this dataabase, the size of the database is 73071.01 MB and the space available is 4705.14MB.

The Files for this database and have following sizes.

MDF file size is 14,272 KB
NDF file size is 74,805,440 KB
LDF log file size is 5,000 KB.

Please suggest the best & easist way to reduce the size of this database as much as possible.

Thanks a million for your prompt help.

Zee
0
Comment
Question by:zeeshanbutt
18 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22628430
so, you have 4G free of 74G data file...
I think, if you really want to shrink the db, you have to first DELETE some data from the tables...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22628456
you can try to shrink, but probably just need more storage

dbcc shrinkdb('dbname', targetsizeinMB)
0
 

Author Comment

by:zeeshanbutt
ID: 22628462
So are you saying that 4G is free, and I can remove it? Is it ok to remove this 4GB?
But thats not enough, I looking to reduce it significantly, and cannot delete the data, but want to remove any unwanted space or logs.

Please suggest.

Thanks a million!!!!
0
 

Author Comment

by:zeeshanbutt
ID: 22628477
chapmandew,

Please explain what do you mean when you say "probably just ned more storage".

Thanks....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22628488
well, if you have that much data in your database and the shrink operation doesn't help you, it is time to add some more disk storage (physical disks)
0
 

Author Comment

by:zeeshanbutt
ID: 22628538
chapmandew,

So are you saying that in my scenario it cannot be further shrink, since its data, and not logs nor empty space that is making this database big??

Please comment.

Thanks...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22628576
No, I am saying IF it cannot be further shrunk (if there is that much data in your db), then you'll have to consider buying more disk space....
0
 

Author Comment

by:zeeshanbutt
ID: 22628599
Well I don't know if it can be shrink or not. That is why I have posted this question here.
Please help !!!!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22629101
And that is why you have to run the command I gave you to shrink it.  Try to shrink it first!!!!!!!!!!!!!!
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22629106
what comes up when you run this?

sp_spaceused 'yourdbname'
0
 

Author Comment

by:zeeshanbutt
ID: 22629192
chapmandew,

When I run sp_spaceused, I get the following no's:

database_size: 73071.01 MB
unallocated space: 4705.14 MB
reserved: 70001648 KB
data: 56472072 KB
index_size: 13491712 KB
unused: 37864 KB

Thanks for your help....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22629322
time to buy some new disks.....shrinking isn't going to help you very much.

OR...as was previously noted, go see if there are some tables you can delete (or at least delete some data from some tables)
0
 

Author Comment

by:zeeshanbutt
ID: 22629343
chapmandew,

Thanks for the information. But in order for me to communite to my boss, I need to tell him exactly why this database cannot be shrink. Based on the sp_spaceused results how does it can be understood that it cannot be shrink. Please exaplain. Also please explain each element of my sp_spaceused result.

Thanks a million for your help.

Zee
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22629405
You should use the dbcc shrinkdatabase command to shrink your database.  You can also use dbcc shrinkfile command as well.  You can also may want to use the notruncate with the shrinkdatabase command first this will reorganize the allocated pages in the database.  Then you can use the truncate only option.

First run:
dbcc shrinkdatabase (dbname, notruncate)
Then run:
dbcc shrinkdatabase (dbname, 0, truncateonly)

I question why you are wanting to shrink the database in the first place.  It is not very out of place for your database to have 4 GB of free space for this size database.  You want some extra space if you are writing to this database.  Otherwise it will have to do a grow operation when it runs out of space.  When it does that it will impact the database performance possibly during a time you don't want that to happen.  Your log file should most likely be larger as well.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22633134
Tell your boss that there is nothing to shrink...all of the space in the table is being used by data.  If you look at unallocated space: 4705.14 MB, it shows very little space that can be reused.  Run this statement to see which will capture how many rows are in each of your tables....paste the top 10 or so rows on here.


create table #t(tablename nvarchar(255), rows int, reserved nvarchar(255), data nvarchar(255), index_size nvarchar(255), unused nvarchar(255))

insert into #t
exec sp_msforeachtable 'exec sp_spaceused [?]'

select * from #t
order by rows desc
0
 

Author Comment

by:zeeshanbutt
ID: 22633639
chapmandew,

Following is the result of your query. B/c of the security reasons I have changed the name of the tables. I inculde all tables here.

Please comment.

Thanks,

Zee


TableName      Rows      Reserved       Data        Index_Size          Unused
Table1      100704195      5228336 KB       5197664 KB        29736 KB          936 KB
Table2      72499677      37637096 KB 26176208 KB 11452328 KB 8560 KB
Table3      50661513      16982472 KB 16779496 KB 200544 KB          2432 KB
Table4      26039850      4323376 KB       4284696 KB        38144 KB          536 KB
Table5      5412482      821704 KB       811896 KB        9616 KB          192 KB
Table6      2865394      1159936 KB      521600 KB        637664 KB          672 KB
Table7      2308751      324640 KB      172624 KB        151472 KB          544 KB
Table8      1856505      290736 KB      287952 KB        2720 KB          64 KB
Table9      1304905      223712 KB      118632 KB      105048 KB      32 KB
Table10      827995      166632 KB      99312 KB      67288 KB      32 KB
Table11      821970      637480 KB      537904 KB      97864 KB      1712 KB
Table12      718450      219720 KB      100848 KB      118376 KB      496 KB
Table13      684059      666704 KB      418840 KB      247544 KB      320 KB
Table14      666365      649744 KB      575192 KB      74288 KB      264 KB
Table15      666365      138328 KB      45592 KB      92224 KB      512 KB
Table16      412418      56872 KB      56056 KB      504 KB      312 KB
Table17      358620      173224 KB      141304 KB      31760 KB      160 KB
Table18      202247      40496 KB      18392 KB      21968 KB      136 KB
Table19      196645      59936 KB      28608 KB      31192 KB      136 KB
Table20      154795      61936 KB      30640 KB      31040 KB      256 KB
Table21      112200      16408 KB      4560 KB      11776 KB      72 KB
Table22      101468      42864 KB      18128 KB      22016 KB      2720 KB
Table23      46900      14696 KB      6576 KB      7984 KB      136 KB
Table24      14182      3440 KB      1272 KB      2128 KB      40 KB
Table25      7208      1200 KB      440 KB      696 KB      64 KB
Table26      6759      672 KB      520 KB      40 KB      112 KB
Table27      4383      720 KB      560 KB      136 KB      24 KB
Table28      3493      824 KB      256 KB      432 KB      136 KB
Table29      2532      1248 KB      640 KB      440 KB      168 KB
Table30      2036      936 KB      304 KB      480 KB      152 KB
Table31      1147      400 KB      240 KB      80 KB      80 KB
Table32      499      240 KB      72 KB      112 KB      56 KB
Table33      466      56 KB      48 KB      8 KB      0 KB
Table34      442      56 KB      16 KB      40 KB      0 KB
Table35      202      48 KB      24 KB      24 KB      0 KB
Table36      147      56 KB      32 KB      24 KB      0 KB
Table37      127      32 KB      8 KB      24 KB      0 KB
Table38      97      24 KB      16 KB      8 KB      0 KB
Table39      84      16 KB      8 KB      8 KB      0 KB
Table40      54      24 KB      16 KB      8 KB      0 KB
Table41      40      104 KB      16 KB      88 KB      0 KB
Table42      37      64 KB      8 KB      56 KB      0 KB
Table43      28      32 KB      8 KB      24 KB      0 KB
Table44      27      32 KB      8 KB      24 KB      0 KB
Table45      26      32 KB      8 KB      24 KB      0 KB
Table46      26      64 KB      8 KB      56 KB      0 KB
Table47      26      64 KB      8 KB      56 KB      0 KB
Table48      24      16 KB      8 KB      8 KB      0 KB
Table49      23      32 KB      8 KB      24 KB      0 KB
Table50      14      56 KB      32 KB      24 KB      0 KB
Table51      11      32 KB      8 KB      24 KB      0 KB
Table52      10      32 KB      8 KB      24 KB      0 KB
Table53      10      32 KB      8 KB      24 KB      0 KB
Table54      9      32 KB      8 KB      24 KB      0 KB
Table55      8      32 KB      8 KB      24 KB      0 KB
Table56      7      48 KB      8 KB      40 KB      0 KB
Table57      7      64 KB      8 KB      56 KB      0 KB
Table58      7      16 KB      8 KB      8 KB      0 KB
Table59      7      32 KB      8 KB      24 KB      0 KB
Table60      7      16 KB      8 KB      8 KB      0 KB
Table61      7      32 KB      8 KB      24 KB      0 KB
Table62      6      16 KB      8 KB      8 KB      0 KB
Table63      6      16 KB      8 KB      8 KB      0 KB
Table64      6      16 KB      8 KB      8 KB      0 KB
Table65      6      32 KB      8 KB      24 KB      0 KB
Table66      5      16 KB      8 KB      8 KB      0 KB
Table67      5      32 KB      8 KB      24 KB      0 KB
Table68      4      32 KB      8 KB      24 KB      0 KB
Table69      4      16 KB      8 KB      8 KB      0 KB
Table70      3      32 KB      8 KB      24 KB      0 KB
Table71      3      32 KB      8 KB      24 KB      0 KB
Table72      1      64 KB      8 KB      56 KB      0 KB
Table73      1      32 KB      8 KB      24 KB      0 KB
Table74      1      64 KB      8 KB      56 KB      0 KB
Table75      1      48 KB      8 KB      40 KB      0 KB
Table76      1      32 KB      8 KB      24 KB      0 KB
Table77      1      16 KB      8 KB      8 KB      0 KB
Table78      1      48 KB      8 KB      40 KB      0 KB
Table79      1      16 KB      8 KB      8 KB      0 KB
Table80      1      64 KB      8 KB      56 KB      0 KB
Table81      0      0 KB      0 KB      0 KB      0 KB
Table82      0      0 KB      0 KB      0 KB      0 KB
Table83      0      208 KB      0 KB      40 KB      168 KB
Table84      0      0 KB      0 KB      0 KB      0 KB
Table85      0      24 KB      0 KB      24 KB      0 KB
Table86      0      0 KB      0 KB      0 KB      0 KB
Table87      0      216 KB      0 KB      48 KB      168 KB
Table88      0      0 KB      0 KB      0 KB      0 KB
Table89      0      13536 KB      11904 KB      24 KB      1608 KB
Table90      0      48 KB      8 KB      40 KB      0 KB
Table91      0      0 KB      0 KB      0 KB      0 KB
Table92      0      0 KB      0 KB      0 KB      0 KB
Table93      0      152 KB      0 KB      48 KB      104 KB
Table94      0      0 KB      0 KB      0 KB      0 KB
Table95      0      0 KB      0 KB      0 KB      0 KB
Table96      0      0 KB      0 KB      0 KB      0 KB
Table97      0      16 KB      0 KB      16 KB      0 KB
Table98      0      0 KB      0 KB      0 KB      0 KB
Table99      0      0 KB      0 KB      0 KB      0 KB
Table100      0      0 KB      0 KB      0 KB      0 KB
Table101      0      0 KB      0 KB      0 KB      0 KB
Table102      0      0 KB      0 KB      0 KB      0 KB
Table103      0      0 KB      0 KB      0 KB      0 KB
Table104      0      0 KB      0 KB      0 KB      0 KB
Table105      0      384 KB      248 KB      64 KB      72 KB
Table106      0      6192 KB      768 KB      120 KB      5304 KB
Table107      0      0 KB      0 KB      0 KB      0 KB
Table108      0      24 KB      0 KB      24 KB      0 KB
Table109      0      0 KB      0 KB      0 KB      0 KB
Table110      0      8 KB      0 KB      8 KB      0 KB
Table111      0      0 KB      0 KB      0 KB      0 KB
Table112      0      0 KB      0 KB      0 KB      0 KB
Table113      0      0 KB      0 KB      0 KB      0 KB
Table114      0      24 KB      0 KB      24 KB      0 KB
Table115      0      0 KB      0 KB      0 KB      0 KB
Table116      0      0 KB      0 KB      0 KB      0 KB
Table117      0      0 KB      0 KB      0 KB      0 KB
Table118      0      0 KB      0 KB      0 KB      0 KB
Table119      0      0 KB      0 KB      0 KB      0 KB
Table120      0      0 KB      0 KB      0 KB      0 KB
Table121      0      0 KB      0 KB      0 KB      0 KB
Table122      0      0 KB      0 KB      0 KB      0 KB
Table123      0      0 KB      0 KB      0 KB      0 KB
Table124      0      0 KB      0 KB      0 KB      0 KB
Table125      0      0 KB      0 KB      0 KB      0 KB
Table126      0      32 KB      8 KB      24 KB      0 KB
Table127      0      0 KB      0 KB      0 KB      0 KB
Table128      0      0 KB      0 KB      0 KB      0 KB
Table129      0      0 KB      0 KB      0 KB      0 KB
Table130      0      16 KB      0 KB      16 KB      0 KB
Table131      0      0 KB      0 KB      0 KB      0 KB
Table132      0      0 KB      0 KB      0 KB      0 KB
Table133      0      0 KB      0 KB      0 KB      0 KB
Table134      0      0 KB      0 KB      0 KB      0 KB
Table135      0      0 KB      0 KB      0 KB      0 KB
Table136      0      0 KB      0 KB      0 KB      0 KB
Table137      0      0 KB      0 KB      0 KB      0 KB
Table138      0      0 KB      0 KB      0 KB      0 KB
Table139      0      0 KB      0 KB      0 KB      0 KB
Table140      0      16 KB      8 KB      8 KB      0 KB
Table141      0      88 KB      0 KB      32 KB      56 KB
Table142      0      40 KB      16 KB      24 KB      0 KB
Table143      0      0 KB      0 KB      0 KB      0 KB
Table144      0      0 KB      0 KB      0 KB      0 KB
Table145      0      152 KB      0 KB      40 KB      112 KB
Table146      0      0 KB      0 KB      0 KB      0 KB
Table147      0      0 KB      0 KB      0 KB      0 KB
Table148      0      0 KB      0 KB      0 KB      0 KB
Table149      0      0 KB      0 KB      0 KB      0 KB
Table150      0      0 KB      0 KB      0 KB      0 KB
Table151      0      0 KB      0 KB      0 KB      0 KB
Table152      0      0 KB      0 KB      0 KB      0 KB
Table153      0      72 KB      32 KB      40 KB      0 KB
Table154      0      3856 KB      472 KB      80 KB      3304 KB
Table155      0      0 KB      0 KB      0 KB      0 KB
Table156      0      0 KB      0 KB      0 KB      0 KB
Table157      0      0 KB      0 KB      0 KB      0 KB
Table158      0      0 KB      0 KB      0 KB      0 KB
Table159      0      0 KB      0 KB      0 KB      0 KB
Table160      0      0 KB      0 KB      0 KB      0 KB
Table161      0      0 KB      0 KB      0 KB      0 KB
Table162      0      0 KB      0 KB      0 KB      0 KB
Table163      0      656 KB      88 KB      8 KB      560 KB
Table164      0      0 KB      0 KB      0 KB      0 KB
Table165      0      0 KB      0 KB      0 KB      0 KB
Table166      0      0 KB      0 KB      0 KB      0 KB
Table167      0      0 KB      0 KB      0 KB      0 KB
Table168      0      0 KB      0 KB      0 KB      0 KB
Table169      0      0 KB      0 KB      0 KB      0 KB
Table170      0      0 KB      0 KB      0 KB      0 KB
Table171      0      0 KB      0 KB      0 KB      0 KB
Table172      0      0 KB      0 KB      0 KB      0 KB
Table173      0      0 KB      0 KB      0 KB      0 KB
Table174      0      32 KB      8 KB      24 KB      0 KB
Table175      0      0 KB      0 KB      0 KB      0 KB
Table176      0      728 KB      88 KB      24 KB      616 KB
Table177      0      0 KB      0 KB      0 KB      0 KB
Table178      0      0 KB      0 KB      0 KB      0 KB
Table179      0      0 KB      0 KB      0 KB      0 KB
Table180      0      4904 KB      4008 KB      40 KB      856 KB
Table181      0      3048 KB      2328 KB      40 KB      680 KB
Table182      0      0 KB      0 KB      0 KB      0 KB
Table183      0      3896 KB      2992 KB      56 KB      848 KB
Table184      0      0 KB      0 KB      0 KB      0 KB
Table185      0      352 KB      48 KB      24 KB      280 KB
Table186      0      0 KB      0 KB      0 KB      0 KB
Table187      0      352 KB      48 KB      24 KB      280 KB
Table188      0      0 KB      0 KB      0 KB      0 KB
Table189      0      0 KB      0 KB      0 KB      0 KB
Table190      0      352 KB      48 KB      24 KB      280 KB
Table191      0      0 KB      0 KB      0 KB      0 KB
Table192      0      8 KB      0 KB      8 KB      0 KB
Table193      0      0 KB      0 KB      0 KB      0 KB
Table194      0      8 KB      0 KB      8 KB      0 KB
Table195      0      0 KB      0 KB      0 KB      0 KB
Table196      0      0 KB      0 KB      0 KB      0 KB
Table197      0      32 KB      0 KB      32 KB      0 KB
Table198      0      0 KB      0 KB      0 KB      0 KB
Table199      0      0 KB      0 KB      0 KB      0 KB
Table200      0      0 KB      0 KB      0 KB      0 KB
Table201      0      0 KB      0 KB      0 KB      0 KB
Table202      0      520 KB      424 KB      72 KB      24 KB
Table203      0      0 KB      0 KB      0 KB      0 KB
Table204      0      0 KB      0 KB      0 KB      0 KB
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22633667
Id say your first 3 tables are your culprits...see if you can remove some rows from those tables (copy them to a different server first).  If you are table to, then you'll be able to shrink the db and get some more space.  Otherwise, you'll have to buy some more space.
0
 

Author Comment

by:zeeshanbutt
ID: 22635302
chapmandew & others,

Thanks for all this useful information.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

16 Experts available now in Live!

Get 1:1 Help Now