Category: SQL Server
-
About Us
At CLOTA Technology, we provide innovative solutions in database technologies, web development, cloud maintenance, and software support to help businesses achieve their goals. Our services include expert SQL and NoSQL database design, implementation, and maintenance with certified DBAs boasting 15+ years of experience. We specialize in custom website and app creation, cloud services for migrations,…
-
Resource Database
Resource Database having below properties: 1. Read only DB 2. Contains system objects 3. Does not contain any user objects 4. Physical name of the DB 5. Each SQL Instance having only 1 Resource DB. 6. DB id of Resource DB always 32767 7. DB backup is not possible of Resource DB.
-
Counters for Monitoring SQL Server Performance
Below are the top few counters which we need to monitor to know SQL Server performance. On the basis of optimal values of the counters, we can identify performance of SQL Server… They are… 1. SQLServer: Buffer Manager: Buffer Cache hit ratio 2. SQLServer: Buffer Manager: Page life expectancy 3. SQLServer: SQL Statistics: Batch Requests/Sec…
-
Endpoint
Endpoint: An endpoint is a SQL Server object that enables SQL Server to communicate over the network. It encapsulates a transport protocol and a port number. An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network. Creation of an end point:- Create endpoint <endpoint name> State=started/stopped/disabled…
-
Deadlock
Deadlock is a situation where two or more transactions wait for completion of each-other for resources but none of them completes. or Deadlock occur between the transactions when 2 or more translations permanently block each-other in such a ways that each transaction having a lock on the resource where the other tasks are trying to…
-
SQL Server Browser
Start and Stop SQL Server Browser Service It’s possible to start/stop the service using SQL Server Configuration Manager or Services.msc. If we stop the SQL Server browser services then we will not connect through named instance at SSMS by manually. Now see if we stop the browser services then we will not connect with named…
-
Database Indexes and Maintenance
SQL Server B-Tree Indexes Root Level Intermediate Level Leaf Level Index Column Selection DMV for analysing Index sys.dm_db_index_physical_stats(null,null,null,null,null) Clustered Index Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can…
-
Orphaned user in Database
List of Orphan user in DB We can list out all the orphan users from a database using below query… USE <database_name> Exec sp_change_users_login @Action=’Report’; GO; Fix Orphan user in DB After finding orphan users, we can do user and login mapping using below query… USE <database_name>Exec sp_change_users_login @Action=’update_one’, @UserNamePattern='<database_user>’, @LoginName='<login_name>‘GO
-
Database
There are two types of database in SQL Server. 1. System Database2. User Databases System databases are default database which are mandatory to start SQL Engine services and user DB get created by SQL user for application use. System Databases Master Records all the system-level information for an instance of SQL Server. MsdbUsed by SQL Server Agent for…