September 5, 2011 at 7:13 am
Good day
I have a database with some 40 tables and few of them have Full Text Search (FTS) enabled upon them. I have inserted about 100,000 sample rows in one of the look tables with FTS. Today I noticed that my backup jobs are creating the backup files of over 300 MB while 90% of my tables are still empty. The MDF & LDF files have grown to just over 650 MB and 16 GB respectively. Can anyone help me understand what is going on and how can I control the size of the database?
I am attaching scripts for two tables one is the dependency table for the actual table with 100,000 rows having FTS enabled. I am also attaching the scripts for FTS indexes.
Thank you.
Kazim Raza
September 5, 2011 at 7:34 am
Looking at the scripts provided the DraftContent table has two nvarchar(max) columns. The storage size, in bytes, is two times the number of characters entered + 2 bytes for each of these two columns. I would hazzard a guess that this is the cause for the excessive growth. Is there a real need for these columns to use this data type?
Thanks
Chris
September 5, 2011 at 7:51 am
Thanks for your reply Chris.
This database is for a research study automation software where users will create their research papers/journals. A research paper might have many different sections (title & content/description).
Consider this link which is a PDF file on SQL Server data backup compression (https://docs.google.com/viewer?a=v&q=cache:Lpf17ipK32IJ:www9.unisys.com/eprise/main/admin/corporate/doc/41371394.pdf+sql+server+2008+white+paper+filetype:pdf&hl=en&gl=pk&pid=bl&srcid=ADGEESi9Cui0ZDEJJ-naHwPllbjf3Qk9Ml6VepgXvDNsNNJOGJ9Bmqyk4EVVyHGcqtfOwtxxwpjKC4dm6VYk4rfG9__YxZG_s0s-glXsJEBNIAHbiOCB05D-32tKPjnEBNOTiItm4zfw&sig=AHIEtbQKx0MBOs1ferW0FvkS5In-9bn6bg)
So all data (text/html/tables/images) in this PDF is in DraftContent table, title goes in DraftContentTitle and its data/description goes into DraftContentDescription column, hence nvarchar(max). GroupIdentifier field is more of a change set ID, to categorize multiple rows falling in same collection.
Is there a better way to handle this scenario? I am pretty sure we can modify the Group Identifier to restrict it to probably GUID(?) however not too sure about DraftContentDescription.
Thank you
Kazim Raza
September 5, 2011 at 8:31 am
Kazim,
Have you considered using FILESTREAM in SQL Server?
http://technet.microsoft.com/en-us/library/bb933993.aspx
Thanks
Chris
September 5, 2011 at 8:34 am
Chris, I won't be storing the PDF file itself in the database. Sections in the PDF I posted and their data are segmented / stored separately within the DraftContent Table and the PDF is generated on the fly by the application.
Thank you
Kazim Raza
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply