SQL Homework – June 2018 – Alter a database

Leave a comment

June 4, 2018 by Kenneth Fisher

Here 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: