Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSDE database deleting data to make space.

Posted on 2007-03-24
3
Medium Priority
?
387 Views
Last Modified: 2012-06-27
Hello Expert-Exchange Members
I have an application that has a MSDE database, this week the database has hit the MSDE limit of 2GB data file.  I have some space that will keep the application going for a couple of weeks.  I need to make more space in the data file.

I have delete data out of the largest tables, but no space has been made in the data part of the database file.  I have never seen this before, I deal with many SQL Database everyday and have been able to delete data from them to make sapce.

I have doubled check to see if the applcation is rebuilding the data in the database but that is not the case.

I think it may have something to do with the fillfactor between the databse table rows as it is set to 0% and I have never seen that before. Is that the case and how would I resove?

Thank you for Reading

Reagrds

GrahamR99

0
Comment
Question by:GrahamR99
[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
3 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 18785380
Hi GrahamR99,

The database files will not shrink in size if you delete data, SQL Server essentially marks that space as usable for any new data, you can see how much of the databse files actually contain data by using sp_spaceused (see below) you can also use your backup size as a rough and ready guide - if your sql backup is 500MB then there is 500MB of data in the database (this is only rough though).  If you really want to reclaim the disk space you can use the DBCC SHRINKFILE command which will shrink the database file.  There is a not a great deal of point to this though unless you need the disk space - details are below.

sp_spaceused:
http://msdn2.microsoft.com/en-us/library/ms188776.aspx

DBCC SHRINKFILE:
http://msdn2.microsoft.com/en-us/library/ms189493.aspx
0
 

Author Comment

by:GrahamR99
ID: 18791553
Hi Mcmonap
You have misunderstood my problem, I am not trying to make the data file smaller on the Disk drive.

The problem I have is that I have deleted two years worth of data out of four tables in the database, these tables are the bulk of the databse, but I have only managed to make a few MB's of space in the file its self.  I am expecting to free up a few 100 MB's in the database as each table is around 500MB in size.

I don't understand how I am deleting rows of data in the tables but only a few MB's is beening freed up.

Regards

GrahamR99
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 2000 total points
ID: 18795711
Hi GrahamR99,

You should be able to see where the bulk of your data lies by running the query below.  Enterprise Manager and SQL Server Management Studio both have prettier graphical interfaces for this information if you are using either of these.

DECLARE
      @tblName VARCHAR(1024)

CREATE TABLE #tblSizes (
      [name] VARCHAR(1024)
      , [rows] INT
      , reserved VARCHAR(20)
      , [data] VARCHAR(20)
      , index_size VARCHAR(20)
      , unused VARCHAR(20))

DECLARE ctbl CURSOR FAST_FORWARD
FOR
SELECT
      TABLE_SCHEMA+'.'+TABLE_NAME
FROM
      INFORMATION_SCHEMA.TABLES
OPEN ctbl

FETCH NEXT FROM ctbl INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
      INSERT INTO #tblSizes
      EXEC sp_spaceused @tblName
      FETCH NEXT FROM ctbl INTO @tblName
END
CLOSE ctbl
DEALLOCATE ctbl

SELECT
      *
FROM
      #tblSizes
ORDER BY
      CONVERT(INT, LEFT(data, LEN(data) - 3)) DESC

DROP TABLE  #tblSizes
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

604 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