Usage of DBCC And DMV`s

1)    What is the use of DBCC commands?
DBCC stands for database consistency checker. There are many DBCC command in SQL Server. We generally use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

They are grouped as:

 Maintenance: Maintenance tasks on Db, filegroup, index etc. Commands include DBCC CLEANTABLE, DBCC INDEXDEFRAG, DBCC DBREINDEX, DBCC SHRINKDATABASE, DBCC DROPCLEANBUFFERS, DBCC SHRINKFILE, DBCC FREEPROCCACHE, and DBCC UPDATEUSAGE.

Miscellaneous: Tasks such as enabling tracing, removing dll from memory. Commands include DBCC dllname, DBCC HELP, DBCC FREESESSIONCACHE, DBCC TRACEOFF, DBCC FREESYSTEMCACHE, and DBCC TRACEON.

Informational: Tasks which gather and display various types of information. Commands include DBCC INPUTBUFFER, DBCC SHOWCONTIG, DBCC OPENTRAN, DBCC SQLPERF, DBCC OUTPUTBUFFER, DBCC TRACESTATUS, DBCC PROCCACHE, DBCC USEROPTIONS, and DBCC SHOW_STATISTICS.

Validation: Operations for validating on Db, index, table etc. Commands include DBCC CHECKALLOC, DBCC CHECKFILEGROUP, DBCC CHECKCATALOG, DBCC CHECKIDENT, DBCC CHECKCONSTRAINTS, DBCC CHECKTABLE, and DBCC CHECKDB. 

2)    What are the DMV’s in SQL Server ?
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
Important:
Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.
3)     What is back process for the DBCC?
When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:
•    Against master, and the instance of SQL Server is running in single-user mode.
•    Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
•    Against a read-only database.
•    Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
•    Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.
4)    Can you explain DBCC CheckDB?
DBCC CHECKDB is a Algorithm  which at  backend checks that:
1. Object Integrity
2. Linkages for text, ntext, and image pages
3. Index and data pages are correctly linked.
4. Indexes are in their proper sort order.
5. Pointers are consistent.
6. The data on each page is reasonable (Allocation Checks).
7. Page offsets are reasonable.
5)    what is the exact use of DMVs?
DMVs can be used in the gathering of baseline information and for diagnosing performance problems. Few important dmvs are:
1. sys.dm_os_performance_counters
2. sys.dm_db_index_physical_stats
3. sys.dm_db_index_usage_stats
6)    Please explain DMV Categories?

Category	Prefix
Common Language Runtime (CLR)	Sys.dm_clr_*
Database	Sys.dm_db_*
Indexing	Sys.dm_db_index_*
Database Mirroring	Sys.dm_db_mirroring_*
Execution	Sys.dm_exec_*
Full-Text Search	Sys.dm_fts_*
I/O	Sys.dm_io_*
Query Notifications	Sys.dm_qn_*
Replication	Sys.dm_repl_*
Service Broker	Sys.dm_broker_*
SQL Server Operating System	Sys.dm_os_*
Transactions	Sys.dm_tran_*
Change Data Capture	Sys.dm_cdc_*
Object	Sys.dm_sql_*
Resource Governor	Sys.dm_resource_governor_*
SQL Server Extended Events	Sys.dm_xe_* Sys.dm_cryptographic_*
Security	Sys.dm_provider_* Sys.dm_audit_*

7)    What are all the SQL Server  Dynamic Management Views(DMV) and Dynamic management functions(DMF)  available in SQL Server?
Use below query to list out all available DMVs present in a SQL Installation :-
SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE ‘dm[_]%’
ORDER BY name
8)    How many types of DMVs are there?
There are two types of dynamic management views:
a.    Server-scoped DMV: Stored in Master Database
b.    Database-scoped DMV: Specific to each database
9)    Explain DBCC inputbuffer()?
DBCC INPUTBUFFER  returns the last sql statement  issued by a client. The command requires the SPID
DBCC INPUTBUFFER (SPID)
10)    List few DMVs for space usage related information ?
sys.dm_db_file_space_usage –  Lists space usage information for each file in the database. Reports on unallocated extent page count.
sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated
sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity
11)    While viewing activity on SQL Server , for example, sp_who2 – the status column displays different states – RUNNABLE – SUSPENDED – RUNNING.   Could you explain the difference?
Some background information on the SQL Schedulers , will make understanding the RUNNABLE – SUSPENDED – RUNNING  model clearer.
Schedulers are made up of three parts . A thread cycles though these three parts
1) Processor
2) Waiter list – threads waiting for resources. Use Sys.dm_os_waiting_tasks to view resource waits for the resources
3) Runnable – thread has all the resources and waiting for the processor. Explore runnable status with the  sys.dm_os_schedulers and sys.dm_exec_requests  DMVs
This leads us into the RUNNABLE – SUSPENDED – RUNNING
1)      RUNNING – thread is executing on the server
2)      SUSPENDED – thread is waiting for resources to become available.
3)      RUNNABLE – the thread is waiting to execute on the processor
12)    Why does RUNNING transition to SUSPENDED ?
Thread is executing and if waiting for a resource moves to SUSPENDED into the waiter list
13)    Why does SUSPENDED  transition into RUNNABLE?
The resource is now available and moves to the bottom of the RUNNABLE queue.
14)    Why does RUNNABLE transition into RUNNING?
Top spid at head of RUNNABLE queue moves to processor
15)    List 5 inportant DMVs for Index analysis.
sys.dm_db_index_usage_stats :- Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.
sys.dm_db_missing_index_details :- Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.
sys.dm_db_missing_index_columns :- Returns information about database table columns that are missing an index, excluding spatial indexes.
sys.dm_exec_query_stats :- Performance statistics for cached plans. The information is only available while the plan remains in the cache.
sys.dm_db_index_operational_stats :- Returning IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert,update, and delete
16)    What is use of DBCC DBREINDEX?
This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
17)    Which DBCC command is used to shrink database files?
DBCC SHRINKFILE :- This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.
18)    Which DBCC command is used to store the Procedure cache related information?
DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.
19)    Explain DBCC TRACEON & DBCC TRACEOFF?
DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. DBCC TRACEOFF – This command turns off a trace flag.
20)    How can you check if any transaction is running on a database or not?
We can use DBCC OPENTRAN to check any running transaction on the database. It is one of the most commonly used DBCC command along with DBCC CHECKDB, DBCC SHRINKFILE, DBCC SQLPERF(logspace) etc.
21)    Can anyone predict how long DBCC Checkdb will run on any database?
As far as estimating how long DBCC CHECKDB will take to run on a given database, it’s very difficult to tell because there are so many variables involved. The following are some factors that affect DBCC CHECKDB’s run time:
•    The size of the database. This one’s not so obvious—it’s not the size of the database that matters, it’s the amount of data that’s in it.
•    The load on the system. DBCC CHECKDB is extremely resource hungry—I like to say it’s the most resource-intensive operation you can run on SQL Server. Therefore, if the server is already heavily loaded, DBCC CHECKDB will be competing for resources and will take a lot longer to run.
•    The capabilities of the system. If the database being consistency checked is very large and structurally complicated, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to provide the resources DBCC CHECKDB needs, slowing it down.
•    The options specified. If the WITH PHYSICAL_ONLY option is specified, the amount of processing that DBCC CHECKDB does is drastically cut down, which usually leads to a significant reduction in run time.
•    The complexity of the database schema. The more features that you use in the database, the more structures there are to be consistency checked, so DBCC CHECKDB will take longer to run.
•    The corruptions that are found. Some corruptions require deeper reprocessing of data to figure out exactly where the corruption is. This can lead to a much longer run time for DBCC CHECKDB.
•    The tempdb configuration. DBCC CHECKDB uses a lot of memory to store intermediate consistency checking data, and that storage usually spills out to the tempdb database. If tempdb isn’t configured well, it can be a bottleneck for DBCC CHECKDB and slow it down.
As you can see, there are too many factors involved to be able to make a good guess. The best way to know how long DBCC CHECKDB will take is to run it.
22)    What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?
DBCC DBREINDEX is an offline operation is used to rebuild the indexes of a table dynamically. This operation requires enough space in the data files. If the space is not enough DBCC DBREINDEX may be unable to rebuild the indexes.
DBCC CHECKDB is used to produce a consistent view of the data by performing a physical consistency check on indexed views, validating integrity of the indexes, objects etc. in earlier versions of SQL, this required locking. Newer versions involve reading the transaction log of the oldest active transaction. REDO and UNDO of the transactions affect the volatile changes to available free space.
23)   How can DMVs help with performance tuning?
Helps to find out the queries that are causing memory or CPU pressure on your system
Helps to investigate caching, and query plan reuse
Helps to identify index usage patterns
Helps to track fragmentation in clustered indexes and heaps
Gives full details on blocking and blocked transactions
24)    What permission does a user need to access the DMV’s?
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions (e.g OS, IO, Threads, tasks etc). These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions (e.g Index, Tables, partition, file etc). These require VIEW DATABASE STATE permission on the database.
25)    How are DMV’s and DMF’s changing the memory consumptions of SQL Server? 
consider the dm_exec_* which store the results of the current workload.
DMV’s are in-memory structures and are anyway’s used by SQL Server internally. It is with SQL Server 2005 that we started exposing them in an official manner rather than doing bit-manipulations with some DBCC commands. Hence there is nothing to be worried about the load or memory consumptions. It is not as alarming as you think.
26)    Which DMV give me query plan or I will use old method to find query plan?
Below DMVs can be used to provide query plan related information :-
sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_exec_query_plan
27)    Name some Security related DMVs\DMFs.?
sys.dm_audit_actions
sys.dm_audit_class_type_map
sys.dm_cryptographic_provider_properties
sys.dm_database_encryption_keys
sys.dm_server_audit_status
28)    Mention some SQL OS related DMVs\DMFs.
sys.dm_os_buffer_descriptors
sys.dm_os_child_instances
sys.dm_os_cluster_nodes
sys.dm_os_hosts
sys.dm_os_nodes
sys.dm_os_memory_pools
sys.dm_os_performance_counters
sys.dm_os_process_memory
sys.dm_os_schedulers
sys.dm_os_memory_objects
sys.dm_os_workers
29)    Name few database related DMVs :-
sys.dm_db_file_space_usage
sys.dm_db_partition_stats
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
30)    Which DMVs are useful to gather information about database mirroring :-
sys.dm_db_mirroring_connections
sys.dm_db_mirroring_auto_page_repair
31)    What are the most important DMVs\DMFs from a DBA perspective.
Execution Related
•    sys.dm_exec_connections
•    sys.dm_exec_sessions
•    sys.dm_exec_requests
•    sys.dm_exec_cached_plans
•    sys.dm_exec_query_plans
•    sys.dm_exec_sql_text
•    sys.dm_exec_query_stats
Index Related
•    sys.dm_db_index_physical_stats
•    sys.dm_db_index_usage_stats
SQL Server Operating System
•    sys.dm_os_performance_counters
•    sys.dm_os_schedulers
•    sys.dm_os_nodes
•    sys.dm_os_waiting_tasks
•    sys.dm_os_wait_stats
I/O Related
•    sys.dm_io_virtual_file_stats
32)How to resolve Allocation and consistency Errors?
Run DBCC checkdb DBName
Repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SomeDB).

What is purpose of database Schema?

Ø  What is Schema ?

      A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
                                      (OR)
We can simply telling , Schema is nothing but Collection of  Database Objects under a database pricipal.

You can assign a user login permissions to a single schema, so that the user can only access the objects they are authorized to access.

Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Ø  Advantages of Database Schema?
     * To group the similar tables
     * To Grant permissions on more than one table at a time
     * No need to change the owner of table(s) if the user is deleted.
     * Objects are not attached to any specific user account. So if the user account needs to be deleted we don’t have to worry about changing the objects owners.
     * It simplifies managing permissions on Schema objects. If the schema owner’s account is to be removed from the database, the ownership of the schema can be transferred to other user without breaking any code.
     * Use of Schema’s with database roles can simplify managing security. I have tried to explain it in more detail later in this post.
     * Overall, maintenance of database become easier and I will recommend the use of schemas if you’re working with more than 20 tables.
Ø  How to create Schema?
    When you create any objects in SQL Server 2005/2008/2008R2/2012/2014 they are allocated to a default schema which is “dbo” (database owner account) in most of the cases. However it is recommended to explicitly specify the schema name when creating objects. 
Syntax:To create Schema
            Create Schema <SchemaName>
Eg: 
Step-1: Use RDYadav
                Go
                Create Schema Library
Step-2: Create table Library.Books(Bid int,Bname Varchar(40))
                Create Table Library.Students(StudId int,Sname varchar(40) ) 
Ø  How to Transfer one schema into another Schema?   
Syntax: ALTER SCHEMA newschema TRANSFER oldschema.TABLEName
Eg: ALTER SCHEMA Library TRANSFER dbo.Pencils

Syntax:  To Grant schema level permissions
Grant ....on schema::[schemaName] to <username>/<rolename>

Eg: Granting permission on Library schema to Madhuri Dixit
       Use RDYadav
       Go
       GRANT SELECT on Schema::[Library] to Madhuri Dixit
       
DENY INSERT ON SCHEMA::Library TO Madhuri Dixit      


Ø  How to know the what permission having a particular user ?

      SELECT state_desc, permission_name, 'ON', class_desc,SCHEMA_NAME(major_id),'TO',USER_NAME(grantee_principal_id)  FROM sys.database_permissions AS Perm  JOIN sys.database_principals AS Prin  ON Perm.major_ID = Prin.principal_id AND class_desc = 'SCHEMA' WHERE major_id = SCHEMA_ID('Library')    AND grantee_principal_id = user_id('TestUser')

      --AND    permission_name = 'SELECT'

Database Backups and Restore-1

1) What are database backups? 

A Database backup is a copy of SQL Server data that can be used to restore and recover the data in case of any failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. There is another option to take Transaction Log backups when the database recovery model of a database is Full. 

2) Types of Database backups? 

We have below type of backup available in SQL Server 2012. 

Full Backup

Differential Backup

Transaction Log Backup

Copy-Only Backup

File or Filegroup Backup

3) What is Full Database backup?

 A full backup is a backup of the entire database that contains all the data and log file records needed to recover the database to the point in time when backup completed. Full backup should be a part of backup strategy for all the business-critical databases. 

Full database backup contains the complete set of data needed to restore and recover a database to a consistent state. It serves as a baseline for all other backups.

 --Back up the AdventureWorks as full backup

BACKUP DATABASE AdventureWorks TO DISK = N'D:\AdventureWorks.bak'

4) What is Differential Backup? 

Differential backup backups up only the data that has changed since the last full backup. A differential backup is not a stand-alone backup it needs a full backup to act as a baseline. For larger databases differential backups is common in order to save space and reduce the backup time.

 In addition to being smaller and faster than full backup, a differential backup makes the restore process simpler. When you restore using differentials you must first restore the full backup followed by the most recent differential backup that was taken.

--Back up the AdventureWorks as differential backup

BACKUP DATABASE AdventureWorks TO DISK = N'c:\AdventureWorksDiff.bak' WITH DIFFERENTIAL

5) What is Transaction Log Backup?

Log backups can be taken only if the recovery model of the database is Full recovery or Bulk-logged recovery. Simple recovery model does not allow transaction log backup because the log file is truncated automatically upon database checkpoints. 

Log backups are taken between full backups to allow point-in-time recovery with the exception of log backups containing bulk-logged records. Without Transaction log backups you can restore data only till the time when the full or differential backup was taken.

--Back up the AdventureWorks transaction log

BACKUP LOG AdventureWorks TO DISK = N'c:\AdventureWorksLog.trn'

 6) What is File or File Group backup? 

Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database. Another potential benefit of having filegroup backups is that if the disk on which a particular file resides fails and is replaced, just the file can be restored instead of the entire database.

BACKUP DATABASE AdventureWorks FILEGROUP='PRIMARY', FILEGROUP ='Secondary'

TO DISK ='D:\AdventureWorks_FileGroup.bak'

7) What is COPY ONLY Backup?

 Copy-only backups are introduced in SQL Server 2005 and are used to create a full database or transaction log backup without breaking the log chain. A copy-only full backup can’t be used as a basis for a differential backup, nor can you create a differential copy only backup.

 --Back up the AdventureWorks database as copy only

BACKUP DATABASE AdventureWorks TO DISK = N'c:\AdventureWorks.bak' WITH COPY_ONLY

--Back up the AdventureWorks transaction log as copy only

BACKUP LOG AdventureWorks TO DISK = N'c:\AdventureWorksLog.trn' WITH COPY_ONLY

8) What are Split Backups?

 SQL Server have one more feature to database backups can split to multiple files. Using this way SQL Server run the multiple thread of database backups for each files and can be completed faster comparatively with less time and IO. 

BACKUP DATABASE AdventureWorks

TO DISK = ‘C:\AdventureWorks_1.bak’

DISK = ‘D:\AdventureWorks_2.bak’,

DISK = ‘E:\AdventureWorks_3.bak’

GO

9) What is Mirrored backup?

 Mirrored database backups can be used to create multiple copies of the database backups on different locations. 

BACKUP DATABASE AdventureWorks

TO DISK = ‘C:\AdventureWorks.bak’

MIRROR TO DISK =  ‘D:\AdventureWorks_mirror.bak’

GO

10) What is Tail log backup? 

A tail-log backup captures any log records that have not yet been backed up (the tail of the log) to prevent work loss and to keep the log chain intact. Before you can recover a SQL Server database to its latest point in time, you must back up the tail of its transaction log. The tail-log backup will be the last backup of interest in the recovery plan for the database.

 Tail log backup is taken in below ways:

 If the database is online follow below syntax:

BACKUP LOG [database name] TO [backup device] WITH NORECOVERY

If the database is offline (example a corrupted database which does not start]

BACKUP LOG [database name] TO [backup device]  WITH CONTINUE_AFTER_ERROR

11) What is Native Backup Compression? 

Database backup compression helps in creating a database backup in a compressed format (Supported SQL Server 2008 onwards based on the Edition).  Enabling database backups to use compression can be done in one of two ways. You can either configure all backups for the instance to be compressed (it is disabled by default), or you can issue the WITH COMPRESSION command for a specific database backup. 

12) How can you enable Database compression on all the native backups? 

Backup compression can be enabled at the SQL Server instance level as below.

 USE master;

 GO

 EXEC sp_configure 'show advanced option', '1';

 RECONFIGURE

 GO

 EXEC sp_configure 'backup compression default', '1';

 RECONFIGURE WITH OVERRIDE;

 GO

 EXEC sp_configure 'show advanced option', '0';

 RECONFIGURE

 GO

13) Is it possible to add password to a backup file in SQL Server 2012 version?

WITH password option is not available any more with SQL Server 2012 onwards.

14) In which recovery model, Transaction Log backups are possible?

Transaction Log backups are possible in Full and Bulk Logged recovery model.

15) What all operations are minimally logged when the database is in Bulk Logged Recovery mode? 

Bulk import operations (bcp, BULK INSERT, and INSERT… SELECT). For more information about when bulk import into a table is minimally logged.

SELECT INTO operations.

Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data. Note that minimal logging is not used when existing values are updated.

WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns. Note that minimal logging is not used when existing values are updated.

CREATE INDEX operations (including indexed views).

ALTER INDEX REBUILD or DBCC DBREINDEX operations.

DROP INDEX new heap rebuild (if applicable).

 16) How do you know if your database backups are restorable? 

We can use RESTORE VERIFY ONLY command to make sure that the Database backups are restorable.

 17) What is the database that has the backup and restores system tables?

 MSDB database contains information about the backup restore.

 18) What are the backup and restore system tables?  What do each of the tables do? 

Here are the backup and restore system tables and their purpose:

 backupfile – contains one row for each data file or log file backed up

backupmediafamily – contains one row for each media family

backupmediaset – contains one row for each backup media set

backupset – contains one row for each backup set

restorefile – contains one row for each restored file

restorefilegroup – contains one row for each restored filegroup

restorehistory – contains one row for each restore operation

19) For differential backups, how is the data determined for those backups? 

DCM page contains information about the extent which are changed after the Full backup. Diff. backup process reads information about the changed extents and those extents are added in the differential backup. 

20) In a situation with full, differential and transaction log backups being issued for a database, how can an out of sequence full backup be issued without interrupting the LSN’s?

 Backup with COPY ONLY option can be used in such a situation.

 21) How can I verify that backups are occurring on a daily basis? 

We can verify the backup history of the database that backups are happening or not. 

backupset table in msdb 

22) What is the meaning of the values in Type column in backupset table. 

This column tells us about the backup type.

 Backup type. Can be: 

D = Database

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

23) What are the permissions required to perform backup? 

The user must be a member of either of the below roles 

Backup:

sysadmin – fixed server role

db_owner –  fixed database role

db_backupoperator – fixed database role

24) Is there any option to prevent All successful SQL Server backup entries from writing to the SQL Server Error Log?

 Yes – We can enable the trace flag 3226.

 25) Assume that we have to take a backup of the database with a backup size of 90 GB. There is no space available in a single disk drive instead there are 4 different drives where we have 25 GB free space on each drive. How can you perform the backup to three different drives? 

We can take backup in split backups.

 BACKUP DATABASE AdventureWorks

TO DISK = ‘D:\Backup\AdventureWorks1.bak’,

DISK = ‘E:\Backup\AdventureWorks2.bak’,

DISK = ‘F:\Backup\AdventureWorks3.bak’,

DISK = ‘G:\Backup\AdventureWorks4.bak’

 26) Explain the below Backup script?

 USE master

GO

BACKUP DATABASE [Test] TO

DISK = N'D:\ Backups\ test_full_native_1.bak'

WITH FORMAT, INIT,

NAME = N'test- Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

 FORMAT – This option tells SQL Server whether or not to overwrite the media header information. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized (NOFORMAT will preserve it).

INIT – By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it’s useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.

NAME – The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.

SKIP – Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn’t care if any backups existing in the backup set have been marked for availability to be overwritten.

NOREWIND – This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.

NOUNLOAD – When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.

27) What are the Backup and Restore Enhancements?

 An enhancement introduced in SQL Server 2012 SP1 Cumulative Update 2 is enable backup and restore from the Windows Azure Blob storage service from SQL Server using TSQL

 28) What are the limitations with Windows Azure Blob storage service? 

The following are limitations specific to this release: 

The maximum backup size supported is 1 TB.

In this implementation, you can issue backup or restore statements by using TSQL or SMO. A backup to or restoring from the Windows Azure Blob storage service by using SQL Server Management Studio Backup or Restore wizard is not currently enabled.

29) What are the restrictions on the Database backups operations?

 Some typical examples include the following: 

You request a file backup of specific files, but one of the files is not online. The operation fails. To back up the online files, you can omit the offline file from the file list and repeat the operation.

You request a partial backup, but a read/write filegroup is offline. Because all read/write filegroups are required for a partial backup, the operation fails.

We request a full database backup, but one filegroup of the database is offline. Because all filegroups are implicitly included in a full database backup, this operation fails.To back up this database, you can use a file backup and specify only the filegroups that are online.

30) What all operations are prohibited when the database backups are running? 

Operations that cannot run during a database backup or transaction log backup include the following:

 File-management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.

Shrink database or shrink file operations. This includes auto-shrink operations.

If you try to create or delete a database file while a backup operation is in progress, the create or delete operation fails.

31) What is Back up WITH CHECKSUM? 

SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database. 

The BACKUP statement optionally computes a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page. When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup.

DBCC COMMANDS WITH EXAMPLES

SQL Server – DBCC Commands

DBCC (Database consistency checker) are used to check the consistency of the databases.
The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server .
The DBCC Commands broadly falls into four categories
        Ø  Maintenance

Ø   Informational

Ø   Validation

Ø   Miscellaneous

  v  Maintenance Commands

Performs maintenance tasks on a database, index, or filegroup.
1. CLEANTABLE – Reclaims space from the dropped variable-length columns in
   tables or index views.
   DBCC CLEANTABLE (‘AdventureWorks’,'Person.Contact’,0)
2. DBREINDEX – Builds one or more indexes for the table in the specified database.
 (Will be removed in the future version, use ALTER INDEX instead)
USE AdventureWorks
DBCC DBREINDEX (‘Person.Contact’,'PK_Contact_ContactID’,80)
3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.
DBCC DROPCLEANBUFFERS
4. FREEPROCCACHE – Removes all elements from the procedure cache
DBCC FREEPROCCACHE
5. INDEXDEFRAG – Defragments indexes of the specified table or view.
DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)
6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database
DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)
7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database
or empties a file by moving the data from the specified file to other files in the same filegroup,
allowing the file to be removed from the database.
USE AdventureWorks;
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)
8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.
DBCC UPDATEUSAGE (AdventureWorks)Informational Commands
 
   v   Informational Commands

              Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.
DBCC CONCURRENCYVIOLATION
2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.
DBCC INPUTBUFFER (52)
3. OPENTRAN – Displays information about the oldest active transaction and the oldest
distributed and nondistributed replicated transactions, if any, within the specified database.
DBCC OPENTRAN;
4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format
for the specified session_id.
DBCC OUTPUTBUFFER (52)
5. PROCCACHE – Displays information in a table format about the procedure cache.
DBCC PROCCACHE
6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target
 on the specified table
USE AdventureWorks
DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid)
7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the
specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG (‘HumanResources.Employee’);
8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also
 be used to reset wait and latch statistics.
DBCC SQLPERF(LOGSPACE)
9. TRACESTATUS – Displays the status of trace flags.
DBCC TRACESTATUS(-1)
10. USEROPTIONS – Returns the SET options active (set) for the current connection.
DBCC USEROPTIONS
  v  Validation Commands

Performs validation operations on a database, table, index, catalog, filegroup, or allocation
 of database pages.
1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)
2. CHECKCATALOG – Checks for catalog consistency within the specified database.
DBCC CHECKCATALOG (AdventureWorks)
3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a
specified table in the current database.
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.
DBCC CHECKDB (AdventureWorks)
5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and
indexed views in the specified filegroup of the current database.
USE AdventureWorks
DBCC CHECKFILEGROUP
6. CHECKIDENT – Checks the current identity value for the specified table and,
if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT (‘HumanResources.Employee’)
7. CHECKTABLE – Checks the integrity of all the pages and structures that make up
 the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE (‘HumanResources.Employee’)
  v  Miscellaneous Commands

Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.
DBCC xp_sample (FREE)
2. TRACEOFF – Disables the specified trace flags.
DBCC TRACEOFF (3205)
3. HELP – Returns syntax information for the specified DBCC command.
– List all the DBCC commands
DBCC HELP (‘?’)
– Show the Syntax for a given DBCC commnad
DBCC HELP (‘checkcatalog’)
4. TRACEON – Enables the specified trace flags.
DBCC TRACEON (3205)

LOGSHIPPING INTERVIEW QUESTIONS AND ANSWERS

1)    What is Log shipping and purpose of Log shipping?
Log shipping is nothing but Shipping transaction log backup periodically one server to another server

2)    What is Primary Server, Secondary Server & Monitor Server?
a)    Primary Server:- It is a production server it holds the original copy of the database. log shipping configured in primary server.
b)    Secondary Server:- it holds stand by copy of the database.we must initialise the database on secondary server by restoring a backup from primary server using either recovery or stand by option.
c)     Monitor Server:- An optional Server is called as Monitor Server that records history and status of backup, copy and restore operations and raises alerts if any operations fail. The Monitor Sever should be on separate server to avoid losing critical information. Single Monitor Server monitors multiple Log shipping configurations.

3)    What are the Jobs running for Log shipping and explain them?
Log shipping having four operations which are handled by SQL Server Agent Job.
a)  Backup Job: - Backup job is created on Primary Server instance and it performs backup operation. It logs history on the local server and monitor severs and deletes old backup files and history information.
b)  Copy Job: - Copy Job is created on Secondary server instance and it performs copies the backup files from primary sever to secondary server. It logs history on the secondary server and monitor server.
c)   Restore Job: - Restore Job is created on the Secondary server instance and it performs restore operation. It logs history on the local server and monitor sever and deletes old files and history information.
d)  Alert Job: - If a Monitor Server is used, the Alert Jobs is created on the Monitor server instance and it raises Alerts if any operations have not completed successfully.

4)    Requirements for Log shipping?
a)    SQL Enterprise or workgroup ,standby installed on all server involved in logons.
b)    data should be in full or bulk recovery model.
c)     case sensitive setting should be same on both servers.

5)    How to configure Log shipping?
a.   Choose Primary Server, Secondary Servers, and optional Monitor server.
b.   Create a File share to keep Transaction log backups (Best to place on a separate computer)
c.   Create a folder for each Secondary server into which transaction log backup copies.
d.   Choose Backup Schedule for Primary Database
e.   Choose Copy and Restore Schedules for Secondary Database
f.    Choose Alert Job schedule for Monitor Server if configured

6)    What are permissions required for Log shipping?
We must have sysadmin on each server instance to configure Log shipping.

7)    In Logshipping which Recovery Models can we used?
We can use either full or bulk logged recovery model for log shipping.

8)    Where you monitoring Log shipping and how?
The following methods can use for monitoring Log shipping.
a)  Monitor server (History Tables):- Monitor Server tracks all statistics, status and errors that could be happen during Log shipping.
1)  Log_shipping_monitor_primary:- Stores primary server status
2)  Log_shipping_monitor_secondary:- Stores secondary servers status
3)  Log_shipping_monitor_history_detail:- Contains history details for logshipping agents.
4)  Log_shipping_monitor_error_detail:- Stores error details for log shipping jobs.
5)  Log_shipping_monitor_alert:- Stores Alert Job ID
b)  System Stored Procedures (MSDB):- System Stored procedures gives the history information about the specified server that are configured in Log shipping.
1)  sp_help_log_shipping_monitor (Run at Monitor Server)
2)  sp_help_log_shipping_monitor_primary @Primary_Database = ‘DBName’ (Run at MS)
3)  sp_help_log_shipping_monitor_secondary @ Secondary_Database = ‘DBName’ (Run at MS)
4)  sp_help_log_shipping_alert_job (Run at Mon Server)
5)  sp_help_log_shipping_primary_database @ Database = ‘DBName’ (Run at Primary Server)
6)  sp_help_log_shipping_secondary_database @ Database = ‘DBName’ (Run at Sec Server)
c)   Transaction Log shipping Status report (Summary Reports):-  This report shows the status of log shipping configurations for which this server instance is a primary, secondary or monitor.
d)  SQL Server Agent Job Histor:- Right click on Jobs > View history
e)  Checking the SQL Server Log

9)    How to failover secondary server, when the Primary Server fails?
If the Primary Server will become un-available, do the following steps.
a)    Take the Tail of Log from Primary server if possible.
b)    Restore Tail of log into all Secondary Database
c)     Remove Log-shipping configuration from Primary Server
d)    Select any one of Secondary server and bring into online with Alter Database DBName set Online
e)    Right click on Primary Database and Generate script for Users and Logins.
f)     Then move the script to Secondary server to create Users and Logins
g)    Re-configure log shipping from New Server (Secondary server)

10) What are errors occurred in Log shipping?
There are two errors are occurred during Log shipping
1)    14420:- This error occurs when the Backup job fails
2)    14421:- This error occurs when the Restoring job fails
11) What is.tuf file.?
while restoring log back up on secondary server uncommited transctions are come to .tuf file.

MIRRORING DATABASE INTERVIEW QUESTIONS.

1) What is Database Mirroring? What are the benefits of that?
Database mirroring is an option to improve the availability of a databases which supports automatic fail over with no loss of data.
Benefits:-
Increases data protection
Increases availability of a database
Improves the availability of the production database during upgrades

2) What are the prerequisites for Database Mirroring?
Database base should be in full recovery model
Database name same on both servers
Server should be on same domain name.
Mirror database should be initialized with principle server

3) What are the Restrictions for Database Mirroring?
a) Maximum 10 databases per instance can support on a 32-bit system.
b) Database mirroring is not supported with either cross-database transactions or distributed transactions.

4) Explain about Principal, Mirror and Witness Servers?
1) Principal Server:-  One Server serves the database to client is called Principal server and it having original data. Can have only one Principal Server and it has to be on a separate server.
2) Mirror Server:- Other server instance acts as a hot or warm standby server is called Mirror server and it having copy of database.
3) Witness Server:- The witness server is an optional server and it controls automatic failover to the mirror if the principal becomes unavailable. To support automatic failover, a database mirroring session must be configured in high-availability.

5) In which Operations are running the Operating Modes?
Asynchronous:- Under asynchronous the principle server does not wait for the response from the mirror server after sneding logbuffer.
Synchronous:- Under synchronous the principle server sends the log buffer to the mirror server and then waits for a acknowledgement from the mirror server.

6) What are the Operating Modes and explain them?
a. High Availability (principle+mirror+witness) :- High-availability mode, runs synchronously. Requires a Witness Server instance. The Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.
principle is not available the witness and mirror will decide automatic failover .mirror becomes online.
b. High Protection (princeiple+mirror):- High-protection mode, runs synchronously. Always commit changes at both the Principal and Mirror. automatic failover is not possible.
c. High Performance:- High-performance mode, runs asynchronously and the transaction safety set to off. The Principal server does not wait for a response from the mirror server after sending the log buffer. The principal server running nice and fast, but could lose data on the mirror server.

7) What is End Point? How u create end point?
An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.
Creation of an end point:-
Create endpoint <endpoint name> State=started/stopped/disabled
as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

8) What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?
The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.
To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints

9) Which Trace flag is used in Mirroring?
Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring.
To set trace flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (add)

10) In which Recovery model we can use in Mirroring?
In mirroring the principal and mirror databases are used only full recovery model

11) What is Role-switching?
Inter changing of roles like principal and mirror are called role switching.

12) What is the syntax to stop the Database Mirroring?
Alter database <database name> set partner off

13) How to configure Mirroring?
a) Choose Principal Server, Mirror Server, and optional Witness server.
b) The principal and mirror server instances must be running the same edition either Standard Edition or Enterprise Edition
c) The Witness server instance can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition
d) Mirror database requires restoring a recent backup and one or more T.log backups of the principal database (with Norecovery)

14) How to monitoring Mirroring?
There are six methods are available for monitoring the Database Mirroring
a) Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.
To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.
b) SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.
c) SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.
d) Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.
e) Profiler:- Profiler many events are providing the status of the Database mirroring
f) System Stored Procedures:-
⦁ sp_dbmmonitoraddmonitoring
⦁ sp_dbmmonitorchangemonitoring
⦁ sp_dbmmonitorhelpmonitoring
⦁ sp_dbmmonitordropmonitoring

15) What is Hardening?
As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.

16) What is Log buffer?
A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database’s log buffer.
17) How to Set a Witness Server to Database Mirroring?
SSMS:- Right Click on Principal Database > Tasks > Mirror > Click on Configure Security > Provide the End point for Witness server > Click oK
T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://prasad.local:5024' (Do this from the Principal Server)

18) How to Remove a Witness Server from Database Mirroring?
SSMS:- Right Click on Principal Database > Tasks > Mirror > Remove TCP address from the Witness > Click oK
T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS OFF

19) How to Setup Fully Qualified Names for Database Mirroring?
I. FQDN Error
One or more of the server network addresses lacks a fully qualified domain name (FQDN).  Specify the FQDN for each server, and click Start Mirroring again.

The syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
Section 1.01
Section 1.02 II. RECTIFYING FULLY QUALIFYED NAMES
1) To View Endpoints:-SELECT * FROM sys.database_mirroring_endpoints;
2) Remove existing all Endpoints from Principal, Mirror and Witness servers :- DROP ENDPOINT [ENDPOINT_NAME]
3) Adding "local" as the primary DNS suffix as follows:-
a) Right-click My Computer, and then click Properties. The System Properties dialog box will appear.
b) Click the Computer Name tab.
c) Click Change. The Computer Name Changes dialog box will appear.
d) Click More. The DNS Suffix and NetBIOS Computer Name dialog box will appear.
e) Enter the appropriate DNS suffix for the domain.
f) Select the Change primary DNS suffix when domain membership changes check box.
g) Click OK to save the changes, and then click OK to exit the Computer Name Changes dialog box.
h) Click OK to close the System Properties dialog box, and then restart the computer for the change to take effect.
4) Reconfigure the Database mirroring either GUI or T-SQL

20) What are the Database Mirroring states?

1) SYNCHRONIZING:-
The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.
At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
2) SYNCHRONIZED:-
When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.
If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.
If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
3) SUSPENDED:-
The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.
  A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session
  SUSPENDED is a persistent state that survives partner shutdowns and startups.
4) PENDING_FAILOVER:-
  This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.
  When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
5) DISCONNECTED:-
  The partner has lost communication with the other partner