SQL Server Agent Not getting started after a Restart of Services Post Fresh Installation:

There are times where the SQL Server and SQL Server Agent Services are running fine post installation but your SQL Server Agent might not start after a restart of your SQL Server and SQL Server Agent services post installation. There may be many causes out of which the one which I am discussing is one.

Root Cause for the Problem:

We have a tendency to change the SQLAgent.Out file default location to some specific location as per out convenience. We may use EXEC master..xp_instance_regwrite to do the same.

Unfortunately in SQL Server 2008 no doubt this will be written in Registry but will not be updated in SQL Server Agent Properties.

Resolution:

This can be resolved by either changing the ErrorLogFile path in Registry to the one which we have configured in SQL Server Agent properties.

Ideally you can find this ErrorLogFile registry key in path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\SQLServerAgent\ErrorLogFile.

Hope this might be of some help

Unable to Shrink/FreeUp TempDB Space

Sometimes we come across situation where in the Entire Space in TempDB will be unallocated space but we are unable to free up space neither we are able to shrink the database or file. This was how I was able to Free Up the unallocated space and it worked for me. So thought of sharing it.

USE TempDB

GO

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE (‘ALL’)
GO
DBCC FREESESSIONCACHE
GO
dbcc shrinkfile (<TempDBLogicalFileName>,SizeInMB)
GO

Note: Logical File name can be obtained using SP_HELPFILE

Updating Email ID for Alerts and Mail Items

Recently I came across a situation where in I had to change the Email ID for all alerts and Mail Items on multiple servers. If you want to accomplish this through GUI it would be quite painful. After digging out I found out an easy way.

1. You can accomplish this by running the below set of query on individual servers.

2. You can accomplish this by running the below set of query on Central Management Server.

For how to configure Central Management Server(CMS) I will add a link soon or may in next post.

Query:

update msdb..sysoperators set email_address = ‘YourEmailID@YourDomain’ where name=’YourAlertName’

GO

update a SET a.recipients=‘YourEmailID@YourDomain’ 

from msdb..sysmail_mailitems a

inner join msdb..sysmail_profile b

on a.profile_id = b.profile_id

where b.description=’YourDescriptionName’

Enabling CDC while Restoring Database.

Restore the Source Database to Destination Database with parameter KEEP_CDC.

Only “KEEP_CDC” needs to be included in “WITH” Option while restoring database.

Ex: WITH KEEP_CDC

The above process will only work if the Source Database is CDC enabled and CDC jobs are already existing on Destination Server.

If the CDC Jobs for destination database are not exiting then two jobs <Databasename>_Capture and <Databasename>_Change needs to be created manually and needs to be added to msdb cdc jobs using below mentioned procedure.

use msdb

GO

exec sys.sp_cdc_add_job ‘change’

GO

exec sys.sp_cdc_add_job ‘cleanup’

GO

Once the above query is executed you will get message as jobs started successfully. After this the changes made to CDC Enabled table will be captured in CDC table.

If the CDC Jobs are already existing for destination database then restoring database with KEEP_CDC option is enough after which it will capture all the changed made to CDC Enabled tables to CDC tables as the Jobs are already existing and running

Technorati Tags: ,,