DBCC log output

DECLARE @Databases NVARCHAR(MAX…” failed with the following error: “Table error: Object ID 1186103266, index ID 2, partition ID 72057765239652352, alloc unit ID 72057766845153280 (type In-row data). The high key value on page (3:11464963) (level 0) is not less than the low key value in the parent (8:1929914), slot 138 of the next page (4:11018747).
Table error: Object ID 1186103266, index ID 2, partition ID 72057765239652352, alloc unit ID 72057766845153280 (type In-row data). The high key value on page (3:11464963) (level 0) is not less than the low key value in the parent (8:1929914), slot 138 of the next page (4:11018747).

MSDN has the detail explanation https://technet.microsoft.com/en-us/library/aa226182(v=sql.80).aspx

1) First thing we have to pay attention to is the object id , Index ID, Partition  ID and Allocation id we can use this information to narrow it down to the actual data

2) Repair the index

In order to run the dbcc repiar commands we need to have the database in single user mode

ALTER DATABASE [] SET SINGLE_USER

DBCC CHECKDB (‘db name’,REPAIR_REBUILD)

This will take care of the issue if we still have the and you are using an enterprise edition SQL Server we can restore that particular page from an earlier database backup

Restore database PAGE = ‘3:11464963‘ from disk =’ ’

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL Log : A significant part of sql server process memory has been paged out

If you seeing this message “A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 1851 seconds. Working set (KB): #####, committed (KB): #####, memory utilization: ##%. ” 

What does it mean: System is running low on memory basically windows is moving the allocated objects from memory into a paged file to deal with memory pressure.

Is SQL Server taking way too much memory? : May be not this is known behavior of windows to trim down the memory used by the current process so it can allot memory to new requests.

SQL Server has mechanism in place to use low resource memory notification it uses a function called CreateMemoryResourceNotification  to create a system wide memory notification if windows detects low memory it sends out a notification with a signal 0 (available physical memory is running low ) or 1 (available physical memory is high) based on the result SQL Server reacts accordingly by performing the necessary action increasing the buffer pool or decreasing it. Note: if neither of these are signaled SQL Server maintains a constant memory foot print.

 

If SQL Sever has a mechanism to deal with low memory why is windows moving the allocated in memory objects to page file?

Can be many reasons for it

  1. Windows might not be sending the notifications in appropriate instances or SQL Sever might not be receiving those events.
  2. SQL Server might not be responding in timely manner may be too slow

When SQL Server receives low physical memory notification it will scale down its memory usage by trimming internal caches. This effect is applied for 5 seconds and then paused for 1 minute. This is to avoid any hungry/faulty application to consume all the memory making SQL Server to  scale its usage continuously. If low memory conditions still exist after 1 minute pause, the effect is applied again. So if there is physical memory pressure even after SQL Server scaling its usage for 5 seconds windows will still page out SQL Server’s working set.

We can query these memory notifications in ring buffer

 

 

SELECT
CONVERT (varchar(30), GETDATE(), 121) as runtime,
DATEADD (ms, a.[Record Time] – sys.ms_ticks, GETDATE()) AS Notification_time,
a.* ,
sys.ms_ticks AS [Current Time]
FROM
(SELECT x.value(‘(//Record/ResourceMonitor/Notification)[1]’‘varchar(30)’AS [Notification_type],
x.value(‘(//Record/MemoryRecord/MemoryUtilization)[1]’‘int’AS [MemoryUtilization %],
x.value(‘(//Record/MemoryRecord/TotalPhysicalMemory)[1]’‘bigint’AS [TotalPhysicalMemory_KB],
x.value(‘(//Record/MemoryRecord/AvailablePhysicalMemory)[1]’‘bigint’AS [AvailablePhysicalMemory_KB],
x.value(‘(//Record/MemoryRecord/TotalPageFile)[1]’‘bigint’AS [TotalPageFile_KB],
x.value(‘(//Record/MemoryRecord/AvailablePageFile)[1]’‘bigint’AS [AvailablePageFile_KB],
x.value(‘(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]’‘bigint’AS [TotalVirtualAddressSpace_KB],
x.value(‘(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]’‘bigint’AS [AvailableVirtualAddressSpace_KB],
x.value(‘(//Record/MemoryNode/@id)[1]’‘int’AS [Node Id],
x.value(‘(//Record/MemoryNode/ReservedMemory)[1]’‘bigint’AS [SQL_ReservedMemory_KB],
x.value(‘(//Record/MemoryNode/CommittedMemory)[1]’‘bigint’AS [SQL_CommittedMemory_KB],
x.value(‘(//Record/@id)[1]’‘bigint’AS [Record Id],
x.value(‘(//Record/@type)[1]’‘varchar(30)’AS [Type],
x.value(‘(//Record/ResourceMonitor/Indicators)[1]’‘int’AS [Indicators],
x.value(‘(//Record/@time)[1]’‘bigint’AS [Record Time]
FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = ‘RING_BUFFER_RESOURCE_MONITOR’AS R(x)) a
CROSS JOIN sys.dm_os_sys_info sys
ORDER BY DATEADD (ms, a.[Record Time] – sys.ms_ticks, GETDATE())

 

We can also increase the LowMemoryThreshold value so the OS will notify applications such as SQL on low memory conditions much earlier and SQL Server can respond to memory pressure much early before the system is starving for memory.

 

How to set the LowMemoryThreshold value (in MB)?

 

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 512

System Reboot is required to take effect.

 

https://msdn.microsoft.com/en-us/library/windows/desktop/aa366541(v=vs.85).aspx

 

http://blogs.msdn.com/b/karthick_pk/archive/2012/06/22/a-significant-part-of-sql-server-process-memory-has-been-paged-out.aspx

Hadoop 1.21 Name node startup issue

While I was trying to poke around in hadoop environment I noticed that namenode wouldn’t start up. After doing some research I found that its the IPv6 issue. So I summarized steps I took to resolve the issue

 

 

 

hadoop jps 1

 

hadoop start 2

 

 

We are starting the hadoop stack

hadoop missing name node 3

we can see the name node seems like it did not start as we can see in jps list

first step is make sure you can ssh local machine (127.0.0.1)

hadoop ssh port activity

I tried tee see port activity looks like entire hadoop stack is using Ip6. we can change this behavior by changing hadoop-env.sh file in conf directory

 

set hadoop to use ipv4

 

export HADOOP_OPTS=-Djava.net.preferIPv4Stack=true

stop all hadoop services and start hadoop stak

hadoop start 5 all

 

 

Standard SQL Server Error log procedure

we spend lot of time trying to look at log files when we run any particular SQl files have a standard frame work to collect these errors and store in table, amkes the application management simple at same time provides some insights into exceptions over the time

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'wm_ErrorLog')
DROP TABLE [wm_ErrorLog]
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'WMSp_ErrorLog_Ins_Error' AND type ='P')
DROP PROCEDURE [WMSp_ErrorLog_Ins_Error]
CREATE TABLE [wm_ErrorLog] (
    [Error_id] INT IDENTITY(1,1)
	,[Error_Number] [INT] NOT NULL
	,[Error_procedure] VARCHAR(max) NOT NULL
	,[Error_Message] [VARCHAR](MAX) NULL
	,[Error_Line] INT
	,[Error_State] INT 
	,[Error_Severity] INT 
	,[SPID] [INT] NULL
	,[Program_Name] [VARCHAR](255) NULL
	,[Client_Address] [VARCHAR](255) NULL
	,[Authentication] [VARCHAR](50) NULL
	,[Error_User_Application] [VARCHAR](100) NULL
	,[Error_Date] [DATETIME] NULL
	,[Error_User_System] [sysname] NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_date] DEFAULT(GETDATE())
FOR [Error_Date]
GO

ALTER TABLE wm_ErrorLog ADD CONSTRAINT [dfltErrorLog_error_user_system] DEFAULT(SUSER_SNAME())
FOR [Error_User_System]
GO

ALTER TABLE [wm_ErrorLog] ADD CONSTRAINT wmpk_wm_errorlog_id PRIMARY KEY (error_id)

GO


Create  PROCEDURE [WMSp_ErrorLog_Ins_Error] (
	 @Error_Number INT = NULL
	,@Error_procedure SYSNAME = 'Ad hoc sql'
	,@Error_Message VARCHAR(4000) = NULL
	,@UserID INT = NULL
	)
AS
BEGIN
	BEGIN TRY
		INSERT INTO wm_ErrorLog (
			[Error_Number]
			,[Error_procedure]
			,[Error_Message]
			,[Error_line]
			,[Error_state]
			,[Error_severity]
			,[SPID]
			,[Program_Name]
			,[Client_Address]
			,[Authentication]
			,[Error_User_System]
			,[Error_User_Application]
			)
		SELECT [Error_Number] = ISNULL(@Error_Number, ERROR_NUMBER())
			,[Error_procedure] = COALESCE(ERROR_PROCEDURE(),@Error_procedure)
			,[Error_Message] = ISNULL(@Error_Message, ERROR_MESSAGE())
			,[Error_line] = ERROR_LINE()
			,[Error_state] =ERROR_STATE()
			,[Error_severity] = ERROR_SEVERITY()
			,[SPID] = @@SPID
			,[Program_Name] = ses.program_name
			,[Client_Address] = con.client_net_address
			,[Authentication] = con.auth_scheme
			,[Error_User_System] = SUSER_SNAME()
			,[Error_User_Application] = @UserID
		FROM sys.dm_exec_sessions ses 
		LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id
		WHERE ses.session_id= @@SPID
	END TRY

	BEGIN CATCH
		-- We even failed at the log entry so let's get basic  
		INSERT INTO wm_ErrorLog (
			ERROR_NUMBER
			,ERROR_procedure
			,ERROR_MESSAGE
			)
		VALUES (
			- 100
			,OBJECT_NAME(@@PROCID)
			,ERROR_MESSAGE()
			)
	END CATCH
END
GO

Above scripts creates two objects a table and procedure

Here is how we should plan to use

BEGIN TRY
RAISERROR(‘error’,16,1)
END TRY
BEGIN CATCH
EXECUTE [WMSp_ErrorLog_Ins_Error]
END CATCH

SELECT * FROM wm_errorlog

Sql error log

How to Fix :: OLE DB provider “SQLNCLI10″ for linked server returned message “Query timeout expired”.

by default a remote query (Query using Linked Server) expires in 10 mins. We can find this value in SQL Server using below queries

 

sys config remote procedure

 

SELECT * FROM sys.sysconfigures
WHERE comment ='remote query timeout'

--or 

SELECT * FROM sys.configurations
WHERE name='remote query timeout (s)'

We can find more details in MS doc

http://msdn.microsoft.com/en-us/library/ms189631.aspx

in order to change it we have to sp_configure and we don’t need to restart the database engine

 

sp_configure 'remote query timeout',3600
go 
reconfigure with override 
go 

This will set the time out to 3600 seconds/ 1 hr

Setup login.sql in sqlplus windows envrionment

it would be tiresome to enter environment variables each time you login to sqlplus, good thing is sqlplus allows us to use a script (login.sql) which has the environment variables defined each time we login (user profile)  https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_two.htm#i1133044 .  Once you define the sqlpath sqlplus looks for login.sql in the path directory and executes it once you login successful. This can be also configured at global level called site profile it allows the DBA to set default env for all the users. so when a user connects through sqlplus first the global site profile (glogin.sql) is executed and then the user profile (login.sql) is executed which is defined in SQLPATH environment variable in Windows

 

sqlpath

 

here %USERPROFILE% is an environment variable has default location of the user who is logged into the machine  C:\USERS\Username

we have to make sure we have login.sql available in that location

 

sql plus login sql

 

you can see below sqlprompt has changed to what we defined in login.sql  (‘&_user.@&_connect_identifier.>’)

sqlprompt sqlplus