Memory setting recommendations for SQL Server 2016.

Physical RAM                           Maximum Server Memory Settings
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

You should leave the minimum server settings at the default as changing this may cause performance problems.

SQL Server queries for finding the highest salary details in a table

Hello Everyone , Hope all are doing good.
Here I am going to write the queries for finding the 1st ,2nd and 3rd highest salary details .

Here is the table which i am using to find out the required information.

ID NAME AGE ADDRESS SALARY
1 ramesh 32 Ahmedabad            2000
2  Kumar 31 Tirupati                   4000
3 akaya 26 Hyderabad              2600
4 Chaitali 25 Mumbai                  6500
5 Hardik 27 Bhopal                     8500
6 kalyani 28 Pune                        4500
7 Huffy 24 UP                           10000


select * from Customer order by SALARY desc
ID NAME AGE ADDRESS SALARY

7 Huffy 24             UP               10000

5 Hardik 27             Bhopal 8500

4 Chaitali 25             Mumbai 6500

6 kalyani 28             Pune             4500

2 Kumar 31            Tirupati  4000

3 akaya 26             Hyderabad 2600

1 ramesh 32            Ahmedabad 2000

There are different ways to find the highest salary details .

      1)select * from customer where salary=(select max(salary) from Customer)
 
     2) select * from customer where salary =( select distinct top 1 salary from Customer order by SALARY desc)

ID NAME AGE ADDRESS SALARY
7 Huffy 24            UP          10000


find out 2nd highest salary 

    1)select * from Customer where SALARY=(select max(salary) from Customer where salary not in (select max(salary) from customer))

  2) select * from customer where salary=(select min(salary) from customer where salary in (select distinct top 2 salary from Customer order by SALARY desc))

ID NAME AGE ADDRESS SALARY
5 Hardik 27          Bhopal 8500


find out 3rd highest Salary 

     1) select * from customer where salary=(select min(salary) from customer where salary in
         (select distinct top 3 salary from Customer order by SALARY desc))

      2)select * from customer where salary=(select max(salary) from Customer where salary < (select max(salary) from Customer where salary < (select max(salary) from Customer)))

ID NAME AGE ADDRESS SALARY
4 Chaitali 25            Mumbai 6500

find out 4th highest SALARY

1)  select * from customer where salary=(select max(salary) from Customer where salary < (select max(salary) from Customer where salary < (select max(salary) from Customer where salary < (select max(salary) from Customer))))

   ID NAME AGE ADDRESS SALARY
   6 kalyani 28                 Pune 4500


Next post , I will write queries for finding the 1st, 2nd and 3rd lowest salary details.

Thanks
ASR

SQL Server 2016 installtion on Linux

Dear All,

Come back after long time . I am going to discussing about SQL Server installation on Linux ,
This is one of the great step by Microsoft. If this success we could see SQL Server Database as NO.1 in Market.

Recently Microsoft released SQL Server vNext CTP1 installation on RED Hat Enterprise Linux 7.2 

Prerequisites for installing the SQL Server .
1)at least 3.5 RAM required for installing the SQL Server 
2) OS :  RHEL 7.2 

Install SQL Server Step by Step on Linux .

1)Logging into Linux with Super User mode .( Need to login into server with sudo account )

  sudo su  ( same as local administrator  permission )

2) Download the Microsoft SQL Server Red Hat  repository configuration file from blew site.

curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

3)Run below command to install the SQL Server

sudo yum install -y mssql-server

4) After package installation completes , run the below configuration script and provide the strong password for SA(minimum 8 characters  length).

sudo /opt/mssql/bin/sqlservr-setup

5) Verify that SQL Server service running status with following command.

systemctl status mssql-server

6) Install the SQL Server tools .
        SQL Server tools contains following tools and utilities 

           command-line tools,
          Microsoft ODBC drivers, and their dependencies.
          sqlcmd: Command-line query utility
          bcp: Bulk import-export utility.

7) Same process which we have followed before starting the installation .

enter with Sudo account 

8) download the Microsoft Red Hat repository.
 

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

9) Run the following commands to install SQL tools 

sudo yum install mssql-tools

Thanks
ASR

How long it will take to complete this backup or Restore ?

Hello Everyone,

 I am going to discussing  about most useful  DMV command,which is Sys.dm_exec_requests.
Every DBA should have to get knowledge as this DMV can use frequently on daily activities.

Select * from Sys.dm_exec_requests   ---

Important columns in this DMV.

• Session_id – Session ID, which initiated the BACKUP / RESTORE Operation
• START_TIME- When the backup or restore operations  were actually started
• Status – Current Status of process
• command –what command  is going on  (BACKUP / RESTORE)
• percent_complete  - How much percentage completed per particular tasks such as Backup, Restore,shrink, Checkdb
• estimated_completion_time  - What is expected time to complete this backup operation( it could be in milliseconds( Estimated_completion\1000 =seconds , estimated_completion\1000\60= minutes) 

Query for finding ,how much percentage completed for particular tasks  such as Backup,Restore and Shrink DB.

select percent_complete  from sys.dm_exec_requests where  command='BACKUP DATABASE' or command='RESTORE DATABASE'

Query for finding the Estimated time to complete the Backup and restore 

SELECT

    session_id,

    start_time,

    status,

    command,

    percent_complete,

    estimated_completion_time,

    estimated_completion_time /60/1000 as estimate_completion_minutes,

   FROM    sys.dm_exec_requests where command = 'BACKUP DATABASE'  or command = 'RESTORE DATABASE'

Output as below..!!

Thanks&Regards

ASR

Editions of SQL Server 2008 R2

Editions of sql server 2008 r2 from Atcheswara Reddy Mule

Issue when overwritting the Existing SQL Server database(Restore Issue)

Hi All,

Most of the cases ,I have seen this restore issue..this might be occur's ,when overwriting the existing database from  production backup file to dev/stage environment..Errors details as shown below.

 

above error means, database using by another porccess so that might be  running or sleeping connections.

we need to check by usinng below query ,what are the connections are opened on particular database .

 

Select * from sys.sysprocesses where db_name(dbid) like 'dbname'

 

by using particular query we can find out ,what are the SPID's opened on particular database .output is as shown below,if connections are opened .

 

when  you are trying to restore database  ,SPID 56 connected to that particular database .that's why unable to overwrite the existing database.

 KIll that 56 SPID by using below query.

KILL 56

then try to restore the database again.it would be success.

2nd method :

first Keep database in single user mode by using below query .

Alter database dbname set Single_User with rollback immediate

then resotre the database .

changed database to multi user mode.

Alter database dbname set multi_User


Thanks
Asr

 

Introduction of SQL Server 2012

Hi All,

let's talk about SQL server 2012  as it's a new trend in  SQL server products and released with new features.
SQL Server 2012 released on April 1,2012.
Now We have SP1 for SQL Server 2012. In RTM there were major issues and those were fixed in SP1(Error : Unable to creating the Packages and Maintenance Plan in SQL server 2012 RTM)

Two types of licensing is available for SQL Server 2012 Enterprise edition: Core-Based Licensing and Server+CAL Licensing.

Prerequisites for Installing the SQL server 2012.
1) .Net Frem Work 3.5 SP1
2)SQL Server Native Client
3)SQL Server Setup support files
 4)SQL server 2012 runs on computer with NTFS file systems and  also runs on FAT32 file systems but it's not recommend as it's less secure than NTFS file systems.
5)SQL Server 2012 requires a minimum of 6 GB of available hard-disk space.
6)Internet Explorer 7 or a later version is required for Microsoft Management Console (MMC), SQL Server Data Tools (SSDT), the Report Designer component of Reporting Services, and HTML Help.
7)Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio

• If you install SQL Server 2012 on a computer with the Windows Vista SP2 or Windows Server 2008 SP2 operating system,
  
• If you install SQL Server 2012 on a computer with the Windows 7 SP1 or Windows server 2008 R2 SP1 or Windows Server 2012 or Windows 8 operating system,
  

SQL Server 2012 supports upgrade from the following versions of SQL Server:

• SQL Server 2005 SP4 or later
  
• SQL Server 2008 SP2 or later
  
• SQL Server 2008 R2 SP1 or later
  
  
  • SQL Server 32-bit editions can be upgraded to SQL Server 2012 on the 32-bit subsystem (WOW64) of a 64-bit server.
    
  • SQL Server 64-bit versions can be upgraded to SQL Server 2012 64-bit server only.
    
  
    
  Note :  SQL Server 2012 supports upgrade from only the following versions: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1. You will be not able to migrate from SQL 2000 to 2012 directly. Migration from 2000 to 2012
  
  You will have to make the migration in two steps:
   Step 1: Make a first migration from SQL 2000 to SQL 2008 for instance. You need to be SQL 2000 SP4, then follow this step : Migration SQL Server 2000 to SQL Server 2008  Step 2: Make a second migration from SQL Server 2008 to 2012.
  
  
  The following scenarios are not supported for SQL Server 2012 failover clusters.
  
  • SQL Server 2012 Enterprise to SQL Server 2012 Developer, Standard, or Enterprise Evaluation.
    
  • SQL Server 2012 Developer to SQL Server 2012 Standard or Enterprise Evaluation.
    
  • SQL Server 2012 Standard to SQL Server 2012 Enterprise Evaluation.
    
  • SQL Server 2012 Enterprise Evaluation to SQL Server 2012 Standard.
    

SQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)

Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32,767	32,767
Filegroups per database	32,767	32,767
Files per database	32,767	32,767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes

Rows per table Limited by available storage Limited by available storage Tables per database3 Limited by number of objects in a database Limited by number of objects in a database Partitions per partitioned table or index    1,000 Important Creating a table or index with more than 1,000 partitions is possible on a 32-bit system, but is not supported. 15,000