0

I have a database which is almost full and we have some options to deal with this:

  1. we can increase the db file size
  2. shrink the database

Before I choose the first option, I want to know how can I check the database size and how much data is really in there so that I may can shrink the database to get some free space.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bobtheguy
  • 33
  • 1
  • 8
  • In most cases you will want to increase the size of your data file. Shrinking your data file can lead to all kinds of nastiness. There are several articles out there on this including this one http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/ – Steve Mangiameli Sep 23 '15 at 22:00
  • so can I do a reorg or rebuilt to free up some space? – bobtheguy Sep 23 '15 at 22:16

2 Answers2

1

So see where you have unused space , what file is how big use the following query....

Use DatabaseName
GO

Select name                                             AS [FileName]
     , size/128.0                                       AS [FileSize(MB)]
     , fileproperty(name, 'SpaceUsed')/128.0            AS [Space_Used(MB)]
     , (size - fileproperty(name, 'SpaceUsed')) /128.0  AS [FreeSpace(MB)] 
From dbo.sysfiles
GO

Finally when you have decided to shrink a file with lots of free space you can use DBCC shrinkfile command to do so.

USE DatabaseName
GO
DBCC SHRINKFILE ('FileName', 10)   --<-- will shrink it to 10 MB
GO

Note

If any of the unused space was occupied by the BLOB data type(text, ntext, xml etc) column, you may not be able to claim back that unused space unless you drop and recreate the table again.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • Thanks, but can you tell me which column is for how big the database and which one is for the how much data is actually in the database right now – bobtheguy Sep 23 '15 at 22:13
0

You can get this information a couple of different ways. In SSMS you can right click on the database, choose properties and look at files. You data and log files will display an "Initial" value indicating size.

You can right click the DB, click on Tasks and Shrink, then Files and the data file should be the default displayed. This will show you your size and free space.

You can also run a script showing you all the size and location info for your databases. There are several out there you can find with a quick Google Search.

Additionally, there are some built in SPs that can get you that info as described here The fourth query in particular shows you the amount of free space.

Steve Mangiameli
  • 688
  • 7
  • 15
  • 1
    Watch out for `sp_msforeachdb` in the proposed solution, I have not experience it myself but sp_msforeachdb has a bad habit of missing out databases . – M.Ali Sep 23 '15 at 22:13
  • I ran the query and does this output tell me that the SizeGB is the actual data size and the SizeMB is the database size which including some unreorged space – bobtheguy Sep 23 '15 at 22:19
  • I removed that link @M.Ali since I missed that, but you are absolutely correct. – Steve Mangiameli Sep 24 '15 at 03:13
  • But any size information that I got from those query is just about the db size, where can I find the query for me to see how much data is actually the file? – bobtheguy Sep 24 '15 at 13:51
  • The fourth query is what you are looking for. Edited the answer. – Steve Mangiameli Sep 24 '15 at 14:15