Saturday, February 25, 2012
Problems with an XML column....?
I have a table with an XML column that stores report definitions. For development purposes I have to delete and reinsert these reports on a fairly regular basis (300+ reports). When I do so, I notice the following: The size of the data file increases by roughly 30MB. The file I am loading the XML from (thru an SSIS package) is only 4MB. If I shrink the database, I can get rid of 20MB, but I still net a 10MB increase. The package gets run frequently. Right now my database is at about 250MB when it should be about 40-50MB. I am trying to avoid having to drop all constraints, truncating the table, and reapplying the constraints. If I disable the step in my package that deals with the XML column, I see no change in the database. It appears to be the DELETE that causes the most damage, as I have tested the package on purely an insert-basis and on a delete-insert-basis. Has anyone seen behavior like this?Since no one has responded in over a month, I am going to close this one out myself. I ended up dropping all of the constraints, truncating, and reapplying the constraints. I still have some growth in my data files but it has slowed considerably. I read that empty pages could be left behind by a DELETE statement, but I have never since file growth like this. Perhaps, SP1 addresses this. I don't know.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment