How to restore a SQL Server database marked as "suspect"

How to restore a SQL Server database marked as "suspect"

A database can go in suspect mode for many reasons like improper shutdown of the database server, corruption of the database files etc….

1.        Check suspect state of DB’s:

SELECT DBName=NAME, [Status]=state_desc

FROM master.sys.databases WHERE state_desc='SUSPECT'

2.        To get the exact reason of a database going into suspect mode can be found  by using the below query:

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

Output of the above query will give the errors in the database.

3.       To repair the database, run the following queries in Query editor:

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(‘yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

  NOTE: Use below values as per your requirement.

REPAIR_ALLOW_DATA_LOSS: Performs all repairs done by REPAIR_REBUILD and includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. These repairs can result in some data loss. The repair may be done under a user transaction to allow the user to roll back the changes made. If repairs are rolled back, the database will still contain errors and should be restored from a backup. If a repair for an error has been skipped due to the provided repair level, any repairs that depend on the repair are also skipped. After repairs are completed,  back up the database.

REPAIR_FAST: Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.

REPAIR_REBUILD:Performs all repairs done by REPAIR_FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss.

SQL Server System Databases Dos and Donts

System Databases Do's and Don'ts

• Data Access - Based on the version of SQL Server query only the recommended objects.  In general the system database objects are being deprecated to a set of views, so be sure all of your scripts are accessing the right objects.  If not, you are going to have a big project in the future to convert all of your scripts.
• Changing Objects - Do not change system objects.  In SQL Server 2005 all of the database objects have been moved to the Resource database which stores the definition for the system objects and can be updated via new SQL Server releases independent of the data.
• New Objects - Creating objects in the system databases is not recommended.  If you have objects that are needed for the instance i.e. administrative items, just create a separate DBA database to store these objects.
• Sneaking a Peak - Up to this point, all of the T-SQL code for the tables, views, stored procedures, functions, etc. has been clear text.  So you can review the objects and learn from the techniques used by Microsoft.
• Dropping Objects - The most prominent reason to drop system objects are for specific types of lock downs and auditing in particular industries.  Although some of those practices are well documented, be sure you understand the ramifications related to administering and developing applications once those restrictions are in place.
• Security - Do not forget about the Public role and Guest user, they are the conduit for users to access the system objects.  So that should answer the question of how people (logins\users) can access the objects based on the object owner or schema, depending on the SQL Server version.
• Backups - Be sure to have a consistent backup process for your system databases.  Including the system databases with your user defined databases might be the best approach if a disaster occurs.
• Scope - Each SQL Server instance (including the Express Edition) has its own set of SQL Server system databases.  As such, if a single Windows server has multiple SQL Server instances installed, a change to one system database only impacts the single instance, not all instances on the Windows server.

SQL Server DATA PURITY option

SQL Server DATA PURITY option


              SQL Server 2005 offers a new option to the DBCC CHECKDB and DBCC CHECKTABLE commands.  This new option is the "DATA_PURITY" check which will look for issues where column values are not valid or out-of-range.  To run the command you issue it just as you would a regular DBCC command along with the added option, such as:
DBCC CHECKDB with DATA_PURITY


              For databases that are created in SQL Server 2005, these checks are on by default, so using or not using the DATA_PURITY option does not affect the outcome of the checks when issuing a  DBCC CHECKDB or DBCC CHECKTABLE.

              For databases that are not created in SQL Server 2005 and then later brought into SQL Server 2005 these checks are useful and will allow you to see if there are any data issues within the database.  Once this is run on a databases one of two results will occur; the first it will come back clean without problems and the second there will be data issues that need to be resolved.  If the purity check comes back without any problems a entry is made in the database header and whenever a DBCC CHECKDB or DBCC CHECKTABLE is issued the DATA_PURITY checks will also be performed regardless of whether you specify the DATA_PURITY check or not.

SQL DBA Finger Tip Commands

SQL DBA Finger Tip Commands

        Most of the SQL DBAs in the present world depend a lot on either Enterprise Manager or Management Studio for many of the common daily operations. However, the below table lists out some common commands that we use daily and if known can be issued with Query Analyzer or Query Editor to retrieve results instantaneously....

Command : sp_helpdb
                   This gives you information about all databases in the instance or specific information about one database.
Usage : sp_helpdb databasename


Command : fn_virtualfilestats
                   This command will show you the number of read and writes to a data file.  Use sp_helpdb with the    database name to see the logical file numbers for the data files and the database id.
Usage :SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid)         
SELECT * FROM :: fn_virtualfilestats(1, 1) 


Command : fn_get_sql()
                   Returns the text of the SQL statement for  the specified SQL handle.  This is similar to using DBCC INPUTBUFFER, but this command will show you additional information.  This can also be embedded in a process easier then using the DBCC command
Usage :DECLARE @Handle binary(20) 
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52 
SELECT * FROM ::fn_get_sql(@Handle


Command : sp_lock
                   This command shows you all of the locks  that the system is currently tracking  This is similar to information you can see in Enterprise Manager.
Usage :sp_lock or sp_lock spid or sp_lock spid1, spid2


Command : sp_help
                   This command gives you information about the objects within a database.  The command without an objectname will give you a list of all objects within the database.
Usage :sp_help or sp_help objectname


Command : sp_who2
                   Gives you process information similar to what you see when using Enterprise Manager.
Usage :sp_who2 or sp_who2 spid


Command : sp_helpindex
                   Gives you information about the indexes on a table as well as the columns used for the index.
Usage :sp_helpindex objectname


Command : sp_spaceused
                  This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.
Usage :sp_spaceused or sp_spaceused objectname


Command : DBCC CACHESTATS
                   Displays information about the objects currently in the buffer cache.
Usage :DBCC CACHESTATS


Command : DBCC CHECKDB
                   This will check the allocation of all pages in the database as well as check for any integrity issues.
Usage :DBCC CHECKDB


Command : DBCC CHECKTABLE
                   This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
Usage :DBCC CHECKTABLE (‘tableName’)


Command : DBCC DBREINDEX
                   This command will reindex your table.  If the indexname is left out then all indexes are rebuilt.  If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.
Usage :DBCC DBREINDEX (tablename, indexname, fillfactor)


Command : DBCC PROCCACHE
                   This command will show you information about the procedure cache and how much is being used.  Spotlight will also show you this same information.
Usage :DBCC PROCCACHE


Command : DBCC MEMORYSTATUS
                   Displays how the SQL Server buffer cache  is divided up, including buffer activity.
Usage :DBCC MEMORYSTATUS


Command : DBCC SHOWCONTIG
                   This command gives you information about how much space is used for a table and indexes.  Information provided includes number of pages used as well as how fragmented the data is in the database.
Usage :DBCC SHOWCONTIG or DBCC SHOWCONTIG WITH ALL_INDEXES or DBCC SHOWCONTIG tablename


Command : DBCC SHOW_STATISTICS
                   This will show how statistics are laid out for an index.  You can see how distributed the data is and whether the index is really a good candidate or not.
Usage :DBCC SHOW_STATISTICS (tablename, indexname)


Command : DBCC SHRINKFILE
                   This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to.  Use the sp_helpdb command along with the database name to see the actual file names used.
Usage :DBCC SHRINKFILE (filename, size in MB) or DBCC SHRINKFILE (DataFile, 1000)

Command : DBCC SQLPERF
                   This command will show you much of the  transaction logs are being used.
Usage :DBCC SQLPERF(LOGSPACE)


Command : DBCC TRACEON
                   This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.
Usage :DBCC TRACEON(traceflag)


Command : DBCC TRACEOFF
                   This command turns off a trace flag.
Usage :DBCC TRACEOFF(traceflag)


Please note that incorrect or inappropriate use of this commands may lead to unexpected results...

Backup and Restore Interrupted Databases

Backup and Restore Interrupted Databases

Most of the times we face the issue of the BACKUP or the RESTORE operation terminating abnormally before completion. For instance look at the below example to understand the backup operation that stopped after 30 % of completion and RESTORE operation failed after 70% of completion

Problem :
BACKUP DATABASE MAXCLONE TO DISK = 'C:\MAXCLONE.BAK' WITH NOFORMAT, INIT, NOSKIP,STATS = 10

10 percent completed
20 percent completed
30 percent completed
.
.
.
..
Backup operation failed....


RESTORE DATABASE [MAXCLONE] FROM  DISK = 'C:\MAXCLONE.BAK' 
WITH NOUNLOAD, REPLACE,  STATS = 10


10 percent completed
20 percent completed
30 percent completed
40 percent completed
50 percent completed
60 percent completed
..
Restore Operation terminating abnormally....

Solution :
Reissue the commands with the keyword RESTART at the end as shown below....

BACKUP DATABASE MAXCLONE TO DISK = 'C:\MAXCLONE.BAK' WITH NOFORMAT, INIT, NOSKIP, RESTART, STATS = 30


30 percent completed
80 percent completed
90 percent completed
.
.
Backup Database successfully processed 1549 pages ....

-- get backup information from backup file

RESTORE FILELIST FROM  DISK = 'C:\MAXCLONE.BAK'

-- restore the database


RESTORE DATABASE [MAXCLONE] FROM  DISK = 'C:\MAXCLONE.BAK'

WITH RESTART, NOUNLOAD, REPLACE,