BuildingCareerForU

Let's understand how shrinking Database can increase Index fregmentation. When the DB is shrunk, the sql server db engine goes at the end of the data pages located in the database and adjust them in the size mentioned for shrink activity. This is the reason why shrink causes data pages unorganized and result in fragmentation. Let's create a database name CitiesPopulation. and some tables like EMployeeInfo EmployeeInfo1 EmployeeInfo2 PopulationInCaror Get the DBId from the output of below query- select * from sys.sysdatabases For my database CitiesPopulation, the DBid is 7. Now check for the index fregmentation status in your Database with the help of below query- SELECT *  FROM sys.dm_db_index_physical_stats(7, null, null, null, null) You can also check the fregmentation for a perticular table by giving the object_Id in the above query e.g. SELECT *       FROM sys.dm_db_index_physical_stats(7, OBJECT_ID('Employeeinfo'), null, null, null

This blog is intended to help the first time users of sql server to interact sql server easily. So let's understand what we should know while installing sql server. SQL server is very interactive database engine. You will find this far more interesting when you install and start using this. Sql server comes in many editions specific to usage. a. SQL Server Enterprise Edition - Specific for enterprise environment b. SQL Server Developer Edition  - This has all the features same as Enterprise Env. This edition can be used only for development work just like writing codes but can't be used as a back end for the application in any enterprise. c. SQL Server Standard Edition  - This supports four cpus. "How to find the number of physical and logical CPUS?" . This some lesser features than the enterprise and developer editions. As I am writing this wire for the users who are using for the first time so I shall not go deep defining the difference between these editions

Will Indian Rupee Cross 55?? It is always recomended to use either Clustered or NonClustered Index on a table for better performance of query execution. In this blog I  shall describe some important features of an Index which will help you to understand how Index plays a role in query execution i.e.-  a-Clustered Index Scan  b-Clustered Index Seek  c-Key lookUp (In SQL Server 2008 it is KeyLookup and it was BookMark lookup in SQL Server 2005)  Let's Do- I am creating a table by copying the data from an existing table EmployeeInfo, which is a very simple table having only four columns, see below (You can also create a fresh table)- EmployeeInfo1 SELECT   * INTO EmployeeInfo1 FROM EmployeeInfo EmployeeInfo1 table looks like- Let's Do- Create Clustered Index on EmpID(INT, Identity) in EmployeeInfo1 with FillFactor=100 CREATE CLUSTERED INDEX X_EmpId_EmpInfo1ON EmployeeInfo1(EmpID) WITH (FILLFACTOR = 100) Now if you will check the Execution Plan for the simple

Q: What is the standard setting of Lookslive, IsAlive and Pending Timeout? Ans: LooksAlive - 5 sec IsAlive - 30 sec Pending Timeout - 180 sec Note- Do not modify Pending Timeout. The value, represented in seconds, is the amount of time the resource in either the Offline Pending or Online Pending states has to resolve its status before the Cluster Service puts the resource in either Offline or Failed status. Q: Can you change failover policy? If Yes then how? Ans: To configure the failover policy, in the Threshold box, enter the number of times the group is allowed to fail over within a set span of hours. In the Period box, enter the set span of hours. For example, if Threshold is set to 10 and Period is set to 6, the Cluster Service fails the group over a maximum of 10 times in a 6-hour period. At the 11th failover in that 6-hour period, the server cluster leaves the group offline. This affects only resources that were failed over; therefore, if the SQL Server resource fail

Q6 - Why is this needed to manage the max memory on failover cluster? Ans: So if you have a two-node cluster, each currently configured with 8 GB of memory as well as two instances of SQL Server (one with 5 GB of memory and the other with 7 GB), 7 + 5 does not equal 8; it equals 12. If they both happen to run on the same node, one will probably be able to get the memory it needs and the other will not. You need to adjust the amount of memory your instances are using so that, in a failover scenario, you do not starve one instance or possibly have it not start up after failover Q7-Why antivirus should not be run on? Ans: If there is no reason to put antivirus software on the cluster nodes dedicated to SQL Server, very secure, and without file shares on it, do not install the software. If your corporate security policy dictates that antivirus software must be configured on all servers, you must set the filtering of the antivirus program to exclude the scanning of all SQL Serve

Will Indian Rupee Cross 55?? Q: What will happen if you try to start the SQL service on the passive node? Ans: This will start because error log, mdf and ldf files are possessed by active node. Q: How exactly the SQL service starts on any node? Ans: Below is the list of all the steps in error log at the of SQL Service starting on one node, all the logs are self-explanatory- 1-SQLServerAgent service successfully stopped. 2-[sqsrvres] OnlineThread: asked to terminate while waiting for QP. 3-17148 :SQL Server is terminating due to 'stop' request from Service Control Manager. 4-17104 :Server Process ID is 9344. 5-This instance of SQL Server last reported using a process id of 6804 at 1/23/2011 10:39:34 AM (local) 1/23/2011 6:39:34 PM (UTC). 6-SQL Server is starting at priority class 'normal'(16 CPUs detected). 7-SQL Server configured for thread mode processing. 8-17125 :Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Block

Will Indian Rupee Cross 55?? Cluster environment is prevalent not only in big organizations but also medium and small. This is observed that in "Sql Server DBA Interview" the interviewer tests candidates with cluster questions. You will always be in the good books of your interviewer if you know at least basic info about cluster. Q1-   What is the status of services on passive node for failover cluster in SQL server? Ans - SQL services will be in manual and stopped. Cluster service will be in automatic and started mode on both the nodes. Q2- Can you move the resources after pausing the node? Ans - Yes resources can be moved after pausing the node. But we can't move them back till the node is paused. Q3-   Where is the prevent failback option? Ans - This is the resource group property. It doesn't allow the resources to move back to the preferred node once the node is ready to take the connections after failover. Q4 - How does

1-       Create Table EmployeeInfo CREATE TABLE EmployeeInfo ( Empid INT IDENTITY ( 1001 , 1 ) PRIMARY KEY CLUSTERED ,   Empname VARCHAR ( 20 ),   SalaryInLaC DECIMAL ( 10 , 2 ),   Location VARCHAR ( 20 )   ) INSERT INTO EmployeeInfo VALUES ( 'AB1001' , 15.2 , 'HYD' ) INSERT INTO EmployeeInfo VALUES ( 'AB1002' , 10.5 , 'PUNE' ) INSERT INTO EmployeeInfo VALUES ( 'AB1003' , 5.2 , 'BANG' ) INSERT INTO EmployeeInfo VALUES ( 'AB1004' , 20 , 'DELHI' ) INSERT INTO EmployeeInfo VALUES ( 'AB1005' , 15.2 , 'HYD' ) INSERT INTO EmployeeInfo VALUES ( 'AB1006' , 10.5 , 'PUNE' ) INSERT INTO EmployeeInfo VALUES ( 'AB1007' , 5.2 , 'BANG' ) ---------------------------------------------------Upto 2000 INSERT INTO EmployeeInfo VALUES ( 'AB2000' , 10.5 , 'DELHI' ) 2. Find out how many of the employees are ther