Top posts
-
Types of Pages in SQL Server
Types of Pages in SQL Server Pages are the basic storage unit of SQL Server. There are several pages available in SQL Server. Each page can store upto 8KB of data. Below are the various types of pages available in SQL Servers: Data Pages Data rows with...
-
script for spid high cpu memory
Below is the query that tells the information about the SPID which has high Memory Usage in SQL Server. SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan FROM sys.dm_exec_query_memory_grants AS mg CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle)...
-
Is it possible to configure resource governor with T-SQL?
Is it possible to configure resource governor with T-SQL? Yes, here is the code for it with explanation in between. Step 0: Here we are assuming that there are separate login accounts for Reporting server and OLTP server. 1 2 3 4 5 6 7 /*-----------------------------------------------...
-
Adithi technologies interview questions
1. Explain WAL concept? 2.How to troubleshoot Service pack installation failed ? 3.Why every transaction log in t log file not data-file? 4.Diff between mirroring and log-shipping? 5. In log shipping copy job fail how to trouble shoot? 6.ACID properties?...
-
SQL SERVER Ranking Functions - RANK, DENSE_RANK, NTILE, ROW_NUMBER
et's take following sample table and data to know about RANK, RANK_DENSE, NTILE and ROW_NUMBER with examples. Create table ExamResult(name varchar(50),Subject varchar(20),Marks int) insert into ExamResult values('Adam','Maths',70) insert into ExamResult...
-
SQL Server Isolation Levels with examples
Following are the different types of isolations available in SQL Server. READ COMMITTED READ UNCOMMITTED REPEATABLE READ SERIALIZABLE SNAPSHOT Let us discuss about each isolation level in details.Before this, execute following script to create table...
-
How to restore database using Litespeed backup file and TSQL command
How to restore database using Litespeed backup file and TSQL command Below is TSQL script to restore database in MSSQL using Litespeed backup file. exec master.dbo.xp_restore_database @database = N'databasename' , @filename = N'Full path of the backup...
-
Working on Disk space issue...
Working on Disk space issue... 1. login into the server. verify below options.. Disk space issue ============== 1. old installation files and service pack files 2. data and log files has to be verify. 3. backup files. 4. error log size 5. sqlagent.out...
-
Commonly used scripts to troubleshooting...
Commonly used scripts to troubleshooting... Regular usage scripts for SQL Server dba ===================== 1. On which drive the database files(mdf & ldf) are located? use master select * from sysaltfiles where filename like 'd%' (OR) select * from master..sysaltfiles...
-
How will database recovery model impact database backups?
How will database recovery model impact database backups? Recovery model basically tells SQL Server on what data to store inside transaction log file and how long to keep that data. There are three types of recovery models in SQL Server which are SIMPLE,...
-
How can you automate performing database backups in SQL Server?
How can you automate performing database backups in SQL Server? We can created maintenance plans and use backup tasks option and schedule the maintenance plan to perform backups automatically. We can also create SQL Server jobs and specify the backups...
-
SQL Server: Instant Deadlock Alert Using WMI in Your Mailbox
SQL Server: Instant Deadlock Alert Using WMI in Your Mailbox In my last post I have explained,how to setup alert for blocking using WMI. In this post let us see how to set up an alert for dead lock, which will help us to trouble shoot the dead lock scenarios....
-
SQL SERVER : How to List All Email Subscription configured in SSRS ?
SQL SERVER : How to List All Email Subscription configured in SSRS ? Often I used to get request from my business managers to get a list of all subscriptions configured in SSRS (SQL Server Reporting Service) along with email distribution list. I did not...
-
SQL Server: Instant Blocking Alert Using WMI in Your Mailbox
SQL Server: Instant Blocking Alert Using WMI in Your Mailbox As a DBA, it is important for all of us to get real time alert on various issues in our inbox to be more proactive and to manage the database servers in better way.There are many third party...
-
How to List All Email Subscription configured in SSRS ?
How to List All Email Subscription configured in SSRS ? Often I used to get request from my business managers to get a list of all subscriptions configured in SSRS (SQL Server Reporting Service) along with email distribution list. I did not find any option...
-
Approaching Database Server Performance Issue 4
Approaching Database Server Performance Issue In the last three parts, we have discussed about different queries that can be used to list the current state of the database server. In this post let us discuss about listing execution stats from the plan...
-
Understanding the concept of SQL Server Failover Cluster
Understanding the concept of SQL Server Failover Cluster Before getting hands on experience in SQL server on failover cluster , I used to read many article about failover clustering. Unfortunately I was not able to digest the concept till I wet my hands.I...
-
Approaching Database Server Performance Issues 2
Approaching Database Server Performance Issues In the Part 1, we have seen how quickly we can check the runnable task and I/O pending task on an SQL server instance. This is very light weight script and it will give the result even if the server is under...
-
Migrate Logins and Passwords across Database Instance
As a part of SQL Server migration we need to migrate all the logins and passwords from one SQL Server instance to other SQL Server instance or you can also transfer specific login and password to other instance for that we will use microsoft knowledge...
-
SQL Server Database Migration Checklist
Migrating a database is very critical and time bound process, if anything goes wrong then its very difficult to rollback things and move back to existing environment again. So its very important to prepare a checklist before migrating a database and also...
-
Transaction and Locks
· What is a “Database Transactions “? · What is ACID? · What is “Begin Trans”, “Commit Tran”, “Rollback Tran” and “Save Tran”? · What are “Checkpoint’s” in SQL Server? · What are “Implicit Transactions”? · Is it good to use “Implicit Transactions”? ·...
-
FORALL bulk collect
The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. CREATE TABLE forall_test ( id NUMBER(10), code VARCHAR2(10),...
-
Create Materialized View Logs
Since a complete refresh involves truncating the materialized view segment and re-populating it using the related query, it can be quite time consuming and involve a considerable amount of network traffic when performed against a remote table. To reduce...
-
DBMS_XPLAN
The DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of...
-
Merge table into another table
SQL> create table student_emails_ext 2 (firstname varchar(40), 3 lastname varchar(40), 4 email varchar(80) ); Table created. SQL> SQL> create table student_emails 2 as select * from student_emails_ext 3 where 0=1 4 / Table created. SQL> SQL> SQL> merge...