Introduction
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 were 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 have practiced the same. After a while, I had 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 few articles which I have written on how to prevent a log file from growing 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 the resources. I strongly advise not to do it.
Here are a few articles I had earlier written on this subject.
Clustered Index Makes the Table to 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.
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 a 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 the other drive.
Here are a few blog posts I have written on TempDB:
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 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:
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)
