SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Homework – May 2018 – Create a database

homework-clipart-homework-alert-free-images-at-vector-clip-art-onlineI’ve really got to make a plan here at some point. So far I’ve been just putting down things as I think of them. In September I had you set up a home lab, and in October I had you download and attach some sample databases. What would have been the next logical step? Creating a database of your own of course. So here we are 7 months later and it’s time to create your own database!

Things to make you go hmmm.
  • What are the default locations for the data and log files.
  • How big is our database going to be?
  • Do the default locations have sufficient space or should we get more? Or maybe put this database on it’s own set of drives?
  • Do we have an estimate for the growth over the next month (during setup)? Year? 5 years?
  • Are we going to need in-memory tables? Filestream?
  • What type of recovery do we need? Full (point in time recovery) or Simple (recover only to the times full and differential backups ended)?
  • Who is going to need access to this database? (This is a test database so just us obviously.)
  • Do we need any database level configuration changes to be different from the default? (compatability level, MAXDOP, collation, etc)
  • Depending on your needs you might even be asking: Should this database be in the cloud?


Requirements for your new database.

(10 points for each task.)

  • The database should have 3 filegroups. One of them read only.
  • The non-PRIMARY read/write filegroup should have two files.
  • The non-PRIMARY read/write filegroup is the default filegroup.
  • Make each new data file 100mb (leave PRIMARY as the default) and the log file 50mb.
  • The size for the file in the read only filegroup should have a max size of 100mb.
  • The autogrowth on the log should be 10mb with a max of 1gb.
  • The autogrowth on the PRIMARY filegroup should be 50mb with a max of 500mb.
  • The autogrowth on the non-PRIMARY filegroup should be 100mb with a max of 10gb.
  • The database should be in SIMPLE recovery.
  • Make the collation SQL_Latin1_General_CP1_CS_AS.
  • MAXDOP should be 2.


Now if you want to get ahead, next month I will have you take this database, and change literally each of these settings.

And as always, yes, I realize that if you are a Sr DBA all of this is easy stuff. This isn’t really targeted at you. It’s meant for Jr and Mid-level DBAs who may not be quite as comfortable with some of these tasks. That said, if you are a Sr DBA, I challenge you to create this database through a script, without using BOL.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...