Solved

Find the bloat in database

Posted on 2011-03-08
5
389 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
Comment Utility
use this query

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

Expert Comment

by:Ryan McCauley
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

16 Experts available now in Live!

Get 1:1 Help Now