we have seen this issue for the SQL servers running on VM, whenever forced reboot occurred / unexpected shutdown happens.
A reboot can fix this issue but without a reboot, we can fix it.
Fix is :
Message
Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa0000006; actual: 0x40002141). It occurred during a read of page (1:2738679) in database ID 6 at offset 0x0000339ee000 in file ‘G:\SQLdb.mdf’.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Steps to resolve the above issue.
Step 1:
— Turnon Trace 3604 to view the DBCC page information
DBCC traceon(3604)
Step 2:
—
Get the objectid and indexid details using page number from the CHECKDB error message,
DBCC page ([PRODUCTION_database],1,2738679)
Metadata: ObjectId = 117575457
Metadata: IndexId = 13
Step 3:
— Get the object name and index details
select OBJECT_NAME(117575457)
select * from sys.indexes where object_id = 117575457
Step 4:
–Check the table for any consistancy errors
DBCC checktable (‘[dbo].[tablename]’)
— run the dbcc check to confirm the correpted index
DBCC checktable (‘[dbo].[tablename]’,13) — 13 is indexid
–Below message will confirm you that the index id 13 is the corrupted index
Msg 8928, Level 16, State 1, Line 1
Object ID 117575457, index ID 13, partition ID 72057598533304320, alloc unit ID 72057598582390784 (type In-row data): Page (1:2738679) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Step 5:
—- Now Drop and recreate the index to resolve this issue. After recreating the index run check db or table against the corrupted table or database
–Script to check the index size
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = 117575457
GROUP BY i.name
ORDER BY i.name
To fix the above issue you need to perform following steps:
dism /online /enable-feature /featurename:NetFx3 /all /source:d:\sources\sxs
Where you need to replace d:\ with your OS mount drive.
3. After Enabling feature successfully , start the SQL Server 2014 setup again. Now you can install SQL 2014 with out any NetFx3 issue.
Steps to enable the ‘Agent XPs’
— To enable the avdanced options
EXEC SP_CONFIGURE ‘show advanced options’,1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE ‘show advanced options’
–To check the current run value of Agent XPs. run_value 0 indicates disabled and 1 for enabled.
exec sp_configure ‘Agent XPs’
— To enable the Agent Xps.
EXEC SP_CONFIGURE ‘Agent XPs’,1
GO
RECONFIGURE
—- END
In few cases even after enabling the Agent XPs, we face below error.
Error: Unable to start service SQLSERVERAGENT on server dbserver12. (mscorlib)
Fix: change the password of agent services account or change the agent services account.
you may encountered this error message while starting the Extended events session / or while Extended events session configuration time.
Cause: if SQL service account does not have access to file system where the events data is storing, you will encounter this error message.
one of my client got the following error while executing bcp using xp_cmdshell. .
exec master..xp_cmdshell ‘bcp dbase.dbo.sales out “\\pharma\data\outputfile.txt” -c -T’
Error: Fix: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##SQL Server xp_cmdshell_proxy_account##’ credential exists and contains valid information.
Fix :
Today while working with bulk insert i got below weird error message.
BULK INSERT Testtable FROM ‘\\abcd\testdoc.txt’ WITH
( FIELDTERMINATOR = ‘,’,ROWTERMINATOR = ‘\n’,FIRSTROW = 1 )
Error message:
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
Cause:
This errors message mislead us to check linked servers and permissions. but the issue is we missed the FIRSTROW attribute.
Workaround:
i fixed this issue by adding FIRSTROW = 2 attribute to the bulk insert statement.
BULK INSERT Testtable FROM ‘\\abcd\testdoc.txt’ WITH
( FIELDTERMINATOR = ‘,’,ROWTERMINATOR = ‘\n’,FIRSTROW = 2 )
Today when i tried to edit the maintenance plan got the above error: After checking the error and SQL Version, i realized that am opening the 2012 sql server in 2008R2 management studio.
Solution: open in SQL server 2012 management studio
When ever we are running a query in management studio , there is a chance to get this error , if the query result set is too big, and more than 2 GB in size.
To resolve this error , Right click on Query window configure the query result to either file or text.
selectstarfromyogi 