SQL SERVER – Database Worst Practices – New Town and New Job and New Disasters

This blog post is written in response to the T-SQL Tuesday hosted by Amit Banerjee. I know Amit personally and am a very big fan of his community activities. I read his blog, read his comments, follow his tweets, and most importantly, I reach out to him when I feel like talking SQL.

Amit has selected a very interesting subject – Best Practices. When I read the subject, I can directly relate this subject to my real world interactions. I have seen so many developers and DBAs making fatal mistakes in the early days of their career. Most of the time, they get another chance because they are new to the job or that particular task, which they are doing for the first time. However, such excuses cannot be made for experts and senior professionals. They are expected to perform and carry out the right practices always.

Instead of writing best practices, I am going to write about few of the worst practices which I have quite often seen in the real world. Some of them are so bad that I often wonder how those who still use them are able to continue on the job so far. Let us go over few of the worst practices I have observed in the industry.

My Log File is Growing Too Big – I Truncated the Log without Taking Backup

This has to be the numero uno of worst practices. Every time I come across such a statement, I almost miss a heartbeat. Before I continue, let me confess that at the beginning of the career, I practiced the same. After a while, I learned it the hard way. This is never a good practice; truncating the log file is not an option. The reason why I rate this as the worst practice: this one mistake can make database impossible to recover. When someone truncates the log file without taking backup, there is no chance of recovery.

Here are a couple of articles which I have written on how to prevent log files from growing too big:

• How to Stop Growing Log File Too Big
• Log File Growing for Model Database – model Database Log File Grew Too Big
• master Database Log File Grew Too Big

I Shrink My Database Daily to Regain the Space

This is one of the popular worst practices. I have seen administrators shrinking the database at the end of the day to gain the space only to lose it the very next day. Shrinking is a VERY BAD operation. It increases fragmentation, reduces the performance, and wastes resources. I strongly advise not to do it.

Here are few articles I had earlier written on this subject.

• Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
• SHRINKDATABASE For Every Database in the SQL Server
• Shrinking NDF and MDF Files – Readers’ Opinion
• SHRINKFILE and TRUNCATE Log File in SQL Server 2008

Clustered Index Makes the Table Sort Every Time. I Do Not Have Clustered Index on Any Table

For an OLTP system, Index is very important and clustered index is the most important index (in my opinion). Clustered index forces order to the table and removes the ‘forwarding records’ problem from the database. Personally, I consider tables without clustered index performance to be unacceptable. In my OLTP system, I always recommend that all tables should have clustered index.

Here is a quick script that can help in identifying a table without clustered index in the database.

• Find Table without Clustered Index – Find Table with no Primary Key
• Clustered Index on Separate Drive From Table Location
• Observation – Effect of Clustered Index over Nonclustered Index

TempDB is Not Important; so I Will Keep It on my Slow Drive

Personally, I have tremendous respect for TempDB. Even though it resets every time the server restarts, this is the single most important database that is shared among all the other databases in the system. This database is used for sorting, temporary objects, triggers, row version and in other operations. Keeping it on the slow drive is not the solution, but the reality is that it will just create many performance-related problems in the overall system. If your TempDB is becoming full, move it to another drive.

Here are a few blog posts I wrote on TempDB.

• T-SQL Script to Find Details About TempDB
• TempDB is Full. Move TempDB from one drive to another drive
• Reducing Page Contention on TempDB
• Improvements in TempDB
• TempDB Restrictions – Temp Database Restrictions
• Ideal TempDBFileGrowth Value

I am Confused between Full, Differential, and Log Backup

Inability to understand the proper recovery model is another worst practice. I have people restoring many differential backups while restoring the database. I quite often see that log file backup interval is so huge it is more than differential backup interval. There are so many factors which can lead to disaster and data loss, leading to people to look for a new job in a new town at times. If you are confused regarding what is tail log backup, then stop, and learn from online books before implementing the backup strategy. Even if you are not responsible for implementing the backup strategy, I would still suggest you to read how to carry out proper backup as you never know when it will land heavily on your job card!

Here are few interesting write-ups on this subject on this blog:

• Backup Timeline and Understanding of Database Restore Process in Full Recovery Model
• Restore Sequence and Understanding NORECOVERY and RECOVERY
• Mirrored Backup and Restore and Split File Backup
• Restore Database Without or With Backup – Everything About Restore and Backup
• Restore Database Backup using SQL Script (T-SQL)

There are many more practices I can write, but I think these five are the top worst practices. Feel free to post your opinions and suggestions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)