Post reply

dropped a TEXT column, Filegroup remains large.

  • March 24, 2005 at 1:50 pm

    #85649

    I have a table that stores every email we get in a day... about 5,000.

    CREATE TABLE [dbo].[tbEmailMessage] (

    [EmailMessageID] [int] IDENTITY (1, 1) NOT NULL ,

    [SendDate] [dtDatetime] NOT NULL ,

    [SendTime] [dtDatetime] NOT NULL ,

    [SenderName] [dtString] NOT NULL ,

    [SenderEmail] [dtString] NOT NULL ,

    [Message] [text] ,

    [RawMessage] [text] ,

    [Timestamp] [timestamp] NOT NULL ,

    CONSTRAINT [PK_EmailMessage] PRIMARY KEY CLUSTERED

    (

    [EmailMessageID]

    ) WITH FILLFACTOR = 90 ON [EMAIL_DATA]

    ) ON [EMAIL_DATA] TEXTIMAGE_ON [EMAIL_DATA]

    GO

    Currently, this filegroup is 10G. The "RawMessage" column stores any attachments that came with the email, while the "Message" column stores only the body of the email. Space is becoming an issue and we want to drop the "RawMessage" column. I successfully issued the following command:

    alter table tbEmailMessage drop column RawMessage

    I expected the TaskPad view in Enterprise Manager to show me that we had freed at least 5 G, but that wasn't the case. I tried to shrink the one datafile that makes up the filegroup, but there was no decrease in size.

    Can someone give me the poop on getting SQL to release that space that I'm not using anymore? BOL is light in this department.

    thanks,

    greg

  • March 24, 2005 at 4:17 pm

    #548882

    Before you drop that column, did you find out how many of the email has attachment?  Sometimes it is true that you don't have that many attachments, thus not saving enough space even if you drop that column out.

    The other way to help with this is to move the old email as your company policy and archive it to a different database on a different server.  Data is data, you can get it as long as you don't delete it.  It might be on a slower machine but you still got what you need.

     

    mom

     

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply