The Apprentice: Detective work

SQL Server Database Training Apprentice Detective

I decided to see how much knowledge and familiarity the Apprentice has retained in the area of Database Properties.

The Setup:

I wanted to simulate a customer engaging him to “Look at the database” because it doesn’t “seem right.”  I created a Sales database on his machine and mis-configured some items, taking it far away from best practices.  It came on line, but had issues 🙂

The first thing he noticed and asked about was the lack of tables or other objects.  My response was that the “customer” was installing a 3rd party application which has two steps:  Create the Database, and then Create the Objects.   They thought the results of step one were odd and called us.

What he found:

  • Auto-Shrink enabled (very common for 3rd party apps)
  • Insane file names (Logical: Bill and Log_Ted, Physical: Taco.mdf – data and Burrito.Mdf – log file)
  • Auto-grow for data file of 1 MB, capped at 5 MB
  • Auto-grow for log of 100%, unlimited

No backups had been taken, but I don’t recall if he found that or if we even discussed it.  He is well aware of backups and recovery models.

This took us down a conversation of best practices, and how to rename files in a database, both Logical and Physical.  What’s really fun is when you want to look up the ALTER DATABASE command to rename the physical files and the internet connection is down…so no MSDN or Google!

We used the GUI to create a script for changing the logical names, then modified that for the physical files instead.  And he already knew that the actual files on disk had to be changed as well.

He did really well on this, with very little prompting.   Well done!

Thanks for reading!

Kevin3NF

 

Leave a Comment

Sign up for our Newsletter

%d bloggers like this: