How to: Start SQL Server Agent When Agent XPs show Disabled

Problem
The SQL Server Agent was found stopped and the status shows “(Agent XPs disabled)” besides the SQL Server Agent, the funny thing is the service was running according to the services console. Tried to start the service from SQL Server Management Studio, but it didn’t work.

This issue appears when ‘Agent XPs’ advance configuration option is disabled and set to 0 for the SQL Server configuration..

Agent XPs is an advanced configuration option which enables the SQL Server Agent extended stored procedures on the server. When this is not enable SQL Server Agent will not be active in SSMS. Most of the time when you start the SQL Server services it automatically enables ‘Agent XPs’, but sometime it fails to enable or sets the value to 0 and then this issue will appear.

Solution
To fix this issue we should first set the ‘Agent XPs’ to 1 and then run RECONFIGURE to bring it into effect. Execute the following query and try to restart the agent service. The SQL Server agent should be online now.

sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Agent XPs',1
go
reconfigure with override
go
sp_configure 'show advanced options',0
go
reconfigure with override
go

This will be the possible output

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

References

http://sqlserverzest.com/2012/09/19/agent-xps-server-configuration-option/

How to: Find Database Restore History

The following query will help you to track back when the database was restore and which backed up file was used to restore for the particular database.

USE msdb;
GO
SELECT
    DBRestored = destination_database_name,
    SourceDB = b.database_name,
    SourceFile = physical_name,
    BackupDate = backup_start_date,
    RestoreDate = restore_date
FROM RestoreHistory h
INNER JOIN BackupSet b
 ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f
 ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate
GO

How to: Kill User Session

Let’s say you want to kill all running process by a particular user or account. The following query will help you to find all related SPID for all processes initiated by a particular user or account.

You may need to replace “USER_ACCOUNT” in the query with your target account name.

SELECT spid from master..sysprocesses
WHERE loginame = N'USER_ACCOUNT'

After you obtain the list of SPID, it’s time for you to reload your weapon, you can simply execute KILL command against those processes.

How to: Start SQL Server with Minimal Configuration

When you encounter configuration problem that prevent SQL Server from starting, you may need to start an instance using the minimal configuration option. This is the start up option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode.

Kindly not what you can do when you start an instance of SQL Server in minimal configuration mode, as below:

  • Only a single user can connect, and the CHECKPOINT process is not executed.
  • Remote access and read-ahead are disabled.
  • Startup stored procedures do not run.

After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.

sqlservr.exe -c -f -s <Instance_Name>

You may want to know:
Starting SQL Server in Minimal Configuration
How to start SQL Server if you lost TEMPDB Data files?

Log Shipping Error “Could not find a log backup file that could be applied to secondary database”

A simple explanation on how log shipping works could be “Backup-Transfer-Restore”. Transaction log will backup on the primary database and transfer the log backup file to the secondary database server, eventually restore it to the database. Thus, any changes on the primary database will reflect on the secondary database.

How to trouble shoot when the log shipping failed and the below error message found in the job history?

Could not find a log backup file that could be applied to secondary database '<DATABASE_NAME>'.

This means the current log backup file which being restored is too recent. A backup from a prior time need to be applied first. Since the log being backed up in sequence on the primary server, the log need to restore according to the sequence as well on the secondary database.

Use the following query to check when is the last log backup file that was copied:

SELECT * FROM [msdb].[dbo].[log_shipping_secondary]

And, also check what was the last log backup file was restore.

SELECT * FROM [msdb].[dbo].[log_shipping_secondary_databases]

Sometime larger transaction log file may takes longer time to copy over to the secondary server while the newer file already copied over, thus it looks unsorted on the secondary database. So wait for the big file to be copied and restart the log shipping job again. Second case, you may need to check the retention time on backup job that run on primary server.

If you see the file on primary server that was not yet copied, you may need to manually copy that over and then start the log shipping again. If the file is not there, you may need to reconfigure the log shipping process again. You can refer to this site on how to configure log shipping for your database.

How to: Change The Window Title in Windows PowerShell

This post will shows you some tips on how to change the Windows PowerShell title to your desire name. This will be very helpful when you on multitasking, for instance tail log files from multiple sources. Thus, it allows you to name your process window nicely.

Execute the following scripts in your Windows PowerShell console.

$a = (Get-Host).UI.RawUI
$a.WindowTitle = "Powershell Title"

How to: Identify Database Creation Date

Sometimes the database creation date will help you to examine whether any database within your SQL Server environment has been dropped and recreated or identify when the new database created. The following T-SQL statement will provide you a simple piece of information on that.

SELECT 
	name AS [DB Name], 
	dbid AS [DB ID]
	crdate AS [Created Date]
FROM 
	master..sysdatabases

Meanwhile, you might probably want to check out how to examine your table creation date and last modified date in order to help you dive deeper into your SQL Server environment.

Clustered vs. Non-Clustered Index

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.

A non-clustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.

You can think of a clustered index as a dictionary in alphabetical order, and a non-clustered index as a book’s index.

Reference:
TechRepublic – SQL Server Database Developer Interview Questions and Answers