12 January 2018

SQL Server Cluster Uninstall Failed- rule instance removal failed

We recently faced this issue, as the server was migrated to virtual and accidentally windows cluster was removed as part of decommission. Box is left out with SQL Server Installation while uninstalling we hit the below error, once the node name was removed it went successful.




Resolution:

1. Remove the entry from regedit.

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Cluster:ClusterName

2. Re-run the uninstall from control panel.


09 January 2018

The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'.



Receiving 'xp_regread' error as soon as connected to SQL Server Management Studio.
OR

Execution of SSIS packages from SQL jobs fails with error: The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'




Solution:

To connect and to view the SQL Server Agent:

USE [master]
GO
GRANT EXECUTE ON xp_regread TO [public]
GO


To Execute Job:

USE [master]
GO
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO [public]
GO



20 December 2017

Expanding VM HDD Size - Oracle VM Box

1. Open the command Prompt
2. CD C:\Program Files\Oracle\VirtualBox
3. VBoxmanage modifyhd D:\Linux\Windows\Wind.vhd --resize 30000
4. Exit from command prompt
5. Boot the VM and expand the HDD

26 September 2017

How To Automate SQL Server Trace

How to automate SQL Server Trace:
============================
  • Start SQL Profiler and select File > New Trace. Specify the events, columns, and filters you want in your trace.
  • Start the trace and then stop it.
  • Export the definition. Click File > Export > Script Trace Definition > For SQL Server 2005. ...
  • Save the trace file.
  • Open the file and create a stored procedure.
  • Add below lines to sp to create trace with date and time
 declare @TrcFileName nvarchar(300) 

set @TrcFileName = N'C:\STARTTrace'+CAST(DATEPART(d,GETDATE()) AS varchar(2))+
CAST(DATEPART(M,GETDATE()) AS varchar(2))+CAST(DATEPART(YYYY,GETDATE()) AS varchar(4))
+CAST(DATEPART(HH,GETDATE()) AS varchar(2))+CAST(DATEPART(MI,GETDATE()) AS varchar(2)) 

exec @rc = sp_trace_create @TraceID output, 0, @TrcFileName, @maxfilesize, NULL 
  • To make SP to start when sql server restarts:
USE MASTER
GO
EXEC SP_PROCOPTION SPNAME, 'STARTUP', 'ON'
GO
  • To check SP which are running at start up:
SELECT ROUTINE_NAME
FROM MASTER.INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1

--see if trace is running

select * from sys.traces
--Mark Trace Store Proc startup option True
EXEC sp_procoption 'StoreProcName', 'startup', 'true';
--Verify start option value is 1
USE MASTER
GO
SELECT VALUE, VALUE_IN_USE, DESCRIPTION
FROM SYS.CONFIGURATIONS
WHERE NAME = 'scan for startup procs'
GO


Msg 19069, Level 16, State 1, Procedure sp_trace_create, Line 1 The trace file name is not valid because it contains a rollover file number (NNN in C:\file_NNN) while the trace rollover option is enabled.

In this case remove the _ (underscore) at the end of the filename.

To rollover files manually use 2 as a parameter in the script.


Lot of thanks to sources :
http://www.techbrothersit.com/search?q=sql+server+profiler
https://dbamohsin.wordpress.com/tag/file-rollover/