SQL Server Articles, Tutorials, News, Tips & Tricks
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.
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.
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.
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.
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.
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.
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.
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.
List of SQL Server Replication Articles and Tips which can help DBAs and Developers.
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.
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.
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.
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.
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.
This article outlines some of the major database backup and restore enhancements which is available in SQL Server 2014.
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.
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
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.
List of SQL Server Management Studio Articles and Tips which can help DBAs and Developers to better use SSMS.
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.
List of SQL Server Database Corruption Related 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.
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.
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.
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.
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.
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.
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).
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 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 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 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 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.
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.
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.
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 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.
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.
This article explains the steps to be followed by a DBA or a Developer to Install SQL Server 2014.
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.
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.
|Latest SQL Server Tips|