Software Applications Programming Errors and solutions.

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.

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?

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 : 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 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 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 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).

What is Full quorum in mirroring in SQL Server? Ans:Full quorum forms when both the partners with witness are connected and performing their roles. “full quorum” means that all three roles are available and all is going swimmingly.

What is system configuration of your cluster servers in SQL? Ans:If I have around 250gb physical and 30 gb allocated forsql , win 2008 sp2 ,they are physical. You should know about this,System configuration means RAM Size and How many core/processor are in CPU.Apart from this what is the OS version

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)

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

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

How to change time to be 1:30:00 am/pm Instead of 13:30:00 ? You can try below queries. SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2) declare @time time = '13:30:00' SELECT convert(varchar, @time, 22)

If the database is in single user mode then You can bring that database to normal state following way. ALTER DATABASE Database_Name SET MULTI_USER Go to ssms ->right click on database properties--> select options then scroll down to "Restrict Access" then change it MULTI_USER

Service Pack, Cumulative Update and Hot Fix are all patches to the bugs faced by Users/Vendors. Severity and No. Of Users facing problem will flow down from Higher to Lower i.e, Service Pack (Higher) --> CU (Medium) --> Hot Fix (Lower).

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

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

Data center is used to store all our servers and need to maintain (power supply,network,connectivity,AC and others) those servers from the DC in a specified safe environment with all the facilities.. without proper approvals even CEO will also not allowed into the DC's for some security reasons.

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.

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 .