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.