MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter






























SQL Server Articles, Tutorials, News, Tips & Tricks


T-SQL Enhancements in SQL Server 2016 for Developers and DBAs

SQL Server 2016 introduces many new T-SQL features for Developers and Database Administrators. In this tutorial we will take a look at some of the new T-SQL enhancements in SQL Server 2016 along with relevant examples.

TRUNCATE TABLE WITH PARTITIONS T-SQL Enhancement in SQL Server 2016

Starting SQL Server 2016, Microsoft has introduced a new clause TRUNCATE TABLE WITH PARTITIONS which can be used to truncate all rows available within a partition.

DROP IF EXISTS T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces a new DROP IF EXISTS statement to DROP objects such as tables, columns, indexes, stored procedures, schemas, triggers and user-defined functions. DROP IF EXISTS statement can be used to check whether the object exists before it is dropped or removed from the database.

ALTER TABLE WITH (ONLINE = ON | OFF) T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces an interesting T-SQL enhancement to improve performance and reduce downtime ALTER TABLE WITH (ONLINE = ON | OFF). This statement will help you alter data types, change column/table collation values, change nullability settings, change column length, and many other options while the table remains online for user connectivity.

MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP T-SQL Enhancement in SQL Server 2016

SQL Server 2016 brings MAXDOP settings, for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP command. Database Administrators can leverage MAXDOP settings to above mentioned DBCC commands to achieve Max Degree of Parallelism.

ALTER DATABASE SET AUTOGROW_SINGLE_FILE T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces two new options to ALTER DATABASE statement which can be used to modify database files namely AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES. In the earlier versions of SQL Server to achieve similar functionality you could use the trace flag 1117. This would allow all the files of the database to auto grow at the same time if they are configured files to auto grow.

ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces two new options to ALTER DATABASE statement which can be used to modify database files namely AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES. In the earlier versions of SQL Server to achieve similar functionality you could use the trace flag 1117. This would allow all the files of the database to auto grow at the same time if they are configured files to auto grow.

COMPRESS and DECOMPRESS T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces two new string functions namely STRING_SPLIT and STRING_ESCAPE..

STRING_SPLIT and STRING_ESCAPE T-SQL Enhancement in SQL Server 2016

SQL Server 2016 introduces two new system functions namely COMPRESS and DEOMPRESS.

FORMATMESSAGE T-SQL Enhancement in SQL Server 2016

FORMATMESSAGE statement is enhanced in SQL Server 2016 to accept a msg_string argument. In the previous versions this function was used to form a message from text located in sys.messages.

SERVERPROPERTY T-SQL Enhancement in SQL Server 2016

SERVERPROPERTY is an inbuilt function which gives you information about the server instance.

How to Start SQL Server with Minimal Configuration

There can be certain scenarios when SQL Server is not starting because of configuration problems. During such scenarios one need to start an instance of SQL Server by using minimal configuration. In this article we will take a look at steps which one needs to follow to start SQL Server with Minimal Configuration to troubleshoot SQL Server configuration issues.

How to Start SQL Server in Single User Mode

There can be certain scenarios when one needs to connect to an SQL Server Instance in a Single User Mode by using the Startup Option -m. For example, the need could be to recover a damaged system database such as Master, Model, MSDB etc or you may want to change the server configuration options. In this article we will take a look at steps which one needs to follow to Start SQL Server in Single User Mode.

What are Virtual Log Files in SQL Server Transaction Log File?

SQL Server Database Engine defines the size of virtual log files dynamically while creating or extending transactional log files in SQL Server. Virtual log files will not have a fixed size and one cannot predict the number of VLFs within the transactional log file. Internally, SQL Server will always try to maintain a smaller number of VLFs. However, there is no way for a database administrator to configure or set a fixed size for VLFs in SQL Server.

Why it is Important to Customize Model Database Default Settings in SQL Server

In this tip, we will take a look at few of the very importance customizations which a DBA must do for a Model System Database so that all the newly created databases on the SQL Server Instance have better configuration that the default database settings.

Different Startup Options for SQL Server Database Engine Service

SQL Server Database Engine supports different startup options for SQL Server Database Engine Service. A database administrator can set the startup options very easily using SQL Server Configuration Manager. In this article we will explore startup options available in SQL Server Database Engine in detail.

How to Start SQL Server without TempDB Database or How to Start SQL Server with Minimal Configuration

This article demonstrates the steps that one needs to follow to start SQL Server without TempDB database or with minimal configuration.

Different Ways to Retrieve SQL Server Configuration Details

There are often times when as a DBA you need to quickly retrieve SQL Server Configuration Details for a SQL Server Instance. In this article we will explore different ways to retrieve SQL Server configuration details using TSQL queries.

How to Find which user deleted the database in SQL Server

In this article we will take a look at the steps which you can follow to quickly identify the user who deleted the user database in SQL Server.

Reasons Behind Large Transaction Log File Size when database is configured for Snapshot Replication

In this tip we will take a look at how to identify and fix sudden surge of transaction log file especially when a database is configured for Snapshot Replication.

SQL Server Replication Articles and Tips

List of SQL Server Replication Articles and Tips which can help DBAs and Developers.

Different Ways to Find Default Trace Location in SQL Server

In this tip we will take a look at different ways to find the location of default trace file in SQL Server and how to enable or disable default trace file in SQL Server.

Different Ways to Enable an Index in SQL Server

This article demonstrates how to enable an index in SQL Server. It is a very useful feature which will help you enable an index which was disabled earlier to check whether the index was really useful or not without actually dropping the index.

Different Ways to Disable an Index in SQL Server

Disabling an Index in SQL Server is a very useful feature which will help you identify whether the index is really useful or not without actually dropping the index.

Download SQL Server 2014 Developer Edition For Free

Microsoft has announced that the SQL Server 2014 Developer Edition will available for free to Visual Studio Dev Essentials members. This article explains how to register and then download SQL Server 2014 Developer Edition for FREE.

SQL Server Best Practice Auto Close Database Option Should Remain OFF

This article explains why it is important to leave AUTO CLOSE database option turned OFF for a Production or a Non Production SQL Server Database across all versions and editions of SQL Server.

SQL Server 2014 Backup and Restore Enhancements

This article outlines some of the major database backup and restore enhancements which is available in SQL Server 2014.

Using System Configuration Checker to Identify Potential Issues before Installing SQL Server

This tip demonstrates how to use System Configuration Checker Tool (SCC) to identify potential issues which one can encounter before even installing SQL Server on any new or existing server.

Discover SQL Server Components Installed Using SQL Server Discovery Report

In this article we will take a look at how to install and use SQL Server Discovery Tool to discover all the SQL Server Components which are already installed on a local server.

How to Fix 'Cannot execute script. Insufficient memory to continue the execution of the program (mscorlib)' error in SQL Server

Learn How to Fix 'Cannot execute script. Insufficient memory to continue the execution of the program (mscorlib)' error in SQL Server

How to Enable and Refresh IntelliSense in SQL Server Management Studio (SSMS)

In this tip we will take a look at the steps you can follow to quickly enable and refresh IntelliSence in SSMS to resolve this issue of IntelliSense not displaying latest schema changes.

How to Change Select Top 1000 Rows and Edit Top 200 Rows Default Value in SQL Server Management Studio

Starting SQL Server 2008, SSMS allows you to Select Top 1000 rows and Edit Top 200 rows. However in previous version of SSMS 2005 the only option was to open the entire table. Let us see how to alter select top 1000 rows and edit top 200 rows default value in SSMS.


SQL Server Management Studio Articles and Tips

List of SQL Server Management Studio Articles and Tips which can help DBAs and Developers to better use SSMS.

How to Detect Database Corruption Issues in SQL Server Using Suspect_Pages Table of MSDB Database?

This article explains how a DBA can use SUSPECT_PAGES table available in MSDB system database to keep track of corrupt pages in any of the SQL Server Database residing on an instance of SQL Server.

How to Fix SQL Server Database Corruption Articles and Tips

List of SQL Server Database Corruption Related Articles and Tips

SQL Server Analysis Services Administration Articles and Tips

List of SQL Server Analysis Services Administration Related Articles and Tips

Why SQL Server Log backups fails and Full backup succeeds? Learn how to Fix BACKUP detected corruption in the database log error in SQL Server

Due to an unplanned reboot of SQL Server one of the transactional log file of a database become corrupt. DBA noticed this issue once regular transactional log backup job started failing with BACKUP detected corruption in the database log error message. This article outlines the steps to be followed to fix BACKUP detected corruption in the database log error in SQL Server.

SQL Server Database Backup Options and Commands a Step by Step Tutorial with Examples

This tutorial will give you compressive information on different database backup options which is available to a DBA or a Developer within SQL Server. The detailed topics mentioned in this tutorial will help you understand database backups in depth whether you are new to SQL Server or an Expert.

Different SQL Server Recovery Models Step by Step Tutorial with Examples

Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. Based on the choice of Recovery Model, SQL Server decides which data it needs to retain within SQL Server transactional logs and for the time period.

SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples

SIMPLE recovery model as the name suggests it is the most basic recovery model which is available in SQL Server. In this recovery model every transaction is written to the transactional log file and once the transaction is completed successfully the data gets written to data file; SQL Server will automatically clear the space used by the transaction within transaction log file for newer transactions.

SQL Server FULL Recovery Model Step by Step Tutorial with Examples

In Full Recovery Model Point in Time recovery of the database is possible as long as you have all the valid database backups along with the transactional log tail backup file. In Full Recovery model all the transactions are retained within the transaction log file until the log file is backed up or the transactional log file is truncated.

SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples

When a database is configured to use a BULK-LOGGED Recovery Model then SQL Server will log minimal amount of information for operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX etc within the transaction log file there by reducing the transactional log file size. The Point in Time recovery of the database is possible only if the last transaction log doesn’t have any BULK-LOGGED operations.

Different Types of SQL Server Backups

SQL Server supports different types of backups for databases. In this tutorial we will discuss in detail each of the available database backup options and how to perform backups using TSQL scripts and with SQL Server Management Studio (SSMS).

Permissions Required to Take Database Backup in SQL Server

In order to take backups a user must be a member of DBCREATOR Server Role and DB_OWNER Database Role else you will receive the below mentioned error while performing backups.

FULL Database Backups in SQL Server Step by Step Tutorial with Examples

Full database backup represent the database at the time when the backup has finished. However, as the database size increases the full database backup takes more time to finish and it will also require more storage space. Hence for larger databases one must supplement a full database backup with a series of differential database backups and even transactional log backups if the database is in FULL or BULK-LOGGED recovery model. Transactional log backup is not allowed when the database is in SIMPLE recovery model this is by design.

DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples

DIFFERENTIAL database backup will only record the data which has changes since the last successful full database backup. This backed up data is known as differential base. When compared to full database backups the differential database backup are quick to complete and will also be smaller in size as this has only the changes thereby reducing the work loss exposure. Differential database backups are very useful especially when subset of database is modified most often than the rest of the database.

TRANSACTIONAL LOG Backups in SQL Server Step by Step Tutorial with Examples

Transactional log backup is only possible when your database is in FULL or BULK-LOGGED recovery model. With the help of Transactional Log backup one can achieve Point in Time recovery for the database in case of any disaster It is highly recommended to perform transactional log backups at regular intervals to minimize the loss of work and to truncate the transactional log.

COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples

COPY_ONLY backup is a special type of SQL Server Backup which is independent of the sequence of conventional SQL Server backups. Normally whenever you take a backup it will affect how later backups are restored. However, when you use COPY_ONLY backup feature of SQL Server one can take a backup of the database without affecting the overall backup and restore procedures for the database.

MIRRORED Backup in SQL Server Step by Step Tutorial with Examples

Using MIRRORED Backup feature a DBA can create up to 3 identical copies of a database backup. This feature is available in SQL Server 2005 Enterprise Edition and later versions.

FILE Backup in SQL Server Step by Step Tutorial with Examples

Using FILE backup one can backup SQL Server Data File individually. While backing up the database you can specify whole of FILEGROUP instead of specifying each database file individually within the FILEGROUP. In case if there is any file within the FILEGROUP OFFLINE may be because of file being restored then the whole FILEGROUP will be OFFLINE and cannot be backup up. A FILE backup can also serve as the differential base for differential file backups. For more information, see Differential Backups in SQL Server.

FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples

Using FILEGROUP backup one can backup all the data files within the SQL Server FILEGROUP individually. While backing up the database you can specify whole of FILEGROUP (i.e., PRIMARY or SECONDARY) instead of specifying each database file individually within the FILEGROUP.

PARTIAL Backup in SQL Server Step by Step Tutorial with Examples

PARTIAL Backups are designed for use under SIMPLE recovery model; thereby to improve flexibility to backup very large database which contain one or more read-only Filegroups. They are useful whenever you want to exclude read-only Filegroups. A partial backup resembles a full database backup, but a PARTIAL backup does not contain all the Filegroups. Instead, for a read-write database, a partial backup contains the data in the primary FileGroup, every read-write FileGroup, and, optionally, one or more read-only files.

TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples

A tail-log backup captures any log records which has not yet been backed up i.e., the tail of the transactional log to prevent any work loss and to keep the transactional log chain intact. Before you can recover a SQL Server database to its latest point in time, one must back up the tail of its transaction log without fail. Note that the tail log backup will be the last backup of which one can take to recovery the database.

How to Install SQL Server 2014 Step By Step Guide for DBAs

This article explains the steps to be followed by a DBA or a Developer to Install SQL Server 2014.

How to Identify Version of PowerShell Installed on Server Using TSQL Script

More and more DBAs are now move towards using PowerShell to perform routine Database Maintenance Tasks on SQL Server. In this article we will go through the quick steps to determine which version of PowerShell is installed on the Server where SQL Server is hosted using TSQL Script.

How to Migrate Existing Database to Partially Contained Database in SQL Server

SQL Server 2012 introduced a new feature namely Contained Databases. In this article we will learn the steps which a DBA needs to follow to Migrate an Existing Database to Partially Contained Database in SQL Server.

Share this Article





Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips





Latest SQL Server Tips