Sunday, 30 July 2017

How to create a transaction log backup in SQL server

Any transactions that happen in the database with respect to SQL server from the login to the select into statements are stored in an integral part of SQL server called “the transaction log”. A transaction log backup is the current situation of the database irrespective of having a full backups or differential backups. Having log backups along with the full backups is highly recommended in SQL server. Usually log backups are taken every 15 minutes to recover the database from a special point of time in case the database is deleted. Ideally, any database will consist of one .ldf file (logical file). However, having multiple log files will never improve performance of a database.

T-SQL Command to take transaction log backup:

STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:
  • Right click on the “database” name
  • Click the option “Tasks”
  • A new window pops with a drop down to select “Transaction log”
  • Select the “Transaction log” option with a specific disk under option “Back up to”
  •  Click “OK” to take the transaction log backup
The screenshot for the same is mentioned below:

Wednesday, 26 July 2017

Simple Way to Shrink Transaction Log File in SQL Server

Simple Way to Shrink Transaction Log File in SQL Server
A lot of times we come across log issues when a lot of transaction happens in the database with respect to .ldf file. At times, if the log records are not in proper place, then there are a lot chances to come across log file growth. Commonly seen backend transactions when the log growth could be either long running queries, bulk data insertions, index reorganization or update statistics. Also, there are chances of log growth when a scheduled log backup runs simultaneously with insertions or updates.

Before shrinking the log file, we should keep in mind to check the log sizes by using the below command:

dbcc sqlperf(logspace)

Additionally, if a log backup is already running due to a scheduled job, then we may get error. To check the active transactions in SQL server, use the below command:


However, the log file could be shrinked with the below TSQL command:

USE <Database_name>
DBCC SHRINKFILE (N'<database_name>_Log' , 0, TRUNCATEONLY)

If a single time shrink doesn’t work, then take log backup and again shrink the log file. TSQL command is shown below:

BACKUP LOG <Database_Name> TO  DISK = N'<Drive_Path>.trn'

Sunday, 23 July 2017

How to Kill a SPID in SQL Server?

If you are a DBA or database developer, you would have encountered a lot of blocking scenarios. Blockings are nothing but clash between two session IDs. A session is nothing but a transaction like insert, update or delete for a particular process, which is assigned in the form of SPID in SQL server.

Further, when we come across a situation to kill a session or SPID in SQL server that means we are trying to stop the transaction. But before killing the SPID, to see the sessions running currently could be viewed using the command as:


To kill the session in a particular database, use the below command:

USE <database_name>
KILL <SPID_Number>

        Example: Kill 278

Wednesday, 19 July 2017

How to turn on Database Mail XP’s in SQL Server?

How to turn on Database Mail XP’s in SQL Server?
While executing a step in SQL server, there is a need to configure the ‘database mail feature’ and ‘Show advanced options’.

We can even encounter an error as shown below while executing a step in job with sp_send_dbmail and extended stored procedures.

Error Message:

“Executed as user: NT SERVICE\SQLSERVERAGENT. SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', search for 'Database Mail XPs' in SQL Server Books Online. [SQLSTATE 42000] (Error 15281).  The step failed.”

To enable the above error, please use the below command.

sp_configure 'show advanced options', 1; 
sp_configure 'Database Mail XPs', 1; 


1. The Database Mail is currently not enabled when “0” in the command. This is the default option set in SQL server
Example: sp_configure 'Database Mail XPs', 0; 
2. The Database Mail can be enabled when “1” in the command

      Example: sp_configure 'Database Mail XPs', 1;

Sunday, 16 July 2017

Granting View Definition permission in SQL Server

Granting View Definition permission in SQL Server
In SQL Server 2008, a user given as pubic permission does have access to view the stored procs, function, triggers and indexes through either sp_helptext or sp_help. In this case, the SQL administrator gives the permission to the user or role to read the precise stored proc, functions or triggers, rather than giving superior permissions to the databases.
By executing the below statement by either developer or non-admins, the following errors are encountered:

USE Shop
Exec sp_helptext <function_name>

The object 'Shop' does not exist in database 'SQLArena' or is invalid for this operation.

However, to overcome the above-encountered error, the user or role is given with the view definition permission. The View definition permission for a user is given below:

To Grant public access to all users across all databases:

USE master

To Grant access to a particular user across all databases
USE master

To Grant public VIEW DEFINITION access to a database

USE Shop

To Grant VIEW DEFINITION access to a particular user to a database

USE Shop

To Grant VIEW DEFINITION access to a particular database and particular stored proc, function, or trigger

USE Shop
GRANT VIEW DEFINITION ON <SP_name, Function_name, Trigger_name> TO <User_name>

The above accesses can be removed using the “REVOKE” command as shown below:
USE Shop


Thursday, 13 July 2017

SQL Server - What is MDF, NDF, LDF?

SQL Server - What is MDF, NDF, LDF?
If you are working on SQL related concepts, then there would be high chances of you coming across .mdf, .ndf and .ldf files. These are files nothing but primary data files, secondary data files, and logical files.

Primary Data File

The Primary data file is the .mdf file, where all the objects such as tables, views, triggers, stored procs, functions, etc., are stored. There is always just one .mdf file for a database. Further, every database should consist of a starting point and this is handled by the primary file. The primary file is held responsible for pointing the other files of a database. .mdf files are stored in the physical storage drive of the computer.

Example: <database_name>.mdf

Secondary Date File

The Secondary data file is held responsible to hold all the data apart from the one handled by the primary data file. There are places where no secondary data files are present for a database. However, the majority times, there are two secondary data files present in a database. Similarly to primary files, the secondary files are as well stored in the physical storage drive of the computer.

Example: <database_name>.ndf

Log File

The log files hold the logical information of a database. It helps in recovering the database. A database can have one or more log files. The log files as well are stored in the local machine. Usually, log file sizes allotted in either a physical drive or mount drive can increase due to an increased transaction in the database. It can further be shrunk with special queries.

Example: <database_name>.ldf