Solved

Find the bloat in database

Posted on 2011-03-08
5
410 Views
Last Modified: 2012-05-11
I have a database in a hosted environment that has maxed out it's allocated space. (600mb) I made a backup which I have downloaded and restored to my desktop. I would like to shrink it but before I do I ran "select * from sysfiles) and it reports a small database which it should be because it is a development database. Except that NTFS it reporting that the "mdf" file is 599mb. It has a lot of tables in it might one of them be holding a lot of data? Could anyone suggest what my next step might be and maybe a fix

Thanks
0
Comment
Question by:ruffone
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 35072780
use this query

USE YourDatabaseName
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35073151
I actually have a small, open-source app that draws you a picture of your database, based on the table and index sizes. Check it out and let me know if it does what you need:

http://sqlspacemap.codeplex.com/
0
 
LVL 4

Author Comment

by:ruffone
ID: 35077459
ryanmccauley,
I ran your application against the database nad got an unhandled exception, "An Item with the same Key has already being added" How do I fix that?

Thanks
0
 
LVL 4

Author Closing Comment

by:ruffone
ID: 35078748
ryanmccauley,
Here is the call stack.
************** Exception Text **************
System.ArgumentException: An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
   at SQLSpaceMapper.MainForm.RefreshDatabaseView() in C:\Development\Personal Projects\SQL Space Mapper\MainForm.vb:line 80
   at SQLSpaceMapper.MainForm.mnuConnect_Click(Object sender, EventArgs e) in C:\Development\Personal Projects\SQL Space Mapper\MainForm.vb:line 124
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
   at System.Windows.Forms.ToolStrip.WndProc(Message& m)
   at System.Windows.Forms.MenuStrip.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35082877
Thanks for the call stack - I've never had it blow up there, but it's good to know there's a problem.

Can you email me at "SQLSpaceMap (at) kilanimccauley (dot) com"? I have a couple of questions about your database and what might have caused that exception (and I'd love to know the results of the query that feeds that dictionary) so I can get it resolved. I'd greatly appreciate your help!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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, …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

825 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