Solved

Why are my database files so big?

Posted on 2013-01-25
12
343 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 143

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 143

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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 143

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
 
LVL 143

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 143

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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, …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

830 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