How to retrieve SQL Server databases backup information?

 SQL Server Backup Information:

This article illustrates how to retrieve SQL Server database backups information, depending upon what exactly you are looking for, this article will help you to locate desired information. Below are few scenarios.

1-     How to retrieve backup information

select * from msdb.dbo.backupset

 Above statement will provide you all the databases backup history, this is going to be our master table to retrieve various type of information about database backups.

2-     How to retrieve backup dates of all the databases including system databases?

                select database_name, backup_start_date,backup_finish_date from msdb.dbo.backupset

3-      How to find out how many times backup was taken of a particular database?

                select count(1) from msdb.dbo.backupset where database_name='Your_db_name'

4-     How to find out latest backup of a particular user database?

select database_name, Max(backup_finish_date) as LatestBackup_Time from msdb.dbo.backupset
       where database_name='Your_database_name'
       group by database_name

5-      How to find out type (Full, Diff and Tran) of database backup?

select database_name, case type
      when 'D'
           then 'FULL'

       when 'I'
                        then 'Diff'

              when 'L'
                                    then 'tran'

end as [Type of Backup]
       from msdb.dbo.backupset

Note: You can add columns as you wish from our master table dbo.backupset
   6-    How to find out if database backup compression is ON or OFF?
    use [master]
    SELECT name, case value when 0 then 'Compression not enabled' when 1 then 'compression Enabled'
      FROM sys.configurations
    WHERE name = 'backup compression default' ;

How to find out who dropped the database in sql server?

Well, it is such a vast topic, and there are multiple ways to find out however What I have used is undocumented process which helped me to retrieve the information really quickly. I don't mind using this statement as it really selects the information.

SUSER_SNAME([Transaction SID]) As UserName,
[Transaction Name],
[Begin Time],
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'dbdestroy'

First of all fn_dblog undocumented function which reads the SLQ Server Log, if you select all the columns, it has some great information however, just to illustrate this topic, I am looking for a user who has dropped the database.

If you notice, I have Transaction Name column filter with value "dbdestroy", you can explore the fn_dblog and see what exactly are the operation specified in transaction name column.

This statement can also be used in finding out who dropped the object in a particular database as well.

SUSER_SNAME function will convert Transaction SID which looks like (0x01050000000000051500000061AB3961A243C225349FF8F250A50000) into the readable username which was holding this session when drop database operation took place.  

What is MAXDOP in SQL Server and How can you configure it?

Maximum Degree of Parallelism (MAXDOP) is an option in SQL server which enables you to configure SQL Server to use available CPU/CPUs to run a single statement in parallel execution plan. MAXDOP configuration highly depends on SQL Server edition, CPU type and operating system.

MAXDOP : 0  -- This is default MAXDOP for SQL Server which enables SQL Server to use all the available CPUs for single statement.

MAXDOP: 2 -- You can use this option to limit SQL Server from using all the available CPUs. It doesn't have to be 2, you can setup any value depending upon your performance gain of your statement with different value of MAXDOP.

In my experience, I had to play with MAXDOP for certain statements coming from different servers in order to find best option of MAXDOP.

You can configure MAXDOP differently for each session as well by using hostname etc. using resource Governor or it can also be used in SQL Server Statement by setting up the MAXDOP value in the statement.

How to Change MAXDOP value in SQL Server using T-SQL?

You can use below statement to change MAXDOP value in sql server. Below statement doesn't need SQL Server services restarted in order to take effect.

sp_configure 'show advanced options', 1;
GOsp_configure 'max degree of parallelism', 2;

How to Change MAXDOP value using Management studio?

Step 1:
Right click on SQL Server instance, go to properties

Step 2:
Click on Options and go to Parallelism Option

Step 3:
Find Max Degree of Parallelism and change value to your desired value.

Fig 1. How to change MAXDOP value in SQL Server

How to Configure CPU Affinity? Step by Step process

This article illustrates how to configure CPU affinity in Windows 7, in higher windows versions, process is more or less the same.

Step 1. Right click on your task bar and click on start task manager

Fig 1. How to configure CPU Affinity

Step 2. Right click on any running application and click on go to process or you can click on Process Tab on the top menu.

Fig 2. How to configure CPU Affinity

Step3. Right click on the process that you would like to configure CPU Affinity of and click on set affinity 

Fig 3. How to configure CPU Affinity

Step 4. Select number of CPUs to assign it to this particular process, in my case, I have select CPU 1 and CPU 3 for this process.

Fig 4. How to configure CPU Affinity

Step 5. You can click on Resource Governor and select your process and view the CPU Usage.

Fig 5. How to configure CPU Affinity

What is CPU Affinity? Why is it helpful to configure CPU Affinity?

This article illustrates what is CPU affinity and why is it helpful to configure CPU affinity?

CPU Affinity:

CPU Affinity sometimes refers as CPU pinning enables a particular process or a thread to use one or more Central Processing Unit, it depends upon the number of CPU configured in your system. It enables particular thread or process to use only configured CPU rather than any CPU that's configured in your Operating system.

CPU affinity is really helpful in a scenario where you have multiple CPUs installed on your operating system and multiple applications running on the system such as SQL SERVER, SQL Server Reporting, SQL Server Analysis etc. Being DBA or SA you are noticing that SQL Server Reporting services are using almost all the CPU and all other applications are running really slow. You can/should configure SQL Server Reporting or any other CPU intensive application to use may be One out of 3 CPU available so that other applications can share CPU resources equally. Please configure according to your configuration, above example is just used for illustration purposes.

You can bind particular process to a particular CPU making sure other applications are not impacted due to CPU hog applications.  Please see my post how to configure CPU Affinity step by step.

How to Track Changes of a particular period? Specified duration

This article illustrates how to track the changes of specified duration, you can specify your interval i.e. begin data and time, end data and time to retrieve particular period changes using below query. If you would like to know further detail, please refer to Book Online.

DECLARE @begin_time datetime,
@end_time datetime,
@begin_lsn binary(10),
@end_lsn binary(10);

SET @begin_time = '2012-01-01 12:00:00.000';
SET @end_time = '2013-01-01 12:00:00.000';

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_YourTableName(@begin_lsn, @end_lsn, 'all'); -- you can query net changes, dbo is your shcema, please replace if you are using different schema
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_YourtableName(@begin_lsn,@end_lsn,'all');-- query all changes, dbo is your shcema, please replace if you are using different schema

How to disable change data capture (CDC) on a table?

This article illustrates how to disable Change Data Capture on a table that has already CDC enabled in a database.


USE Your_Database_Name
GOEXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',--Source Schema, if used different, please replace it with your own schema
@source_name = N'YourTableName', --Table name that needs CDC disable on
@capture_instance = N'dbo_YourTableName' -- This would be combination of your schema and tableName

How to enable Change Data Capture on one or more tables in a SQL Server Database?

You can enable CDC on one or more table using below T-SQL Statement

Use [Your_Database_name]

EXEC sys.sp_cdc_enable_table
@source_schema = N'Your_Schema_name', --Table Schema name such as dbo
@source_name = N'Table_name', --Table name that you would like to enable CDC on such as mytable
@role_name = NULL,
@filegroup_name = N'FilegroupName' -- File Group for Change Data Capture table such as Secondary

How to check if Change Data Capture is enabled or disabled on a SQL Server Database?

You can check whether Change Data Capture is enabled or disable on a particular database in SQL Server using below T-SQL Statement.

   use master
   SELECT [name], is_cdc_enabled
   FROM sys.databases where name = 'Your_db_name'

Results :

is_cdc_enabled= 0 ---CDC is disabled or not enabled on this database

is_cdc_enabled= 1 ---CDC is enabled on this database

How to disable Change Data Capture on SQL Server Database?

You can disable Change Data Capture of any database which has CDC enabled already using below T-SQL Statement.

     Use [YourDatabase_name]
     EXEC sys.sp_cdc_disable_db



How to enable Change Data Capture of a SQL Server database?

You can enable Change Data Capture of a SQL Server database using below T-SQL Statement.

      Use [YourDatabase_name]
      EXEC sys.sp_cdc_enable_db


How to set SQL Server database in Emergency mode? Why do we need to set SQL Server database in Emergency mode?

a) How to set SQL Server database in Emergency mode?

There are different ways that you set SQL Server Database in Emergency mode, however this article will illustrate using T-SQL Database configuration statement
Alter database Your_database_name Set Emergency

b) Why do we need to set SQL Server database in Emergency mode?
Sometimes you run into a situation when you don't have good backup or backup of database is corrupted. Your database went into suspect mode due corrupt log or data in the database. Emergency mode is one of the database recovery technique used to repair the data lose or rebuild the log so that you can bring the database in recoverable state and online.
It is sort of tricking the system tables to put the database in emergency mode so that you can perform all the repairs in order to recover the database. 

How to put SQL Server Database in Read Only Mode?

How to put SQL Server database in read only mode?
There are couple of ways you accomplish this task, using T-SQL database configuration statement or using SQL Server management studio. This article illustrates how to put database in read only mode using management studio.
1- Open SQL Server Management Studio
2- Connect to the SQL Server instance where your desired database resides
3- Right click on the database and go to properties
4- Click on Option
5- Navigate to State and find Database Read-Only option
6- Set Read-Only option to True

Fig 1. How to set SQL Server Database to Read only mode

Fig 2. How to set SQL Server Database to Read only mode
Fig 3. How to set SQL Server Database to Read only mode