• Skip to content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

Header Right

  • Home
  • Blog
  • Services
  • Pocket DBA™
  • Speaking
  • Contact
  • About

T-SQL Tuesday: Still dealing with the same problems

October 11, 2016 by Kevin3NF 2 Comments

t-sqltuesday

This post is my contribution to the monthly blog party known as “T-SQL Tuesday.”  This month is being hosted by Andy Mallon, and covers  the topic We’re still dealing with the same problems.

The topic was inspired by a Tweet from Allan Hirt.   Allan is exactly right.

I entered the IT field officially ( meaning full-time) in 1998 as an Access Developer.  I’ve done desktop support, database dev, network, phone systems and for the last 17 years I’ve been working exclusively with SQL Server, v. 6.5 – 2016.

In all these years, we still have users that don’t know to reboot a frozen laptop, store passwords anywhere but a sticky note and call me to “fix the internet” (pro tip…its broken.  Go read comments on any news article.)

Narrowing this down to my slice of the IT world (SQL) and then more to Admin, and even further took some time.   The thing that I see and deal with the most came down to “blindly clicking OK and accepting all the defaults, all the time.”

I get it.

On my first SQL installation, I clicked OK and yes right through the install wizard and had a functional SQL 2000 install on my desktop.   On the C drive, which was almost full already.

Installation defaults that are going to bite you (not version specific, and the installer is getting better):

  • Files all on the C drive
  • One TempDB data file (improved in SQL 2016)
  • Backups on C drive
  • No automated backups
  • Allow SQL to use ALL the memory
  • Allow SQL to use ALL the CPUs
  • Builtin\Administrators group not default*
  • Compressed backup set to OFF

If you have any of the above, please research each and sort out why I listed it…you will learn more along the way than I can teach you here.  Each of these is documented extremely well by both Microsoft and the SQL Community.

* let the religious debate start in 3…2…1…

Another issue that is not done at install time, but shortly thereafter when you create your first database is the VERY common failure of setting up a new Database using the default FULL Recovery model, without a Transaction Log backup plan in place.

There are hundreds, maybe thousands of forum posts asking the same question: “My database is 1GB, but my T-log is 500GB and filled the drive.”

I had this question brought to me just 2 weeks ago at SQL Saturday Dallas by developer turned accidental DBA.  We spent 30 minutes discussing his database restore failures and why it took so long.   At the very end he mentioned that the .LDF file was HUGE compared to the .MDF file, and then walked off to the next session.  That HUGE .LDF is taking most of the restore time due to writing zeros into it well after the data is written.

Long story short:  Make sure you understand the defaults before you install, and implement a proper backup plan.  You can learn these easily and I’m expensive if I need to come fix them for you 🙂

Kevin3NF

 

 

 

 

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to email this to a friend (Opens in new window)
  • Click to print (Opens in new window)

Related

Filed Under: Accidental DBA, backup, Install, SQL, TSQL2sday

About Kevin3NF

Reader Interactions

Trackbacks

  1. Nothing New Under The Sun – Curated SQL says:
    October 12, 2016 at 7:05 am

    […] Kevin Hill reminisces and warns: […]

    Reply
  2. T-SQL Tuesday #83: Roundup - Andy Mallon - AM² says:
    October 16, 2016 at 7:50 pm

    […] Kevin Hill urges us to pause before clicking “next, next, next, next” on that installlation wizard in his post about blindly clicking OK and accepting the defaults […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

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

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Categories

  • Accidental DBA
  • Apprentice
  • Azure
  • backup
  • backup
  • Beginner
  • Career
  • Configuration
  • Cycling
  • Dallas DBAs
  • Deployment
  • Emergency
  • Encryption
  • EntryLevel
  • Fries
  • Goals
  • HADR
  • Index
  • Install
  • IRL
  • Management
  • Oracle
  • PASS
  • Performance
  • Personal
  • Pluralsight
  • PowerShell
  • Puzzle
  • red gate
  • Restore
  • Security
  • Speaking
  • SQL
  • sql 2005 log shipping suspect
  • sql injection
  • SQLSaturday
  • SSIS
  • SSMS
  • Summit
  • TIL
  • Tools
  • Training
  • Troubleshooting
  • TSQL
  • TSQL2sday
  • Uncategorized
  • vendor code modify
  • video
  • vNext

Copyright © 2019 · Genesis Sample on Genesis Framework · WordPress · Log in

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.