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 the SQL server called “the transaction log”. A transaction log backup is the current situation of the database irrespective of having full backups or differentialbackups. Having log backups along with the full backups is highly recommended in the 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 the performance of a database.

T-SQL Command to take transaction log backup:

BACKUP LOG [SQL_TEST] TO  DISK = N'C:\SQLArena\LOG\SQL_TEST.trn'

STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:

1. Right-click on the “database” name

2. Click the option “Tasks”

3. A new window pops with a drop-down to select “Transaction log”

4. Select the “Transaction Log” option with a specific disk under the option “Back up to”

5. Click “OK” to take the transaction log backup

The screenshot for the same is mentioned below:

Related post:

"Shrink log file of a database 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 the proper place, then there are a lot of 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 the error. To check the active transactions in SQL server, use the below command:

sp_whoisactive

However, the logfile could be shrunk with the below TSQL command:

USE <Database_name>

GO

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'

WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

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 the clash between two session IDs. A session is nothing but a transaction like an insert, update or delete for a particular process, which is assigned in the form of SPID in the 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, see the sessions running currently could be viewed using the command:

EXEC SP_WHO2 ACTIVE

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

USE <database_name>

GO

KILL <SPID_Number>

Example: Kill 278

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; 

GO 

RECONFIGURE; 

GO 

sp_configure 'Database Mail XPs', 1; 

GO 

RECONFIGURE 

GO

Further,

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;

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 procedures, 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

Go

Exec sp_helptext <function_name>

ERROR:

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:

Grant public access to all users across all databases:

USE master

GO

GRANT VIEW ANY DEFINITION TO PUBLIC

Grant access to a particular user across all databases

USE master
GO
GRANT VIEW ANY DEFINITION TO <User_name>

Grant public VIEW DEFINITION access to a database

USE Shop

GO

GRANT VIEW ANY DEFINITION TO PUBLIC

Grant VIEW DEFINITION access to a particular user to a database

USE Shop

GO

GRANT VIEW ANY DEFINITION TO <User_name>

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

USE Shop

GO

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

GO

REVOKE VIEW DEFINITION TO <User_name>

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 Data File

A 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 of times, there are two secondary data files present in a database. Similar 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

Related interesting article:

"Check Available and Free Disk Space in SQL Server"

"Identifying Last Full, Differential and Log Backup Time for All Databases in SQL Server"