SQL Server DBA : 2013

Thursday, June 20, 2013

Display the size of all tables in a database

Below is the query to get the list of tables with sizes in a database:

CREATE PROCEDURE getAllTablesSize
AS
BEGIN
      DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;
      CREATE TABLE
            #temp (
                   [name] varchar(250),
                   [rows] varchar(50),
                 [reserved] varchar(50),
                 [data] varchar(50),
                 [index_size] varchar(50),
                 [unused] varchar(50)
                   );
      INSERT #temp EXEC ('sp_msforeachtable ''sp_spaceused ''''?''''''');
      UPDATE #temp
      SET
            [rows] = LTRIM(RTRIM(REPLACE(t.rows,'KB',''))),
            [reserved] = LTRIM(RTRIM(REPLACE(t.reserved,'KB',''))),
            [data] = LTRIM(RTRIM(REPLACE(t.data,'KB',''))),
            [index_size] = LTRIM(RTRIM(REPLACE(t.index_size,'KB',''))),
            [unused] = LTRIM(RTRIM(REPLACE(t.unused,'KB','')))
      FROM #temp AS t
      SELECT
            SUM(CAST([reserved] as decimal))/1024 AS 'Total reserved MB',
            SUM(CAST([data] as decimal))/1024 AS 'Total data MB',
            SUM(CAST([index_size] as decimal))/1024 AS 'Total index_size MB',
            SUM(CAST([unused] as decimal))/1024 AS 'Total unused MB'
      FROM
            #temp
      SELECT
            [name] ,
            CAST([rows] as INT)'rows' ,CAST([reserved] as INT)/1024 'reserved MB',
            CAST([data] as INT)/1024 'data MB' ,
            CAST([index_size]/1024 as INT)'index_size MB',
            CAST([unused] as INT)/1024 'unused MB'
      FROM
            #temp
    ORDER BY name
      DROP TABLE #temp;
END
GO
--EXECUTE getAllTablesSize

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

Friday, March 22, 2013

Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

If you try to drop a user that owns a schema, you will receive the following error message:

The database principal owns a schema in the database, and cannot be dropped.

In order to drop the user, you need to find the schemas they are assigned, then transfer the ownership to another user or role.

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('<USER_Name>')

-- now use the names you find from the above query below in place of the SchemaName below

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

Thursday, February 7, 2013

SQL SERVER DBA CONCEPTS

CORE DBA CONCEPTS of SQL SERVER

The History of SQL Server and Relational Databases
Relational database history and SQL Server and ANSI Standards
Relation between Application and Database

Overview of Windows Concepts
Role of Windows Operating System in SQL Server Administration
Windows Operating System Basics
Overview of Built-in accounts, Service accounts
Overview of Users and Groups
Overview of Services

Overview of Network Concepts
Role of Network in SQL Server Administration
Basics of Network Concepts, Network drives
Introduction to Work Group, Domain
Introduction to Active Directory Services (ADS)
Introduction to Domain Controller (DC)
Introduction to Domain Naming Server (DNS)
Introduction to Dynamic Host Configuration Protocol (DHCP)

SQL Server Editions
Different Editions of SQL Server and their features
Tools, Utilities and Components of SQL Server
Choosing Appropriate SQL Server Edition
Licensing issues with SQL Server

Installing and Configuring SQL Server
Pre-installation steps and Installation SQL Server
Configuring the run-time SQL Server system
Patching SQL Server - Service Packs, Hot fixes
Finding Right Service Pack or Hot Fix to be installed
Problems and precautions involved in SQL Server Patching
Upgradation from SQL server 2000/2005 to 2008 and Migration

SQL Server Databases
System Databases Master, MSDB, Model, Temp and Resource
Creating User Databases

SQL Server Data Storage Management
SQL Server File Management Architecture
Creating Database Files
Creating SQL Server Log Files
Using SQL Server File groups
Moving Files or File Groups

SQL Server Security Management
Windows Security Management for the server
Windows Authentication Model
SQL Server Rights and Role Management
SQL Server Authentication and Mixed Mode Authentication
Logins
Mapping roles and databases to logins

SQL Server Backup & Recovery
Overview of SQL Server Backup and Recovery
Full Backups, Incremental Backups and Log Backups
Overview of Backupset, Backupmediaset, Backupmediafamily
Recovery Models in SQL Server
Restoring a SQL Server Database
Difference between Restore and Recovery
Performing Recovery
Backup and Restore through third party tools like Redgate

Overview of Testing, Development, Staging and Production Server Environments
Designing Production server from Test Server and Vice versa
Naming Convention for servers(Compliant to Industry Standards)

SQL Architecture and Memory
Physical Database Architecture
Relational (Logical) Database Architecture
Memory Management

Physical Database Engine Architecture
Pages and Extents
Physical Database Files and Filegroups
Space Allocation and Reuse
Table and Index Architecture

Relational Database Engine Architecture
Query Process Architecture
Memory Architecture
Managing Memory for Large Databases
Thread and Task Architecture
Understanding Non-Uniform Memory Access (NUMA)
Buffer Management
Distributed Query Architecture

Management
Policy Management
Configuring Database Mail
SQL Server Logs
Distribution Transaction Coordinator
Data Collection and Resource Governor (In 2008 only)

SQL Server Agent
Jobs and Job Activity Monitor
Alerts
Operators
Proxies
Error Logs

SQL Server Job Scheduling
Common Database Scheduled Jobs
Setting SQL Server and server alerts thresholds
Creating Customized Error Conditions
Scheduling multi-step job streams
Troubleshooting of failed Jobs

Maintenance Plans
Overview of Maintenance Plan
Tasks in Maintenance Plans
Creation of Maintenance Plan
Monitoring Maintenance Plan
Troubleshooting of failed Jobs created by Maintenance Plan

DBCC Commands
Introduction to Database Console Commands
Maintenance DBCC Commands
Informational DBCC Commands
Validation DBCC Commands
Miscellaneous DBCC Commands

Metadata
Understanding Metadata and its importance
System Tables and Dynamic Management Views(DMV), System Stored Procedures
Querying System Tables, DMV’s and Stored Procedures to obtain Metadata
Role of Master and MSDB in maintaining Metadata

SQL Server Configuration Manager (SSCM)
Managing Services with SQL Server
Starting and Stopping Instance from SSCM
Starting and Stopping SQL Agent from SSCM
Configuring Protocols, Ports and Aliases in SSCM
Introduction to SSIS and DTS
Using DTS to export and Import SQL Server Table data

HIGH AVAILABILITY
Overview of Disaster Recovery and High Availability solutions
Disaster types
Recovery solutions
Standby database overview
High Availability

SQL Server Database Replication
Replication Components and Replication Terminology
Replication Topologies – Central Publisher, Central Publisher with Remote Distributor, Central
Subscriber, Central Distributor, Publishing Subscriber
Types of Replication – Snapshot Replication, Merge Replication and Transactional Replication,
Peer-Peer Replication
Configuring Replication – Merge Replication , Transactional Replication, Snapshot Replication

Log Shipping
Log Shipping Overview
Log Shipping Deployment and Administration
Configuring Log Shipping using Management Studio and T-SQL
Monitoring Log Shipping

Database Mirroring
Important:
Introduction
Database Mirroring Overview
Database Mirroring Dynamics
Database Mirroring Availability Scenarios
Implementing Database Mirroring
Database Mirroring and High Availability Technologies
Conclusion

SQL Server Clustering
Overview of Clustering
Hardware and Software Requirement for Clustering.
Installing and Configuring VMWARE for building virtual environment
Installing Windows 2008 clustered environment
Installing SQL Server Active Passive (Cluster) on Windows 2008 clustered environment
Adding a node to existing SQL Server cluster
How to patch SQL Server Clustered Environment by applying SP1 on SQL Server 2008 cluster instance.
What is SQL Server Active Active Clustering
Installing SQL Server Active Active Clustering
Installing Analysis Services (SSAS) on a clustered environment
Installing / Configuring Integration Services (SSIS) on a clustered environment
Remove a node from a running clustered instance
Un-installing SQL Server Cluster

PERFORMANCE TUNING

SQL Architecture and Memory
Physical Database Architecture
Relational (Logical) Database Architecture
Memory Management

Table and Index Structure
Partitioning Tables
Partitioning Index
Clustered Index
Table Scan and Index Scan
Index Seek vs. Index Scan

Locking and Concurrency
Locking Mechanism
Types of Locks
Isolation Levels in SQL Server
Live Lock, Dead Lock and Blocking detection and resolution
Killing processes at OS level

Query Optimization and Programming Efficiency
T-SQL Tuning
Query execution Plan
Estimated Execution Plan

SQL Server performance counters
Understanding different Performance Counters
Processor:% Processor Time
PhysicalDisk:Avg.Disk Queue Length
PhysicalDisk: Disk Read Bytes/sec and Physical Disk: Disk Write Bytes/sec

Server Performance and Activity Monitoring
Perform monitoring tasks with Windows tools
Create SQL Server database alerts with Windows tools
Perform monitoring tasks with SQL Server Management Studio
Perform monitoring tasks with SQL Trace by using Transact-SQL stored procedures
Create and modify traces by using SQL Server Profiler
Start, pause, and stop traces by using SQL Server Profiler
Open traces and configure how traces are displayed by using SQL Server Profiler
Replay Traces by using SQL Server Profiler
Create, modify, and use trace templates by using SQL Server Profiler
Use SQL Server Profiler traces to collect and monitor server performance

Database Engine Tuning Advisor
Create Workloads
Tune a Database
View Tuning Output
Implement Tuning Recommendations
Create XML Input Files
Perform Exploratory Analysis
Review, Evaluate, and Clone Tuning Sessions

Performance Tools and Monitoring
SP_TRACE_SETFILTER (T-SQL) for tracking engine process events
System Monitor for tracking resource usage
Activity Monitor in SSMS to display Processes running on an instance, Blocked Processes,Locks,User activity
System Stored Procedures
DBCC(T-SQL)
Built-in-Functions(T-SQL)
Trace Flags(T-SQL)
SQL Server Profiler
Resource Governor
Data Collection

SQL Server performance tips
Proactive Actions that Ensure Optimized SQL Server Performance
Establishing a Smart SQL Server Monitoring Plan

Friday, January 18, 2013

Microsoft SQL Server builds

Please find the sql server latest builds in the given below link:

http://sqlserverbuilds.blogspot.com/

Monday, January 7, 2013

SQL Server 2008 : Transparent Data Encryption

Transparent data encryption (TDE) is a new feature introduced in SQL Server 2008 that allows an entire database to be encrypted.
Unlike column-level encryption, in TDE the encryption and decryption of data is performed automatically by the Database Engine, and this is fully transparent to the end user and applications.
The encryption of a database using TDE helps prevent the unauthorized access of data in the scenario in which physical media or backups have been lost or stolen.
Transparent data encryption uses a database encryption key (DEK) for encrypting the database.
The DEK is stored in the database boot record and is secured by a certificate stored in the master database.
The database master key is protected by the service master key, which is in turn protected by the Data Protection API.
When TDE is enabled on a database, attaching data files to another SQL Server instance or the restoring of a backup to another SQL Server instance is not permitted until the certificate that was used to secure the DEK is available.

Implementing Transparent Data Encryption:

Implementing TDE for a specific database is accomplished by following these steps:

Create a master key.

USE master;
GO

--Create the master key which is stored in the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mystrongpassword$$';
GO
Create or obtain a certificate protected by the master key.

CREATE CERTIFICATE MyCertificate
with SUBJECT = 'Certificate stored in Master Db'
GO
Create a database encryption key and protect it by the certificate.

USE AdventureWorks2008R2
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCertificate
GO
Configure the database to use encryption.

ALTER DATABASE AdventureWorks2008R2
SET ENCRYPTION ON
GO

After you enable TDE, you might want to monitor the progress of the encryption. This can be done by running the following query:

SELECT DBName = DB_NAME(database_id), encryption_state
FROM sys.dm_database_encryption_keys ;
GO

DBName                           encryption_state
--------------------                 ----------------------
tempdb     3
AdventureWorks2008R2    3

This query returns the database encryption state. A database encryption state of 2 means that encryption has begun, and an encryption state of 3 indicates that encryption has completed. When the tempdb database and user database you are encrypting reach a state of 3, the entire user database and tempdb database are encrypted.

When TDE is enabled for a given database, encryption is applied to a variety of files related to the database, including the following:

Database Data Files— All data files that contain the database data are encrypted. These files typically have the extension .mdf or .ndf.
Database Log Files— The transaction log files are encrypted so that no clear text is visible in the files. These files typically have the extension .ldf.
Database Backups— All database backups, including full, differential, and log, are encrypted.
Tempdb— If any databases on a server are encrypted with TDE, the tempdb database is also encrypted.

SQL Server DBA

Thursday, June 20, 2013

Display the size of all tables in a database

Thursday, May 30, 2013

SQL DBA reference websites

Wednesday, May 22, 2013

How to Recover Database from Suspect Mode in SQL Server

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

Thursday, May 2, 2013

SQL Server: Database Basics

Wednesday, May 1, 2013

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

Friday, March 22, 2013

Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Thursday, February 7, 2013

SQL SERVER DBA CONCEPTS

SQL SERVER DBA CONCEPTS

Friday, January 18, 2013

Microsoft SQL Server builds

Monday, January 7, 2013

SQL Server 2008 : Transparent Data Encryption

Blog Archive

Followers