Purging data off of an MSDE database.

How do I purge data off of an MSDE database. I only want to keep 6 months of data in the database. Right now I have data going back to 2004. I get errors about every 10 seconds. "Primary File Group is Full" is the error I am getting.

Hi,

you may archive it to another database/server, refer http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8846 http://sqljunkies.com/Forums/ShowPost.aspx?PostID=1855 and http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic814.aspx for "Primary File Group is Full" error.

Hemantgiri S. Goswami

|||

Hi,

So basically what you are telling me is that there is no way to purge data off an MSDE database?

---

|||hi;

if you dont want to extend your primary group and you want to delete the old data you will have to implement some logic, assuming that the table contain all the same field for saving the data where data has to be filtered, I would create a script to run within a job. If you want to automate the script creation you can do something like:

SELECT 'DELETE FROM ' + '[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + '] WHERE SomeDateColumn >= DATEADD(m,-6,GETDATE())'

FROM INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'), 'IsMSShipped') = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Travis H wrote:

Hi,

So basically what you are telling me is that there is no way to purge data off an MSDE database?

---

definately their is a way to purge off the data as explained by Jens but i thought if you would like to archive it rather then purging, thus i spoted some links FYI.

Hemantgiri S. Goswami

|||Depending on your version and edition you could also use horizantal partitioning which would move the older records to the other partition on probably another file group.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de