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

SQL Homework – June 2018 – Alter a database

homework-clipart-homework-alert-free-images-at-vector-clip-art-onlineHere we are again. The beginning of another month. At the beginning of each month I put out a SQL Homework post with the intent of getting junior DBAs/database developers to try new things, mid-level DBAs/database developers to practice things they should already know, and Sr DBAs/database developers a boost to their ego (Ha! That’s easy!) or a pointed reminder that they may not know everything (oops, missed that). Last month I asked you to create a database with a rather specific set of requirements. This month

It’s a year later, new things to make you go hmmm.
  • How close was our 1-year growth estimate? Do we need to adjust our 5-year estimate?
  • Do the current file locations have sufficient space or should we get more? Do we need to move some of this database (or all of it) to a new set of drives to free up space for other databases?
  • How is the space for our backups? Are we able to maintain the number of backups we want on disk? What is our total retention of backups? I.e. are we moving them from disk to tape or some other storage? If so how long do they stay on that other storage?
  • Security changes over time, should we do a quick review and clean up any permissions that are no longer needed?
  • What is our current database version? Is a new one out with features we want? Should we start thinking about an upgrade?


Changed requirements for your database.

(10 points for each task.)

  • Turns out they want a new version of this database. Make a duplicate copy of the database named [dbname]_v1.
  • Rename the existing database [dbname]_v2.
  • Rename the logical and physical file names as well.
  • We are running low on space. Move the file for the primary filegroup to a new location.
  • Add a table to the read_only filegroup. Put 10 rows of data in it.
  • We need a new filegroup. It should have two files. 100mb each, 10mb auto growth, max size 300mb.
  • Make the new filegroup the default filegroup.
  • Change the database to FULL recovery.
  • We’ve added some new CPUs, change MAXDOP to 4 for this database only.
  • The new cardinality estimator isn’t working out too well for us. Turn it off for this database only.
  • Bonus points: Set this database up to allow for memory optimized tables.
  • 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 change up this database through one or more scripts, without using BOL. And if even that’s easy, do it in a different scripting language. Powershell maybe?


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...