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 🙂
