SQL Server DBA : May 2013

Thursday, May 30, 2013

SQL DBA reference websites

Wednesday, May 22, 2013

How to Recover Database from Suspect Mode in SQL Server

Database in Suspect Mode reasons:

1. When records are inserted (it could be bulk inserts through Stored Procedure) in Application Database from application web form, if power failure on the Database Server Machine occurs during at that time , will there be chances of data corruption for those specific database records.

2. When records are inserted (it could be bulk inserts through Stored Procedure) in Application Database from application web form, if someone stops the SQL SERVER Database Engine Service, will there be chances of data corruption for those specific database records.

3. When the data files are not available.

Solution:
1. Restore the database backup file.

2. If no backup is available, then run the following commands

   ALTER DATABASE Databasename SET EMERGENCY
   DBCC CHECKDB 'Databasename'
   ALTER DATABASE Databasename SET SINGLE USER with ROLLBACK IMMEDIATE
   DBCC CHECKDB 'Databasename, REPAIR_ALLOW_DATA_LOSS'
   ALTER DATABASE Databasename SET MULTIUSER

Sunday, May 19, 2013

Cannot execute a program. The command being executed was "C:\Windows\Microsoft.NET\Framework64\v2.0.50727\csc.exe" /noconfig /fullpaths

Hi Team,

While performing patching SQL server 2008 R2 SP2 CU6 on an Active/Active cluster having two nodes e.g. Node1 and Node2, I encountered an unusal error which indicates:

SQL Server Setup has encountered the following error:

Cannot execute a program. The command being executed was "C:\Windows\Microsoft.NET\Framework64\v2.0.50727\csc.exe" /noconfig /fullpaths @"C:\Users\uswal.dba.va_c\AppData\Local\Temp\-euzaneo.cmdline".

Error code 0x84B10001.

Solution:

1. Start installing the SQL Server 2008 R2 Cumulative Update, by clicking on setup. It will start
    the extraction of files to a shared disk where there is large amount of Disk space.
2. Once extraction is complete, manually copy the extracted files (check the latest timestamp) to
    the C drive ( Non clustered disk).
3. Cancel the current installation which is in progress.
4. Start installation again by clicking the setup.exe from C drive , where you have manually
    copied the extracted files.

Thursday, May 2, 2013

SQL Server: Database Basics

System Databases

Master: composed of system tables that keep track of server installation as a whole and all other databases that are eventually created. Master DB has system catalogs that keep info about disk space, file allocations and usage, configuration settings, endpoints, logins, etc.
Model: template database. Gets cloned when a new database is created. Any changes that one would like be applied by default to a new database should be made here
Tempdb: re-created every time SQL Server instance is restarted. Holds intermediate results created internally by SQL Server during query processing and sorting, maintaining row versions, etc. Recreated from the model database. Sizing and configuration of tempdb is critical for SQL Server performance.
Resource [hidden database]: stores executable system objects such as stored system procedures and functions. Allows for very fast and safe upgrades.
MSDB: used by the SQL Server Agent service and other companion services. Used for backups, replication tasks, Service Broker, supports jobs, alerts, log shipping, policies, database mail and recovery of damaged pages.

Database Files

Primary data files: every database must have at least one primary data file that keeps track of all the rest of the files in the database. Has the extension .mdf.
Secondary data files: a database may have zero or more secondary data files. Has the extension .ndf.
Log files: every database has at least one log file that contains information necessary to recover all transactions in a database. Has the extension .ldf.

Creating a Database

New user database files must be at least 3 MB or larger including the transaction log
The default size of the data file is the size of the primary data file of the model database (2 MB) and the default size of the log file is 0.5 MB
If LOG ON is not specified but data files are specified during a create database, the size of the log file is 25% of the sum of the sizes of all the data files.

Expanding or Shrinking a Database

Automatic File Expansion:

The file property FILEGROWTH determines how automatic expansion happens
File property MAXSIZE sets the upper limit on the size

Manual File Expansion: use the ALTER DATABASE command with the MODIFY FILE option to change the SIZE property to increase the database file size

Fast File Initialization: adds space to the data file without filling the newly added space with zeros. New disk content is overwritten as new data is written to the files. Security is managed through Windows security setting SE_MANAGE_VOLUME_NAME

Automatic Shrinkage:

Same as doing DBCC SHRINKDATABASE (dbname, 25). Leave 25 % free space in the database after the shrink
Thread performs autoshrink as often as 30 minutes, very resource intensive

Manual Shrinkage: use DBCC SHRINKDATABASE if you want to shrink.

I highly recommend not to shrink the database.

Filegroups

Can group data files for a database into filegroups for allocation and administration purposes.
Improves performance by controlling the placement of data and indexes into specific filegroups on specific drives or volumes.
Filegroup containing the primary data file is called the primary filegroup, there is only one primary filegroup.
Default filegroup: there is at least one filegroup with the property of DEFAULT, can be changed by DBA.
Use cases when -not- to use filegroups:

DBA might decide to spread out the I/O for a database: easiest way is to create a database file on a RAID device.
DBA might want multiple files, perhaps to create a database that uses more space than is available on a single drive: can be accomplished by doing CREATE DATABASE with a list of files on separate drives

Use cases when you want to use filegroups:

DBA might want to have different tables assigned to different drives or to use the table and index partitioning feature in SQL Server.

Benefits:

Allows backup of parts of the database.
Table is created on a single filegroup, allows for backup of critical tables by backing up selected filegroups.
Same for restoration. Database can be online as soon as primary filegroup is restored, but only objects on the restored filegroups will be available.

Wednesday, May 1, 2013

During the SQL Server Service Pack installation, "Consistency validation for SQL Server registry keys" failed.

Description:

Issue: Installation of SQL Express 2008 fail with the error: "Consistency validation for SQL Server registry keys" failed

Error:
Rule "Consistency validation for SQL Server registry keys" failed
The SQL Server registry keys from a prior installation cannot be modified. To Continue; see SQL Server Setup documentation about how to fix registry keys.

Solution-1:

Navigate to:
%ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Log

Search (in Detail_GlobalRules.txt) for lines containing:
"could not fix registry key"

Run regedit

Set full control permissions for the appropriate registry keys mentioned in "Detail_GlobalRules.txt" file.

Re- run the installation.

Solution-2

Steps
1. Located HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server in registry
2. Right click and go to Permission
3. Click on Advance
4. Tick on both check box
I. Inherit from parent the permission...
II. Replace permission entries on all child objects..., click OK
5. Click OK again

SQL Server DBA