Solved

Why are my database files so big?

Posted on 2013-01-25
12
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
length of the password hash sha1:64000 to set sql field property. 13 105
Checking for column changes SQL 2014 4 39
SQL profiler 3 47
SQL query 45 31
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, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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