select * from sys.dm_db_file_space_usage

We can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.

sys.dm_db_file_space_usage has following columns:

1. database_id – will always be 2, database id of tempdb.

2. unallocated_extent_page_count – total number of free extents in the database file

3. version_store_reserved_page_count – pages allocated for version store

4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…

5. internal_object_reserved_page_count – pages allocated for internal objects

for details about other columns returned refer BOL.

Server roles

Role is a collection of privileges.  

There are 8 server roles in sql server they are as follows.

1. Sysadmin
2. Server admin
3. Setup admin
4. DB creator
5. Disk admin
6. Bulk admin
7. Process admin
8. Security admin

Sysadmin:  The members of this role can perform any operation on the server. This is the root level entry on the server.

Databases

There are 3 types of databases in SQL, they are as follows

1. System databases
2. User databases
3. Sample database

System databases: These are the default databases.There are 5 system databases, they are 

1. Master
2. Model
3. Msdb
4. Tempdb
5. Resource

Master: It is the core database in server, without master, server won't start.Master database will store all the system objects physically. Master database will maintain the logins information, linked servers information, files of each database and end points information will be stored.The recovery model for the master database is simple.

Model: It is a template database for all the user defined databases. User defined databases are inherited from the model database that means user defined database acquires the same properties of the model database.The recovery model for the model database is full.

Msdb: Msdb is used to manage the SQL server agent configurations ie., scheduling alerts and jobs. If msdb is down,sql server agent goes down then there is no automation. It will maintain all the automation's in the server.The recovery model for the master database is simple.

Tempdb: Tempdb is used to store the temporary objects ie., tables, objects, views etc. each time sql server instance is restarted all the temp objects are flushed out or destroyed, so permanent objects cant be created in tempdb. The recovery model for the master database is simple. If tempdb is full then performance issues will raise, it has to be restart the server and restarting the server is not possible then we are recycling the log file or creating new log file by using sp_cycle_errorlog.

Resource: Resource database is the hidden database and it is a read only database which contains all  the system objects stored physically, and logically stored in all the system databases.

Log Shipping

It is a database level high availability(HA). It runs through 4 jobs they are as follows

• Backup job
• Copy job
• Restore job
• Alert job

To configure this HA ie., log shipping we need three servers they are

• Primary server 
• Secondary server
• Monitor server(it's an optional server)

How Log Shipping will works?

Backup job: It will run on primary server. It will take the backups of the log transactions with frequent intervals(for every 15 mins) from primary database in to the network path/folder ie., shared folder. It will delete the old files more than 3 days(72 hours) from the same folder.

Copy job: Copy job will run from secondary server.Copy job will copy the files from backup folder to the local folder in the secondary server.It will  delete the older files more than 3 days.

Restore job: This job will run on secondary server. Restore job will restore the log files from local folder to secondary database.

Alert job: Alert job is created from monitor server, if monitor server is not configured then this job is created on both the servers(primary and secondary).One alert job is created per instance.It will send the alerts when the log shipping fails.

When the data file or log file is damaged or goes to suspect mode, how to bring online?

First we have to check which file is missing or damaged by using error log

xp_readerrorlog.

It clearly shows .mdf is damaged of .ldf is missing/damaged

In case of data file(.mdf) is missing/damaged follow these steps to bring back online

• Take the tail log backup from that database.
•  Restore recent full backup, differential backup(if any) and log backups with no recovery and      finally restore tail log backup with recovery.
• Then the database comes online.

In case of log file(.ldf) is missing/damaged follow these steps to bring back online

• alter database <db name> set emergency 
• alter database <db name> set single_user
• dbcc checkdb ('db name', repair_allow_data_loss)
• alter database <db name> set multi_user

Differences between Log shipping and Mirroring

Log Shipping	Mirroring
It is manual fail over process.	It is automatic fail over process.
Here we can add n no. of secondaries to single primary server.	There is no possibility to add multiple mirror servers to a principal serve.r
Data updations runs through jobs.	Data updations are happening through end points.
In log shipping we are configuring secondary server with standby mode so that reporting purpose is used.	In mirroring we will always configure mirror server in no-recover mode reporting is not possible.
Data loss may be occur due to damage of data or log file.	Here zero data loss.
Here we will have monitor server to monitor the log shipping databases.	Here we will have witness server to monitor the mirroring databases.

Shrinking of Log File

    Shrinking of log file concept has to be done when we will receive disk space issue, in such scenario we have to follow these steps

1.  First we have to check why disk space issue is raised like if it is raised due to data file size we have to add extra disk space.
2. If it is raised due to log file size we have to shrink log file for this check with this below  command  DBCC sqlperf(logspace) it will show from which database is causing the issue.
3. Then find the data file size and log file size using this stored procedure SP_helpdb <db name>
4. Here the shrinking is done when the size of the log file  is more than half of the size of data file  ie.,if data file size is 30 mb then the log file size is 15 mb is recommended.
5. Take one log backup to reduce the usage of log file. 
6. Next find logical name of that log file by using this stored procedure SP_helpdb <db name> 
7. Then execute this command use master go DBCC shrinkfile('logfile name', required size in mb)