SQL Agent Service not starting after SQL 2008 Upgrade

Root Cause
The service control was looking to old path , for example when you run the sc config, you can locate that its not in mssql10.dev1

C:\>sc \\GANESHJ qc sqlagent$dev1

[SC] GetServiceConfig SUCCESS
SERVICE_NAME : sqlagent$dev1
TYPE : 10 WIN32_OWN_PROCESS
START_TYPE : 2 AUTO_START
ERROR_CONTROL : 1 NORMAL
BINARY_PATH_NAME : C:\Program Files\Microsoft SQL Server\MSSQL$DEV1\binn\sqlagent.exe -i DEV1
LOAD_ORDER_GROUP : TAG : 0
DISPLAY_NAME : SQLAgent$DEV1
DEPENDENCIES : MSSQL$DEV1
SERVICE_START_NAME : .\svcganeshj

But the same has to be as below: "C:\Program Files\Microsoft SQL Server\MSSQL10.DEV1\MSSQL\Binn\SQLAGENT.EXE" -i DEV1

Fix
To fix, navigate in registry to and modify the same to correct value Hklm\system\currentcontrolset\services\sqlagent$prdpos and modify the same to correct value as above.

Upgrade from SQL 2000 to SQL 2008 failed

In-place upgrade from SQL 2000 to SQL 2008 failed with error

Error Description
Error: 33009, Severity: 16, State: 2.
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'msdb'. You should correct this situation by resetting the owner of database 'msdb' using the ALTER AUTHORIZATION statement.

Error: 912, Severity: 21, State: 2
Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 33009, state 2, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

ROOT CAUSE
Different owner of msdb and master caused upgrade script to fail.

1) Run select name,owner_sid from sys.databases
It would Return something like this 0x0105000000000005150000004E69B for msdb and 0x01for master.

2) We can see it’s because owner SID is not same for master and msdb.
Now Start SQL from command prompt before that ensure SQL Server service is stopped.

net start mssqlserver /f /T3608

3) Exec sp_helpdb ‘msdb’
It will show owner as which is not matching with master. where the master’s owner was sa.

4) Exec sp_changedbowner 'sa'
This will change the owner as sa for msdb

5) ALTER AUTHORIZATION ON DATABASE::msdb TO [sa]

6)After this stopp and start the sql from command prompt and run Repair option

7) Repair completed smoothly

How to Fix Corrput SQL 2008 Installs

How to fix corrupt SQL 2008 installs

This works for any version From SQL 2000 to SQL 2008.

Most of the times the issue is with the registries or some files left and we are not able to proceed with the install. So we need to have a total uninstall for the corrupted SQL server, for this we can use the tool .

http://support.microsoft.com/kb/290301 - Windows Installer CleanUp Utility

Install the tool and search for SQL components which need to be removed, i.e remove all the SQL 2008 components listed by the tool.

Once we have removed all the components via the tool, restart the machine and then run the setup, it should work like a charm :)

You can even use Procmon.exe with a filter on setup100.exe (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) to see what was going on when SQL was being installed, just to see if its failing to put an entry in a registry or a file change failure.

DMV for finding the CPU Intensive Query - SQL 2005

Use this Query to find the Query which has consumed more of CPU

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
creation_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
total_logical_reads,
last_logical_reads,
min_logical_reads,
max_logical_reads,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC

Login Failed - SQL 2005

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ...

But the same message would be logged on Server Error log as

2009-02-05 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8
2009-02-05 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]

The key to the message is the 'State' which the server will accurately set to reflect the source of the problem. In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password. The common error states and their descriptions are provided in the following table:


ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required