Search This Blog

Friday, September 25, 2009

No connection could be made because the target machine actively refused it.

I faced this situation yesterday .
We were using Tivoli to connect to SQL Server 2005 (Clustered instance) to take backups.

But it was failing with the error (not printing the complee error here):
No connection could be made because the target machine actively refused it.

I asked myself why the server is refusing the client request ??
Since the connection was failing , this issue had to do with connectivity .

I started checking few things :

1) Port on which SQL is listening
2) named or default instance
3) Protocols enabled @ server level and @ client level .

It was a default instance on some port other than 1433 (as hackers can catch it easily).We found that it was SQL Server that was using this port ...how ??
By connecting to SQL Server through management studio using

tcp:servername\instancename , portname

and it connected .We also did netstat -aon and or -aonb(this takes a lot of time as it also finds the exe name) and confirmed that only SQL Server is using this port.

Then we checked SQL Server Client network utility (cliconfg) and found that no protocols were enabled .We enabled named pipes and TCP/IP (not sure how it got disabled).

Tried to connect tivoli which failed again .Finally we forcibly made tivoli to connect to SQL Server usin gthat port by creating a TCP/IP alias ...
This worked and our issue got resolved ..

In past I found that the port SQL Server is using is either blocked or being used by other application .I that case stop that application and create the alias .

Hope this helps !!!

Regards
Abhay

Saturday, September 12, 2009

Recreating Builtin/administrator account in SQL Server 2005

Someone at client's site removed the builtin admin from SQL Server as it was one of the risks mentioned in the agreement .Everything was fine .

However,during maintenance SQL server did not come up after the server reboot .They tried it with SA but they also forgot the SA pasword :-) ....awesome ...
They were about to uninstall and reinstall SQL server when we finally did the steps below .It took me a lot of time but one of the options worked (with my previous MS experience ofcourse :) ).Below are the repro steps and the solution .




Repro of issue :

1) Delete the Builtin/administrator account .
2) tested it through sqlcmd and got the error 18456 Level 14 State 1.
3) assume that i have forgotten the SA password as well.


Solution:

1) Stop SQL Server service and start it with -m
2) go to C:\Program Files\Microsoft SQL Server\90\Tools\Binn through cmd prompt
3) type sqlcmd -E and hit enter .If its named instance then sqlcmd -
-SServer\instance -E and hit enter.
4) you will get > sign
5) commands you need to use
use master
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=
[master], DEFAULT_LANGUAGE=[us_english]
go

6) You are done.Exit out of it .
7) restart you SQL Server service without -m parameter.

Happy learning .....

Thursday, September 10, 2009

Script to copy errors form error log and get it through mail .

This tim ei was asked to create job/SP that is capable to send a daily mail to DBAs with selected errors form error logs. Below are the steps to create a stored procedure to copy the errors from errorlogs to a table and then send the table as an attachment to our inbox .You might have to modify it as per your need .Or you can use WMI for SQL Server errorlogs ..I did not try that yet ...but will post it once done ...

for mailing you can also use CDO...will post that stuff soon :)






Steps included are :



1) Creating database mail account



2)Creating database mail profile



3) increasing the errorlogs to a default of 50 from 7



4) creating a table to store xp_readerrorlogs values



5) filtering the errors though a query



5) using database mail to send the output as an attachment to inbox



6) Finally recycling the errorlog











USE [msdb]

GO



/*Step 1:Creating a database mail account */

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Test1',

@description = 'Test account for Database Mail',

@email_address = 'your_email.com',

@display_name = 'Test1',

@mailserver_name = 'smtp..com'



/*Step 2:Creating a database mail profile */

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'TestProfile',

@description = 'Test Profile for database mail'







/*Step 3: adding database mail account to database profile created earlier*/

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'TestProfile',

@account_name = 'Test1',

@sequence_number = 1



/*Step 4:To make the TestProfile we created a default public profile */



EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'TestProfile',

@principal_name = 'default',

@is_default = 1 ;







/* To increase the number of error logs to 50 :

1: regedit >> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer

2: create a REG_DWORD key 'NumErrorlogs' and set the value to 50

*/



/* Step 6:create a table for keeping the error log values */



USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO



CREATE TABLE [dbo].[errorlog](

[logdate] [date] NULL,

[processinfo] [varchar](20) NULL,

[line] [varchar](1000) NULL

) ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO







/* Step 7: Create a stored proc for :

-> Truncating the table we created above .

-> Inserting the values from error log into the table we created above

-> Sending the mail .

-> cycling the errorlog

*/



Create proc [dbo].[ErrorlogEmail]

as

truncate table errorlog

insert into errorlog exec xp_readerrorlog

DECLARE @SQL varchar(2000)

DECLARE @date varchar (2000)

DECLARE @File varchar(1000)

select @date= convert(date,GETDATE())

SET @SQL = 'select * from errorlog where line like (''%Severity%'') or line like (''15 seconds'') or line like (''%latch%'')or line like (''%BEGIN STACK DUMP%'')or line like (''%time-out%'')and logdate = convert(date,GETDATE())'

SET @File = 'Errorlog report'+@date+'.csv'



EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'test',

@recipients = 'your email.com',

@subject = 'Errorlog report',

@body = 'Attached please find the Daily errorlog report',

@query =@SQL ,

@attach_query_result_as_file = 1,

@query_attachment_filename = @file,

@query_result_header = 1,

@query_result_separator = ' ',

@query_result_no_padding = 1,

@query_result_width = 32767



/* Step 8: recycle errorlog */

EXEC master.sys.sp_cycle_errorlog

print 'Error log recycled'



Hope this helps ...

Happy Learning ...

Wednesday, September 2, 2009

WMI alert that can detect any alteration to the SP [SQL 2005 and above]

This is the WMI alert that can detect any alteration to the Stored Procedure .Its going to tell you the date , login name , user name , object name .
Like this we can create the alert on anything you want .As I said in my previous post ,WMI is very flexible , uses less resources and much faster .


/* Step 1: creating the table to capture the Alter Proc information */

USE adventureworks
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Alterprocevents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Alterprocevents]
GO

CREATE TABLE [dbo].[Alterprocevents] (
[AlertTime] [datetime] NOT NULL ,
[Object_name] varchar(100),
[Login_Name] varchar(100),
[user_name] varchar(100),
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_AlterprocEvents_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

--CREATE INDEX [Alterproc_IDX01] ON [dbo].[Alterprocevents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
--GO

/*Step 2 : Creating the Job that will enter values into the Alterprocevents table created above*/
/*Error handling is also added and we are running it in a transaction*/
USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Alter proc event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Alter proc event', @delete_unused_schedule=1

GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Alter proc event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to Capture Alter proc event events',
@category_name=N'[Uncategorized (Local)]',
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO [dbo].[Alterprocevents](
AlertTime,
object_name,
login_name,
user_name
)
VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(objectname)))'',
N''$(ESCAPE_NONE(WMI(loginname)))'',
N''$(ESCAPE_NONE(WMI(username)))''
)',
@database_name=N'adventureworks',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/* Step 4: Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to alterproc_change')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to alterproc_change'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to alterproc_change',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DDL_PROCEDURE_EVENTS',
@job_name='Capture Alter proc event' ;
GO


Hope you like it .
Regards
Abhay

WMI alert for deadlocking (no need to configure trace flags 1222) : For SQL 2005 and above only

Before I proceed forward , I just wanted to say that scriting through WMI is amazing ..You will take some time in learning it and might get bored with syntax ...But believe me it will catch your interest as anything is posible wih it ..

Here is the WMI script to trace the deadlock .Its only for SQL Server 2005 and above as I have to see if SQL Server 2000 supports WMI namespace by default.
This also has the mailing script , which we can use for all kinds of alerts .WMI scrips are faster and less resource consuming .I have also added error handling and kept everything important in a transaction , so that it rollsback completely .This is for one database but we can configure for as many by only adding more alerts which will be using the same job.

IMP : Its not going to work on RTM as there is a Bug where the job fails giving the incorrect syntax error (actualy it parses successfully).I applied SP3 and it worked .So not sure if it works on SP1 and SP2 .

Part 1 :

1) Creating the table to capture the deadlock information.
2) Creating the Job that will enter values into the Deadlockevents table created above.
3) Insert graph into LogEvents.
4) Creating the alert and associating it with the Job to be fired.
5) Create a stored proc for sending the deadlock information as .CSV file through the mail.
6) Changing the flag to 1 so that next time this information is not sent.


/* Step 1: creating the table to capture the deadlock information */

USE
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DeadlockEvents]
GO

CREATE TABLE [dbo].[DeadlockEvents] (
[AlertTime] [datetime] NOT NULL ,
[DeadlockGraph] [xml],
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_DeadlockEvents_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [DeadlockEvents_IDX01] ON [dbo].[DeadlockEvents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
/*Service account and sql operator option are optional*/
/*Error handling is also added and we are running it in a transaction*/
USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Deadlock Graph')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Deadlock Graph', @delete_unused_schedule=1

GO
DECLARE @ServiceAccount varchar(128)
SET @ServiceAccount = N''
DECLARE @SQLOperator varchar(128)
SET @SQLOperator = N''

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to DEADLOCK_GRAPH events',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@ServiceAccount,
@notify_email_operator_name=@SQLOperator, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO DeadlockEvents (
AlertTime,
DeadlockGraph
)

VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(TextData)))''
)',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DEADLOCK_GRAPH')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to DEADLOCK_GRAPH'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Capture Deadlock Graph' ;
GO


/* Step 5: Create a stored proc for sending the deadlock information as .CSV file */

Create proc [dbo].[Deadlock_rpt]
as
DECLARE @SQL varchar(2000)
DECLARE @date varchar (2000)
DECLARE @File varchar(1000)
select @date= convert(date,GETDATE())
SET @SQL = 'select * from deadlockevents where flag = 0'
SET @File = 'Deadlock report'+@date+'.csv'

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'your email.com',
@subject = 'Deadlock report',
@body = '***URGENT***Attached please find the deadlock report',
@query =@SQL ,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_header = 1,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_width = 32767


/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
update dbo.DeadlockEvents set flag = 1 where flag = 0
go


Part 2:

1) Creating the DbMmail account
2) Creating a DbMail profile.
3) Adding account to profile.
4) making the profile as public.


USE [msdb]
GO

/*Step 1:Creating a database mail account */
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test1',
@description = 'Test account for Database Mail',
@email_address = 'your_email.com',
@display_name = 'Test1',
@mailserver_name = 'smtp..com'

/*Step 2:Creating a database mail profile */
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestProfile',
@description = 'Test Profile for database mail'



/*Step 3: adding database mail account to database profile created earlier*/
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestProfile',
@account_name = 'Test1',
@sequence_number = 1

/*Step 4:To make the TestProfile we created a default public profile */
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestProfile',
@principal_name = 'default',
@is_default = 1 ;

How to drop all the user databases in one GO

It was Saturday Morning and I had a task @ hand .
One of my collegues wanted me to write a script to drop all the user databases at one go ..

I was initially reluctant as I thought any DDL can not take variables .But then I tried with some other stuff which did not use variables with DMLs.

Initially I came up with the script below :

Create table drop_db(name sysname)
Insert into drop_db select name from sysdatabases where dbid >4
Select 'drop database ' + name from db_name

Copy the output and execute.You canuse the same trick for other purposes as well .

But then I wanted to give it some more try and came up with the final script .

Drop table #db_name
Go
Create table #db_drop (name sysname,flag int identity (1,1) )
Insert into #db_drop select name from sysdatabases where dbid>4
Declare @@flag1 int
Select @@flag1 =max (flag) from #db_drop

While @@flag1 >0
Begin
Declare @string nvarchar (30)
Declare @dbname sysname
Select @dbname =name from #db_drop where flag=@@flag1
Print @dbname + ' is being deleted'
Set @string =' drop database ' + @dbname
Execute sp_executesql @string
Set @@flag1 =@@flag1 +1
End
Drop table #db_drop