Skip to main content

Posts

Showing posts from 2014

The Top Ten Software Companies in India

Names TCS LIMITED WIPRO LIMITED INFOSYS TECHNOLOGIES LIMITED SATYAM COMPUTER SERVICES LIMITED I-FLEX SOLUTIONS LIMITED TATA INFOTECH LIMITED CMC LIMITED MPHASIS BFL LIMITED MASTEK LIMITED MASTEK LIMITED NIIT LIMITED

Career and opportunity in NGO'S

Working in NGO needs different sets of knowledge, attitude and skills. And obviously it offers a good salary also. But the initial days are very crucial as every moment a development worker has to deal with people. In India different courses offered by various institutions offers an opportunity to enter into development sector (NGO sector is known as development sector for the professionals). The reputed institutes includes, TISS, Tata –Dhan Academy, XISS,KIIIT and IRMA. Etc. these institutes offers different courses in different names. Like MSW, Development Studies, Development Management, Rural Management etc. The job opportunity is very good. Initially one can earn between Rs.15000-20000. In abroad one can earn more but in most of the cases it need at least three years of experience. lease visit website of the few reputed NGOs. You could visit devnetjobs.com where you will find many opportunities. There are many NGOs working in Bangalore. Please explore with them. In ma

Difference in CHAR and VARCHAR2 Data type in SQL

Char is fixed length Datatype. and Varchar is variable length datatype. Suppose you have 2 columns, 1 with Char(40) another with Varchar2(40)  and both columns has values with only 10 characters in a record that time SQL server still occupy 40 character space in system for 1st column and 10 character space in system for 2nd column. Simple pick char if length is going to remain same otherwise pick varchar as a speed both have its advantage . Choosing data type cannot be done by yes or no you have to choose data type as per your requirement both are good and bad. 

Suggestions to get rid of from recovery mode of SQL database

These are just some Suggestions to get rid of from recovery mode of SQL database. Mostly suspension of database made due to any long process and long suspended query which generating deadlock and infinite loop for your database. Try these option. A. Alter database dbname set emergency Alter database dbname set single_user dbcc checkdb(‘dbname’,repair_allow_Data_loss) alter database dbname set multi_user B. we have to run dbcc checkdb only in the third step,because User does not want to loose the data. If that don't works, then we have to use allow data loss. but first option is better to restore with available backup, if back up is not there then we have to use this method. C.First find which file is corrupted, if Data file corruption immediately take Tail log backup then restore full backup followed by differential , log backups with no recovery finally restore tail log backup with recovery. D. 1. Ensure that the device file is actually available. 2. Use the supplemental stored

Finding Reasons for Slow SQL Server database performance

If you found your SQL Database performance is very slow then you can check below points. 1.Check if any blocking. 2- Check execution plan for any costly operator. 3- We can run DTA and take help from that but keep in mind it's not necessary to implement all suggestions provided by DTA. 4. Index fragmentation . 5. Missing indexes. 6. Stale update stats 7. Blocking & Deadlocks of queries. 8. Excessive compilation & recompilation. 9.Check long queries using trace of suspended records on high working time of transactions. 10.Check RAM utilization at SQL Server.

What is VLF in SQL?

VLF  stand for virtual log file. Its the internal structure of log file. Virtual log file means while running (DBCC loginfo)it will shows the status 0 indicates Recoverable 1 indicates Active 2 indicates unused 3.indicates reused. The Tlog file internally divided into virtual log files. You can see how many vlfs you have in your log file can be find out by running dbcc loginfo. Too many vlfs will might slow down the db recovery. Each vlf will be having status 0 to 9. These status number indicates the current vlf's status i.e 0 reusable 1 waiting for log backup 2 Active and so on.

What is a minimal logged in SQL

1.Minimal information it writes in the log file, when databse in bulk model. 2.If transaction is minimally logged only enough information is logged in transaction log to rollback the transaction no information is logged to roll it forward. This is why you loose point in time recovery if operation is minimally logged.

What is difference between Single user mode and Offline mode om SQL

1.When database is in offline users cant connect we need to bring our database in to emergency mode to connect to db for this we will try to bring in single user mode so that we can able to connect with single user. 2.Single user mode means access only SA admin,offline mode means no one user can not access. 3.To bring the database online from offline,there is no need of putting it in emergency mode. 4.If  database  in single user mode it will allow only one connection, it is in offline we can't do any thing.

Total IPs Required for fail-over clustering in SQL

How many Total ip's required build win&SQL Failover clustering Active/Active & Active/Passive? Ans. In a 2 node cluster each ip for each node and one ip for win cluster and one for msdtc and one for each cluster group .. If u use private network for heartbeat then 2 more ip .. So total of 5-7 In a two node cluster 7ip's are needed two private ip's for connecting to quarum. one for node A and other for node B, two public ip's one for node A and other one For node B and one for msdic and one for msclust and one ip for switch for connecting to san drives in which data is stored.  Total 7ip's are needed.

Script to apply any user permission to the all databases of SQL

/* It is a new feature and it has ability to create user defined server roles and assign server level/scope permissions. */ /* - Following actions are performed to implement this new feature: - Created Server role - Created Login and made member of server role - Granted Standard view permission After completion above steps the login has ability to make connection any database with data reader permission. */ USE master GO CREATE SERVER ROLE ALLDBREADER Go CREATE LOGIN [DBREADER] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE ALLDBREADER ADD MEMBER [DBREADER] GO GRANT CONNECT SQL TO ALLDBREADER GRANT VIEW ANY DATABASE TO ALLDBREADER GRANT VIEW ANY DEFINITION TO ALLDBREADER GRANT VIEW SERVER STATE to ALLDBREADER GRANT CONTROL SERVER TO ALLDBREADER DENY SHUTDOWN TO ALLDBREADER Go USE master GO CREATE SERVER ROLE ALLDBREADER Go GRANT CONTROL SERVER TO ALLDBREADER DENY ALTER ANY DATABASE TO

Script or query for FAILED JOBS Details with error Messages in SQL

You can use following Script or query for FAILED JOBS Details with error Messages in SQL. I hope it will help you. USE Msdb Declare @lastRunDate int =replace(CAST(getdate()as date),'-','') SELECT Sj.name as JobName ,CASE SJH.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeded' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Canceled' END AS JobStatus,SJH.message from sysjobhistory SJH inner join sysjobs SJ ON SJH.job_id = SJ.job_id where SJH.step_id = 0 and sjh.run_date =@lastRunDate and SJH.run_status =0

What is SQL Server Profile

SQL Server Profiler. It is a graphical based tool provided by SQL Server. It enables you to trace a query. For example.you have a SP which you wish to trace. you can execute the SP and start a profiler trace on it. It will show you row by row execution of the SP SQL Server Profiler is used to trace and analyze the SQL Server running processes and queries in the cases of performance issue. It is taken high memory so not used in main server. It is have so many events for trace. so your select desired event and filter it. It can use to find the blocking by applying trace and catch everythng runing on the server

How to Get historical data of blocked process in SQL

WITH [Blocking]AS (SELECT w.[session_id] ,s.[original_login_name] ,s.[login_name] ,w.[wait_duration_ms] ,r.[scheduler_id] ,w.[wait_type] ,r.[status] ,r.[wait_resource] ,w.[resource_description] ,s.[program_name] ,w.[blocking_session_id] ,s.[host_name] ,r.[command] ,r.[percent_complete] ,r.[cpu_time] ,r.[total_elapsed_time] ,r.[reads] ,r.[writes] ,r.[logical_reads] ,r.[row_count] ,q.[text] ,q.[dbid] ,p.[query_plan] ,r.[plan_handle] FROM [sys].[dm_os_waiting_tasks] w INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id] INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id] CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p WHERE w.[session_id] > 50 ) Insert into Blocking_v1 ([session_id] ,[blocking_session_id] ,[WaitingUserSessionLogin] ,[BlockingUserSessionLogin] ,[scheduler_id]

What is Covering Index in SQL

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional look-up to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation. covering index is a non-clustered index which stores additional columns at leaf level to avoid bookmark lookup to heap/clustered index.this is typically created using INCLUDE clause. Let say we have a table tab1 and we have columns starts from column 1 to 10. and we have clustered index on column1 and non clustered index on column 2 . for example you written a query select * from tab1 where column 2=200. this query first will go and look for the matching data in non clustered index and because of our query want to retrieve all the columns it will perform key look up , not helathy. this time i had one more non clustered index on column3 and column4 ,composite.in this case our query

Port in sql server

SQL server assign default port 1433 to default instance...for named instance it dynamically allots a port number, ideally it is five digit port number. To see port of SQL Server you can check as below. Go to configuration manager-->SQL Serve network Configuration-> Select instance-->right click TCP/IP properties-->IP Properties

Dedicated admin account in SQL

Dac means dedicared admin account, which is used to access sql server at .by default 1433 is sql server port , 1434 ia dac account. Starting the server in single user mode will use DAC (Dedicated Administrative Connection) connection. DAC Connection will use If the number of connections are limited for the instance and all are connected and if some one wants to connect and fails to troubleshoot if DBA want to connect he will also be restricted then he can use DAC connection to connect to the instance. When ever the SQL Server standard connection not establish the connection then we go with DAC connection . only sys admin can use this DAC to connect the server and troubleshoot the problems.

Interview questions for sql DBA

1.What are Differences in Delete & Truncate . 2.What are Differences in UNIQUE and Primary key ? 3. What are the agents involved in replication 4.Which Types of clusters and how many ips are required to install failover cluster? 5.Configuration of logshipping and DB mirroring. 5.What are Types of backups and backup scenario? 6.What is deadlock and blocking? 7.Differences between clustered and non clustered indexes? 8.Why SQL Server Agent is used? 9.What are Types of System databases 10.What are the steps for restoring a database?

what is linked server in sql server?

Linked server means to retrieve the data from the other server to connect our own instance, before configure the linked server we must know the target server login and password then only we access the data from the other server. Linked server means access the data from one server another server is called linked server. store procedure is sp_addlinkedserver. When we have to run the distributed transactions we need to linkup the servers internally we use linked servers concept.Here distributed transactions means A query which has to executed on multiple servers at a time is called distributed transactions.

Checkpoint and lazywrite in SQL

Checkpoint : Write all the dirty pages into buffer cache. It is not clear from buffer cache is called checkpoint.check point is the internal process which will take dirty blocks from buffer pool to data file on interval based. Lazywriter: Write all the dirty pages into buffer cache. It is clear from buffer cache is called lazywriter Checkpoint and lazywrite do the same job of flushing out the (8k size) pages from the memory to disk the only difference is of when they do this task. A lazy write does its task when there is resource contention and sql engine needs to process more pages and bring them in the memory. The lazy writer in this case gets all the pages which have been residing in the memory for the longest time and flushes them to the disk. A checkpoint on the other hand does the task for the SQL engine to create a stable point from which SQL can recover in case there is a failure of service during some processing. A checkpoint is not issued due to lack of resources. Please not

Grant Revoke privilege in SQL

Grant/Revoke are the keywords which uses to grant/revoke privileges on an object to/from a user/group. Grant is a keyword you use when you want to authorize/authenticate. a principal and Revoke is the Keyword used when you want to take back the Authorization/authentication from the Principal. Principal refers to Lo-gin at instance level.Database user at Database level. Grant,revoke,deny,with grant are object level security.Grant means giving permission on a particular object to user.Command: grant privilege on object name to username. Revoke means taking back the permission. Command: revoke privilege on object name from username. Deny means restrict user not to do something command: deny privilege on object name to username. With grant means by using grant user can give grant permission to other user command: grant privilege on object name to user with grant.

Clustered index and non clustered index in SQL

Clustered index which reordered the table and saved separately as index table(data pages stored as B-tree) there can be one clustered index per db & Non clustered index is typically a index(data pages stored as heap) A table can have only one clustered index with many non clustered indexes. The leaf nodes of the cluster index stores data pages and non clustered index stores key values(where as table includes clustered index) or rid's(table contains non cluster index only).

Advantage and disadvantage of Team Viewer , Ammy Admin and show my PC etc

Advantage of Team Viewer , Ammy Admin and show my PC etc You can share your desktop to other user or pc to take live support help from your friend or colleague. You can also help to other friends also. If you want to use limited hours in a month use of   Team Viewer , Ammy Admin and show my PC then you can use trial version but if you want to use lot of time in a single month then you need to buy license. You can buy license online. Disadvantage of Team Viewer , Ammy Admin and show my PC Mostly these type of connections registers all clicks and keypress, that means they can know about all your logins and passwords and you PC could be hacked or misused(Its possibility but mostly they do not do it).

How will you upgrade for Active and Active cluster server

To upgrade for Active & Active cluster server Active to active means you will install SQL server on each node so they will be active to active. First transfer both sql server service on node A and upgrade Node B then transfer both services on node B and upgrade node A and then split the service on separate nodes(Node A & B)

Datebase-Unavailable database:testdb is unavailable(suspect,offline,recovering etc) in SQL

SQL Error:  Database-Unavailable database  :testdb is unavailable(suspect,offline,recovering etc) 1.If the database is offline, the approach should be to bring the database online by either from GUI or from query : ALTER DATABASE <database_name> SET ONLINE and if it still doesn't come up..we should check the logs for the reasons..and based on the outcome we have to proceed accordingly. 2.If the Database is offline, then try to make the database online using the script ALTER DATABASE <database name> SET ONLINE. If it is not recover the database to online, then check in event viewer for possible error, if error is 17204--then data file has damaged,if error is 17207--- then log file has damaged,If you see this error then the database is there in suspect state. To Recover the suspect state database u need to follow steps as below: If data file damaged: 1. Take the tile log back up. 2.Restore Full back up with no recovery. 3.Restore Differential & Log Backups

How to Shrink TempDB in Sql Server?

To Shrink TempDB in Sql Server below ideas can help you to handle the issue. If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command. 1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are: (i)check if any locks are there through (sys.dm_tran_locks)or sp_locks (ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance (iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE wh

Database Administrator daily tasks

These are the task list that must be done by a DBA on daily basis. 1) Check System Event Logs and SQL Server Error Logs for unusual events. 2) Verify that all scheduled jobs have run successfully. 3) Confirm that backups have been made and successfully and moved to the backup storage or secure location. 4) Monitor disk space to make sure SQL Servers will not run out of disk space. 5) Periodically monitor performance of database and system using both System Monitor and SQL Server Profiler. 6) Monitor and identify blocking issues. 7) Keep a log of any changes you make to servers, instance setting, database setting and monitoring script. 8) Create SQL Server alerts to notify you of problems, and e-mailed to you. Take action as  needed. 9) Regularly restore backups to a test server in order to verify that you can restore them. Summary: Installing DB software Creating databases Performing upgrades of the database and software to new release levels Starting u

Index rebuilding in SQL

When index are rebuild that means the old indexes on the table are dropped and new indexes on the table are created. Due to the creation of the indexes on the table all the data is organised in the leaf level (cluster index) and organised simultaneously. Thus maximum the number of rows in your table , maximum will be the page count and maximum will be the time taken in rebuilding the index. Indexing is nothing but ordering of pages, thus it depends on page count

SQL DBA Checklist

Following can be SQL DBA Checklist on daily,weekly and monthly basis. 1.SQL DBA daily checklist can include Database backup status, Database integrity checks, failed jobs. 2.Weekly checklist can include index reorganization, update stats etc . 3.Monthly checklist can include Restoration drill for critical databases to ensure backup's availability in case of any unusual happening etc.these are general points.although it depends on current environment and issues how you define your checklist as a DBA.

Some Light on Roleback and commit transaction in sql recovery mode

If log truncation is performed when your transaction is still running then it will truncate only committed transactions and there is no impact on the current running transaction. if your transaction is completed and after that log truncation is performed it will clear your transaction. SQLServer internally maintain some lsn or modified pages details in one page so if you rebooted the server, if transaction is completed then changes applied into mdf files and there is no change in the DB and it will online after reboot. But if you rebooted the server in the middle of transaction then after server is online , database went into recovery mode and it will rollback all changes as per the changes after all the rollback completes DB will come into online .

Way to find differential backup for SQL Database

1.Check MSDB backup history for that database select * from msdb.dbo.backupset where name='dbname' and type='i' order by backup_finish_date desc 2.dbo.backupset: provides information concerning the most-granular details of the backup process dbo.backupmediafamily: provides metadata for the physical backup files as they relate to backup sets dbo.backupfile: this system view provides the most-granular information for the physical backup files USE AdventureWorks GO SELECT TOP 100 s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn, CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN &

How To Identify The Long Running Queries in SQL?

These queries can help you  To Identify The Long Running Queries in SQL. 1.SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where total_elapsed_time >= 800000000 ORDER BY total_elapsed_time / execution_count DESC; 2.DBCC opentran(This command will display the longest running transactions on the database) and select * from sys.dm_tran_active_snapshot_database_transactions(this command will display the transactions about replication and mirroring and snapshot isolated related transactions)

How to add multiple foreign keys in a table from different reference tables in SQL

To  add multiple foreign keys in a table from different reference tables in SQL you can try below methods in sql query editor. 1. Alter table table_name add constraint constraint_name foreign key(col_name) references table_name(col_name) GO. 2.Create foreign constraint WITH NOCHECK first then CHECK it with another alter statement.. Make sure to put GO in between each statement.. Just to safe side, SET ANSI PADDING ON on the top and OFF at the button..

How to add multiple log files in SQL Database

Below query can create ldf file.Its creating second ldf file on Testdb Database. You can create as many as you want. USE [master] GO ALTER DATABASE [TestDb] ADD LOG FILE ( NAME = N'TestDB_Log2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\Testdb_Log2.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO I Hope this could be your expected solution.

How to patch service pack for sql server clustering?

Patch the passive node first then reboot the node and then fail over instance on that node and then in similar manner patch the other node. This you can apply for sql 2008 and above. In SQL Server 2012, there is a Cluster Aware Update that will automatically Update all the nodes. Start applying the service pack on the passive node and then failover the instance to passive node after the installation is over on the passive node now start applying the service pack on active node and again fail-over the node. On SQL Server 2008/ 2008R2 : Apply patches/hot-fixes on passive node(N2) first then reboot the server. once the passive node(N2) is up, fail-over the resources from active node(N1) to passive node(N2), perform the same steps on N1 node and fail-over the resources from N2 to N1. On SQL Server 2005 : Apply patches/hot-fixes only on active node(N1), same will be applied on passive node(N2) too. On SQL Server 2005 we need down time during this activity. But on SQL server 2008/2008R2 we

How to add files to logshipped SQL database?

Run log back up job on primary server and disable backup job to make sure not to run any backup jobs further until we enable again. check the log back ups if there are any need to be restored on secondary db, restore them if any until all the log backups complete. Then disable the log shipping add mdf-ldf file take a log backup and restore the same on secondary database them enable log shipping and enable backup job as well. If the drives and path exist on secondary log shipping will not break, if the drives are different obviously log shipping will fail.u have to disable all jobs in primary make sure that in between no backups will happen. restore that log file backup with move option. If the path of both files are same it wont break logshipping. If.If path is different then manually restore that particular log backup with move option.

What is latches in sql server

Microsoft SQL Server provides counters to monitor internal SQL Server resource called latches.Monitoring the latches to determine user activity and resource usage can help you to identify performance bottlenecks like Average Latch Wait Time,Waits/sec We can monitor the performance by using cpu usage and memory usage what is use of latch. Latches are light weight locks which SQL server acquires for pages in buffer. Latch is a synchronization mechanism between threads.Latch protects access to in memory data structures. latches are lightweight as compare to locks. the best example to understand latch is TEMP DB LATCH CONTENTION.

SQL Server Build version

This is a list of ll SQL Server Build versions till Year 2014 6.50.201 SQL Server 6.5 RTM 6.50.213 SQL Server 6.5 SP1 6.50.240 SQL Server 6.5 SP2 6.50.258 SQL Server 6.5 SP3 6.50.281 SQL Server 6.5 SP4 6.50.415 SQL Server 6.5 SP5 6.50.416 SQL Server 6.5 SP5a SQL Server 6.5 7.00.0623 SQL Server 7 RTM 7.00.0699 SQL Server 7 SP1 7.00.0842 SQL Server 7 SP2 7.00.0961 SQL Server 7 SP3 7.00.1063 SQL Server 7 SP4 SQL Server 7 8.00.0194 SQL Server 2000 RTM 8.00.0384 SQL Server 2000 SP1 8.00.0534 SQL Server 2000 SP2 8.00.0760 SQL Server 2000 SP3 8.00.2039 SQL Server 2000 SP4 SQL Server 2000 9.00.1399 SQL Server 2005 RTM 9.00.2047 SQL Server 2005 SP1 9.00.3042 SQL Server 2005 SP2 9.00.3042.01 SQL Server 2005 "SP2a" 9.00.3054 SQL Server 2005 KB934458 9.00.3077 SQL Server 2005 Security Update 9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 9.00.3161 SQL Server 2005 SP2 CU1 9.00.3175 SQL Server 2005 SP2 CU2 9.00.3186 SQL Server 2005 SP2 CU3 9.00.3200

Error no:1418 on Mirroring in SQL

The server network address "xxxxx" can not be reached or does not exist. Check the network address name and reissue the command. Pls check endpoints on all servers using SELECT * FROM sys.endpoints If 1418 error: check it these, duplicate end points exists, tcp/ip enabled or not, check editions principal and mirror, windows firewall off, check login not created in mirror instance. How to delete duplicate end points if exists ? IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'EndPoint_name') DROP ENDPOINT [EndPoint_name] GO

SQL Error on running of Enterprise portal ,sharepoint site

I am getting following SQL Error on running of Enterprise portal ,sharepoint site Could you advice solution on this error. Unknown SQL Exception 10054 occured. Additional error information from SQL Server is included below. A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) Possible solution: An error has occurred while establishing a connection to the server. When connecting to SQL Serve and this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.)

Reason for application is running slow from a DBA side in SQL

There might be some reasons behind it: 1. Blocking situation 2. lack of userfull indexes 3. Lack of updated statistics 4. deadlock situation 5. network issue 6.Check the status of query.It can be either of two reasons.Either external or internal factor.External factors are resource crunches .Internal factors are because of missing index, invalid cache plan, fragmentation,so on.Starting point to identify higher level and then do a deep dive into particular area.

Why we go for Replication in SQL

Log shipping, mirroring & replication are various types of HA (high availability) features. Each of them have their own set of pros and cons and various sets of features. Hence there is no compulsion and requirement to implement all of them. Rather the decision to choose them is driven by business requirement and at times the resources available for use. Db mirroring and log shipping are database level HA the replication is object level HA. If you have SSRS, then go for replication, never keep OLTP database to fetch SSRS reports. Replication is never a DR lets take an example that for your reporting environment you habe used transactional replication. On the reporting database server for best performance you build plenty of indexes which are not required on the OLTP environment therefore I believe clustering mirroring and log shipping are the best option always have a powerful backup strategy that will always help you.

Move Reporting server database instance from one server to another in SQL Server

Following the steps below to Move Reporting server database instance from one server to another in SQL Server 2005,2008,2012 etc 1. Take the backup report server and report server temp db and restore in another server. ( if you want to move db also) 2. In the new server open reporting service configuration manage and re configure with new server details and start again. 3. Remove the encryption keys . Then reports will work from new server.

Committed and uncommitted data in Log Backup in SQL

Transaction log backup captures the transaction log which contains a record of all committed or uncommitted transactions. In full recovery model, some time log file getting full that and first we will check log bak taken r not the we ill take log backup then it will clear all committed transaction from log file this will happen if have doubt create on database do some bulk(select into or insert into) trans and check log file size and take t log back then compare sizes. Transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.In the event of of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server.On restarting the server, database goes through the recovery process.During this recovery process , the transaction log is used to make sure that all committed transaction

What is nolock in SQL Server

Nolock is nothing but it decrease the concurrency of busy system. When there is nolock for data then that data can be taken by another transaction. Reads the dirty page irrespective to the isolation level. (Not in all scenario). NOLOCK is used in SELECT queries as query hint, it functions the same way as READ UNCOMITTED isolation level where dirty reads are possible. It is highly recommended for all applications where Performance and concurrency is vital and dirty reads takes a backseat. Like in non-banking and web based applications it is much used, and also in internal web applications used in back-office by multiple users. We can use NOLOCK in all non-financial and non-payment related transactions most of the times. Every domain and case needs to be verified.

What is the use of sqlserver browser service?

Sql browser service is used to connect to a instance within the server. It runs at the OS LEVEL of architecture. Sql server browser service is used by sql server for named instance name resolution and for server name listing. i.e., for default instance default port will be 1433 and for named instance port will be dynamically allocated to provide the named instance port details browser service is used.

Function of functional keys on keyboards

Default ports in computer,network peripherals

Software configuration,installation

what is difference between configuration and installation of software? Answer: Software installation is a process in which we set up software ready for execution usually done by a installer. while configuration includes to set up a complete environment in which discussed software can execute. or simply we can say installation is a sub set of configuration.

Unexpected growth of sql log file in sql 2008

The log file size grows and takes all free space sometimes.  Sometimes it works OK. Following advice may help you to handle the situation I may be mistaken, but I seem to remember a problem with a previous version of SQL doing this when some other process was interfering with the database files, like a system backup, antivirus scan, system updates or a fragment. When the process interferes with the SQL directory, the log files grew out of control until the hard drive filled up.  If you are running something like this, be sure to exclude your database directories from it. If you have a different server handling a function like this, exclude the db directories or server all together. You could put these database In full recovery and set transaction log backups or set up a job that runs DBCC Shrink file. and schedule it to run prior to mid-night. But you should read through the error log and event logs to determine the cause of the problem.

Important SQL Questions for Exam Preparation

1. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names? A. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\'; B. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE; C. SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%'; D. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\'; 2. The number of records in a table emp is given by statement A. Select total(*) from emp; B. Select count(*) from emp; C. Select sum(*) from emp; D. Select total from emp; 3. Write a query to Display the Name and studentid of all Students of branch CSE and ECE in alphabetical order by name. 4. Can you use the SQL JOIN and SQL HAVING clauses in one SQL statement? A .Yes. B . No. C . It depends. D . Don't know 5. # Which four are valid Oracle constraint types? A.

Major Indian Software and Services Companies List

A G Technologies Pvt Ltd, Mumbai ERP, IT enabled services, web enablement, application development A P Technosis Ltd, Noida Software development (Java, VB, ASP), Offshore services, Consultancy Aalayance Ecom Services Pvt Ltd, Bangalore Enterprise Application Integration, Object Technologies, Public Key Infrastructure, Software development and testing ABO Software Private Limited, New Delhi Electronic Commerce, Electronic Data Interchange, Telecomm-unication - X.25, Frame Relay, Object Oriented Programming Accel Software Solutions Limited, Chennai Software Development, IT Training, IT Enabled Services, E-Biz Solutions Accenture Services Pvt. Ltd., Mumbai Project deliver, Custom application development, Application management & conversion, Creation of client-specific facilities Access Networks India Pvt Ltd, Trichy Protocol development, CTI development, CRM systems, operational support systems for telecom service providers Ace Software Exports Ltd, Rajkot Database Creation, Software