February 24, 2010 at 3:26 pm
Greetings all. So according to the microsoft 70-432 book, creating a database you should create a second filegroup and make it default as this "allows the system files to stay on the primary and any new objects created will be on the secondary file". I also read that SQL Server 2008 uses an algorithm to synchronously fill each file up.
This is not working on my database;
I created a staging database to load as400 files from. So I created the database, configured the files and filegroups so that there are 2 file groups. The second one (the one that is NOT primary) is set to default. THEN I created all the tables and indexes, and then ran my ETL process to load the database. After loading the 30gb, I noticed my primary file was 30GB. I also noticed I could drop the secondary file group at will with no errors so I know for a fact no data is in them.
Am I missing a setting or something? That seems fairly straightforward and simple to me...
Link to my blog http://notyelf.com/
February 24, 2010 at 3:32 pm
How did you create the tables?
February 24, 2010 at 3:34 pm
It just occurred to me as you sent that. I scripted the tables from the "generate scripts" feature of a previous poorly built database. I forgot it automatically scripts the 'on primary'...sigh 😀
Link to my blog http://notyelf.com/
February 24, 2010 at 4:51 pm
Yup that was the problem. After doing this for 10 years you would think I would no not to trust Microsoft's auto generating scripts by now 😀
Link to my blog http://notyelf.com/
February 25, 2010 at 5:22 am
It happens to all of us. 🙂
So, did you drop and recreate the tables, or change the clustered indexes so that they were on the secondary filegroup?
March 1, 2010 at 5:10 pm
😀
I just dropped and rebuilt the database. It is a staging database we pull from an AS400 so there are no clustered indexes on them. Plus through various bits of my own code and pre-generated scripts from the scripter in SQL Server, I can create the staging database and all its structures, permissions, etc in a few seconds and just run the ETL to load the data back in :-D.
Works like a charm now too!
Link to my blog http://notyelf.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply