Solved

Why are my database files so big?

Posted on 2013-01-25
12
342 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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 …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 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