Link to home
Start Free TrialLog in
Avatar of Ruffone
Ruffone

asked on

Find the bloat in database

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
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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/
Avatar of Ruffone
Ruffone

ASKER

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
Avatar of Ruffone

ASKER

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