Solved

Find the bloat in database

Posted on 2011-03-08
5
428 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
[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
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql2016-WIn10: standard,for SQL servc-account.. 51 55
sql 2016 data tools breakdown.. 1 36
Please explain Equi-join 3 44
store vs query adhoc - no show rows 4 37
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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, …
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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