Restoring Damaged Pages in SQL Server
SQL Server introduces a new command which is an extension to the RESTORE command can help you perform the above operations. This will help you maintain availability of your database and yet restore portions of your database. (Only the portion that is being restored will be unavailable) FYI: This feature is currently available with SQL [...]
Database in Emergency Mode
In SQL Server 2005/2008/2012, updates to system tables have been completely restricted even for system administrators. But there may be situations when you may need to put the database into EMERGENCY mode in SQL Server 2005/2008/2012 and in order to do so, starting SQL Server 2005 onwards, Microsoft provides a new command with ALTER DATABASE [...]
Data Availability when Creating Large Indexes on Tables
Whenever a clustered index is created, dropped or rebuilt, SQL Server puts a SCH-M (Schema modification) lock on the table which prevents all user access to the underlying table for the duration of the operation. This is true when creating a clustered index on a table but when you create a non-clustered index on a [...]
SQL Server Object Shortcuts Using Synonyms
Starting SQL Server 2005 onwards, you can now create short-cuts for object names inlcuding long object names that contain four part object names (ex: ServerName.DatabaseName.OwnerName.ObjectName) Starting SQL Server 2005 onwards, Microsoft Introduced a new feature called SYNONYMS that can help you create short-cuts for long object names for any of the below object types: 1) [...]
SA Password Lost! Now What?
Here’s the scenario: You are a proud and a trusted DBA of your organization who is responsible for some important services running on SQL Servers in the production environment. To prevent any unauthorized access to your production environment, you have decided to perform the following steps that are kind of best practices to secure your [...]
Performing Large Data Updates, Deletes and Inserts
Did you know that you can now use the TOP operator for Deleting, Inserting and Updating data in SQL Server tables? Using Top operator for DML operation can help in executing very large operation by breaking the process into smaller pieces. This will help with increased performance and helps with improving database concurrency for larger and [...]
Rebuilding System Databases
REBUILDM.EXE, the popular tool for rebuilding system databases that existed until SQL Server 2000 is no longer available in SQL Server. For those of you who don’t know what REBUILDM.exe was, it was a tool that would let you rebuild system databases from scratch. Take an example if a disk on which your system databases were [...]
SQL SERVER INDEXING OPERATIONS ON STEROIDS
Author: Saleem Hakani (Microsoft Corporation) Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time? There are many things that happen behind the scenes that could contribute to the slowness of index operations. Some of the reasons include: 1. Load on SQL Server, 2. Out of date statistics, [...]