Here we will discuss about the common SQL Server misconception that most DBA feel that they are real. Some myths are mostly about the behaviour change of a feature or command, so newbies take care of that, some myths may not be the case for all scenarios.

MYTH-1 - DBCC CHECKDB causes Blocking:

The first misconception about the Dbcc Checkdb has that it causes Blocking. Why?Because it takes locks by default. Well its untrue! When you run DBCC CHECKDB, it prevent log truncation, then run crash recovery on transaction log itself. It take table schema-stability logs(SCH_S) that block table scans and its schema modifications.

MYTH-2 - Data File Shrink does not Affect Performance:

Its a biggest myth many DBAs have that Shrinking does not affect the performance. Its totally untrue! Shrinking Datafile is evil! Shrinking data file not only affects the performance of the Database while running or after running database but also it causes massive Index Fragmentation, which definitely poor down the performance of the range scan. Log shipped data, transaction replication, database mirrored data, they all get affected after its run. Shrinking Data file can turn out to be expensive in terms of I/O ,locking, transaction log generation. The only option when your data file wont get affected by its performance is when you use WITH TRUNCATEONLY.

MYTH-3 - Database Corruption : What to do & What Not to do

Some common misconception about Database Repair are as follow:

1. Repair commands does not cause data loss- Its totally untrue! It depends, If you are using REPAIR_ALLOW_DATA_LOSS , then there is chances of losing data.

2. You can run repair commands without running DBCC CHECKDB: Wrong! Database Repair command (DBCC CHECKALLOC, DBCC CHECKTABLE, CHECKTABLE) are options to check consistency check. DBCC CHECKDB allow you to check the physical as well as logical integrity of all objects within specified database. These commands do not have to be run separately from DBCC CHECKDB.

3. If you have run repair command, everything’s is fine: No, this is one of the biggest misconception DBA’s have. You should always run DBCC CHECKDB command after every repair command. Regularly running DBCC CHECKDB command is able to find deeper level corruption. Also, Repair commands may cause data loss. So you cannot rely on the Repair commands, that if you have ran it, you will not get any issue.

4. DBCC CHECKDB fixes every type of corruption: No! There are some corruption we think of, DBCC CHECKDB might repair , but, ultimately, it leads to huge amount of data loss. Issues like PFS Page header corruption, Common value corruption, Metadata corruption are some of the corruption issues that cannot be fixed by the repair command. The command end up performing time-consuming data extraction from the corrupt database and losing alot of data. So! Make sure to take regular backup of your database.

5. Interrupting long running operation can cause transaction: Its untrue!! If you stop or interupt any long running transaction, it doesn’t mean that corruption has occur. The corruption wont happen unless it has physical level corruption like bug in SQL Server.

MYTH-4 - Restarting SQL Server / Rebooting Windows Server or Attach/Detach SQL Database, can Fix Database Corruption:

Its a myth! Not even a single thing can help in fixing SQL Server Database Corruption. The corrupt page need to be restored or repaired.Also, Trying to detach your database when it is in SUSPECT mode lose every possibility of repairing corrupt database.

If you are thinking, rebooting helps in fixing the corruption, then you are invariably wasting your time. You need backup and redundant system to failover to.

MYTH-5 - Misconception around Backups in SQL Server

1. It require full backup after breaking the log backup chain: No! All you need is data backup that bridges LSN gap. You dont need to take full backup to continue with log backups.

2. Full Backup and Differential Backup clears the log: Its untrue! Log backup consists of all the details of logs since last log backup. The only thing that clear log in full or bulk logged recovery model is a Log Backup.

3. Its a misconception that backups test existing page checksums. No! It does so only when you use WITH CHECKSUM option.

4. The size of log backup will be the size of log: Its completely false! When log operation occur, there is transaction log space reserved incase the transaction rollback.

5. You can take backup of Mirror database- Untrue! You cannot take backup of either mirrored database or database snapshot. Mirror database is only accessible by database snapshot. And you cannot take backup of both things.

6. No neccessity to backup your system databases- No!! you should always take backup of master, msdb, model.As system db contains configuration of databases like security info, agent jobs, backup history etc. So make a habit of taking regular backup of these databases too!

Its a myth that you should have plan of good backup strategy, No!!! Plan Restore Strategy!!!

Conclusion:

Beaware! Maybe you are in a myth in some cases around SQL Server Database. The above blog discusses the myth around Database corruption , Backup and shrinking.