Purpose of system databases in MS #SQL Server

 Purpose of system databases in MS #SQL Server.

https://youtu.be/Kx7xFDDjtcU

Learn #sql, #dba, #dataScience, #powerBI technologies.

Crystalspiders Institute

contactus@crystalspiders.com

+91 7338457517

SQL query to get the persons whose date of birthday falls in next specified no of days

-- SQL Query to pull the persons whose date of birth falls in the next specified no of days.

Here is the sample query for the same.

create table #empdata (eid int identity(1,1), dob date)
go
insert into #empdata (dob) select '2000-12-30'
insert into #empdata (dob) select '2000-12-31'
insert into #empdata (dob) select '2000-12-29'
insert into #empdata (dob) select '2000-12-29'
insert into #empdata (dob) select '2000-01-01'
insert into #empdata (dob) select '2000-01-02'
insert into #empdata (dob) select '2000-12-03'
insert into #empdata (dob) select '2000-12-30'
insert into #empdata (dob) select '2000-12-26'
go
declare @today date = '2019-12-28'
declare @noofdays int = 10
declare @enddate date = dateadd(dd,@noofdays,@today)
select * from (
select *
, datediff(dd 
           ,@today 
       , dateadd(yy
,iif(year(@today) <> year(@enddate) and month(dob)=1
         ,datediff(yy,dob,@today)+1
     ,datediff(yy,dob,@today))
             ,dob)
  ) as [noOfDaysToGo]
from #empdata
) as persons where noOfDaysToGo BETWEEN 0 AND @noofdays
go


Results:
---------
eid dob noOfDaysToGo
1 2000-12-30 2
2 2000-12-31 3
3 2000-12-29 1
4 2000-12-29 1
5 2000-01-01 4
6 2000-01-02 5
8 2000-12-30 2

States of database files

Online: Files will be online if the DB is in online/offline/emergency/read-only state.

Restoring: If the db/file being in the middle of restore process (DB Restore with norecovery mode),                     then the DB files shows RESTORING state.

Suspect: File will go on suspect state if it is failed during the recovery process.

• If the file in primary file group is failed during the recovery process, then the entire database will go on suspect state.
• If the file is not part of primary file group, then only that file will go on suspect state.
• If the db/file is in suspect state, then do DB/File restore or CHECKDB with allow data loss option.

Recovery pending: File went into this state, if the recovery process postponed due to piecemeal restore process.

         A database can also shows recovery pending state, if any of files (t-log) are missed out while

         recovery process or lack of system resources (disk space, cpu, memory...).. these system

         resources may not allow the DB to come to online.

         Solutions:

         i. Try to take db offline and bring it back to online, in case your database went into recovery

             pending due to lack of resources.

         ii. Set the database to emergency mode, then Detach the DB, attach the DB without any log                     files (use the script and remove the log file details in the script), it will add a new log file and               DB will come back to online. Use this method in case of missing/corrupted t-log file.

             We can't detach the database if it is in recovery pending mode. Put that db into emergency

             mode before detach.

             OR else

            Set the database to offline, move the data and t-log files to new paths, and alter the database                in order to add those new paths to database (don't add the t-log file(s), just alter only data                    files path), try to set the database to online. In this case, the DB will come to online by

            creating a new log file in the default location. 

            OR else

            Simply put the database to offline and set it back to online.. When we are setting it back to

            online, the missing t-log file will be automatically added to default path.

          iii. In case, the t-log/data file got corrupted and unable recover the database. set the db to

           emergency, run the CHECKDB with allow data loss, set the db into online.

SQL Server DBA Daily Activities

Daily Activities
----------------
Ensure server Availability. Check any of the SQL servers are down
Check any of the disks running on out of space
Monitor the maintenance plans - Backup, Rebuild/Reorganize the index, update stats, Clean up the history
Monitoring TempDB database files and their Sizes
Regular Jobs (If any errors follow up with leads)
Sql Services Monitoring
Monitoring Event Logs and Error Logs when ever required
Activity Monitor to check the performance of server
Long Running Jobs.
Check for Blocking sessions
Check for any deadlocks
Check the Monitoring tool (SpotLight) for any job failures and work with associate team
Review the Incident/Change/Request tickets
Attend team meeting every day
Fill the daily status report ( How many tickets are completed, in-progress, active/open)

Weekly Activities
-----------------
Check if any tables are created in system database and drop them
Check if the space availability in the servers. Delete the unnecessary files from C:, D:, E: drives. Raise a request to add more storage if any drive running out of space
Check the growth of databases in all the servers
Fill the timesheets on time

Difference between TOP, OFFSET & OFFSET with FETCH

The TOP will help to display the no.of records

The example query to use TOP. The following query will display only top 10 records.

SELECT TOP(10) SalesOrderID AS SOID, SalesOrderDetailID AS SODID,

OrderQty AS [orderQuantity], UnitPrice as pricePerUnit

  FROM [AdventureWorks2012].[Sales].[SalesOrderDetail]

  WHERE SalesOrderDetailID < 10000

  order by SODID desc

Logical query processing when we are using TOP with ORDER BY:

  First FROM clause will execute

  Second WHERE clause will execute 

  Third SELECT clause will execute

  Fourth ORDER BY clause will execute

  Fifth TOP will execute 

  

-- How to skip the top no.of records from the result set?

We can use OFFSET to skip the records.

How to execute a sql statement on multiple SQL Server instances at the same time?

I assume that you are able to connect to all the SQL Server Instances.

Step 1: Open the SQL Server Management Studio, Go to View in main menu and select 'Registered Servers'. Then will display a Registered Servers window as shown in below images.

How to restore a database (TDE Enabled) from one SQL Server instance to another SQL Server instance

I assume that you are taking backup and restoring the database on the same version and edition of SQL Server Instances.

We will get the following errors while doing restoration of a database (TDE Enabled) from one SQL Server Instance to another SQL Server Instance.