Solved

Find the bloat in database

Posted on 2011-03-08
5
399 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short film showing how OnPage and Connectwise integration works.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now