Resolution for the Maintenance plan error “The INSERT statement conflicted with the FOREIGN KEY constraint


Topic: Resolution for the Maintenance plan error “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_sysmaintplan_log_subplan_id”.” At SQL server 2008 R2.

 

 

We have faced the strange issue at sql server 2008 R2 and suddenly start to failed all the  backup maintenance plan due to above  error.

 

Reason: This is caused by multi connections stored within maintenance plan. When we create or edit a maintenance plan from another instance, it creates another connection. It happened cause sometime we are register the sql server from other box and want to modify the current maintenance plan as it automatically saves a new  connection and tries to insert job history in new connection causing FK Violation and eventually job fails.

 

Resolution: Please upgrade the service pack 2 in sql server 2008 R2 environment to resolve the issue permanently or recreate the new maintenance plan as like old.  To avoid such incident Please login to same sql server instance whenever we wish to update\modify any main plan and don’t use other box  to connect the server.

Resolution for Microsoft OLE DB Provider for SQL Server: Creating or altering table ‘Test’ failed because the minimum row size would be 8204, including 255 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.


We have faced the issue few weeks back and workaround follow for the same as like below.

Workaround: As per the below error log  it is clearly indicate that we  are trying to create a table that has a row with FIXED data columns in excess of 8060 bytes. The Row-overflow data and rows in excess of 8060 bytes is only possible using VARIABLE data types..

.if we change our table syntax to the following, we’ll notice it should work without issue: create table dbo. Test (x char(60) not null, y varchar(8000) not null)

some important facts

  • Table row can have more than 8060      bytes. (2GB Max)
  • varchar, nvarchar, varbinary,      sql_variant, or CLR user-defined type columns can have max 8000 bytes.
  • varchar(max), nvarchar(max),      varbinary(max), text, image or xml data type columns have no restrictions.
  • All the other data type columns      (other than mentioned in above three points) width addition must be still      under 8060 byte row limit.
  • Index can only be created which      falls with-in 8060 byte row limit.

Note: The length of individual columns must still fall within the limit of 8,000 bytes for varchar, nvarchar, varbinary, sql_variant, and CLR user-defined type columns. Only their combined lengths can exceed the 8,060-byte row limit of a table.

How do I know which advanced futures are used in Database at SQL 2008 enterprise edition


How do I know which advanced futures are used in Database at SQL 2008 enterprise edition

 

As we are know that all futures are available at enterprise edition but the same will not available at standard edition. Due to business requirement if we have to move the database enterprise to standard then It can be important to know which Enterprise features have been enabled. To retrieve details of which SQL Server Enterprise functions have been enabled, you just need to issue the simple SQL statement given here:

 

SELECT * FROM sys.dm_db_persisted_sku_features( Only applicable at SQL 2008 also)

 

Futures are available at enterprise edition which not available at standard.

 

Compression Indicates at least one table or index uses data compression

Partitioning Indicates the database contains partitioned tables, indexes, schemes, or functions

Transparent Data Encryption Indicates the database has been encrypted with transparent data encryption

Change Capture Indicates the database has change data capture enabled

 

If one table is compressed in entire database then it is indicate

 

Output:

 

feature_name                   feature_id

Compression                     100

Modified UPDATESTATISTIC script with Full Scan option for performance improvement


Topic: Modified UPDATESTATISTIC script with Full Scan option  for performance improvement

 

 

As we are aware about that sp system stored procedure sp_updatestats does not  have ability to update  statistics with the FULLSCAN option so here query can’t determine the actual execution plan. I have observed that most of index are skipped. Due to the same since couple of months we have faced severe performance issue for most of the server in our project. I have modified script which use the Full scan parameter Exec sp_MSForEachtable ”update statistics ? with fullscan”.

 

I have tested it in our Citagus test environment and it is working as expected. I have captured the index statics  report before and after to make sure that script is working perfectly. If the statistics are not updated since long days so it will take some more time during initial implementation stage but the completion time will be decreased once it start to run daily basis cause only those tables are updated which is required. Please check it from your end and expedite with your comments. You can make the below script as store procedure to implement it as a job or can be implement direct to job step to ensure it should be follow on specific schedule as per your environment.

 

Note: Script can be applicable for SQL 2005 and SQL 2008

 

————————————————————————————————————————————————————————————————

 

DECLARE

@dbname varchar(1000),

@parentname varchar(255),

@SQLString VARCHAR (1000),

@ctrl CHAR (2),

@command varchar(1000)

SET @ctrl = CHAR (13) + CHAR (10)

DECLARE UpdateCursor CURSOR FOR

select [name]

from sys.sysdatabases where name not in

(

‘model’,

‘tempdb’

)

order by 1

OPEN UpdateCursor

FETCH NEXT FROM UpdateCursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

select @command =

use [‘+@dbname+’]

Exec sp_MSForEachtable ”update statistics ? with fullscan”

exec (@command)

FETCH NEXT FROM UpdateCursor INTO @dbname

END

CLOSE UpdateCursor

DEALLOCATE UpdateCursor

GO

Rectified the Page corruption issue by page restore option


Rectified the Page corruption issue by page restore option

The database corruption is very common issue in DBA world but we can restore the specific page which is corrupted by page restore option. We have implemented successfully in our project and written the below article If the DBCC CHECKDB  job has failed and as an output  we have received the below output.

Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘ DimSalesReason’ (object ID 2088535921).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘ AdventureWorksLT2008’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorksLT2008).

 

Work around:

From this you can see what page is corrupted (1:94299)

The first thing to do is check if it is data in a heap, in a clustered index, or in a non-clustered index. In the above text you can see it is index ID 0. You could also examine the page (1:94299 in database ‘yourdb’) as follows:

DBCC TRACEON (3604, -1)
GO
DBCC PAGE(‘ AdventureWorksLT2008’, 1, 94299, 3)
GO
In the output you will see something like:

Metadata: IndexId = n

Note: If n is greater than 1 it is a non-clustered index and can safely be dropped and recreated. If n is 0 or 1 you have data corruption and need to perform one of the options described below.
Option  1: Restoring specific corrupted  page  from a backup
If the recovery model is FULL (or BULK_LOGGED, with some limitations), you can back up the tail of the log, perform a restore (with no recovery) from the last clean full backup, followed by subsequent log backups and finally the tail of the log.

If only a few pages are affected you have the option of selectively restoring only the bad pages, as follows:

RESTORE DATABASE AdventureWorksLT2008 PAGE = ‘1:94299’
FROM DISK = ‘E:\ AdventureWorksLT2008.bak’
WITH NORECOVERY
Option 2: If the recovery model is simple you don’t have that option, and have to accept that a restore from the last full backup will result in subsequent transactions being lost. In this case, or if you have no backups at all, you may decide that an automatic repair is the only option.

 

ALTER DATABASE AdventureWorksLT2008 SET EMERGENCY;
GO

ALTER DATABASE AdventureWorksLT2008 SET SINGLE_USER;
GO
DBCC CHECKDB (AdventureWorksLT2008, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE AdventureWorksLT2008 SET MULTI_USER;

Delete specific file by use of Sql server xp_cmdshell command


Topic: Delete specific file by use of Sql server xp_cmdshell command

 

We are use centralize backup server to keep the backup and required some maintenance to delete all the old file. Here use the below script to delete the backup file by use of xp_cmdshell command from sql server.

 

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is older than 1/20/2013.

 

EXEC xp_cmdshell ‘FORFILES /p c:\BACKUP /s /m *.sql /d 1/20/2013 /c “CMD /C del /Q /F @FILE”‘

 

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old.

 

EXEC xp_cmdshell ‘FORFILES /p c:\BACKUP /s /m *.sql /d -30 /c “CMD /C del /Q /F @FILE”‘

 

Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old and the file name starts with an “F_”.

 

EXEC xp_cmdshell ‘FORFILES /p c:\BACKUP /s /m F_*.sql /d -30 /c “CMD /C del /Q /F @FILE”‘

Rebuild Job failure issue at SQL server 2008 R2 environment incorrect settings: ‘QUOTED_IDENTIFIER’


We have received the below error:

 

Error:

 

ALTER INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed.

 

 

Resolution:

 

Please pasted the below three lines on the rebuild job step

 

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

Go

SET ARITHABORT ON

 

 

Explanation:

These are the two widely used SET options in SQL Server. Most developers explicitly set these options while creating Stored Procedures, Triggers and User Defined Functions but many are unclear on why we need to explicitly SET them? And why they are special compared to other options?

The default behavior is ON in any database. As per BOL 2008 this option will always be set to ON in the future releases of SQL Server and any explicit SET to OFF will result an error. So avoid explicitly setting this option in future development work.

These two SET options are special because whenever a stored procedure or a Trigger or a User Defined Function is created or modified with these options explicitly SET; SQL Server remembers those settings in the associated object metadata. And every time the object (stored procedure, Trigger..etc.) is executed SQL server uses the stored settings irrespective of what the current user session settings are. So the behavior of the stored procedure is not altered by the calling session settings and the usage of the SET option behavior inside the SP is always guaranteed.

We can get any procedure or trigger or function settings for these options from the sys..sql_modules metadata table.

SELECT uses_ansi_nulls, uses_quoted_identifier  FROM sys.sql_modules WHERE object_id = object_id('SampleProcedure')

SQL server 2012 Backup issue and resolution


We have received the below failure for one database at SQL server 2012 64 bit enterprise edition

 

Error:

A database from an earlier version of SQL Server becomes unusable when you attach it to an instance of SQL Server 2012

 

Msg 5901, Level 16, State 1, Line 1

 

One or more recovery units belonging to database AdventureWorksLT2008 failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

 

Msg 9003, Level 20, State 15, Line 1

The log scan number (43607:149:1) passed to log scan in database AdventureWorksLT2008 ‘ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

 

Workaround:

 

http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_SNAC_CU2_2703275_11_0_2325_x64/11.0.2325.0/free/453121_intl_x64_zip.exe)

 

Need to be downloading and update the service pack

 

I have attached the below four  link which indicate the KB article for Microsoft® SQL Server® 2012 issue and resolution. All the links are specially for 64 bit edition.

 

Package:

———————————————————–

———————————————————–

KB Article Number(s): 2703275

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_SNAC_CU2_2703275_11_0_2325_x64/11.0.2325.0/free/453121_intl_x64_zip.exe)

 

———————————————————–

KB Article Number(s): 2550375, 2554905, 2567366, 2658474, 2665649, 2667211, 2673997, 2675233, 2675522, 2677198, 2678796, 2679342, 2682488, 2682819, 2683304, 2685132, 2685743, 2688968, 2689336, 2689903, 2693652, 2698150, 2699163, 2699820, 2699949, 2699968, 2702015, 2702879, 2703152, 2703275, 2705236, 2705571, 2707166, 2708594, 2710782, 2711145, 2711315, 2711683, 2714785, 2717234, 2717239, 2719217, 2720619, 2720673, 2720678, 2720690, 2720961, 2721122, 2721610

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_PPExcel_CU2_2703275_11_0_2325_x/11.0.2325.0/free/449400_intl_x64_zip.exe)

 

———————————————————–

KB Article Number(s): 2550375, 2554905, 2567366, 2658474, 2665649, 2667211, 2673997, 2675233, 2675522, 2677198, 2678796, 2679342, 2682488, 2682819, 2683304, 2685132, 2685743, 2688968, 2689336, 2689903, 2693652, 2698150, 2699163, 2699820, 2699949, 2699968, 2702015, 2702879, 2703152, 2703275, 2705236, 2705571, 2707166, 2708594, 2710782, 2711145, 2711315, 2711683, 2714785, 2717234, 2717239, 2719217, 2720619, 2720673, 2720678, 2720690, 2720961, 2721122, 2721610

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/SQLServer2012_RTM_CU2_2703275_11_0_2325_/11.0.2325.0/free/449398_intl_x64_zip.exe)

 

———————————————————–

KB Article Number(s): 2550375, 2554905, 2567366, 2658474, 2665649, 2667211, 2673997, 2675233, 2675522, 2677198, 2678796, 2679342, 2682488, 2682819, 2683304, 2685132, 2685743, 2688968, 2689336, 2689903, 2693652, 2698150, 2699163, 2699820, 2699949, 2699968, 2702015, 2702879, 2703152, 2703275, 2705236, 2705571, 2707166, 2708594, 2710782, 2711145, 2711315, 2711683, 2714785, 2717234, 2717239, 2719217, 2720619, 2720673, 2720678, 2720690, 2720961, 2721122, 2721610

Language: All (Global)

Platform: x64

Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/nosp/2012_RTM_DAC_CU2_2703275_11_0_2325_x64/11.0.2325.0/free/449402_intl_x64_zip.exe

Enable Instant File Initialization to help improve of the performance for big databases which reside on Cluster environment


To get the better performance for big environment like where database size more than 200 GB at cluster environment then  we can  enable  Instant File Initialization (“Perform volume maintenance tasks”) for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the “Perform volume maintenance tasks” local security policy. In a cluster we  have to grant the right on all nodes. If there are multiple instances on a server or cluster, we have  grant this right to each instance’s security group. I have tested the same in our test environment and it is really very good option during allocation of the large space .

 

This permission keeps SQL Server from “zeroing out” new space when you create or expand a data file (it is not applied to log files).  This helps performance for CREATE DATABASE, ALTER DATABASE, RESTORE, and AUTOGROW. It can have a significant positive impact on how long it takes to create or expand a data file, but there is a small security risk in doing so. That is because a file “delete” really just deallocated the space and a new allocation can reuse that space which may still have data in it. When you do not zero out the existing space there is a possibility that someone could read data that you thought had been deleted. It is very common to turn Instant File Initialization on. Many shops consider the increased performance benefit to far outweigh the small security risk, but you must weigh the cost and benefits within environment.

 

How to grant this right/permission (tested on Windows 2008) to each instance of SQL Server:

 

  • Run lusrmgr.msc on the server to find the appropriate group name for each instance of SQL Server. For example: SQLServer2005MSSQLUser$SERENITYHOME$KAYLEE (SQL 2005 named instance), SQLServerMSSQLUser$SerenityHome$WASH (SQL 2008 named instance), or SQLServerMSSQLUser$SerenityHome$MSSQLSERVER (SQL 2008 default instance).
  • Run secpol.msc on the server.
  • Under Security Settings on the left, go to Local Policies and under that to User Rights Assignment.
  • Under Policy on the right side, go to “Perform volume maintenance tasks” and double click on it
  • On the Local Security Setting tab click on the “Add User or Group” button
  • In “Select Users, Computers, or Group”
    • Click on “Locations” and choose either your local computer name (for local groups/standalone) or your domain (for domain groups/clusters)
    • Click on “Object Types” and check “Groups”
    • In “Enter the object names to select” enter your SQL Server group created by SQL setup (standalone) or your cluster domain group (for clusters).
    • Choose “OK”
    • Restart SQL Server

To know the all the information about the restored databases


As per our day to job we have to restore the database in different server as per the requirement and some time we have to give all the information to business owner to validate the task done by person . The business owner required the information who , when and what backup is used to restore the database . We can use the below script to get to know all the information whatever we are looking for.

 

Script:–

 

SQL 2005 script:

 

DECLARE @dbname sysname, @days int

SET @dbname = NULL –substitute for whatever database name you want

SET @days = -30 –previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’

  WHEN rsh.restore_type = ‘F’ THEN ‘File’

  WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’

  WHEN rsh.restore_type = ‘I’ THEN ‘Differential’

  WHEN rsh.restore_type = ‘L’ THEN ‘Log’

  WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’

  WHEN rsh.restore_type = ‘R’ THEN ‘Revert’

  ELSE rsh.restore_type

 END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

 rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all

ORDER BY rsh.restore_history

 

 

SQL 2008 and 2008R2:–

 

–Script: small change is there

 

DECLARE @dbname sysname, @days int

SET @dbname = NULL –substitute for whatever database name you want

SET @days = -30 –previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’

  WHEN rsh.restore_type = ‘F’ THEN ‘File’

  WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’

  WHEN rsh.restore_type = ‘I’ THEN ‘Differential’

  WHEN rsh.restore_type = ‘L’ THEN ‘Log’

  WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’

  WHEN rsh.restore_type = ‘R’ THEN ‘Revert’

  ELSE rsh.restore_type

 END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

 rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all

ORDER BY rsh.restore_history_id

 

 

 

 

Result set:–

 

Database

Restored By

Restore Type

Restore Started

Restored From

AdventureWorks

RAJIB\Rajib

Database

2012-11-24 13:55:10.103

D:\SQL Database Backup\AdventureWorks_backup_2012_10_26_075210_1174434.bak