?
Solved

Why are my database files so big?

Posted on 2013-01-25
12
Medium Priority
?
355 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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 1200 total points
ID: 38820510
what happens if you use NOTRUNCATE instead of TRUNCATEONLY?
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 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

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 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