File Name: sql server dba questions and answers .zip
Production, information conversion, technical fields, communication, etc. In this SQL Server DBA Training Course, you will gain mastery over recovery models, backup solutions, server-level roles, server profiler, traces, extended events, etc.. These hands-on exercises will prepare you to work in real-time scenarios. There is a parcel of chances from many presumed organizations on the planet.
Ans: Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Requires hardware that supports hot-add CPU. How can we check whether the port number is connecting or not on a Server? What is the port numbers used for SQL Server services?
Named instances get a random port number. The browser service runs on port UDP Start SQL Server in different modes? How to recover a database that is in suspect stage? After you execute this statement SQL Server will shutdown the database and restart it without recovering it.
Any attempt to modify data will result in an error similar to the following:. Before you turn the database over to your users you should run other statements to ensure its transactional consistency. To rollback an SP you have to uninstall the entire product and reinstall it. Some people are saying that we can do it by backup and replace the resource DB. But I am not sure about that.
What is a deadlock and what is a live lock? How will you go about resolving deadlocks? Each process would wait indefinitely for the other to release the lock unless one of the user processes is terminated. A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. At TekSlate, we offer resources that help you in learning various IT courses. We avail both written material and demo video tutorials.
SQL Server is not responding. What is an action plan? A long-running query blocking all processes and not allowing new connections. From the command prompt run the script on the SQL server and get the result to a text file.
This can occur when you specify new files in a directory to which the SQL Server service account does not have access. When we specify —f the SQL server creates new tempdb files at default file locations and ignores the current tempdb data files configuration. Take care when using —f as it keeps the server in single-user mode. Once the server is started change the tempdb configuration settings and restart the server in full mode by removing the flag -f. Why SQL server is better than other databases?
Ans: I am not going to say one is better than others, but it depends on the requirements. We have a number of products in the market. It is a database of a European Bank High Security. It is offering a high level of security. Ans: Security. Logins can be authenticated by certificates. High Availability. Database Mirroring can use cheap hardware. More bit options offer chances for consolidation. What are the Hotfixes and Patches? Ans: Hotfixes are software patches that were applied to live i.
Ex: If a select query returning duplicate rows with aggregations the result may be wrong…. Which key provides the strongest encryption? Ans: AES bit. The longer the key, the better the encryption, so choose longer keys for more encryption. However, there is a larger performance penalty for longer keys. What is the difference between memory and disk storage? Ans: Memory and disk storage both refer to internal storage space in a computer.
What port do you need to open on your server firewall to enable named pipes connections? Ans: Port Named pipes communicate across TCP port What are the different log files and how to access it? SQL Server retains backups of the previous six logs, naming each archived log file sequentially. OUT, whereas archived files are numbered sequentially. The security log records authentication information, and the system log records service startup and shutdown information. If the summary.
You can find the Profiler logs in the log. KeyWord Search for a combination of Keyword 1 and Keyword 2. Date1 and Date2: Retrieves data between these two dates. Modify the value to the number of logs that you want to maintain.
With the configuration files, you can point the packages from prod to dev and vice versa in just a few seconds. The packages and config files are just stored in a directory of your choice. We have a table which is 1. Ans: You may want to add insert timestamps and update timestamps for each record. Every time a new record is inserted, stamp it with the DayTime and also stamp it with the date-time when updated. Also possibly use partitioning to reduce index rebuilds.
Which one we have to choose for SQL Server user databases? Ans: Check out the charts in this document. It shows how the disks are setup. It will depend on what the customer wants to spend and the level of reliability needed. Raid 10 pronounced Raid one-zero is supposed to have the best in terms of performance and reliability, but the cost is higher. Ans: By far, the most effective configuration is to set tempdb on its own separate fast drive away from the user databases.
I would start at a general size of 10 GB for each size. What will happen to the Transaction? I believe that is what we have on active. How you do which node is active and which is passive. So the current owner is the active node and other nodes are passive. What are the common trace flags used with SQL Server? Ans: Deadlock Information: , , Do you know about the Resource Database?
Ans: All sys objects are physically stored in the resource database and logically available on every database. Resource database can faster the service packs or upgrades. Really does resource faster the upgrades? Can you justify it? Ans: Yes, in earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file.
We are also capable of the rollback the process because it just needs to overwrite the existing with the older version resource copy. Ans: No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.
Can we take the backup for Resource DB? Ans: No way. The only way if you want to get a backup is use windows backup for option resource MDF and pdf files. Ans: I do alter column to BigInt. Ans: Yes I am sure. Assume there are 50, records inserted per second into the table.
If we inserted 1 lakh records per second into the table then it would take 2,, yrs. If we inserted 1 million records per second into the table then it would take , yrs. If we inserted 10 million records per second into the table then it would take 29, yrs. If we inserted million records per second into the table then it would take 2, yrs.
Are you prepared in attending an interview? Are you worried for job interview preparation? Then do not worry, you are at a right place. Below are the list of frequently asked SQL DBA interview questions and answers which can make you feel comfortable to face the interviews:. Question 1.
What are the different types of backups? Answer: The SQL server offers 4 types of backups to suit the need of the administrator. Complete backup :The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed. Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up mechanism.
Here, in this list of the top SQL Server interview questions, you will learn the most important interview questions to clear your SQL Server interview. SQL is one of the most common skills that recruiters look for not only in IT-related jobs but also in various other job profiles, including business, finance, insurance, and other domains. Following are some of these frequently asked SQL Server interview questions in the corporate world:. How can SQL Server instances be hidden? How can we check whether the port number is connecting or not on a Server DBA? How can you start SQL Server in different modes?
How can it be changed? Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages.
Ans: Yes! Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer. Requires hardware that supports hot-add CPU. How can we check whether the port number is connecting or not on a Server?
Download PDF 1. What are the two authentication modes in SQL Server? There are two authentication modes — Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. This is mainly used to capture and save data about each event of a file or a table for analysis. What is recursive stored procedure?
Забудьте об. Поехали. Свет от фары пробежал по цементным стенам. - В главный банк данных попал вирус, - сказал Бринкерхофф.
Бринкерхофф пожал плечами и подошел к окну. - Электроснабжение уже наверняка восстановили. - Он открыл жалюзи.
Джабба заглянул в распечатку. - Вот что я хочу сказать. Червь Танкадо не нацелен на наш банк данных. - Он откашлялся. - Он нацелен на фильтры безопасности.
Этих слов я и ждала от. Он пожал плечами: - Как только мы получим ключ, я проинформирую директора. Сьюзан не могла не поразить идея глобального прорыва в области разведки, который нельзя было себе даже представить. И он попытался сделать это в одиночку. Похоже, он и на сей раз добьется своей цели. Ключ совсем. Танкадо мертв.
На мгновение Беккер задумался. Потом изобразил смущенную улыбку. - Неужели это так заметно. - Как ее зовут? - Женщина лукаво подмигнула. - Меган, - сказал он печально.
There are a lot of opportunities from many reputed companies in the world.Narbuysoran 02.06.2021 at 18:17
You are at accurate place.Parnella L. 06.06.2021 at 10:36
Top 50 sql server dba interview questions and answers pdf. These 50 interview questions are suitable for both freshers and experienced. It insight you to.