.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Question about unallocated space and unused space in a database

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi, every expert,

I am running SQL server 2008 SP1, I've got a question about unallocated space and unused space in a database.

In my DB I run sp_spaceused as follows:

exec sp_spaceused 'DB_00001'

database_name database_size unallocated space


DB_00001 2004.13 MB 49.64 MB

reserved data index_size unused

------------------ ------------------ ------------------ --------------

1531248 KB 412720 KB 165168 KB 953360 KB

Question 1: As I understand, the unallocated space is never used by the database, while the unused space is once used by database but got freed by deleting etc (I mean deleting only removes part of the pages in section, for removing all the pages in section, see my 2nd question) ..am I correct?

Question 2:  if the deleting operation frees up the whole section, will the free section show as unallocated space? If the answer is no, how does SQL server handle this free section?

Question 3: If we shrink the datafile using the TRUNCATEONLY option, according to my understanding, the unallocated space can certainly be freed up as the following statement in Book Online, am I correct?

TRUNCATEONLY Releases all free space at the end of the file to the operating system but does not perform any page mov

View Complete Post

More Related Resource Links

Space usage - Database


Hi Gurus,

My sql server 2005 instance has 83 databases. I would like to know how much space is being used by all the databases and top 5 list. Please let me know how to get this information.





Do I need free space when doing full database restore in SQL Server 2005?


I have a drive 'F' with 40G free space. I try to restore a database whose data + log file size is 60G.

The restore fails. Is it because a full restore needs  free disk space of 60G in this case?


How can I reclaimed Content Database space.

I recently deleted a very large amount on data from a site (~500 gigs).  Unfortunately the database still shows a size of 570 gigs to the operating system.  When I did a stsadm -o ENUMSITES -url thesite.  It shows onyl 112 gig of used storage space.  I have run the DefragIndices from this KB article http://support.microsoft.com/kb/943345.  What else can I do to reclaim this space.  We are extremely close to filling up this drive array.  Thanks.

T-SQL for database size and space available (like on Database Properties page on SQL 2008 SSMS)


How can i (using T-SQL) get the database size and free space like the database properties page of SQL 2008 SSMS shows? I want to be able to display that information in our software.


Replace special character with space throughout the database



My requirement is to replace the special characters with space throughout the databse. How can I do this? I am new to this sql server. So any one could you please help me in this aspect.

Thanks in advance

Prevent UnUsed space of nvarchar column



Suppose i have table like this:

 myTable table
id int not null,
myCol varchar(100) not null

myTable may have over 1,000,000 recordes. but in most of them length of myCol is lower than 20 char. So most of myCol remain unused. How can i prevent this garbage?

Best. Morteza

Could not allocate space for object 'dbo.WebProxyLog'.'IX_WebProxyLog_DateTime' in database 'w3

I have a SQL Server 2008 running on Windows 2008.

The main database is located on the D: drive and the total capacity of the disk drive is 435GB.

I have started to receive the following messages in the error log file:
"Could not allocate space for object 'dbo.WebProxyLog'.'IX_WebProxyLog_DateTime' in database 'w3proxy' because the 'PRIMARY' filegroup is full".

The database "w3proxy" has totally filled the D: dirve and there nothing else to delete on the D: drive to free some disk space.

At the moment I am unable to get an extra disk drive which I believe would solve the disk space problem.

But can anyone suggest any other way to free up the disk space until I can get an extra disk drive?

Thank you.

WSS 3 Storage Space vs. Database File Size


Our SharePoint (WSS 3.0) databases are on a SQL-2000 server that's starting to get low on disk space. In order to buy some time to move content into either our MOSS or SP-2010 environment we did some cleanup in SharePoint by archiving & removing several old sites and removing older document versions in a couple of libraries. These changes were made 3 days ago. The Site Settings | Storage Space Allocation page indicates that content size is a little under 37 GB, down from a bit more than 40 GB. (I did make sure that the deleted files / versions were also removed from the site collection recycle bin.)

What has us puzzled is that the physical database file on the SQL Server has not gotten smaller and the amount of "free space" that can be reclaimed has actually gone down - it's currently at about 200 MB. The database file size is over 57 GB; 20 GB larger than SharePoint reports.

So, I have a few questions:

  1. Is it normal for the content database file size to be significantly (50-60%) larger than the amount of content that SharePoint reports?
  2. Is it normal for the database not to get smaller (or at least have more internal free space) when a large amount of content is removed?
  3. Is there any way that we can recover some of this space to get some breathing room on the SQL Server?



database interface design question


For handy database interface, is it good to use SqldataSources exclusively intead of EntLib functions?

Thanks for any feedback.

Image processing, determine white space and crop



I have created a image upload for our ID card system to allow users to upload new images, edit them and submit for approval.  This is all working well with a combination of asp.net (c#) and jquery to give a seemless editor etc.

We have had a few users who I cannot believe have scanned a photo of themself and then just submitted it with the A4 page of white and little photo.

I am wondering if anyone can help with a suggestion on how I could possibly reduce the load of these coming through (I can't believe they can be this stupid), it stipulates UK passport standards.

Would my only option be to check for a single continues colour that occurs and if it reaches a certain percentage point say 60% then to just send back an error stating they need to crop the image.

Can anyone help with providing sample or that I can use to move this one on.

I am still in disbelief on this one.  the other one is they take a photo of whole body and other in it and cut straight down middle chopping the people out but supplying a photo that no use for an id photo card....but that I can't imagine being solved programatically as I need to determine where their face dimensions are and what percentage of the actual photo is taken up by it and if it below a threshold reject it.

Bitmap FromFile space "file not found"


I am using aspose.slides to create PPTx's. Some of the process involves loading jpg's from another server and including them in the presentation. This works fine in my development environment. When I upload the site to its server, the jpg loading fails.

I am trying to access jpg's on the other server by:

   oFS = New FileStream(sFile, FileMode.Open, FileAccess.Read)

This causes an access violation error when run from the server.

I also boiled the code down to reading the file directly:

   oImg = System.Drawing.Bitmap.FromFile(sFilePath)

This causes a file not found error.

We have narrowed the problem down to the fact that the File Path has a space in it. How does one solve this problem (while still keeping the space, there is a lot of legacy data)? I have tried replacing the space with %20 or +. I have tried using Server.UrlEncode. The error message always comes back with the encoding in place (like no decoding

Office Space: Custom Field Types for SharePoint 2007


Custom field types provide a powerful way to define a reusable SharePoint column definitions.

Ted Pattison

MSDN Magazine May 2009

Office Space: Custom Auditing In SharePoint


Learn how to enable an auditing solution for Windows SharePoint Services (WSS) 3.0 with customized control pages in Microsoft Office SharePoint Server (MOSS) 2007.

Ted Pattison

MSDN Magazine September 2008

Office Space: Automated SharePoint Site Branding


Here's a custom branding solution for SharePoint sites that integrates Master Pages and CSS files at the level of the site collection.

Ted Pattison

MSDN Magazine July 2008

Office Space: From VBA Macro to Word Add-in


See how to use Word to capture a VBA macro and use Visual Studio Tools for Office to wrap it up into a deployable Word add-in.

Robert Bogue

MSDN Magazine May 2008

Office Space: Simplify SharePoint Development with STSDEV


Ted Pattison shows how to use a new STSDEV utility to set up and deploy SharePoint development projects in Visual Studio in an easy and repeatable manner.

Ted Pattison

MSDN Magazine March 2008

Office Space: Security Programming in SharePoint 2007


This month Ted Pattison presents an overview of programming security and permissions for Windows SharePoint Services 3.0.

Ted Pattison

MSDN Magazine February 2008

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend