Month: January 2013

by

The merge process was unable to create a new generation at the ‘Subscriber’

The merge process was unable to create a new generation at the ‘Subscriber’

Merge replication fails with below error

Error:
The merge process was unable to create a new generation at the ‘Publisher’

Unable to decide if MakeGeneration is needed

The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting,restart the synchronization with verbose history logging and specify an output file to which to write.

 Verbose Error log:
The merge process was unable to create a new generation at the ‘Subscriber’. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)Get help: http://help/MSSQL_REPL-2147200994
Resolution:
Change the generation_leveling_threshold to 0 in sysmergepublications table in publisher and in subscriber. Do not update the system tables directly.
Follow the steps mentioned in workaround section of http://support.microsoft.com/kb/953568

by

Update,Insert or Delete on tables at subscriber fails in transactional replication

When you update/insert/delete rows in table in subscriber which is part of transactional replication with updateable subscriber we will end with below error
{
Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
You do not have the required permissions to complete the operation.

Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
Updateable Subscriptions: Rolling back transaction.
Msg 3609, Level 16, State 1, Procedure trg_MSsync_upd_State, Line 133

The transaction ended in the trigger. The batch has been aborted.
}
Above error is raised in sp_check_sync_trigger and according to the below logic in “sp_check_sync_trigger”  
If you have a update/delete/insert trigger in a table which is not part of replication (MSreplication_objects) then we raise “You do not have the required permissions to complete the operation.”
{

if not exists (select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so 
        on ro.object_name = so.name) 
        where so.object_id = @trigger_procid and ro.object_type = ‘T’  
        and (@owner is null or schema_name(so.schema_id) = @owner)) 
    begin 
        raiserror(14126, 16, 3) 
        return –1 

   end
}
Resolution
Identify all the triggers of the table for which you are not able to update/delete/insert rows and identify the trigger which is not part of MSreplication_objects and disable it.
You can use the below query to identify triggers in table which is not part of replication

select * from sys.objects where type=‘TR’ and name not in (select object_name from  MSreplication_objects)
and parent_object_id in (select OBJECT_ID(‘Replace your Table here’))

       

by

SSMS fails with below error when you connect (or) explore the databases

SQL Server management studio might fail with below error  when you connect (or) explore the databases.
Error in SQL Server 2012
TITLE: Microsoft SQL Server Management Studio ——————————
Object reference not set to an instance of an object. (Microsoft.VisualStudio.Platform.WindowManagement)
——————————
Error in SQL Server 2008 and SQL Server 2005
BUTTONS:
Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.VisualStudio.OLE.Interop.IServiceProvider’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{6D5140C1-7436-11CE-8034-00AA006009FA}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop)

regsvr32 /u ieproxy.dll
regsvr32 ieproxy.dll

Cause
1. You would get above error if ieproxy.dll is not registered properly. Re register Ieproxy.dll.
C:\Program Files\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files\Internet Explorer>regsvr32 ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 /u ieproxy.dll
C:\Program Files (x86)\Internet Explorer>regsvr32 ieproxy.dll
2 .Net framework corruption (Repair Dot Net)
3. regsvr32 actxprxy.dll
4. If the problem persists after doing above steps then search the registry for class-id mentioned  in error message (in this error it is 6D5140C1-7436-11CE-8034-00AA006009FA). Once you find the class in registry explore ProxyStubClsid32 and copy the GUID in default key. Search for the copied GUID again in the registry and locate the DLL for which we are failing and register that Dll.

by

Linked server from SQLServer2012 to SQLServer2000.

How to create Linked server from SQLServer2012 to SQLServer2000.
When you create linked server from SQLServer2012 to SQLServer2000 you cannot use SQL Native client 11 because it cannot connect with  SQL Server 2000
Each version of SQL native client will support the version of SQL Server that it ships with, two earlier versions and two later versions
Ref: http://blogs.msdn.com/b/sqlnativeclient/archive/2008/02/27/microsoft-sql-server-native-client-and-microsoft-sql-server-2008-native-client.aspx
When you use SQLOLED it will switch to latest SQLNCLI.
Reference: http://msdn.microsoft.com/en-us/library/ms190479.aspx
The Microsoft SQL Server Native Client OLE DB provider is the provider that is used with SQL Server if no provider name is specified or if SQL Server is specified as the product name. Even if you specify the older provider name, SQLOLEDB, it will be changed to SQLNCLI when persisted to the catalog.
You create a data source and use MSDASQL (Microsoft OLE DB Provider for ODBC) as long as OS supports it.
Sample
sp_addlinkedserver ‘Server\Shiloh’, @provider = N’MSDASQL’, @datasrc=N’SQL2000datasource’, @location=N’System’,@srvproduct=N’MSDASQL’;
Alternatively you can use SQL Server native client 10.0 but it expects “begin distributed transaction” for all the queries.
by

How to detect low memory conditions in SQL Server using ring buffers output

Use the below query to determine the low memory conditions in SQL Server using the sys.dm_os_ring_buffers It gives the historical memory usage of SQL Server and internal and external memory pressure information .


SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[1]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[2]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[2]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@type)[1]', 'varchar(30)') AS [type],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@state)[1]', 'varchar(30)') AS [state],
cast(record as xml).value('(//Record/ResourceMonitor/Effect[3]/@reversed)[1]', 'int') AS [reserved],
cast(record as xml).value('(//Record/ResourceMonitor/Effect)[3]', 'bigint') AS [Effect],

cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' --and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC
Go

Once you get the output of above query you can use the steps in A significant part of SQL Server process memory has been paged out to troubleshoot the issue further

by

How to bring the database online when one or more data files is accidentally deleted

How to bring the database online when one or more data files is accidentally deleted.
 
Follow the steps below when you don’t have other options like restoring from backup. This has to be the last step to bring the database online and remember data in file which is deleted will be lost. This might cause inconsistent data.
 
1. Take physical file level backup of all the database files
2) Rename all the data files and log files [Ex :N:\ Data.MDF  to N:\Data.MDF_original ]
3) Create a new empty database with the same physical, logical file names and file groups
4) Now take the missing file OFFLINE
5) Take the database offline and replace the original files and try to bring the database online
 
The database will come online, only the missing file will be in OFFLINE STATE and will not be accessible.
If the missing file belong to separate file group we can remove the file group else you may have to export all the data from this database to new database else we cannot take FULL Database backup as one of the database file is not online for file group.
 
 
 
 
— Create New database
USE [master]
GO
 
CREATE DATABASE [MisTest] ON  PRIMARY
( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’ ,   MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’ ,   MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ ,   MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
 
–Take the database offline
Alter database MisTest set offline
go
 
–Now rename the NDF file [to simulate the missing file scenario]
sp_configure ‘show advanced options’,1
go
reconfigure with override
go
 
sp_configure ‘xp_cmdshell’,1
go
reconfigure with override
go
 
xp_cmdshell ‘rename c:\MisTestNDF.ndf MisTestNDF_1.ndf’
go
 
 
–Try to bring the database online but it fails
Alter database MisTest set online
go
 
Msg 5120, Level 16, State 5, Line 1
Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
Msg 945, Level 14, State 2, Line 1
Database ‘MisTest’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
 
 
–Take the database offline and detach
Alter database MisTest set offline
go
USE [master]
GO
ALTER DATABASE [MisTest] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db@dbname = N’MisTest’
GO
 
–Now try to attach the database with missing file, but it fails with the below error
USE [master]
GO
CREATE DATABASE [MisTest] ON
( FILENAME = N’C:\MisTest.mdf’ ),
( FILENAME = N’C:\MisTest_log.LDF’ )
,( FILENAME = N’C:\MisTestNDF.ndf’ )
FOR ATTACH
GO
 
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\MisTestNDF.ndf”. Operating system error 2: “2(failed to retrieve text for this error. Reason: 15105)”.
 
–Rename the original files
xp_cmdshell ‘rename c:\MisTest.mdf MisTest_1.mdf’
go
xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_1_log.ldf’
go
 
–Now recreate the Empty database with the same physical & logical file name
CREATE DATABASE [MisTest] ON  PRIMARY
( NAME = N’MisTest’, FILENAME = N’C:\MisTest.mdf’  , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N’MisTestNDF’, FILENAME = N’C:\MisTestNDF.ndf’  , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’MisTest_log’, FILENAME = N’C:\MisTest_log.LDF’ , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
 
 
–Mark the missing file as offline
Alter database MisTest
MODIFY FILE (NAME = ‘MisTestNDF’,OFFLINE)
 
–Take the database offline
Alter database MisTest set offline
 
— Replace the original file and bring the database online
xp_cmdshell ‘rename c:\MisTest.mdf MisTest_2.mdf’
go
xp_cmdshell ‘rename c:\MisTest_log.ldf MisTest_2_log.ldf’
go
 
xp_cmdshell ‘rename c:\MisTest_1.mdf MisTest.mdf’
go
xp_cmdshell ‘rename c:\MisTest_1_log.ldf MisTest_log.ldf’
go
Alter database MisTest set online
go
 
— The only problem is that we cannot take the backup of the database as the file is offline
 
backup database Mistest to disk = ‘c:\mistest.bak’
 
Msg 3636, Level 16, State 2, Line 1
An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.
Msg 3046, Level 16, State 2, Line 1
Inconsistent metadata has been encountered. The only possible backup operation is a tail–log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
 
–We cannot even remove the file as we get this error
 
USE [MisTest]
GO
ALTER DATABASE [MisTest]  REMOVE FILE [MisTestNDF]
GO
 
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
 
–Now try to remove the file & file group
 
 
–Try the database back up again but it would fail
 
backup database Mistest to disk = ‘c:\mistest.bak’
go
 
Msg 3636, Level 16, State 2, Line 2
An error occurred while processing ‘BackupMetadata’ metadata for database id 8 file id 3.
Msg 3046, Level 16, State 2, Line 2
Inconsistent metadata has been encountered. The only possible backup operation is a tail–log backup using the WITH CONTINUE_AFTER_ERROR or NO_TRUNCATE option.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
 
 
 
USE [MisTest]
GO
ALTER DATABASE [MisTest]  REMOVE FILE [MisTestNDF]
GO
 
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup ‘Secondary’ because the filegroup is not online.
Msg 5042, Level 16, State 7, Line 1
The filegroup ‘Secondary’ cannot be removed because it is not empty.
 
Ignore the above error and proceed further.
 
ALTER DATABASE [MisTest] REMOVE FILEGROUP [Secondary]
GO
 
Now we can take full database backup normally  
 
 

 

by

System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc) error when we try to restore the database

When we try to restore database in SQL Server we get below error and restore database fails

Error

{
——————————
TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Exception of type ‘System.OutOfMemoryException’ was thrown. (Microsoft.SqlServer.Management.Sdk.Sfc)
——————————
BUTTONS:
OK
——————————
}

Cause
We get above error when we have large number of records on backup history table.
Check the count of rows on below table

select count(1) from msdb.dbo.backupfile   with (nolock)
select count(1) from msdb.dbo.backupfilegroup  with (nolock)
select count(1) from msdb.dbo.restorefile  with (nolock)
select count(1) from msdb.dbo.restorefilegroup with (nolock)
select count(1) from msdb.dbo.restorehistory  with (nolock)
select count(1) from msdb.dbo.backupmediafamily with (nolock)

Resolution
We may have to reduce the number of records in the above tables. We can manually delete records based on date (or) If we do not want backup history we can truncate all the above tables.

 

by

How to configure the size of SQLServer error log

When running SQL Server certain times we may have to limit the size and number of SQL server errorlogs

To limit the size of SQL Server errorlogs add a new registry key GetMaxErrorLogSizeKb under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\ErrorLogSizeInKb
By default, this key is absent. Modify the value to the size of errorlog(In Kb) you want to maintain.

To increase the number of log files, add a new registry key “NumErrorLogs” (REG_DWORD) under below location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL.X\MSSQLServer\
By default, this key is absent. Modify the value to the number of logs that you want to maintain.

To learn more about sp_readerrorlog and its parameters read Beyond XP_READERRORLOG (Parameters of XP_READERRORLOG)

by

Remote query using a linked server generated memory dump and fails

When you fire a remote query using a linked server it may generate a memory dump and fail with error below
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Resolution
1. Apply the fix mentioned in http://support.microsoft.com/default.aspx?scid=kb;EN-US;939965 if you are using OLEDB provider for DB2.
2. Few RDBMS allow index to contain column name twice. Dump the indexes of table which you are querying and check if any index has duplicate column name If you find a duplicate column name remove it. Linked server query will start working.

by

Lock pages in memory is recommended or not

One of SQL Server expert recently asked  below question in distribution list 
1. LPIM is not recommended?
2. LPIM is not required?
Answer:
When we have LPIM privilege for SQL Server startup account  AWE allocator API’s are used for memory allocation. Memory allocated using  AWE allocator API’s are not part of Process working set ,hence cannot be paged out and not visible in private bytes or working set in task manger and Perfmon àPrivate bytes or Perfmon àworking set.
Advantage: SQL Server working set (BPOOL) cannot be paged by windows  even when there is system wide memory pressure.
Disadvantage: Operating system will starve for memory when there is system wide memory pressure. OS has to completely rely on SQL Server to respond to low memory notification and scale down its memory usage . SQL Server may not respond fast enough to low memory condition at system level because OS is already starving for memory. LPIM prevents only the BPOOL from paging, Non-Bpool components can still be paged and we have critical portions of SQL Server memory like thread stack, SQL Server Images/DLL’s  in Non-Bpool which can still be paged by OS.
So many disadvantage…. But still why do we recommend LPIM in some places?
In earlier versions of windows 2003 when there is system wide memory pressure windows memory manger would trim one-quarter of working set of all the process. Imagine If SQL Server is using 200GB of RAM and there is system wide memory pressure, Windows memory manager would move 50 GB of
SQL Server working set to page file and we would end with performance problems. If LPIM is enabled OS cannot trim.
Imagine there is a faulty application in the server and it leaks memory  fast , It might consume all the memory in the server and windows memory manager might trim all of SQL Server working set.
Known issues in windows like the one in This link can cause windows memory manager to trim the working set of SQL Server process suddenly. Windows has a background process which keeps  writing the contents of working set to page file, so when there is paging only the dirty pages  needs to be moved to the page file others are already backed by back ground process, So paging would be very fast and SQL Server working set would be moved to page file in seconds before SQL Server responds to low memory resource notification from OS causing negative performance.  This link might throw more clarity.

In systems with large amout of memory (Ex: 1 TB )we might get non yielding scheduler situations when allocating memory in conventional memory model. LPIM is only option is this case.
 
LPIM can be used in servers in which it might take long time to identify the cause of the working set trim. It is always suggested to identify the cause of TRIM before choosing LPIM in first place.