Solved

Why are my database files so big?

Posted on 2013-01-25
12
338 Views
Last Modified: 2013-02-04
Hi Experts,

I have shrunk my database using the following

DBCC SHRINKDATABASE( DBName, TRUNCATEONLY)
DBCC SHRINKFILE ( ATLord, TRUNCATEONLY)

SHRINKFILE seems to work grat on the log file but does nothing for the Data file!  After the cleanup I end up with this:

Tables
and yet my file sizes are this:

files
What's going on here?  AM I missing something?

Thanks!
0
Comment
Question by:axnst2
  • 6
  • 5
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38819709
either you have tables that are indeed so big, OR you have (some) table(s) that have no clustered index, but heavy insert/update/delete.

in which case, creating a clustered index on those tables will make the shrink work.
0
 

Author Comment

by:axnst2
ID: 38819781
I posted a picture of my top 4 largest tables!  Even if I add everything up I still shouldn't be anywhere near what the file size is!  Someting isn't adding up!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38819806
this high value of "reserved" vs the quite low value of actual data size seems to confirm what I write above.
so, check the scripts from this page:
http://blog.sqlauthority.com/2007/05/26/sql-server-2005-find-table-without-clustered-index-find-table-with-no-primary-key/
and make sure all your tables get some clustered index.
0
 

Author Comment

by:axnst2
ID: 38819856
After running the script, none of my top 4 tables are on the list!  And either way, the numbers still just don't add up!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38820291
did you nevertheless add clustered index to all of the tables listed?
it may not be a "big" table eating up space in the db files.
0
 

Author Comment

by:axnst2
ID: 38820308
ha?  How does that even compute?  I am not exactly a DB expert so please bear with me!
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38820361
another thing ... where the data file(s) created with large initial sizes?
eventually the shrink won't shrink below that initial size.
also note that tables are not the only space consumers; you also have indexes.
0
 

Author Comment

by:axnst2
ID: 38820413
Are you lloking at the image I attached in my initial post at all?  That includeds all of the indexes too!  Still the numbers are nowhere near what the file size is!  It's got to be something else!
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 38820510
what happens if you use NOTRUNCATE instead of TRUNCATEONLY?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 38820615
I don't know how you got those initial numbers.

Let's see how many pages SQL itself says are unused, per non-log file.

Run the command below on the db and check the results:


DBCC SHOWFILESTATS
0
 

Assisted Solution

by:axnst2
axnst2 earned 0 total points
ID: 38836132
OK, so I needed to run SHRINKFILE on the datafile too instead of running SHRINKDATABASE!
0
 

Author Closing Comment

by:axnst2
ID: 38850445
Thanks for the help anyways!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

28 Experts available now in Live!

Get 1:1 Help Now