How to: Validate Data at the Subscriber

Reffer this link

http://technet.microsoft.com/en-us/library/ms151251.aspx

Rebuild System Databases In SQL 2005

We need to rebuild the system databases if the master database is corrupted or damaged. Let us discuss in detail how to rebuild system databases in SQL server 2005.

Step 1: Take a full backup of all the System and User databases prior to rebuilding the system databases as we need to restore it later to avoid data loss.

Step 2: Copy the SQL 2005 setup files from the CD to the hard disk. In the command prompt, navigate to the folder which contains the setup.exe file. In my case it is available in D:\Setups\SQL 2005\Servers folder. The SQL Server we are going to rebuild is currently running. Now type the below command,

start /wait setup.exe /qn INSTANCENAME=”MSSQLSERVER” REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=”XXXX”

where XXXX is the name of the password.

INSTANCENAME=”MSSQLSERVER” for default instance of SQL 2005 and
INSTANCENAME=”MSSQL$INSTANCENAME” for named instance of SQL 2005.

For example,
If you have a named instance named as “Suresh\Test” then type as below,
INSTANCENAME=”MSSQL$TEST” in the above command

Refer the below screenshot for the same.

Step 3: After executing the command in step 2 the rebuild process will start and will complete within 5 minutes. You can verify whether the databases are rebuild by navigating to folder containing the data and log files for the system databases. If you arrange them using modified date it will clearly show the time when it was last modified and it is the time when we executed the command in Step 2.



Step 4: Once the rebuild is completed, connect to the SQL server using SSMS. In the object explorer only the system databases will be available.
If any user db were present prior to rebuild it will be lost and we need to perform as below to retrieve it.
Restore from the backup taken in Step 1 (or)
We can attach from the data and log files of the user db as they will not be cleaned up during rebuild process.

NOTE : No Need to detach all the user databases before rebuild as the ldf and mdf files will be present in the same path as usual and will not be overwritten.

Now we need to restore the system databases from the backup which we took in Step 1.
Master database can be restored only in Single user mode (refer Step 5) and other dbs can be restored normally.

NOTE : The ldf and mdf files of the system databases will be overwritten and hence we cannot perform detach/ attach operation.

Step 5: In order to restore master database perform the below steps,

Stop SQL server and start it using the below command from the command prompt

NET START MSSQLSERVER /c /m which will start SQL in single user mode

Recompile All The Stored Procedure on Specific Table

I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.

Example:

----Following script will recompile all the stored procedure on table Sales.Customer in AdventureWorks database.
USE AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO
----Following script will recompile specific stored procedure uspGetBillOfMaterials only.
USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetBillOfMaterials';
GO

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008

In this article, server A and server B are different servers. Additionally, both server A and server B are running SQL Server 2005.

Note This information also applies to SQL Server 2008.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B. Additionally, the users may receive the following error message:
Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)
This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:
On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
Open a new Query Editor window, and then run the following script.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.
1. Run the following statement.
EXEC sp_help_revlogin
The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
2. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section.
3. Open a new Query Editor window, and then run the output script that is generated in step 3.
Back to the top
Remarks
Review the following information before you run the output script on the instance on server B:
• Review the output script carefully. If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. For more information about how to resolve these orphaned users, click the following article number to view the article in the Microsoft Knowledge Base:
240872 How to resolve permission issues when you move a database between servers that are running SQL Server
If server A and server B are in the same domain, the same SID is used. Therefore, the users are not likely to be orphaned.
• In the output script, the logins are created by using the encrypted password. This is because of the HASHED argument in the CREATE LOGIN statement. This argument specifies that the password that is entered after the PASSWORD argument is already hashed.
• By default, only a member of the sysadmin fixed server role can run a SELECT statement from the sys.server_principals view. Unless a member of the sysadmin fixed server role grants the necessary permissions to the users, the users cannot create or run the output script.
• The steps in this article do not transfer the default database information for a particular login. This is because the default database may not always exist on server B. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.
• The sort order of server A may be case insensitive, and the sort order of server B may be case sensitive. In this case, the users must type all the letters in the passwords as uppercase letters after you transfer the logins and the passwords to the instance on server B.

Alternatively, the sort order of server A may be case sensitive, and the sort order of server B may be case insensitive. In this case, the users cannot log in by using the logins and the passwords that you transfer to the instance on server B unless one of the following conditions is true:
o The original passwords contain no letters.
o All the letters in the original passwords are uppercase letters.
The sort order of both server A and server B may be case sensitive, or the sort order of both server A and server B may be case insensitive. In these cases, the users do not experience a problem.
• A login that already is in the instance on server B may have a name that is the same as a name in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15025, Level 16, State 1, Line 1
The server principal 'MyLogin' already exists.
Similarly, a login that already is in the instance on server B may have a SID that is the same as a SID in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15433, Level 16, State 1, Line 1
Supplied parameter sid is in use.
Therefore, you must do the following:
1. Review the output script carefully.
2. Examine the contents of the sys.server_principals view in the instance on server B.
3. Address these error messages accordingly.
• In SQL Server 2005, the SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement.

SQL Server 2008: Hot-Add CPU (and affinity masks)

SQL Server 2005 introduced the concept of hot-add memory, to allow for dynamic workload handling. SQL Server 2008 increases these capabilities by adding hot-add CPU as well. 'Hot-add' means being able to plug in a CPU while the machine is running and then reconfigure SQL Server to make use of the CPU ONLINE! (i.e. no application downtime required at all)

There are a few restrictions:

You need a 64-bit system that support hot-add CPU (obviously :-))
You need Enterprise Edition of SQL Server 2008
You need Windows Server Datacenter or Enterprise Edition
When you plug in the new CPU, SQL Server won't automatically start using it. If you think about it, it can't - you may not want that CPU to be used by SQL Server - so it has to be told that it can use it. You do that by setting the appropriate affinity masks and then running a RECONFIGURE.

Ok - now we come to the bit that needs the "(and affinity masks)" in the title. What's an affinity mask? In a nutshell, it's a bitmap of all the CPUs on the machine that specifies which ones are available for general SQL Server Engine use, and which ones are available only for SQL Server I/O. There are two kinds of masks, a regular affinity mask, and an affinity I/O mask. The regular affinity mask, if all zeroes, says that Windows decides who get's what CPU when. If the affinity mask is non-zero, then there's a bit per CPU. If it's set to 1 then SQL Server can use the CPU. If a bit is set to 1 in the affinity I/O mask, then the CPU can only be used for I/O. A bit cannot be set in both masks. Now - an affinity mask is 32-bits wide, so if you have more than 32 CPUs, you need to use two more affinity masks, called affinity64 and affinity64 I/O. These do the same thing but for CPUs 32-63 on the machine.

sequence for restoring differential database backups

The sequence for restoring differential database backups is:

Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.

SQL Server 2005 Recovery Models

SQL Server 2005 Recovery Models

Full Recovery Model
Bulk-Logged Recovery Model
Simple Recovery Model


Full Recovery Model
The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.

 

For heavy OLTP databases, there is overhead associated with logging all of the transactions, and the transaction log must be continually backed up to prevent it from getting too large.

 

Benefits:

1. Most resistant to data loss
2. Most flexible recovery options - including point in time recovery

Disadvantages:

• Can take up a lot of disk space
• Requires database administrator time and patience to be used properly

Bulk-Logged Recovery Model
The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.

 

The following transactions are minimally logged in a Bulk-Logged Recovery Model:

 

SELECT INTO
bcp and BULK INSERT
CREATE INDEX
Text and Image operations

Benefits:

1. Transaction log stays small
2. Easier from an administration standpoint (don’t have to worry about transaction logs)
3. Disadvantages:
4. Not for production systems
5. Point in time recovery not possible
6. Least data resistant recovery model

Simple Recovery Model
The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.

 

Benefits:

1. Transaction log stays small
2. Easier from an administration standpoint (don’t have to worry about transaction logs)
3. Disadvantages:
4. Not for production systems
5. Point in time recovery not possible
6. Least data resistant recovery model

find Unused indexes in SQL Server 2005

USE AdventureWorks


GO

DECLARE @dbid INT

SELECT @dbid = DB_ID(DB_NAME())

SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),

INDEXNAME = I.NAME,

I.INDEX_ID

FROM SYS.INDEXES I

JOIN SYS.OBJECTS O

ON I.OBJECT_ID = O.OBJECT_ID

WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1

AND I.INDEX_ID NOT IN (

SELECT S.INDEX_ID

FROM SYS.DM_DB_INDEX_USAGE_STATS S

WHERE S.OBJECT_ID = I.OBJECT_ID

AND I.INDEX_ID = S.INDEX_ID

AND DATABASE_ID = @dbid)

ORDER BY OBJECTNAME,

I.INDEX_ID,

INDEXNAME ASC

GO
 
 
This is only works for SQL Server 2005 only

clustering in SQL Server

It is high avilability concept only. It does not provide any 'Fault Tolarene' (i.e, I have Data Base, in this some table is missing.. By using Clustering we cont restore that table data.)

Adv:
No down time i say ( i.e 99.9999%) not 100%.

Note:
It is totally related to Windows based only. Not Sql Server related.

Defination:
It is concept of providing high avilability. By using using this we can reduce down time.

Note:
After installation of Clustering the total sql server behaves like normal sqlserver. There is no change in behaviour.

Clustering supports only Server Operating Systems.

Requirements:

1. Minimum Two machines(we cal this machine as NODE) with same Hardware (Same Ram, Same Hard drive, Same Processor)
2. Same Server Operating system(win 2003server..) in each node(Machine)
3. Active Directory in another machine(domain Controller)
4. SAN Box(Shared Array) like External Hard Disk.
5. 2 Network cards for each machine
6. 6 IP's (1 for Public Cluster, 1 for Public sqlserver, 4 for Network cards)

IDEA:

 Quorum Drive:
All sql server installation things are stored.

FAQS:
1.What is Active/ Passive ?
2.Is sql server behaves different afetr we instal sql server in Cluster machine?
   Ans:  No, It behaves normal
3.How to take Offline/Online?
  Ans: Right Click on Group--> Select Offline
4.What are the issues you faced?
  Ans: The node getting down
5.What are the minimum IP's Required for Clustering?
   Ans: For Two Cluster environment we need 6 IP's
6.How to trace failOver?
7.What happend We plugged out SAN Box Cable?
8.Is it support Load balancing?
   Ans: No
9.What is the minimum storage space require for Quorum?
   Ans:500 MB
10.How much time it takes come online from Offline?
    Ans: 1 min
11.What is hart Beat?
    Ans: It is a process of authenticating both nodes it alive or not
12.What is group?
   Ans: A group is nothing but managing node resources seperatly
13.Is it posible to install Two Sql Server Instances in single Group?
   Ans:No, This feature is avilable in 2008 version
14.How to Stop and Start SQl Servr Service in Cluster Environment?
  Ans: From Cluadmin then Click on group-->Right Click on service appear in right side pannel and then  click stop/start. dont go to services.msc and stop from there.
15. how to Check Is our Machine participating in Clustering?
  Ans: Select serverproperty('IsCluster')
16.What is the Difference Between 32 bit and 64 bit?
17. How many cluster nodes are support in SQLSERVER 2005 with windows 2003 server?
 Ans:8 nodes
18. How many cluster nodes are support in SQLSERVER 2008 with windows 2008 server?
  Ans:16 nodes
19.How to Add node fro Cluster?
  Ans: Down load from the link fro etailed information... ClickHere

SQLCODE4YOU: Select Query Life Cycle

SQLCODE4YOU: Select Query Life Cycle: "We need to look more into select query execution. How it take query from client and how to perform internal operations and soon. Download p..."

Select Query Life Cycle

We need to look more into select query execution.
How it take query from client and how to perform internal operations and soon.
Download ppt for Step by step understatding of select query life cycle.

Click Here To Download PPT(power point presentation)

1486 Error in Mirroring

Error Description:
Database mirroring transport is disabled in the endpoint configuration ............

Solution:
ALTER ENDPOINT [mirroring] STATE= STARTED

Here 'mirroring' is my end point name..

Enabling Dedicated Administrator Connection Feature in SQL Server 2008

http://www.mssqltips.com/tip.asp?tip=1801

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT

When to use @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT ?



@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.



To give you a quick overview....

» @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

» SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

» IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.



@@IDENTITY
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.



SCOPE_IDENTITY (Transact-SQL)
It returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.



IDENT_CURRENT (Transact-SQL)
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. It returns NULL when the function is invoked on an empty table or on a table that has no identity column.

Extents and pages

What are extents and pages in the context of SQL Server 2005 ? What are the different types extents and pages in SQL Server 2005 ?



An extent is a block of eight pages totaling 64KB in size. Because the extent is the basic unit of allocation for tables and indexes and all objects are saved in a table of some kind, all objects are stored in extents.



SQL Server has two types of extents:

» Uniform: In uniform extents, all eight pages are used by the same object.

» Mixed:Mixed extents are used by objects that are too small to take up eight pages, so more than one object is stored in the extent.



When a table or an index needs additional storage space, another extent is allocated to that object. A new extent will generally not be allocated for a table or index until all pages on that extent have been used. This process of allocating extents rather than individual pages to objects serves two useful purposes. First, the time-consuming process of allocation takes place in one batch rather than forcing each allocation to occur whenever a new page is needed. Second, it forces the pages allocated to an object to be at least somewhat contiguous. If pages were allocated directly, on an asneeded basis, then pages belonging to a single object would not be next to each other in the data file. Page 1 might belong to table 1, page 2 might belong to index 3, page 3 might belong to table 5, and so on. This is called fragmentation. Fragmentation can have a significant negative impact on performance. When pages for a single object are contiguous, though, reads and writes can occur much more quickly.



PAGES
At the most fundamental level, everything in SQL Server is stored on an 8KB page. The page is the one common denominator for all objects in SQL Server. Many types of pages exist, but every page has some factors in common. Pages are always 8KB in size and always have a header, leaving about 8,060 bytes of usable space on every page.



SQL Server has eight primary types of pages:

Data pages
Data pages hold the actual database records. The data page is 8,192 bytes, but only 8,060 of those bytes are available for data storage because a header at the beginning of each data page contains information about the page itself. Rows are not allowed to span more than one page, but if you have variable-length columns that exceed this limit, you can move them to a page in the ROW_OVERFLOW_DATA allocation unit.

Index pages
Index pages store the index keys and levels making up the entire index tree. Unlike data pages, you have no limit for the total number of entries you can make on an index page.

Text/image pages
Text and image pages hold the actual data associated with text, ntext, and image datatypes. When a text field is saved, the record will contain a 16-byte pointer to a linked list of text pages that hold the actual text data. Only the 16-byte pointer inside the record is counted against the 8,060-byte record-size limit.

Global Allocation Map pages
The Global Allocation Map (GAM) page type keeps track of which extents in a data file are allocated and which are still available.

Index Allocation Map pages
Index Allocation Map (IAM) pages keep track of what an extent is being used for—specifically, to which table or index the extent has been allocated.

Page Free Space pages
This is not an empty page; rather, it is a special type of page that keeps track of free space on all the other pages in the database. Each Page Free Space page can keep track of the amount of free space of up to 8,000 other pages.

Bulk Changed Map pages
This page contains information about other pages that have been modified by bulk operations (such as BULK INSERT) since the last BACKUP LOG statement.

Differential Changed Map pages
This page contains information about other pages that have changes since the last BACKUP DATABASE statement.

Raid levels

RAID levels


RAID-0

RAID-0 uses disk striping; that is, it writes data across multiple hard disk partitions in what is called A stripe set. This can greatly improve speed because multiple hard disks are working at the same time. You can implement RAID-0 through the use of Windows Server software or third-party hardware. Although RAID-0 gives you the best speed, it does not provide any fault-tolerance. If one of the hard disks in the stripe set is damaged, you lose all of your data. Because of the lack of faulttolerance, Microsoft doesn’t recommend storing any of your SQL Server data on RAID-0 volumes.



RAID-1

RAID-1 uses disk mirroring. Disk mirroring actually writes your information to disk twice — once to the primary file and once to the mirror. This gives you excellent fault-tolerance, but it is fairly slow, because you must write to disk twice. Windows Server allows you to implement RAID-1 with a single controller, or you can use a controller for each drive in the mirror, commonly referred to as disk duplexing. This is the recommended place for storing your transaction logs because RAID-1 gives fast sequential write speed (writing data in sequence on the disk rather than jumping from one empty spot to the next), a requirement for transaction logs.



RAID-5

RAID-5—striping with parity — writes data to the hard disk in stripe sets. Parity checksums will be written across all disks in the stripe set. This gives you excellent fault-tolerance as well as excellent speed with a reasonable amount of overhead. You can use the parity checksums to re-create information lost if a single disk in the stripe set fails. If more than one disk in the stripe set fails, however, you will lose all your data. Although Windows Server supports RAID-5 in a software implementation, a hardware implementation is faster and more reliable, and we suggest you use it if you can afford it. Microsoft recommends storing your data files on this type of RAID because data files require fast read speed as opposed to transaction logs, which need fast write speed.



RAID-10

You should use RAID-10 (sometimes referred to as RAID 0+1) in mission-critical systems that require 24/7 uptime and the fastest possible access. RAID-10 implements striping with parity as in RAID-5 and then mirrors the stripe sets. So, you get the incredible speed and faulttolerance, but RAID-10 has a drawback. With this type of RAID you get the added expense of using more than twice the disk space of RAID-1. Then again, we are talking about a situation that can afford no SQL Server downtime

Troubleshooting Transactional Replication

Troubleshooting Transactional Replication
Here is the scenario…

I received an alert today that one of the order tables (articles) on a subscriber would not allow NULL values. Well nothing was changed on the subscriber side so I had to figure out where the issue was.

Fortunately, the Order ID was logged in the error message so on the distribution server\database and all of the other fields for that record had NULL values. I wanted to remove the offending rows from replication so I ran the following on the distribution database: exec sp_browsereplcmds. This returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. From here you can grab the xact_seqno field from the result set and then you are able to remove the offending rows from the Distribution.dbo.msrepl_transactions and Distribution.dbo.msrepl_commands tables. Here are the steps:

Step 1 – Stop the SQL Server Agent on the Distribution Server this will stop all replication transactions from going into the distribution database.

Step 2 – Run the following T-SQL commands in the distribution database

use distribution
go
delete from msrepl_transactions where xact_seqno =
go
delete from msrepl_commands where xact_seqno =
go

Step 3 – Start the SQL Server Agent on the distribution server this will start all replication transactions to go into the distribution database.

Step 4 – If need be, restart the synchronization of the distribution agent on the distribution server. Replication should now continue.

Find Disk Free Space in Sql Server 2005 or 2008

Execute following procedue in Query window

Use Master
GO
EXEC master..XP_FixedDrives
GO

Example:

Send Disk Space Alerts using SQL Server 2005

Step 1: Create the database mail profile account using SQL Server Management Studio. Give the profile name to “FreeSpaceAlertMails”

Step2: Create the below procedure in master database which will check the disk space.

CREATE PROCEDURE sendAlertMails
AS
BEGIN
SET NOCOUNT ON
DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(3),
freeSpace FLOAT
)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @availableSpace = ROUND((freeSpace)/1024,1) FROM #tbldiskSpace WHERE driveName = ‘E’

SET @alertMessage = ‘(host:yourpool.sqldbpool.com)E:\ Disk Space Critical. Free Space Available on E:\ Drive is ‘ + CAST(@availableSpace AS VARCHAR) + ‘GB’

IF @availableSpace < 10
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘FreeSpaceAlertMails’,
@recipients = ‘yourmail@gmail.com’,
@body = @alertMessage,
@importance = ‘High’,
@subject = ‘domain.com Disk Critical E Drive’ ;
END

DROP TABLE #tbldiskSpace

END

Trouble shooting Alerts

Troubleshooting SQL Server Alerts

If you have problems with Microsoft SQL Server alerts, review this
troubleshooting checklist to find potential solutions.

1. Check that you are using the latest SQL Server service pack.

Because many alert bugs were fixed in SQL Server service packs, you
should install the latest SQL Server service pack. Check "How can I
check what SQL service pack am I running?" to find out which SQL
Server service pack you are running.

2. Check that the account the SQLServerAgent services runs under is a
member of the Domain Users group.

The LocalSystem account does not have network access rights.
Therefore, if you want to forward events to the application logs of
other Windows NT or Windows 2000 computers, or your jobs require
resources across the network, or you want to notify operators through
e-mail or pagers, you must set the account the SQLServerAgent service
runs under to be a member of the Domain Users group.

3. If all of the alerts are not firing, check that the SQLServerAgent
and EventLog services are running.

These services must be started, if you need the alerts to be fired.
So, if these services are not running, you should run them.

4. If an alert is not firing, make sure that it is enabled.

The alert can be enabled or disabled. To check if an alert is enabled,
you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click the appropriate alert to see if the alert is
enabled.

5. Check the history values of the alert to determine the last date
that the alert worked.

To view the history values of the alert, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click the appropriate alert to see the alert history.

6. Verify that the counter value is maintained for at least 20
seconds.

Because SQL Server Agent polls the performance counters at 20 second
intervals, if the counter value is maintained for only a few seconds
(less than 20 seconds), there is a high likelihood that the alert will
not fire.

7. Check the SQL Server error log, SQL Server Agent error log, and
Windows NT or Windows 2000 application log to get a more detailed
error description.

Comparing the dates and times for alert failure events between the SQL
Server error log, the SQL Server Agent error log, and the Windows NT
or Windows 2000 application log can help you to determine the reason
of the failure.

8. If the alert fires, but the responsible operator does not receive
notification, try to send 'e-mail', 'pager', or 'net send' message to
this operator manually.

In most cases, this problem arises when you have entered an incorrect
'e-mail', 'pager', or 'net send' addresses. If you can send an 'e-
mail', 'pager', or 'net send' message manually to this operator, check
the account the SQL Server Agent runs under, as well as the operator's
on-duty schedule.

9. If the alert fires, but the notification is not timely, decrease
the 'Delay between responses' setting for the alert and try to send
notifications to as few operators as possible.

To decrease the 'Delay between responses' setting for the alert, you
can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click the appropriate alert and choose the 'Response'
tab.
5. Specify a new 'Delay between responses' setting.

10. Alert cannot send e-mail notification with xp_logevent or
RAISERROR.

This is an SQL Server 7.0 and SQL Server 2000 bug. This happens if the
alert is defined to be restricted to a specific database other than
the master database. To work around this, you can define the alert on
the master database, or all databases. To define the alert on all
databases, you can do the following:

1. Run SQL Server Enterprise Manager.
2. Expand a server group; then expand a server.
3. Expand Management; then expand SQL Server Agent.
4. Double-click the appropriate alert and choose '(All Databases)'
in the Database name combobox.

11. Alerts are triggered incorrectly when Security Auditing is
enabled.

This is an SQL Server 7.0 and SQL Server 2000 bug. This bug was fixed
in SQL Server 2000 service pack 1 and in SQL Server 7.0 service pack
4. To work around this problem, you can disable Security Auditing, or
you can install the service packs.

12. After Installing SQL Server 7.0 service pack 3, SQL Server Agent
alerts may fail to work.

This is an SQL Server 7.0 service pack 3 bug. To work around this, you
should install SQL Server 7.0 service pack 4.

13. Responses for performance condition alerts are sent every 20
seconds, regardless of the 'Delay between responses' setting for the
alert.

This is an SQL Server 7.0 bug. To work around this, you should install
SQL Server 7.0 service pack 1 or higher.

SQLCODE4YOU: DIFFERENCE BETWEEN SQLSERVER 2005 AND 2008

SQLCODE4YOU: DIFFERENCE BETWEEN SQLSERVER 2005 AND 2008: "DIFFERENCE BETWEEN 2005 AND 2008: Feature NameSqlSearver 2005Sqlserver 2008 Backup CompressionNot availableAvailable Page level compressionN..."

DIFFERENCE BETWEEN SQLSERVER 2005 AND 2008

DIFFERENCE BETWEEN 2005 AND 2008:

Feature Name	SqlSearver 2005	Sqlserver 2008
Backup Compression	Not available	Available
Page level compression	Not available	Available
Data Compression	Not available	Available
Clustering	8 nodes are supported in Enterprise Edition	16 nodes are supported in Enterprise Edition
Resource Governor	Not available	Available
File Stream	Not available	Available
Facets	Not available	Available
Encryption	Not available	Available ( In mirroring all transactions are encrypted while moving data)

Show header form Data Base backup with out backup restoration

List header data from Backup without restoration:
Here is code for list heade that is in Backup. It just show header information.
Syntax:
RESTORE HEADERONLY FROM DISK='path of your backup file'

EXAMPLE:
RESTORE HEADERONLY FROM DISK='D:\mydatabse_backup.bak'

Here 'mydatabse_backup.bak' is my data base backup name. So replace this name with your database backup file name.

List All files from Backup without restoration, How?

List All files from Backup without restoration:

Here is code for list all files that are in Backup. It just show files list not restore.
Syntax:
RESTORE FILELISTONLY FROM DISK='path of your backup file'

EXAMPLE:
RESTORE FILELISTONLY FROM DISK='D:\mydatabse_backup.bak'

Here 'mydatabse_backup.bak' is my data base backup name. So replace this name with your database backup file name.

SQL Server 2008 FILESTREAM

Implementing SQL Server 2008 FILESTREAM functionality
The new FILESTREAM functionality in SQL Server 2008 lets you configure a varbinary(max) column so that the actual data is stored on the file system, rather than within the database. You can still query the column as you would a regular varbinary(max) column even though the data itself is stored externally.

The FILESTREAM feature integrates Windows NTFS with the SQL Server database engine by storing binary large object (BLOB) data as files on the local file system. You can use Transact-SQL statements to query, insert or update the data, or you can use Win32 file system interfaces for direct streaming access to the data.

Microsoft recommends that you use FILESTREAM only if (1) your BLOB data files are, on average, larger than 1 MB, (2) you need fast read access to that data, and (3) your application uses a middle tier for application logic. Otherwise, you should use a regular varbinary(max) column.

If you do decide to implement a FILESTREAM column, you'll need to take the following steps:
Enable FILESTREAM support on the SQL Server 2008 service.
Configure the database to support FILESTREAM storage.
Define a column that supports FILESTREAM storage.

SQLCODE4YOU: Resource Governor in SQL Server 2008

SQLCODE4YOU: Resource Governor in SQL Server 2008: "Resource Governor in SQL Server 2008 This is new version for Sql Server 2008. Resource governor is one of the best additions in SQL Server..."

Resource Governor in SQL Server 2008

Resource Governor in SQL Server 2008


This is new version for Sql Server 2008.
Resource governor is one of the best additions in SQL Server 2008. It gives you a real control on your environment as a DBA and can be very useful for better utilization of your database server resources. So far so good, but don’t just start using it without knowing how it actually restricts resource utilization. Following are some points to remember for better utilization of this feature:

Normally, most of us assume that if we restricts a memory usage for a user A upto 10% only then he will never be able to utilize more than 10% of server in any case. Right? Wrong. Actually, Resource governer only restricts user to utilize not more than 10% if it feels that remaining memory is not available but if memory is available and there are no pending workload then it will allow the user to use more than its allowed quota of 10%. This is there to optimize the utilization of memory and avoids wastage of resources. But it can have worse effects also because if User A fires it query before other users then server will start utilizing all the available memory and all other users which came afterwards will suffer the consequences.

Please note that Resource Governor is for database engine not for other services in SQL Server 2008. It means you can not control usage of Reporting Services, Analysis Services or Integration Services.

If you have multiple instances running on same machine then you can not use Resource Governor to manage load between these instances.

Keeping these points in your mind will help you to better understand how to use resource governor and what to expect. It is one of the best tools to manage your load and highly recommended but make sure you know the pros and cons of it.

For more information Please visit, It's supper article
 http://www.sql-server-performance.com/articles/per/Resource_Governor_in_SQL_Server_2008_p1.aspx

Why Logshipping supports only FullBackup and Bulk-logged recovery models?

Logshipping means ship log files to another location from stand by server.
Log file contains all the DML statements information.
The basic aim behind Logshipping is to maintain all old transactions in seperate place.

If we use simple recovery model all the DML Statements information is not recorded So, This is not supported for logShipping.

Where as in the case of full/bulklogged recovery model all the DML Statements coresponding log information is avilable in log files. So LogShipping accepts only Full/Bulk-Logged recovery models.

Run command for sqlserver 2008

1.click on start button
2.click on run from start menu
3.type SSMS
4.press enter
5.It opens SQLServer 2008 or 10.0 version

Run command for for sqlserver 2005

1.click on start button
2.click on run from start menu
3.type sqlwb
4.press enter
5.It opens SQLServer 2005

RESTORE VERIFYONLY In SQL Server 2005

RESTORE

Usage:

RESTORE VERIFYONLY FROM  DISK = 'C:\www\EMP_backup_201010130110.bak'

Out Put:
The backup set on file 1 is valid. 

VERIFYONLY  is used to check wether our backup is working or not. this is not 100% but upto some extent we can conclude that our backup works fine.

DBCC CHECKDB

The question I ask in an interview is:
"You get an error in the logs indicating that you have a torn page or checksum error. What do you do?"

Half the candidates look confused. Of the rest, roughly 90% tell me to run DBCC CHECKDB with one of the repair options! When I challenge them to explain how CHECKDB performs the repair, none are able to answer, unaware of the damage they might be doing.

So here is how to use DBCC CHECKDB, and what to do when you have a torn or corrupt page.

So How Do I Use It?

The primary purpose is to check for consistency errors, and should ideally be run every day.
The basic syntax is:
DBCC CHECKDB ('DB Name') WITH NO_INFOMSGS
NO_INFOMSGS prevents an excessive number of informational messages from being generated. There are several other options, but this is the syntax you should aim to use as it performs all integrity checks.

This may take a long time on large databases and you may want to specify the PHYSICAL_ONLY option. This checks physical on-disk structures, but omits the internal logical checks. The syntax is:
DBCC CHECKDB ('DB Name') WITH PHYSICAL_ONLY

What Do I Do?

You do have backups don't you? You might be lucky; a non-clustered index can be dropped and rebuilt but actual data, such as a clustered index, cannot.
By far the best option is to restore from a backup, but let's look at how you investigate which pages are affected and what type of data is affected:

Look at the output from DBCC CHECKDB. You may see something like this:


bject ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed. See other errors for details.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.

CHECKDB found 0 allocation errors and 2 consistency errors in table 'yourtable' (object ID 2088535921).

CHECKDB found 0 allocation errors and 2 consistency errors in database 'yourdb'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (yourdb).

From this you can see what page is corrupted (1:94299)
The first thing to do is check if it is data in a heap, in a clustered index, or in a non-clustered index. In the above text you can see it is index ID 0. You could also examine the page (1:94299 in database 'yourdb') as follows:

DBCC TRACEON (3604, -1)
GO
DBCC PAGE('yourdb', 1, 94299, 3)
GO
In the output you will see something like:
Metadata: IndexId = n
If n is greater than 1 it is a non-clustered index and can safely be dropped and recreated. If n is 0 or 1 you have data corruption and need to perform one of the options described below.

Restoring from a backup
If the recovery model is FULL (or BULK_LOGGED, with some limitations), you can backup the tail of the log, perform a restore (with norecovery) from the last clean full backup, followed by subsequent log backups and finally the tail of the log.
If only a few pages are affected you have the option of selectively restoring only the bad pages, as follows:


RESTORE DATABASE yourdb PAGE = '1:94299'
FROM DISK = 'C:\yourdb.bak'
WITH NORECOVERY

If the recovery model is simple you don't have that option, and have to accept that a restore from the last full backup will result in subsequent transactions being lost. In this case, or if you have no backups at all, you may decide that an automatic repair is the only option.

Automatic Repair Options
First let me emphasise the importance of running a backup BEFORE you go any further.
Have a look at the output of the original CHECKDB. It will specify the minimum repair level.
REPAIR_REBUILD
If the minimum repair level is REPAIR_REBUILD you have been lucky.
The syntax is
DBCC CHECKDB('DB Name', REPAIR_REBUILD)
REPAIR_ALLOW_DATA_LOSS

This attempts to repair all errors. Sometimes the only way to repair an error is to deallocate the affected page and modify page links so that it looks like the page never existed. This has the desired effect of restoring the database's structural integrity but means that something has been deleted (hence the ALLOW_DATA_LOSS). There are likely to be issues with referential integrity, not to mention the important data that may now be missing.
The syntax is

DBCC CHECKDB('DB Name', REPAIR_ALLOW_DATA_LOSS)
Make sure you run DBCC CHECKCONSTRAINTS afterwards so you are aware of referential integrity issues and can take the appropriate action.

And Finally
My original reason for writing this was to stress that the correct action when faced with corruption is nearly always to restore from a backup. Only use the automatic repair options as a last resort, and with full understanding of the damage this may do.
Just as important is that regular backups are an essential part of a DBA's responsibilities, and you should use the FULL recovery model with regular log backups for all but the most trivial databases.

DBCC CHECKDB is a powerful tool, but also very dangerous in the wrong hands.
Maybe instead of adding the REPAIR_ALLOW_DATA_LOSS option, Microsoft should have created a separate DBCC command called:
DBCC DELETE_DATA_BECAUSE_I_COULDNT_BE_BOTHERED_TO_TAKE_A_BACKUP

SQLCODE4YOU: When and how to use Database Snapshots

SQLCODE4YOU: When and how to use Database Snapshots: "SQL Server 2005 offers many features that did not exist in previous versions of the product. One such feature is Database Snapshots. Databas..."

When and how to use Database Snapshots

SQL Server 2005 offers many features that did not exist in previous versions of the product. One such feature is Database Snapshots. Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.

How does this new feature work?
The initial thought is that the snapshot needs to make a complete copy of your database so the data stays static. If this were the case, the snapshot would offer no advantage over a backup and restore.

Here's what actually happens: When you create the snapshot, a shell of your database file is created. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.

If you look at the picture directly below, 90% of the data has not been changed in the primary database, so 90% of the data is still retrieved from here when a query is issued. On the reverse, 10% of the data has changed to satisfy the results for a query that uses the data it would read from the sparse file.

What are the advantages?

• Since SQL Server only needs to deal with the data that has changed for Database Snapshots to work, the initial creation of the snapshot or subsequent snapshots is extremely fast.
• Data is only written to the sparse file as data changes, so your overall disk space needs don't double each time you create a new snapshot -- as they would if you were using backup and restore for the secondary copy.
• You can create several snapshots for the same database, so you could have rolling snapshots occurring every hour for auditing purposes.
• It is considerably easier to work with snapshots and much faster than backup and restore operations.
• you can create a static reporting environment quickly with minimal effort.
• Corrupt or deleted data can be retrieved from the snapshot to repair the primary database.
• Database changes can be audited with the use of snapshots.
• You have the ability to restore your database using a snapshot.

What are the disadvantages?

• Your user community is still hitting your primary database. If you already experience performance issues because of reporting requirements, using snapshots for reporting will not resolve that problem.
• The snapshot is tied to your primary database, so you cannot take the primary database offline. If the primary database goes offline, you won't be able to access any of your snapshots.
• The Database Snapshots feature is only available in the Enterprise Edition.
• If data changes rapidly, this option may not offer benefits for keeping snapshots around for a long period of time.
• Full-text indexing is not supported in the snapshot.

How is it used?

The following guidelines show you how simple it is to create and use database snapshots.

Creating: To create a database snapshot you must use a T-SQL command; managing snapshots is not currently supported through Management Studio. The syntax to create the snapshot is as follows:

CREATE DATABASE Northwind_Snapshot1000 ON
(
   NAME = Northwind_Data,
   FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1000.ss'
) AS SNAPSHOT OF  Northwind

If you wanted to create a snapshot every hour, you could issue the above statement again and just change the name of the database and the file. Now you will have two snapshots, but different static data.

CREATE DATABASE Northwind_Snapshot1100 ON
 (
 NAME = Northwind_Data,
 FILENAME = 'C:\MSSQL\Snapshot\Northwind_data1100.ss'
 )
AS SNAPSHOT OF Northwind

Using: Using the snapshot is just like using any other database. You attach to the database and execute your queries. The thing to remember though is that this is a read-only copy, so you can't issue UPDATE, DELETE or INSERT statements.

Dropping: To get rid of a database snapshot, drop the snapshot just like you would drop any other database.

DROP DATABASE Northwind_Snapshot1000

Restoring: One nice thing about the snapshots feature is that you can restore your entire database back to the point in time when the snapshot was taken. If there were issues with your database and you needed to revert back to the exact time the snapshot was taken, you could use a restore command. Another option is to use snapshots to restore a test environment back to its state when the testing began. So you can take a snapshot, let your users or developers do their testing and then restore the snapshot so the database is back to the point in time prior to when testing began. To restore a database using a snapshot you can use the following command:

RESTORE DATABASE Northwind FROM DATABASE_SNAPSHOT ='Northwind_Snapshot1100'




What is your suggestion for restarting SQL Server as SQL DBA?

Microsoft suggest that 99days. After 99 days we need to restart SQL Server. So that the tempdb and all cleared.

How to find bloked process in SQL Server 2005/2008

SQL Server 2005 or 2008 provides several ways to find which sessions are currently working and which session is bleked some table.

SP_WHO, SP_WHO2 are two commands to finding out who are blocked.

Example: Open Sql Server 2005 or 2008 management Studio. Then type Sp_Who it dispalys like...

51 0 sleeping                       NT AUTHORITY\SYSTEM PRAKRIT25                                                                                                                        0     msdb AWAITING COMMAND 0

 0 indicates bloked status...

Same like who we have another option called Sp_who2

Insert or Update Script Generator

/*


SQL Server Row Script Creator

SURESH October 2010



This script will generate script to insert/update from a source table in one database to an

identical destination table in another database or server. It can be run for inserts or updates,

and can be run for a single row in insert and update mode, or all rows in a table for insert mode.

*/



declare @tab varchar(50)

,@pk1Val varChar(10)

,@pk1Name varChar(50)

,@qt char(1)

,@StatementType varChar(10)

set nocount on

/*

Instructions:

1) open script and connect to the source database

2) Change the variable values to change the output options for the script below (@tab, @statementtype etc)

3) execute the script (best to use text output)

4) copy the script output into a script window, and run on the destination table.



@Tab = the name of the source table

@pk1Val = if selecting a single row or doing an update statement, the value of the primary key for that row

@pk1Name = if inserting a single row or doing an update statement, the name of the column for the primary key

@StatementType = either 'INSERT' to create an insert statement or 'UPDATE' for an Update statement

*/

select @tab = 'hari', @pk1Val = '', @pk1Name = '', @StatementType = 'INSERT'



declare @tabName varchar(50)

, @colName varchar(50)

, @colType varchar(50)

, @collength varChar(50)

, @colOrder int

, @IsIdent char(1)





create table #output (Line varChar(4000), LineOrder int)

create table #ColumnValues (ColName varChar(250), ColOrder int, RowNumber int, ColValue varchar(4000), colType varchar(50))



declare @out varchar(4000)

,@lineCounter int

,@ColValue varchar(4000)

,@sortCol varchar(50)





/* get the ordering column */

select @sortCol = sc.Name

from sysobjects so

inner join syscolumns sc

on so.id= sc.id

inner join systypes st

on sc.xtype = st.xusertype

where so.Name = @tab

and ((sc.status = 0x80) OR (ColOrder = 1 and not sc.status = 0x80 ))







/* put in the repeating values based on the columns*/

declare objCurs CURSOR FOR

select so.name, sc.name, st.name, sc.length, Case when sc.status = 0x80 then 'Y' else 'N' END as IsIdent, ColOrder

from sysobjects so

inner join syscolumns sc

on so.id= sc.id

inner join systypes st

on sc.xtype = st.xusertype

where so.Name = @tab



DECLARE @counter int, @numCols int, @RowNumber int, @LastRowNumber int, @maxRowNumber int, @maxColOrder int



select @numCols = count(sc.id)

from sysobjects so

inner join syscolumns sc

on so.id= sc.id

where so.Name = @tab



--select @numCols --debug code



open objCurs

Fetch from objCurs

into @tabname, @colName, @colType, @colLength, @isIdent, @colOrder



while @@fetch_status = 0

begin

SET @counter = 0

/* get the value from the table */

if @IsIdent = 'N'

BEGIN

--select @TabName,@ColName, @ColType, @ColLEngth, @isIdent, @ColOrder --debug code

/* increase better type handling by inserting more case statments, handling different data types */

if datalength(@pk1Name) = 0 or datalength(@pk1Val) = 0

begin

/* getting all rows in the table */

exec ('insert into #ColumnValues (ColName, ColOrder, ColValue, ColType)

select ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName + ' order by ' + @SortCol + ' ' +

' declare @counter int set @counter = 0 ' +

' update #ColumnValues Set @Counter = RowNumber = @Counter + (' + @numCols + ' * 10) where ColName = ''' + @colName + '''' )

end

else

begin

/* filtering by a pk val */

exec ('insert into #ColumnValues (RowNumber, ColName, ColORder, ColValue, ColType)

select 0, ''' + @colName + ''', ' + @ColOrder + ', Convert(nvarchar(4000),' + @colName + ') , ''' + @colType + ''' from ' + @tabName +

' where ' + @pk1Name + ' = ' + @pk1Val)

end





end /* if @isIdent = 'n' */



Fetch Next from objCurs

into @tabname, @colName, @colType, @colLength, @IsIdent, @ColOrder

end



--select * from #ColumnValues --debug code

select @MaxRowNumber = Max(rowNumber) from #columnValues --keep highest row number so we know when we are finished

select @MaxColOrder = max(ColOrder) from #ColumnValues where RowNumber = @MaxRowNumber



/* next cursor for outputting the results from the retval table into the output table */

declare ColVal_Curs cursor for

select ColName , ColOrder , RowNumber , ColValue , colType

from #ColumnValues

order by RowNumber, ColOrder



open ColVal_Curs



--set the last row number to the first in the table, so post loop checking works

select @lastRowNumber = min(rowNumber) from #ColumnValues

set @lineCounter = @LastRowNumber --initialise at the first row



fetch from ColVal_Curs into

@colName, @ColOrder, @RowNumber, @colValue, @ColType



while @@Fetch_status = 0

BEGIN /* cursor loop */



/* get the quote type to enclose the value from the column type */

select @qt = case @colType

when 'nvarchar' then ''''

when 'nchar' then ''''

when 'DateTime' then ''''

when 'ntext' then ''''

when 'varchar' then ''''

else ''

end





--select @linecounter, @colName, @ColOrder, @RowNumber, @colValue, @ColType

if not @ColValue is null



if @rowNumber = @lineCounter

select @out = case @statementType

when 'UPDATE' THEN 'Update ' + @tab + ' SET '

when 'INSERT' then 'INSERT INTO ' + @tab + ' ('

end

begin

if @StatementType = 'UPDATE'

BEGIN

select @Out = @out + @colName + ' = ' + @qt + COALESCE(@ColValue, 'NULL') + @qt + ',' -- + @ColType

insert into #output (Line, LineOrder)

values (@out, @lineCounter)

end

if @statementType = 'INSERT'

BEGIN

/* put comma in */

if @lineCounter > @RowNumber --not first line in set of values for row

select @out = @out + ','



/*put in the name of the column */

insert into #output (Line, LineOrder)

values (@out + @colName

, @lineCounter)



if @lineCounter > @RowNumber --not first line in set of values for row

select @out = ','

else

select @out = ''

/* put in the value of the column */

insert into #output (Line, LineOrder)

values (@out + @qt + COALESCE(@ColValue, 'NULL') + @qt

, @lineCounter + 10 + @numCols)



END

end /*not @ColValue is null */

select @lineCounter = @lineCounter + 1

set @out = ''

/* get the next record*/

fetch from ColVal_Curs into

@colName, @ColOrder, @RowNumber, @colValue, @ColType

--select @ColOrder, @MaxColOrder, @@Fetch_Status --debug code

if (@rowNumber > @lastRowNumber) or (@RowNumber = @MaxRowNumber and @MaxColOrder = @ColOrder and @@FEtch_Status = -1)

BEGIN

/* this bit of processing is done whenever the set of columsn in a row changes to the next row of the original table*/

/* ie we are building a record to insert, and the PK changes because we are at the next record */

/* remove the last comma from the last line */

declare @lastLine int



if @statementType = 'UPDATE'

begin

/*remove last comma*/

update #output

set Line = left(Line,datalength(Line)-1)

where lineOrder = @LineCounter



/* insert a 'where' clause */



insert into #output (line, LineOrder)

select ' WHERE ' + @pk1Name + ' = ' + @pk1Val, Max(LineOrder) + 1 from #output



end

if @statementType = 'INSERT'

BEGIN

/* put in a 'values' statement between the column names and the column values */

insert into #output (Line, LineOrder)

values (') VALUES (', @LastRowNumber + @numCols + 5)

/* close off the lot */

insert into #output (line, lineorder)

select ')', Max(LineOrder) + 1 from #output

END

set @lastRowNumber = @RowNumber

set @lineCounter = @RowNumber /* reset linecounter for next set */

End /* if rownumber > last row number */



end /* cursor loop */



close objCurs

deallocate objCurs



close ColVal_Curs

deallocate ColVal_Curs



/* get the statements out from the list*/

select line as [Copy script from output window below] from #output order by lineorder



/* bug tracking code - uncomment to diagnose problems

select distinct RowNumber from #ColumnValues order by 1

select * from #ColumnValues

order by RowNumber, ColOrder, ColName, ColValue

*/

drop table #output

drop table #ColumnValues

set nocount off

Export excel sheet to SQL Database Table

Insert data into Sql table read from excel sheet. OPENROWSET() is a method for read the data from excel sheet. Before we run this script make sure that 'Testexcel.xls’ is avilable in 'C' Drive.

INSERRT INTO tbL_excel
SELECT *
FROM OPENROWSET
(
‘Microsoft.Jet.OLEDB.4.0′,
‘Excel 8.0;Database=\\C\Testexcel.xls’,
‘SELECT * FROM [Sheet1$]‘
)
 
Note: It is not work for windows 2007 excel sheets. Because those sheet extenction is '.xlsx'

SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

CSV stands for Comma Separated Values.
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(
ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME
)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

 
 
 
 
 
 
 
 
 
 
 
 
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
 
BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

Where is ROWID in Sqlserver 2005 like oracle?

The nearest equivilant is the Row_Number() function, but it doesn't work


exactly like the orable version.
Here's an example...

Select firstname,Row_Number() over( order by firstname ) as RowId from mytable Order By Firstname

This will produce the same results. Why is it different? The over part of the command. This specifies the order that the numbers are to be generated. But it's not necessarily the order of the resultset. The order by at the end of the statment does that.

Restrictions on Updating Data through Views

You can insert, update, and delete rows in a view, subject to the following limitations:

1. • If the view contains joins between multiple tables, you can only insert and update one table in the view, and  you can't delete rows.
2. • You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
3. • All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
4. • Text and image columns can't be modified through views.
5. • There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.

How to set alias name for column while retrival in sqlserver 2005

Alias name is name which is given by user....

Select Emp_ID, Emp_name from Emp
o/p:
Emp_ID Emp_name
---------------------
101       suresh
102       kumar

If i need to display Emp_ID as Employee Number and Emp_name as EmployeeName, Then we will change our querry as follows

Select Emp_ID AS  Employee Number , Emp_name AS EmployeeName from Emp
o/p:
 Employee Number   EmployeeName

---------------------------------------
101                                   suresh
102                                   kumar

Compare two counts in where clause in sqlserver 2005

We can compare two counts in where clause of select statement:

Select * from Emp where
 (
  (select count(*) from dept where ISactive=1) = (select count(*) from dept where Status=1)
 )

In this case it fetch records only the counts are equal...

truncate time part from datetime in sqlserver 2005 or remove time part from datetime

Example:

My date looks like '2010-09-15 16:59:27.797'. If i want to remove time part and i want to display only date part we can use following code snippet.

Declare @test datetime


set @test=getdate()

select convert(varchar(10),@test,101)
 
After execution of above 3 statements output looks like;
-----------------
09/15/2010
 
Note: we may pass column name as argument insted of @test(variable).
 
 Ex: select convert(varchar(10),@column_name,101) from my_table
       my_table is table and @column_name is name of column that contains datetime values....

Microsoft SQLSERVER DBA InterView Questions

Here is the good collection for Sql server Data Base Administartor Interviews. Download from the following link. Enjoy............

Click here to download.

If any one has questions or any corrections needed feel free to send mail.

Brief about Select….into statement with an example in Sqlserver 2005

Select into is used to create back up copies of tables. It selects data from one table and inserts into another.

Syntax:
Select * INTO dest_table_name FROM source_table_name

Example: Select data from customer table into customer_backup
Select * INTO customer_backup FROM customer

It can also be used with WHERE clause for a condition.

Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;

Brief about Bulk copy with an example.

Answer
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file and then imported into the SQL server using the bcp command. It can also be used to transfer data from a table to a file.

What is “Index Tuning Wizard” in sqlserver 2005?

Index Tuning Wizard is a software application that identifies tables which have inefficient indexes. It makes recommendations on how indexes should be built on a database to optimize performance. The recommendations are based on T-SQL commands that the wizard analyzes.
Come up with comments if any one wants to add more for this article... :)

List out what Index Tuning Wizard can do in Sqlserver 2005

1. It identifies tables in need of an index change
2. Implements recommendations
3. Determines how a proposed change might affect performance
4. Has the capability to make indexes change immediately and schedule them
5. Helps to tune indexes.

What are high availability features in Sql server 2005?

1. Log Shipping
2. Mirroring
3. Replication
4. Failover Clustering

1) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.
Usage Scenario: You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.

2) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror ). Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.

3) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active. It has no down time
Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.

4) Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. Here’s a good article on adding geo redundancy to a failover cluster setup.

You might want to look at the licensing options of SQL Server, various editions available and how they map to above features