Microsoft Data & AI

Select DB_NAME(database_id)
 file_id, 'Error case' = CASE event_type 
 WHEN 1 THEN '823 or 824 or Torn Page'
 WHEN 2 THEN 'Bad Checksum'
 WHEN 3 THEN 'Torn Page'
 WHEN 4 THEN 'Restored'
 WHEN 5 THEN 'Repaired (DBCC)'
 WHEN 7 THEN 'Deallocated (DBCC)'
 END,
 page_id,
 error_count,
 last_update_date,
 PageType = Case
 When page_id - 1 % 8088 = 0 Then 'Is PFS Page'
 When page_id - 2 % 511232 = 0 Then 'Is GAM Page'
 When page_id - 3 % 511232 = 0 Then 'Is SGAM Page'
 When page_id BETWEEN 0 and 9 Then 'Is boot Page'
 Else 'Is Not PFS, GAM, or SGAM page' 
 End
 From msdb..suspect_pages

Known bug when trying to edit DTS packages:

described here, but the two links in the article – Microsoft SQL Server 2008 Feature Pack page and Feature Pack for Microsoft SQL Server 2005 page.

Microsoft SQL Server 2005 Backward Compatibility Components available on both links, but only on the second one it will work with 2008R2 (Despite the fact that the date of Feature Pack release is earlier).
In addition, here, in the first comment are correct pathes to copy *. dll and *. rll files.
In SSMS 2012 DTS package management is discontinued.

When migrating a database from SQL Server 2000 to SQL Server 2005, it became clear that the line

RAISERROR ('test error %S', 16, 1, @test)

due to wrong written %S instead of %s SQL Server 2000 simply works wrong (returns only the first character of the parameter), but SQL Server 2005 does not create a procedure:
Msg 2787, Level 16, State 1, Line 6
Invalid format specification: ‘%S’.

Because such writing could occur in any procedure, we can find them all::

SELECT name FROM sys.procedures WHERE CHARINDEX ( '%S', OBJECT_DEFINITION(object_id) COLLATE Latin1_General_CS_AS) >0

In one server SQL Server 2005 when I tried to open the properties of the database using the GUI I saw:

Indeed, we cannot determine the database owner:

select owner_sid, suser_sname(owner_sid) from sys.databases where name='model'

It was probably the domain user that no longer exists in AD.
Change the owner with procedure sp_changedbowner in the database model is prohibited::

use model; EXEC sp_changedbowner 'sa'

Msg 15109, Level 16, State 1, Line 1
Cannot change the owner of the master, model, tempdb or distribution database.

We can change the sid directly in the system table, as described here, but it is, really, is not recommended by MS way.

So we use the fact that owner of any attached database become the current user, and running SQL Server with the key /T3608, execute under the user’s session sa:

EXEC master.dbo.sp_detach_db @dbname = N'model'
GO
CREATE DATABASE [model] ON
( FILENAME = N'E:\DATA\model.mdf' ),
( FILENAME = N'E:\DATA\modellog.ldf' )
FOR ATTACH
GO

Restart the SQL Server, we see:

select owner_sid, suser_sname(owner_sid) 'owner name' from sys.databases where name='model'

If it possible, you can open the port for the SQL Server Browser, listed here.
If not, you need to create an alias or just connect by specifying port in the connection string:

where a named instance INSTANCENAME running on port 1453.

OS version: Windows Server 2008R2 EE x64
SQL Server version: SQL Server 2008R2 x64 SP1

Due to unspecified “Oracle provider for OLE DB” error twice restart SQL Server service.
An entry in the Application log:

01:46:12 PM Information SERVERNAME.domain.a 1001 Windows Error Reporting N/A N/A Fault bucket , type 0 Event Name: APPCRASH Response: Not available Cab Id: 0 Problem signature: P1: sqlservr.exe P2: 2009.100.2500.0 P3: 4dfb6221 P4: StackHash_6d63 P5: 6.1.7601.17725 P6: 4ec4aa8e P7: c0000374 P8: 00000000000c40f2 P9: P10: Attached files: C:\Users\username\AppData\Local\Temp\WERC049.tmp.appcompat.txt C:\Users\username\AppData\Local\Temp\WERCB13.tmp.WERInternalMetadata.xml C:\Users\username\AppData\Local\Temp\WERDDCA.tmp.mdmp C:\Users\username\AppData\Local\Temp\WERE22E.tmp.WERDataCollectionFailure.txt These files may be available here: C:\ProgramData\Microsoft\Windows\WER\ReportQueue\AppCrash_sqlservr.exe_acc33c1fca791edc578ecac2be501126d4755535_cab_64a1e249 Analysis symbol: Rechecking for solution: 0 Report Id: 15cbe2fd-ae2a-11e1-a618-3c4a927b0a88 Report Status: 0
01:46:12 PM Error SERVERNAME.domain.a 19019 MSSQL$SQL1 Server N/A The MSSQL$SQL1 service terminated unexpectedly.

Reason restart SQL Server is an access violation – heap corruption:

STACK_TEXT:
00000000`00000000 00000000`00000000 oraoledbutl10!Unknown+0x0
FAILURE_BUCKET_ID: ACTIONABLE_HEAP_CORRUPTION_heap_failure_block_not_busy_AFTER_CALL_c0000374_OraOLEDButl10.dll

To prevent this situation in the future, remove the property “allow in process” Oracle provider, to the provider for a linked Oracle server work out of the process SQL Server:

Further, according to this article, customize access to MSDAINITIALIZE.

Now provider error “Oracle provider for OLE DB” will not lead to restart the SQL Server.

In SQL Server 2005 we able to change the location of the resource database files what is stated in msdn.
If we look the same on the SQL Server 2008 R2 , we read:
The resource database cannot be moved.

Let’s be curious, and try to do the same, as described in msdn for SQL Server 2005:

C:\>NET START MSSQLSERVER /f /T3608

Version check:

select @@version

Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1(Build 7601: Service Pack 1) (Hypervisor)Specifying a new location:

ALTER DATABASE mssqlsystemresource
 MODIFY FILE (NAME=data, FILENAME= 'C:\DATA\mssqlsystemresource.mdf');
 ALTER DATABASE mssqlsystemresource
 MODIFY FILE (NAME=log, FILENAME= 'C:\DATA\mssqlsystemresource.ldf');

The file “data” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “log” has been modified in the system catalog. The new path will be used the next time the database is started.Move the database mssqlsystemresource files:

C:\>move "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.*" C:\DATA\

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.ldf
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.mdf
2 file(s) moved.And restart the SQL Server – it works.

Version:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Problem:
There is a full-text index on the table IndexTable (about 3 billion rows).
When start populating getting a lot of errors in the full text log:

2012-07-17 18:34:26.04 spid25s Error ‘0x80043630: The filter daemon process MSFTEFD timed out for an unknown reason. This may indicate a bug in a filter, wordbreaker, or protocol handler.’ occurred during full-text index population for table or indexed view ‘[TS_MageDB].[dbo].[IndexTable]’ (table or indexed view ID ‘667201477’, database ID ‘7’), full-text key value ‘1986286701’. Attempt will be made to reindex it.

This was happening during the 9 days (and haven’t been large load on the CPU and / or queuing to disk.), and full-text index has not been populated.

There is nothing helpful for me here (because of 2008 R2, not 2008) and here (just “We have therefore resolve this issue as by design”)

Resolution:
Only recreating the index helped, and, after that, populating:

DROP FULLTEXT INDEX ON [dbo].[IndexTable]
CREATE FULLTEXT INDEX ON [dbo].[IndexTable]( [IndexValue] LANGUAGE [Russian])
KEY INDEX [PK_IndexTable] ON ([FT_IndexTeble], FILEGROUP [ftfg_FT_IndexTeble])
WITH (CHANGE_TRACKING = OFF, STOPLIST = SYSTEM)

or, if we want to start populating manually later, for example, at night:

...CHANGE_TRACKING = OFF, NO POPULATION...

and for start:

ALTER FULLTEXT INDEX ON [dbo].[IndexTable] START FULL POPULATION

After a half-day process is finished, the error no longer appeared.

dbcc sqlperf('logspace')

Nevertheless: