Diary of a Techie

I have a client whose database is growing at the rate of about 60 gb per month. It is quite obvious that the db will be huge in no time at all. Remember, as the DB grows, so does the related files: Logs, backups etc.

So I googled for a way to solve this and found : http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx Where the author talked about sql2005’s database partitioning abilities.

Just go to the site and you will find code samples to pattern your sql script to convert the database to use partitioned file groups. I will discuss one problem I enountered here. This has to do with constraints that already exists in the database.

I had to drop the constraints and recreate them using the datecreated field that I wanted. On another table, I had to Alter table to add a new column. And then the application I was working with did not know about this datecreated field, so i had to figure a quick an easy way to go around it.

For this issue, i used the default value for the field. Every month, i would just set a current months date to simulate the partition function that uses the date created function to place records into the files.

However, the BOL (books on line) stated: Alter Table alter column column name set default (‘value’)
This doesnt work. Another author suggested that we use the drop/add constraint as the default value is really a constraint. And this is the one that worked for me.

Hope this diary entry helps you.

Filed under: Computing, Sharepoint