Detach Database Issue

We know that detach database removed the database from the SQL Server Instance,but the advantage of detach database is the database files are not deleted like in drop database statement.Hence I can copy the same .mdf & .ldf file to some other drive or in fact some other server and attach the same and use it.

Now what if the database is marked as suspect and you want to move the files to some other server and check the same.So you issue a sp_detach_db statement.SQL Server doesnt throw any error and the database is removed from the instance.

And now you copy the same to a new server and you find you are unable to attach the same using sp_attach_db.

Error Message
Server: Msg 1813, Level 16, State 2, Line 1Could not open new database 'UserDB'. CREATE DATABASE is aborted.Device activation error. The physical file name 'C:\C:\Program Files\Microsoft SQL Server\90\Data\UserDB_log.ldf' may be incorrect

Cause
This happens because database was in suspect mode and hence the database shout down was not proper and yoy recieve a 1813 error mentioning the same

Resolution
1) Create database with the same name and also with the same .mdf and .ldf files in the same path
2) Change the database status to offline
3) Overwrite the files from the source server to the destination server
4) Now when you make the database online,the DB should be up or at least it will be in suspect as before

Rebuild Database fails

Error Message Description
Rebuild Master failed with error
-1:The error occurred during server configuration. Refer to install\cnfgsvr.out
and the log\errorlog files in C:\Program Files\Microsoft SQL Server\MSSQL for diagnostic information.

Cause
Master Database is rebuild from a CD and the .mdf and .ldf files are read-only in the CD and hence the same gets copied over,hence the SQL Server is unable to start.

Resolution
Copy the files from CD to a share location and remove the read-only option for database files and then rebuild the master

Master Database is crashed.How do I recover the same

If the master database is crashed SQL Server would not start and the same can be found in SQL Server Error log.So the first step would be to rebuild the master database for which read the below artcile

Rebuild Master

Once the master is rebuilded SQL Server will be up and running , but you will not be able to find your User databases or logins you have added to the SQL Server.This happens because this master datbase file is copied from Install path, so now latest backup of master database needs to be restored over the existing master database.

If its user database we can use the simple restore command to restore the latest backup,but can the same be done for master? Answer is 'No'.

So first SQL Server needs to be started in Single User Mode,for which stop the running SQL Server and goto command prompt and go to directory where sqlservr.exe (Bin Folder)
and type

sqlservr.exe -f -m
-f For Minmal configuration
-m Single User Mode

Now I can fire the same restore command as before which would be

RESTORE DATABASE master FROM DISK='D:\master_full_backup.bak'

On executing the above command you immediately notice a message saying SQL Server is terminating the process which means restore is successful and the server needs to be started again in normal mode for settings to take effect.

The master database has been successfully restored. Shutting down SQL Server.
The SQL Server is terminating this process


Now you can see you user databases and logins present as before.If there are jobs configured on the server msdb database also needs to be restored.

Rebuild Database Utility

First lets look at cases of when Rebuild Utility needs to be used by a SQL Administrator

These are the 3 cases
(i) Master database files are corrupt or not available
(ii) When the default collation need to be changed
(iii) Model database files are corrupt or not available

Now lets go in detail on what are the steps to use these and what happens internally when we rebuild the database.

In SQL 2000 we use Rebuildm.exe which is present in bin directory to start the rebuild process.
The basic parameters provided are the:

1. SQL Server Instance Name,
2. Source Directory(i.e. Network Path or CD from which SQL Server is installed),
3. Collation Settings(Default is Dictionary Order, case-insensitive for use with the 1252 Character Set)

Now what happens internally is .mdf & .ldf files of master,model,msdb,tempdb are copied to the data files drive.And also you could see a series of script run after the copy,so needless to say that if you copy these files manually to the data files path SQL Server will still start but not receomended as those scripts are not run when you copy the same manually.

In SQL 2005 , rebuildm is changed to term called as REBUILDDATABASE and again it has the above 3 paramters implemented in a slighlyly different way.

start /wait setup.exe /qn INSTANCENAME= REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= SQLCOLLATION=

Instead of specifying the Source Directory path we map drive the same to the local server and
then start the SQL Server Setup again with REBUILDDATABASE=1 option.Also if you dont specify that SQLCollation parameter then the default collation is used.

Rows Count in a Table

If I ask anyone how to find the number of rows in a table everyone would give in a immediate answer just do count(*) from the table to find the total row count.

Now let me add some more restrcitions to the same , I would want the query not to be using the table name in the from clause,then you might wonder how is that possible , use the below query to acheive the same.

select rowcnt
from sysindexes
where indid = 1
and id = object_id('sysobjects')

Here Indid = 1 refers to the clustered index,hence needless to say that the query will work only if table has clustered index and the indexes are built regularly.

Comma Separated Values to Tables

Many people ask me whether a set of values provided from a Frone End Application can be compared acrossed a column value in the DB.

Ideal example would be we have a multi select List Box and user selects more than one value and clicks on search now the values he selected should be compared to a column using 'IN' clause but we never get to know how many value user chooses in the list box.To solve this use this function below


CREATE FUNCTION fn_colstoRows (@InputSTR VARCHAR(5000))
RETURNS @ROWS TABLE ( ROWVALUE VARCHAR(5000))
AS
BEGIN
DECLARE @iPosition INT
SELECT @iPosition = 1
DECLARE @iPrevPosition INT

WHILE CHARINDEX(',',@InputSTR,@iPosition) > 0
BEGIN

INSERT INTO @ROWS
SELECT SUBSTRING(@InputSTR,@iPosition,CHARINDEX(',',@InputSTR,@iPosition)-1)

SELECT @InputSTR = SUBSTRING(@InputSTR,CHARINDEX(',',@InputSTR,@iPosition)+1,LEN(@InputSTR))

END

INSERT INTO @ROWS
SELECT @InputSTR

RETURN
END

On Executing this using the below statement
select * from dbo.fn_colstorows('Value1,Value25,Value3')

Output is as follows

ROWVALUE
-------------------
Value1
Value25
Value3

So now we converted a comma separated 3 columns to 3 rows

Get Number of Days in a Month

First Step is move to the next month using Dateadd with Month
SELECT DateAdd(MM, 1, GETDATE())

Next step is Extract the datepart
Day(DateAdd(MM, 1, GETDATE()))

Now if you subtract the value obtained in first step with the second step you will get the last day of previous month and if you again use day function for the same you will get the number of day for the month.

So the single query to retreive the same would be
Select Day(DateAdd(MM, 1, GETDATE()) - Day(DateAdd(MM, 1, GETDATE())))

What is DAC

SQL 2005 DAC
DAC is the abbreviation for Dedicated Admin Connection.DAC is a special diagnostics connection which is available to trouble shoot when SQL Server is not responding to normal queries. And as the name implies this can be used only by members of sysadmin fixed server role.

How it Works?
By default SQL Server listens on TCP port 1433 which is the default port of SQL, so when a user executes any queries, it uses server port 1433 for executing
the same and produces result to client. DAC listens by default on 1434 so it is not muddled with user traffic; hence it is always available for connections.

DAC consumes minimal resources and as it is meant only for diagnostics there are limitations on what can be done inside a DAC connection. Below mentioned
are the few limitations:

1) There can be only one DAC connection allowed per server. If you try to open 1 more DAC connection you receive a error 17810 and the connection is denied.
2) Command like BACKUP, RESTORE cannot be run
3) As only limited resources are available do not run complex queries which have multiple joins.
4) By default ,DAC cannot be accessed from remote machines, which can be changed by tweaking server configurations using
Sp_configure ‘remote admin connections’,1
Reconfigure with Override

Now let’s see what all works effectively in a DAC connection.

Querying dynamic management views
Sys.dm_exec_requests - To find blocking queries.
Sys.dm_os_memory_cache_counters - To check for health of system memory cache.
Sys.dm_exec_sessions - For information about active sessions.

Querying Catalog views like sys.objects, sys.columns, etc…

DBCC Commands
DBCC FREEPROCCACHE – To remove all procedures from procedure cache
DBCC FREESYSTEMCACHE – To remove all unused entries from system cache
DBCC SQLPERF(LOGSPACE) – To find log file usage

KILL command to kill any connections

How to Use
From command line
SQLCMD -S -E -A
Here -A is the paramter which specifies it is a DAC connecion.

From SQL Manangement Studio
In the Server Name dropdown specify ADMIN:ServerName to establish a DAC connection

What are Schemas

Schemas are introduced new in SQL 2005,but do we see it is really a new feature which is not available in SQL 2000 ? Answer would be partially No , in turn the way
objects were referred in a select is made more organised by means of schemas.Lets take in a example to understand this better.

In SQL 2000 , lets assume there exists a User called 'Robert' exists in pubs database and he creates a object named author_report in database 'pubs' so the fully qualified select would be like:

select * from pubs.Robert.author_report

So what happens if Robert leaves the company, we many need to remove his ID from SQL Server and this object owner has to be changed before dropping the user which becomes paint to do everytime and everytime you may need to change your Front End Code also if the object is directly referred

To overcome this there is a new layer called schema is introduced , so now the select statement would be like this.
select * from pubs.Report.author_report

Here "report" is a schema name and not a user name, in turn Robert would be mapped to what is called as Schema.So objects created by Robert would be with
Schema name Report and not user name Robert,so if RObert leaves the company just the user needs to be dropped and no other changes are required which sounds really cool.

So each users will have a default schema mapped,specified when a user is created.By default it is "sys".Schema can be created by using CREATE SCHEMA statement

To Move object from one schema to another, in our case transfer Authors_Report from Report to default schema "sys"
ALTER SCHEMA sys TRANSFER Report.Authors_Report

To Map Database User to a schema other than sys
CREATE USER Robert FOR LOGIN Robert WITH DEFAULT_SCHEMA = Report

System View
sys.schemas - Contains a row for each database schema

Useful Event Class in Profiler

Deprecation
Over the years many commands have been depreciated(means No longer authorised to use),i.e. whenever a newer version is released certain commands may hav been
changed to newer style and would come with a warning that older code might be removed in next release of versions.
One example would be DUMP statement which is used in earlier versions for taking backup,this event class is really useful when you want to migrate to a new version and it can help you in identifying what all codes needs to be changed for migration.

Database
This is a collection of events to monitor automatic changes in the database and log file size changes.

Scans
Reports objects for which Table or Clustered Index scan is performed when a particular query is executed.

Security Audit
This events monitor security.Failed logins,password changes,role changes, and so on,are contained in this category.

How to calculate Table Size

Calculate space used by a Single row of the table.
1) First is we need to add the storage requirementts for each of the data type present in the table
2) Add null bitmap by the formula below
null_bitmap = 2 + ((number of columns + 7)/8)
3) Next is variable length columns like varchar,for which use the below formula:
variable_data_size = 2 + (number of variable columns * 2) + max_varchar_size
4) And calculate the sum of fixed data size like char,int,bigint,numeric,datetime
5) So now the total row size would be sum of all these values
Row_Size = Null_Bitmap + Variable_data_size + Fixed_Data_Size + Row_Header(4 bytes)

Now to find total number of rows in a page.Each page size is 8192 bytes with the page header,leaving that we can have 8096 bytes of data.
Therefore number of Rows would be
8096 / (Rowsize+2)

Total Number of Pages a particular table has can be found by the following formula
Number of Pages = Number of Rows in Table/Number of Rows per page

Backup Question

Please follow the link below to view case scenarios on Backup/Recovery
Case Scenarios

Order by Inside View

Can Order by be used inside a view

Create View vw_Emp_Salary
As
Select Emp_ID,Emp_Name,Emp_Sal from Emp_Salary


Suppose I want to order by employees who get more salary first,then view definition would look like:

Create View vw_Emp_Salary
As
Select Emp_ID,Emp_Name,Emp_Sal from Emp_Salary
ORDER by Emp_Sal desc

But This gives a error when compiled saying "Order By cannot be used unless TOP is specified",but if I use TOP wont my resultset of rows get reduced.So the query would be

Create View vw_Emp_Salary
As
Select TOP 100 PERCENT Emp_ID,Emp_Name,Emp_Sal from Emp_Salary
ORDER by Emp_Sal desc

SQL Objects

Tables
Tables are the backbone of SQL Server and is knows as Entity in Database World.Tables are in turn called as data containers, by which SQL stores its data.

Data in stored in the form of rows and columns.On viewing a table you will find that it looks similar to data stored in a excel sheet.

As the rows and column format is choosen to store data it makes it easier to read and interpreted the data in easier and effective way.

Ex:- Select * from Employee would list down table details


Views
Now that I have the table and what if dont want certain users to certain highly sensitive columns like password.Or consider the case like I do not want to view salaries of employees who are not reporing to a particular manager.

First Case is called Hiding Columns which is Vertical and the second case is hiding Rows,both can be acheived by what is called a View in SQL Server.

View is called as virtual table and there it doesnt contain physical data,in turn the table has data and that is fetched when a view is accessed.

Table Columns
EmployeeID
EmployeeName
EmployeePassword
Active

View Definition:
Create View vw_Employee
AS
Select EmployeeID,EmployeeName,Active from Employee

So now we have left password column in table definition which makes the data secure from users using views.

Next advantage of views would be resuablity which will be dealt when joining multiple tables to access data.

Transaction Log Size grows huge of Case Scenario mentioned below

A particular big transaction with UPDATE statement affects 1 million records.
Every time I run this SP/Query, my transaction log is growing too much and some times it is failing. What all the remedy actions you suggest?

Answer would be split the big transaction into multiple small transactions

Lets suppose it is just a simple update

Begin tran bigger_tran
Update tableA
Set col = value
Where condition
Commit tran


Now the split transaction would be

declare @ctr int
declare @trancount int
declare @ln_error int
declare @ln_rowcount int

set @ctr =1
set @trancount = 0

/* This updates only 500 records at a time */

Set ROWCOUNT 500

while (@ctr <> 0)
begin

begin tran chunk_tran

update big_tran_to_small_tran
set j = null
where j is not null

select @ln_error = @@error, @ln_rowcount = @@rowcount

if @ln_error <> 0
begin
rollback tran chunk_tran
break
end
if @ln_rowcount = 0
begin
set @ctr=0
end
else
begin
commit tran chunk_tran
set @trancount = @trancount +1 -- verification only
end

end

set rowcount 0

Pick a Random name from Table

Select Top 1 [Emp_Name] From [Emp_Salary] Order By NewId()

Display SQL records with Row Number

Select RowNum = (Select Count(1) From [Emp_Salary] EI
Where EI.[Emp_Id] <= EO.[Emp_Id]),
[E].[Emp_Name],
[E].[Emp_Sal]
From [Emp_Salary] EO

The Table should have at least one unique column which is Emp_ID in the above example.And also the above one displays the records in ascending order.

For descending order use the one which is below:
Select RowNum = (Select Count(1) From [Emp_Salary] EI
Where EI.[Emp_Id] >= EO.[Emp_Id]),
[E].[Emp_Name],
[E].[Emp_Sal]
From [Emp_Salary] EO

P.S:- Please note that SQL 2005 already has row number concept in built for which just use

select row_number() OVER(ORDER BY Emp_Id ),* from Emp_Salary

Getting the nth highest salary along with the details

For 2nd Highest Salary we can use the queries below

Using TOP Clause
Select * From [Emp_Salary]
Where [EMP_Sal] In (Select Min([EMP_Sal])
From [ERecords]
Where [EMP_Salary] In (Select Distinct Top 2 [EMP_Sal]
From [ERecords]
Order By [ESal] Desc))

Second Way
Select * From [Emp_Salary] [ES1]
Where (2 = (Select Count(Distinct [Emp_Sal])
From [Emp_Salary] [E2]
where [E1].[ESal] <= [E2].[ESal]) )

In the above corresponingly change the number from 2 to 'n' to fetch the nth highest value

Delete Duplicate Records from the table

Suppose there are multiple duplicate records in the [Emp_Salary] table whose structure is as follows

Create Table [Emp_Salary](EMP_ID Varchar(10), EMP_Salary decimal(5,2))

Step 1
Adding a identity column in [Emp_Salary]
Alter Table [Emp_Salary]
Add [Id] Int Identity(1,1)

Step 2
Deleting duplicate records from [Emp_Salary]
You should include all columns of the table [Emp_Salary] in the 'Group By' Clause accept [Id] like


Delete From [Emp_Salary]
Where [Id] Not In (Select Min([Id])
From [Emp_Salary]
Group By [EMP_ID],[EMP_Salary])

Step 3
Droping the newly added column.


Alter Table [Emp_Salary]
Drop Column [Id]

Now the table [Emp_Salary] contains the unique records

What is the difference between SET and SELECT ?

Though you can assign values to local variables using either SET/SELECT,
There are few differences.

 SET can be used for assigning one variable at a time whereas Select can be used for multiple variable Assignments

 When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row.

 A single SELECT statement assigning values to 3 different variables, is much faster than 3 different SET statements assigning values to 3 different variables. In this scenario, using a SELECT is at least twice as fast, compared to SET.

Compare BCP and BULK INSERT?

(i) BCP is command line utility where as bulk insert is t-sql statement.

(ii) BCP can be used for import And export where as bulk INSERT for import only

(iii) While importing Large text files, bulk insert is proven to be faster than BCP because bulk insert converts the data into TDS (tabular data stream) , which is sql server complaint whereas BCP converts data into ODBC data Stream ,
again which has to be converted into TDS.

What's the difference between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster.

What is the difference between Clustered Index and Non clustered index and what factors should I consider for Clustered Index?

Clustered Index defines the physical order of data storage and hence, only 1 clustered index is possible on a table.

In the Presence of Clustered index on a table, Non clustered index stores the indexed columns information along with clustered index key. In absence of Clustered index, Non Clustered Index stores ROWID. Max of 249 Non Clustered Indexes can be created on a table.

As I have the option of Only 1 clustered index can be created on a table, it is very critical to determine the right columns for clustered index.

I consider the following Attributes of column:

Columns in clustered index are not supposed to be updateable. Because, every time value in column in the clustered index is changed, SqL Server has to re-order the data pages.

Search Criteria:

Column should be searched for a given range.
Or
Column should be used for “Order by”
Or
Column should be used for “Group By”

What is a deadlock and what is a live lock ?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Error Number: 1205

Steps to resolve dead locks:
Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:
• Rolled back, undoing all the work performed by the transaction.
• Resubmitted by applications because they were rolled back when deadlocked.
To help minimize deadlocks:
• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a low isolation level.
• Use bound connections

What are Isolation Levels? What are the differences?

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation):

Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation
level at the connection level.

Read Uncommitted
A transaction operating at the Read Uncommitted level can see uncommitted changes made by other transactions. At this level of isolation, dirty reads, nonrepeatable reads, and phantoms are all possible.

Read Committed
A transaction operating at the Read Committed level cannot see changes made by other transactions until those transactions are committed. At this level of isolation, dirty reads are not possible but nonrepeatable reads and phantoms are possible.

Repeatable Read
A transaction operating at the Repeatable Read level is guaranteed not to see any changes made by other transactions in values it has already read. At this level of isolation, dirty reads and nonrepeatable reads are not possible but phantoms are possible.

Serializable
A transaction operating at the Serializable level guarantees that all concurrent transactions interact only in ways that produce the same effect as if each transaction were entirely executed one after the other. At this isolation level, dirty reads, nonrepeatable reads, and phantoms are not possible.

ACID Properties

Atomicity
A transaction must be an atomic unit of work; either all of its data modifications are performed or none of them is performed.

Consistency
When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

Isolation
Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

Durability
After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

Why should not I create Procedures prefixed by “sp_” in user defined database?

SQL Server considers Stored Procs prefixed by “sp_” as System Stored Procedures and while executing, SQL server looks for the SP in master database and then, in the current database.
This has 2 disadvantages.
(i) Additional verification for the SP in master db causes overhead.
(ii)If it happens that SP with same name exists in master db, SP in master db will be executed.

Solutions:
DO NOT CREATE SPs that starts with “sp_”

If already SPs had been created, whose names start with “sp_” and application already using them, Ensure that calling those Stored Procedures are always qualified by dbname.owner.SP

Exec mydb.dbo.sp_test

Versioning of Stored Procs

Can we have multiple versions of Single Stored Procedure at the same time?

Yes, is is possible.This feature is very handy in deploying changes in existing Stored Procedure.Creation of Multiple versions for a Single Stored Procedure is possible by suffixing “;” followed by version number at the end of CREATE PROC STATEMENT.

Ex:
Create proc multi_version_sp (;1 default)
As
….
….

Create proc multi_version_sp (; 2 )
As
….
….

Create proc multi_version_sp (; n )
As
….
….

Executing Particular version:
Exec multi_version_sp invokes version 1 by default
Exec multi_version_sp ;2 invokes version 2

P.S: Dropping the first version of SP drops all versions.

What is the difference between Temporary tables and Table variables ?

Temporary tables are created using
“create table #table” (local)
“create table ##table” (global)

Temporary tables are created in tempdb. They are categorized into local , global temporary tables.

Local tables can be accessed by the process (SPID), by which they had been created and once the process is closed, they are destroyed automatically.

Global tables can be accessed by all the processes after it has been created. Once created, they are still accessible till last reference is closed or the process, which is created is closed, whichever is earlier.

Unlike Temporary tables, table variables reside in memory. This is the major advantage over temporary tables.

P.S: SQL Server converts table variable into temporary table automatically, if the resultset holded by table variable is too big. Hence, While working on large data to hold temporarily, it is recommended to opt for temp tables.

SQL 2005 - Login & User Creation

The syntax for adding a login to SQL Server 2000 would be as follows:

sp_addlogin 'UserA','Password','public_db' which means add the userA with password as 'password' and the default Database when the user logs in would be public_db.

Still the same hold good in SQL 2005 but due to the threat that this SP might be removed from future versions lets start using the below statement which is almost similar

CREATE LOGIN UserA WITH PASSWORD = 'password' DEFAULT_DATABASE = 'public_db';

For adding Windows login we used sp_grantlogin but from now on try using the below to add Windows login to Servers

CREATE LOGIN [DomainA\Robert] FROM WINDOWS;

And for adding user to DB we used sp_adduser which adds the user to public role,instead use CREATE USER

CREATE USER UserA FOR LOGIN UserA WITH DEFAULT_SCHEMA = Test_Schema;

SQL 2005 - Version Numbers

How to find what version of SQL is running.

you can SELECT @@Version as in SQL 2000 to find the version of SQL Server.One more option would be is to use sp_server_info like

sp_server_info 500

Output
500 SYS_SPROC_VERSION 9.00.1399
So here is the tablw which represent what Service Pack has been appiled.

9.00.1399 for RTM( Release to Manufacturing)
9.00.2047 for SP1
9.00.3042 for SP2

SQL 2005 - Sys.configurations

sysconfigures is the table which stores the SQL Server configurations in SQL 2000.The same is available as view in SQL 2005 as sys.Configurations.

However to update the same we use the same procedure sp_configure as we used in SQL 2000.

There are some new configurations which are added to SQL 2005,lets us what new has been added considering that we already know what configurations are available in SQL 2000

server trigger recursion - To Allow recursion for server level triggers
clr enabled - CLR user code execution enabled in the server
remote admin connections - Dedicated Admin Connections are allowed from remote clients

So now we have 14 configurations in SQL 2005 when compared to 11 in SQL 2000.

And with Advanced Options enabled we have 62 configurations as compared to 37 in SQL2000.

Some interesting new options are xp_cmdshell - Enable or disable command shell which is very useful and makes the SQL more secured.

And separate setting for each Extended stored procedures like Agent XPs,SQL Mail XPs,Database Mail XPs,SMO and DMO XPs.

So to view the configuration options you can use same as sp_configure or use the blow query to view the same.


select * from sys.configurations
where is_advanced column shows whether it is advanced option or not and also is_dynamic suggests whether reconfigure with override and restart of SQL is required for settings to take effect

Sysobjects

Interesting Table Design of sysobjects
Sysobjects is the meta data table which gets populated whenever objects like Tables,Views,Procs,Functions,Primary Keys are created.

If you look at table definition then you may find unique clustered index located on id column.
And also in relationships only the ID column is used in various tables like syscolumns,syscomments,etc..

So what it means from the table structure is there can be more than object with same name as there is no uniqueness enforced on name as such by table design.Now lets try these steps.

Create table T1
( A Int)
Go

You receive a message "Commands completed successfully"

Now we create one more object with same T1 as
create proc t1
as
begin
set nocount on
end

On executing the same i receive a error message as

Msg 2714, Level 16, State 3, Procedure t1, Line 5
There is already an object named 't1' in the database.

So we get to know that this validation is enforced at SQL application level that name is again a unique key so that the same objects names cannot exist in two different object types.

Check Disk Space

How to check for available free disk space and report databases files which will be affected

This involved two steps.
Use the sys_master_files to find the location of files and the size of each files.This table is similar to the one names sysaltfiles which is present in master DB in SQL 2000,but in SQL 20005 sys_master_files can be referred from any database not necessarily master. Below mentioned is the query for fetching the same.And second step is to append the result with xp_fixeddrives procedure.


DECLARE @Free_Space TABLE
( DriveLetter CHAR,FreeSpace BIGINT )
INSERT INTO @Free_Space(DriveLetter,FreeSpace)
EXEC master..xp_fixeddrives
SELECT DB_NAME(database_id) As DBName
,CASE TYPE_DESC WHEN 'ROWS' THEN 'DATA'ELSE 'LOG' END As FileType
,SUBSTRING(physical_name,1,1) As DriveLetter
,CEILING(CONVERT(NUMERIC(10,0),SUM(SIZE*8))/1024) As [SpaceUsed(in MB)]
,FreeSpace As [FreeSpace(in MB)]
from sys.master_files, @Free_Space
WHERE SUBSTRING(physical_name,1,1) = DriveLetter
group by db_name(database_id),CASE TYPE_DESC WHEN 'ROWS' THEN 'DATA'
ELSE 'LOG' END,
SUBSTRING(physical_name,1,1),FreeSpace
ORDER BY 1,2

Views & Stored Procs

Strange behaviour on using sp_rename procedure in Views ,Procedures & Functions


sp_rename takes in the parameter of Old object name and new object name.

So to rename a table authors in pubs DB we would use


sp_rename 'authors','authors_new' and the entry is updated in sysobjects name column for the
Specific Object table Details and name are stored in only sysobjects hence a update was made effectively by SQL when sp_rename function is executed

Lets see the same in case of Views and Stored Procedures
I have view named vw_authors and I have just select * from authors As

Create View vw_authors
As
select * from authors

Now I rename the same to vw_authors_new then it would change the same in sysobjects table and it I will no longer be able to refer the same with vw_authors

But what happens when I use sp_helptext and view the contents of the vw_authors_new it still shows As

Create View vw_authors
As
select * from authors

Why is this happening?

Its because the update of name happens only in sysobjects table and not in syscomments table.
Syscomments has the definition of view or procedure in column named text and hence the same cannot be updated in the column with the new name.To avoid this do not rename views or procedures,always drop and create the same to have consistency between sysobjects and syscomments

How to add a Not Null Column to Table which has data. Also change the column order to represent not null values and then followed by null values

1) Drop the foreign key Constraints whether this table is being referred as child table.
2) Create a table with existing columns and add not null columns first(including new column)and add null column.
3) Insert the data from existing table to new table created with default value specified for new not null column.
4) In the select statement used HOLDLOCK TABLOCKX.Here HoldLock is used to specify Isolation Level as SERIALIZABLE.and TABLOCKX prevents other users from reading or updating the table
5) Add same foregin keys to the new table
6) Drop the old table
7) Rename new table to old table name using sp_rename
8) Add Clustered and non-clustered Indexes
9) Add foregin keys constraints to child tables which uses this table as primary.

P.S:- Steps 3 to 8 has to be in a single transaction.

Recover last Transaction Log When the Master and the Database Files Are Damaged

If the master database and the data file of a user database are bad, but the transaction log file of the database is still accessible, you can still backup the last active transaction log of the database to reduce the loss of data by following these steps:
1) Rename the transaction log files. 2) Rebuild the master database. 3) Create a similar database. The new database does not have to be the same size; however, it must contain the same number of data and log files. 4) Stop SQL Server. 5) Delete all the data files of the newly created database so that it will fail recovery.
Replace the log files of the new database with the original log files so that you can backup the transaction log.
6) Restart SQL Server.
7) Run this command to back the tail of the log:Backup Log to Disk = With NO_TRUNCATE
8) Use the sp_dbremove stored procedure to remove the database.

Case Scenarios for Interview

Case Scenario - 1
Database Recovery Model is Full and scheduling is as follows: Full Backup Daily night at 09:00 PM and every 3 hours log backup

Time Action Done
09:00 P.M. Back up full database
12:00 A.M. Back up transaction log
03:00 A.M. Back up transaction log
06:00 A.M. Back up transaction log
09:00 A.M. Back up transaction log
10:00 A.M. Back up full database (Forced by User)
12:00 P.M. Back up transaction log
02:00 P.M. Failure occurs
1) Restore the latest database backup which is of 10:00 A.M. With NoRecovery.
2) Restore the transaction log backup which is of 12:00 P.M. With Recovery as that is our last log backup.
3) But we would restored data only till 12:00 PM and what happens to the data added/modified between 12:00 PM to 02:00 PMIs it lost and cannot be recovered?

The answer is neither yes nor no.It depends on the situation and criticality of the failure.So now if we can recover what would be the steps for the same.

1)Try taking a log backup,it will allow log to be backed up provided log files(.ldf) are not corrupt and the failure is only to data files.If log backup succeeds then you have database entirely upto the point of failure.
Command: BACKUP LOG database_name TO WITH NO_TRUNCATE
2) Restore the latest database backup which is of 10:00 A.M. With NoRecovery.
3) Restore the transaction log backup which is of 12:00 P.M. With NoRecovery.4) Restore the tail transaction log backup With Recovery.


Case Scenario - 2
Database Recovery Model is Full and scheduling is as follows:
Full Backup Every Week Sunday night at 09:00 PM and Differential Everyday night 11:00 PM and every 1 hours log backup

Time Action Done
Sun 09:00 P.M. Back up full database
Mon 11:00 P.M. Differential Back up
Tue 11:00 P.M. Differential Back up
Wed 11:00 P.M. Differential Back up
Thurs 11:00 P.M. Differential Back up
Fri 11:00 P.M. Differential Back up
Sat 11:00 P.M. Differential Back up
Sun 12:00 A.M. Back up transaction log
Sun 1:00 A.M. Back up transaction log
Sun 1:10 A.M. Failure Occurs

Answer is
1)Try taking a log backup,it will allow log to be backed up provided log files(.ldf) are not corrupt and the failure is only to data files.If log backup succeeds then you have database entirely upto the point of failure.
Command: BACKUP LOG database_name TO WITH NO_TRUNCATE
2) Restore the latest database backup which is of Sun 09:00 P.M. With NoRecovery.
3) Restore the latest Differential backup which is of Sat 11:00 P.M. With NoRecovery.
4) Restore the transaction log backup which is of 12:00 A.M. With NoRecovery.
5) Restore the transaction log backup which is of 01:00 A.M. With NoRecovery.
6) Restore the tail transaction log backup With Recovery.

NOW the question is What if Sat & Friday Differential backups are also corrupt or deleted.Then database can be restored only till Thursday ?

Answer would be a definite no.Database can be recovered till the point of failure as we would have log backups happening every 1 hour hence we would restore the Thursday Differential backup and restore all the logs from thursday and receovery the database with the tail backup.

Why clustered index takes longer time to be dropped?

Generally speaking, indexes can speed up queries tremendously. This comes at the cost, as changes to the underlying data have to be reflected in the indexes when the index column(s) are modified.

Before we get into the reasons why dropping a clustered index can be time-consuming, we need to take a short look at the different index structures in SQL Server.
Every table can have one, and only one clustered index. A clustered index sorts the data physically according to its index key. And since there can only be one physically sort order on a table at a time, this sounds pretty obvious. If a table does not have a clustered index, it is called a heap.

The second index structure is called a non-clustered index. You can create non-clustered indexes on tables with clustered indexes, heaps, and indexed views. The difference between both index structures is at the leaf level of the index. While the leaf level of a clustered index actually is the table’s data itself, you only find pointers to the data at the leaf level of a non-clustered index. Now we need to understand an important difference:
When a table has a clustered index created, the pointers contain the clustered
index keys for that row.

When a table does not have a clustered index, the pointers consist of the so-called RowID, which is a combination of FileNumber:PageNumber.

When you understand this distinction, you can derive the answer to the original question yourself. When you drop a clustered index, SQL Server will have to recreate all non-clustered indexes on that table (assuming there are any). During this recreation, the clustered index keys are replaced by the RowID. This is a time-consuming operation, especially on larger tables or tables with many indexes.

Transaction Log Backups

Transaction Log Backups

The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.

When restoring a transaction log backup, Microsoft SQL Server rolls forward all changes recorded in the transaction log. When SQL Server reaches the end of the transaction log, it has re-created the exact state of the database at the time the backup operation started. If the database is recovered, SQL Server then rolls back all transactions that were incomplete when the backup operation started.

Transaction log backups generally use fewer resources than database backups. As a result, you can create them more frequently than database backups. Frequent backups decrease your risk of losing data.

Note Sometimes a transaction log backup is larger than a database backup. For example, a database has a high transaction rate causing the transaction log to grow quickly. In this situation, create transaction log backups more frequently.

Transaction log backups are used only with the Full and Bulk-Logged Recovery models.

Using Transaction Log Backups with Database Backups
Restoring a database using both database and transaction log backups works only if you have an unbroken sequence of transaction log backups after the last database or differential database backup. If a log backup is missing or damaged, you must create a database or differential database backup and start backing up the transaction logs again. Retain the previous transaction logs backups if you want to restore the database to a point in time within those backups.
The only time database or differential database backups must be synchronized with transaction log backups is when starting a sequence of transaction log backups. Every sequence of transaction log backups must be started by a database or differential database backup.
Usually, the only time that a new sequence of backups is started is when the database is backed up for the first time or a change in recovery model from Simple to Full or Bulk-Logged has occurred.

Truncating the Transaction Log
When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space.

Although the transaction log may be truncated manually, it is strongly recommended that you do not do this, as it breaks the log backup chain. Until a full database backup is created, the database is not protected from media failure. Use manual log truncation only in very special circumstances, and create a full database backup as soon as practical.
The ending point of the inactive portion of the transaction log, and hence the truncation point, is the earliest of the following events:
The most recent checkpoint.
The start of the oldest active transaction, which is a transaction that has not yet been committed or rolled back.

This represents the earliest point to which SQL Server would have to roll back transactions during recovery.
The start of the oldest transaction that involves objects published for replication whose changes have not been replicated yet.
This represents the earliest point that SQL Server still has to replicate.
Conditions for Backing Up the Transaction Log
The transaction log cannot be backed up during a full database backup or a differential database backup. However, the transaction log can be backed up while a file backup is running.

Do not back up the transaction log:
Until a database or file backup has been created because the transaction log contains the changes made to the database after the last backup was created. For more information, see If the transaction log has been explicitly truncated, unless a database or differential database backup is created after the transaction log truncation occurs.

Restoring Transaction Log Backups
It is not possible to apply a transaction log backup:
Unless the database or differential database backup preceding the transaction log backup is restored first.
Unless all preceding transaction logs created since the database or differential database was backed up are applied first.
If a previous transaction log backup is lost or damaged, you can restore only transaction logs up to the last backup before the missing transaction log.
If the database has already recovered and all outstanding transactions have been either rolled back or rolled forward.
When applying transaction log backups, the database must not be recovered until the final transaction log has been applied. If you allow recovery to take place when applying one of the intermediate transaction log backups, you cannot restore past that point without restarting the entire restore operation, starting with the database backup.
Creating a Sequence of Transaction Log Backups
To create a set of backups, you typically make a database backup at periodic intervals, such as daily, and transaction log backups at shorter intervals, such as every 10 minutes. You must have at least one database backup, or a covering set of file backups, to make log backups useful. The interval between backups varies with the criticality of the data and the workload of the server. If your transaction log is damaged, you will lose work performed since the most recent log backup. This suggests frequent log backups for critical data, and highlights the importance of placing the log files on fault tolerant storage.
The sequence of transaction log backups is independent of the database backups. You make one sequence of transaction log backups, and then make periodic database backups that are used to start a restore operation.

Recovery and Transaction Logs
When you finish the restore operation and recover the database, all incomplete transactions are rolled back. This is required to restore the integrity of the database.
After this has been done, no more transaction log backups can be applied to the database. For example, a series of transaction log backups contain a long-running transaction. The start of the transaction is recorded in the first transaction log backup, but the end of the transaction is recorded in the second transaction log backup. There is no record of a commit or rollback operation in the first transaction log backup. Therefore, if a recovery operation runs when the first transaction log backup is applied, the long-running transaction is treated as incomplete. Data modifications recorded in the first transaction log backup for the transaction are rolled back. SQL Server does not allow the second transaction log backup to be applied after the recovery operation has run.

Therefore, when restoring transaction log backups, the database must not be recovered until the final transaction log has been applied. This prevents any transactions from being partially rolled back. The only time outstanding transactions need to be rolled back is at the end of the last restore operation.