Best Practices for tempdb

The tempdb is a system database which is available for all the users connected to that instance of SQL Server.
This database is used to hold

• Temporary User Objects
• Internal Objects created by the Database Engine
• Row Versions that are generated by data Modifications

Since tempdb is used by all users and the system by itself for all their temporary operations, it is very much important that we optimize the tempdb and follow best practices for getting better performance out of it.

Below are some best practices that can be followed for tempdb

• Create the number of data files for tempdb based on the number of CPU's present on that system. Example: if the system has 4 CPUs then create 4 data files for tempdb with one Log file.
• Place tempdb files on the fastest available Drive.
• Isolate tempdb on a separate disk from other databases.
• Make all the data files of tempdb the same size.
• Disable autogrow option for all tempdb files and make sure you have enough space in them.
• Make sure to Commit or Rollback the transactions and if not done, then any space allocated for that transaction may not be released.

Piecemeal Restore

What is Piecemeal Restore?
Piecemeal restore is a process which allows databases that contain multiple filegroups to be restored and recovered in stages.

Which Version of SQL Server supports Piecemeal restore?
Piecemeal restore was introduced in SQL Server 2005 and is supported in SQL Server 2005 and later versions.

What are the Limitations?

The Database should contain multiple files or filegroups and should have at least One Read-Only filegroup.

Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

Types of Piecemeal Restore?

• Offline
  In an offline piecemeal restore, the database is online after the partial-restore sequence. Filegroups that have not yet been restored remain offline, but they can be restored as you need them after taking the database offline.
  All editions of SQL Server 2005 and above support offline piecemeal restores.
• Online
  In an online piecemeal restore, after the partial-restore sequence, the database is online, and the primary filegroup and any recovered secondary filegroups are available. Filegroups that have not yet been restored remain offline, but they can be restored as needed while the database remains online.
  SQL Server 2005 Enterprise Edition and later versions support Online piecemeal restores.

T-SQL Query to find List of Tables that do not have Primary Key

We know that Primary Key is a must for setting up articles in Transactional Replication.
This query list those tables which do not have Primary Key in that database.

USE <DatabaseName>
GO
SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name]
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
Order by name
GO

Update statistics for all user database

In one of my previous post "What is Statistics in SQL Server", I had explained about what is Statistics.
Today, I am posting a Query which will update statistics on all user database in that Instance.

EXEC sp_MSForeachdb 'USE [?];
IF ''?'' not in (''master'',''model'',''msdb'',''tempdb'',''distribution'') 
 AND DATABASEPROPERTYEX(''?'',''Updateability'') = ''READ_WRITE''
BEGIN
Print ''Updating statistics for database "'' + ''?'' + ''"''
EXEC sp_updatestats
END'

This Query will exclude the system database and those database which are not in Read_Write status.

Database Corruption and Recovery

What is database corruption?

Inconsistency in the internal structure of the database with respect to data or log files is known as database corruption

What causes database Corruption?

• Physical Inconsistency - One or more access paths to the data may be invalid.
• Logical Inconsistency  - One or more pointers to the data may be invalid

How to detect Corruption?

• Status of database set to “suspect”
• Evident from Error log and/or Event Viewer logs
• Consistency errors from DBCC CHECKDB
• T-SQL Queries or application throwing corruption related error (Database might be online without being suspect)
• SQL Server Startup Failure
• Failure while Restoring or Attaching databases

What are the causes that leads to database corruption?

• Hardware failure event
• Power outage
• SAN crash
• NTFS or File System Corruption
• FTDisk Errors
• Bad Block or Disk Corruption
• Outdated  or Faulty Drivers
• Failed Restore/Attach
• Due to Bad File/Page
• Abnormal Termination of the process
• Corrupted Header
• User Error
• File Deletion/Renaming
• File Swapping
• 3rd Party Software
• Filter Drivers
• Outdated/Faulty Device Driver

Recovery Flows

• master Database

• model Database

• msdb Database

• tempdb Database

• User Database