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

2011/7/16 18:14:17英文系列评论(0)浏览(2123)

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 really 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 couple of articles which I have written on how to prevent log file 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 the 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 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.

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 when 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 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 another drive.

Here are a few blog posts I have written 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!