After restore a database with override option, while trying to map a user to that database it will throw error as : User, group, or role 'hello' already exists in the current database. (Microsoft SQL Server, Error: 15023)

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for User 'hello'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

User, group, or role 'hello' already exists in the current database. (Microsoft SQL Server, Error: 15023)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

In this case we have to delete/drop that user from that particular database, again we have to create a user under that database.

How to enable SQL Server Agent?

How to enable SQL Server Agent

Procedure:

Execute the following Queries and refresh the server

Step1:

exec sp_configure 'agent XPs'
if it display an error that advanced options are disabled then by running the following query enable the show advanced options and reconfigure

Step2:  Run the Query to enable advanced options

exec sp_configure 'show advanced options' , 1
go
reconfigure
go

step 3:
exec sp_configure 'show advanced options'
go

Step4:
Enable agent XPs by using the query

exec sp_configure 'agent XPs'
go
exec sp_configure 'agent XPs',1
go
reconfigure
go
exec sp_configure 'agent XPs'

Step5:
now restart the SQL Server Agent / Server. so that SQL Server Services should be enabled

still if it doesn't start then

Step 6:
goto start-->run type services.msc and press OK

Step 7:
Services window will be opened then goto SQL Server Agent, Right click on it and go for start

Now we can see the SQL Server Agent started in our Object Explorer

When we are trying to attach the database it will throw an error as CREATE FILE encountered operating system error 5(Access is denied.) While attempting to open or create the physical file.... (Microsoft SQL Server, Error 5123).

For this error the solution is that there are two different logins in the server, here detaching is happened with one login and while attaching with other login. So there is no access to attach the database, while detaching the database whoever detaching the database that user is the owner of the mdf file so we need to change the permissions.

right click on that mdf file --> select properties--> check permissions of the mdf file--> there we can see only one account has permission on that mdf file, because that account is detached the database so that the user have full rights.

To solve this issue click on 'Add..' add that other login and give full rights to that user, do this process for the ldf file as well. Once you have completed this task click on 'OK' button.

Now try the attach process, it will attach the database successfully. 

How to GRANT ALTER TRACE permission in SQL Server 2005/2008?

If user don't have profiler run permission, though he has dbowner access because profiler is a server level so to give profiler access permission follow below any one procedure.When the user is not having 'sa' permission and don't have Alter trace permission, he can't run profiler.

Method 1: By using GUI.

1) Connect the server in SSMS(SQL Server Management Studio)

2) Go to Object Explorer
3) expand the Security folder
4) expand the Logins folder
5) Right click Login name and select properties
6) Select securables tab
7) Click search
8) Select The server 'server name'
9) Click OK
10) Select permission Alter trace
11)Select Grant
12)Click OK.

Method 2: By using SQL script.

use master
GRANT ALTER TRACE TO [Username]

How to remove ALTER TRACE permission.

To revoke the ALTER TRACE permission from the user follow below any one of the methods.

Method 1:By using GUI.

1) Connect the server in SSMS(SQL Server Management Studio).

2)  Go to Object Explorer
3) Select and expand the Security folder
4) Select and expand the Logins folder
5) Right click Login name and select properties
6) Select securables tab
7) Click search
8) Select The server 'servername'
9) Click OK
10) Select permission Alter trace
11)unselect Grant permission
12)Click OK.

Method 2:  By using SQL script.

use master

REVOKE ALTER TRACE FROM [username]

If login is unable to delete, what are the reasons to get the errors? how to solve the issue?

                  When we are deleting the login, by normally it will delete. In some cases it will throws an error.First read the error why it is unable to delete, based on those errors we will solve the issue

• unable to delete the user because schema is owned by the user.

In this situation, it clearly says that the user is the owner of a schema so that the login is unable to delete. To find out which schema is owned by that user, go to the database which is shown in the error dialogue box expand the database -> expand security -> select schemas -> press F7   then in right side pane it will show the name and owner of the schema then right click on properties -> then change  schema owner as dbo -> click OK. It will delete now.

• in another case it throws an error that could not drop login 'some user' as the user is currently logged in.

 In this situation to find the open session by that user run the stored procedure sp_who2 the output will show with spid, now check that spid what it is running by using this command                        dbcc inputbuffer(spid) then kill that spid if it is running less priority query, mostly that spid is in sleeping state so we can kill that then delete the login.

How to add a Linked Server?

To add a linked server using SQL Server Management Studio(SSMS), open the server where you want to create a link from in object explorer.

Step 1: In SSMS -> object explorer -> server -> server objects ->  Linked servers(right click on the linked server folder and select "New Linked Server")

then click on that New Linked Server option, it will display as follows.

Step 2: The New Linked Server dialog will appears as follows

Step 3: For "Server Type" make sure that "Other Data Source" is selected.

Step 4: Provider-- Select "Microsoft OLE DB Provider for SQL Server".

Step 5: Product Name--SQLSERVER

Step 6: Data source-- Type the actual server name and instance name (SERVERNAME\INSTANCENAME).

Step 7: Provider String-- Blank.

Step 8: catalog--Optional.

Step 9: Click the security and click on "Be made using the login’s current security context", so that it provides more secure.

Step 10: Click on "Server Options" -> in right pane change RPC & RPC Out to "True" as follows.

Step 11: Click OK, and the New Linked Server is created.

How to rebuild the server when the master database is crashed?

If master is crashed the server will not restart then follow these steps to bring back server online.

Step 1:
             First we have to check server error-log there it will mention restore the master database or re-install in event viewer-->windows logs-->application it will show that the master database is crashed, have to restore or re-install.

Step 2:
             Rebuild the system databases from command prompt. Go to the following path and run setup as follows
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

Step 3:

             Once the above command runs successfully, check the summary.txt file, if the final result is passed and check the requested action as Rebuilddatabase.

Step 4:
             Restart the SQL server services.

Step 5:
             Connect to the instance. But you cannot see any user defined databases. The rebuilding process has created fresh\brand new master  database so there are no other databases and all previous configurations are lost.

Step 6:
             Now we have to restore the master database to get all the previous configurations. Go to command prompt and run the instance in single user mode
net stop instancename
net start instancename /m

Step 7:
            Connect to the instance and take new query to restore master database. Once we restore master database, then we can get all the previous configuration values including user defined databases.

Step 8:
           Click on Connect button and connect to the instance.

Step 9:

           Restore master database as follows
            restore database master from disk='path.bak' with replace

Step 10:
            Restart the SQL server instance, it will start in multi user mode, here we can see all the user defined databases.

sp_who & sp_who2

There is a very useful system stored procedure called sp_who on SQL Server that lists the users and processes running on the system. It comes handy when you want to know the loading on the SQL Server or see if any process is blocked.It will give result as running spids, who login to the system with host name and what he is running on that machine.

sp_who
 

sp_who2 

It will give the more information that CPU time, disk io with last batch time and that login where (ie., on which database) he is executing the query.

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)

sql server training

 Introduction to SQL server

SQL server is a collection of four major services.They are

1. Database Server(database engine)
2. Reporter Server
3. Integration Server
4. Analysis Server