Home » Posts tagged 'SQL server 2008'

Tag Archives: SQL server 2008

Always on\Database Mirroring Automatic Page Repair

A special thanks to Daniel Jones [daniel.jones.0543@gmail.com] for showing up interest and writing informative article for our blog (http://mssqlfun.com/).

 

Always on\Database Mirroring Automatic Page Repair

One of the most useful as well as hottest feature in SQL Server is mirroring of database. It is helpful for numerous of organizations that implements the successful as well as relatively high-availability of inexpensive strategies. However, in all the latest versions of SQL Server its service is being updated in numerous of ways such as database mirroring automatic page repair. It repairs the corrupt pages of the database. The automatic page repair is maintained by the database mirroring and Always on Availability Groups. After some specific types of errors, which had occurred in the database further make it in non-readable form. An availability replica tries to automatically repair the pages. The replica, which is unable to tread the page from another replica. If the request is implemented then, all the non-readable pages are replaced by the readable copies. It mainly resolves the occurrence of error.

Note: The automatic repair is completely differing from DBCC repair as all the database is stored by an automatic recovery of page.

Errors Occurred by Automatic Page-Repair

Database Mirroring of Automatic page repair efforts to repair in the data file through which an operation is failed as mentioned below:

  • Error 883

Description: Action is executed only if the OS implement CRC (cyclic redundancy check), which fails on the data.

Instances: ERROR_CRC. The operating system value for this error is 23

  • Error 824

Description: Logical

Instances: Logical data errors like checksum of torn write or bad page.

 

Techniques to Resolve Errors

  • Primary Database

The primary database of automatic page repair is tried when the state of database is SYNCHRONIZED. Moreover, the principal is still transferring log records for the database to the secondary. The elementary sequence of actions in an automatic attempt of page-repair are as mentioned:

  1. When an error of read comes up on a data page in the primary database then, the primary inserts row in the suspect_pages table with the correct status of an error. For database mirroring, the principal then, requests a copy of page from the mirror. For the Always On Availability Groups, the primary broadcasts the appeal to all the secondary’s and acquires the page from the first to react. The request identifies the ID and LSN, which is presently at end of flushed log. The page is marked as pending restore. This marks it inaccessible throughout the automatic page-repair effort. Attempts to utilize this page in the course of the repair attempt will fail with an error code 829.
  2. After getting page request, the secondary gaps till it has recreated the log up to the LSN stated in the request. After that, the mirror efforts to utilize the page in its database copy. If the page can be used, the mirror directs the copy of page to the principal. Otherwise, the secondary proceeds an error to the principal and the automatic page-repair effort fails.
  3. The primary methods the response, which holds the fresh copy of page.
  4. After the automatic page-repair effort fixes a suspect page, the page is noticeable in the suspect_pages table as reinstated.

 

  • Secondary Database

The input and output errors on pages, which happen on the secondary database are held in generally the similar way by mirroring and Always On Availability Groups.

  1. With mirroring of database, if the mirror comes upon one or more page errors of I/O when it rebuilds a log record, the session of mirroring arrives with the SUSPENDED state. Through Always On Availability Groups, if a replica of secondary meets one or more page errors of I/O when it recreates a log record, the secondary database enters the state of SUSPENDED. At that point, the mirror insets a row in the suspect_pages table with the suitable error status. The secondary then, requests a copy of page from the primary.
  2. The principal attempts to use the page in its database copy. If the page can be edited, the primary directs the copy of page to the secondary.
  3. If the mirror obtains copies of each page it has demanded, the secondary efforts to restart the mirroring session. If an automatic page-repair go fixes a suspect page, the page is clear in the suspect_pages table as reinstated.

If secondary does not obtain a page, which is requested from the principal, the automatic page-repair effort fails. With mirroring of database, the mirroring session leftovers suspended. With Always On Availability Groups, the secondary database leftovers suspended. If the mirroring session or secondary database is restarted manually, the corrupted pages will be again hit at the time of synchronization phase.

View Attempts of Automatic Page-Repair

The mentioned dynamic management previews return rows for the up-to-date automatic page-repair efforts on a given availability database, with maximum of 100 rows per database.

  • Always On Availability Groups:

sys.dm_hadr_auto_page_repair

Gives a row for each automatic page-repair effort on any database availability on availability replica, which is hosted for any group availability by server instance.

  • Database mirroring:

sys.dm_db_mirroring_auto_page_repair

Returns row for all automatic page-repair try on any database mirrored on the server instance.

Conclusion

It is important to repair the page to maintain the continuity of the work. Therefore, we have discussed Database mirroring automatic page repair in the above discussion that makes easy for users to remove the hurdle and understand it clearly.

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

How Row versioning impact tempDB ?

Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal.

Question: Application tea raise concern “How can a select statement on table consume huge amount of resources in tempDB?”

Answer:

As per BOL (https://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx) : Row versioning-based isolation levels (RCSI – Read Committed Snapshot Isolation) reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions. Row versioning-based isolation levels increase the resources needed by data modifications.

While updating the row in a table or index, the new row is marked with a value called the transaction sequence number (XSN), XSN is unique number and keeps updating with upcoming new command \ transactions. When updating a row, New version is generated. If DB is already having previous version of this row, previous version of the row is stored in the version store and the new version of the row contains a pointer to the old version of the row in the version store. New row version stores corresponding XSN value for reflecting the time the row was modified.

All version stores are linked in a chain. SQL Server keep running clean up task to remove old versions which are not in use. As long as a transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. This is the reason due to which long running open transaction can cause huge tempDB row versioning space.

Here, Row X is getting updated by multiple transactions (Tv,Tx,Ty,Tz) and each time new version store is created. When DB engine tries to clear old unused version stores, it found open transaction using version X. DB engine not able to clear any row version from version store after version X. Due to which TempDB size keeps increasing due to row versioning.

–Check Size of tempDB consumed by Version Store

SELECT SUM(VERSION_STORE_RESERVED_PAGE_COUNT) AS [VERSION STORE PAGES USED],

(SUM(VERSION_STORE_RESERVED_PAGE_COUNT)*1.0/128) AS [VERSION STORE SPACE IN MB],

SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS [INTERNAL OBJECT PAGES USED],

(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [INTERNAL OBJECT SPACE IN MB],

SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS [USER OBJECT PAGES USED],

(SUM(USER_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [USER OBJECT SPACE IN MB],

SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS [FREE PAGES],

(SUM(UNALLOCATED_EXTENT_PAGE_COUNT)*1.0/128) AS [FREE SPACE IN MB]

FROM SYS.DM_DB_FILE_SPACE_USAGE;

–Find session using version store

SELECT A.*,B.KPID,B.BLOCKED,B.LASTWAITTYPE,B.WAITRESOURCE,B.DBID,B.CPU,B.PHYSICAL_IO,B.MEMUSAGE,B.LOGIN_TIME,B.LAST_BATCH,

B.OPEN_TRAN,B.STATUS,B.HOSTNAME,B.PROGRAM_NAME,B.CMD,B.LOGINAME,REQUEST_ID

FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A

INNER JOIN SYS.SYSPROCESSES B

ON A.SESSION_ID = B.SPID

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

SQL Server Cluster LooksAlive and IsAlive Check

Windows Cluster Service use the “heartbeat” process to check whether each node is alive at both the OS level and SQL Server level. Heartbeat signals works on UDP.

Microsoft Windows Cluster service perform 2 health checks to perform node & resource availability check.

LooksAlive Health Check

1) LooksAlive is a quick lightweight health check

2) Run at interval of 5 Seconds

3) Does not impact performance but does not perform a thorough check

4) The check will succeed if the service appears to be running even though it might not be operational

5) If Fails calls “IsAlive” health check

6) Polling interval can be changed by adjusting LooksAlivePollInterval property of Cluster service

IsAlive Health Check

1) Run at interval of each 60 Second

2) Perform more detail check then LooksAlive

3) Run @@SERVERNAME to ensure that SQL Server is responding to queries

4) Does not ensure that all user databases are operational

5) Retired 5 times & then try to reconnect SQL Server

6) If all 5 retry fails, the SQL Server resource fails & Windows Cluster service will try to bring it online on other node as per configuration

7) Polling interval can be changed by adjusting IsAlivePollInterval property of Cluster service

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Creation & Deletion of Database Snapshot by SQL Agent Job

Some days back, I received one query on my facebook page that user want to create Database Snapshot frequently & want to delete the oldest with same frequency. Quite interesting scenario. I did not go in deep why he has such requirement but I am clear that It can be done very easily.

So let’s do it. I have setup 2 scripts one for creation of snapshot & another is for deletion of snapshot. We can schedule a SQL agent jobs with 2 steps using these scripts.

Step 1 :-

DECLARE @CREATE_SS VARCHAR(MAX)

DECLARE @DT VARCHAR(100)

SET @DT = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),’-‘,’_’),’ ‘,’_’),’:’,’_’)

SET @CREATE_SS =

‘CREATE DATABASE TEST_’+@DT+’ ON

( NAME = TEST, FILENAME =

”C:\TEST_’+@DT+’.SS” )

AS SNAPSHOT OF TEST’

EXEC (@CREATE_SS)

Step 2 :-

IF(SELECT COUNT(*) FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’))>1

BEGIN

DECLARE @DROP_SS VARCHAR(MAX)

DECLARE @SS_NAME VARCHAR(100)

SELECT TOP 1 @SS_NAME = NAME FROM SYS.DATABASES WHERE SOURCE_DATABASE_ID = DB_ID(‘TEST’)

ORDER BY CREATE_DATE ASC

SET @DROP_SS = ‘DROP DATABASE ‘+ @SS_NAME

EXEC (@DROP_SS)

END

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Renaming or Changing SQL Server Cluster Instance

Renaming or Changing SQL Server cluster instance process is bit different from renaming a stand-alone instance.

Refer : Rename or Change SQL Server Standalone Default Instance http://mssqlfun.com/2014/06/30/rename-or-change-sql-server-standalone-default-instance/)

Refer : Rename or Change SQL Server Standalone Named Instance (http://mssqlfun.com/2014/07/29/rename-or-change-sql-server-standalone-named-instance/)

The name of the virtual server is always the same as the name of the SQL Network Name (the SQL Virtual Server Network Name). You can change the name of the virtual server, you cannot change the instance name like in the case of standalone named instance. You can change a virtual SQL server named SQLServerinstance1 to some other name, such as SQLServerNewinstance1, but the instance part of the name, instance1, will remain unchanged.

Rename a virtual server :-

1. Using Cluster Administrator, change the SQL Network Name or SQL Server DNS Name to the new name.

2. Take the network name resource offline. This takes the SQL Server resource and other dependent resources offline as well.

3. Bring the SQL Server resource back online.

Verify the Renaming Operation :-

1) Using @@ServerName

2) Using sys.servers

Minimize network propagation delay :-

ipconfig /flushdns

ipconfig /registerdns

nbtstat –RR

You are done ! You should ask your application teams to reconnect with new Name or if they are using IP address then no changes required.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to add node to SQL Server Failover Cluster from SQL Server 2008 onwards ?

Steps : How to add node to SQL Server Failover Cluster from SQL Server 2008 onwards ?

1) Open SQL Server Installation Center > Click Installation from Left > Click Add node to a SQL Server Failover Cluster from right

2) Click Run to start setup

3) Click Ok to cont., In case of any failure, you need to fix that first

4) Enter Product Key & click Next

5) Accept EULA & Click Next

6) Click Install to install setup files

7) Click Next, In case of any failure, you need to fix that first

8) Enter Product Key & Click Next

9) Accept EULA & Click Next

10) Select SQL instance to add node

11) Enter Service account password & Click Next. You cannot change service account here, it must be same as existing setup

12) Click Next after Error reporting option

13) Check Setup Rules & click Next. In case of any failure, you need to fix that first

14) Check Configuration & Click install to start installation

15) Installation in progress

16) Installation completed successfully

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

How to install SQL Server Failover Cluster from SQL Server 2008 onwards ?

Steps to install SQL Server Failover Cluster from SQL Server 2008 onwards :-

1) Open SQL Server Installation Center > Go to Installation from Left > Click on New SQL Server Failover installation from right

2) Click RUN to start setup

3) Click ok after validation check. In case of any failure, you need to clear that first

4) Click INSTALL, to install setup files

5) Click Next to cont.

6) Enter Product Key & click Next

7) Accept the EULA & Click Next

8) Select Features, you want to install & Click Next

9) Specify SQL Server instance name & Click Next

10) Check Disk space requirement & Click Next

11) Mention SQL Server Cluster Group Name & Click Next

12) Select Cluster Disk, You want to use for installation & Click Next

13) Provide SQL Server Cluster VIP & Click Next

14) Check your security policy & Click Next

15) Specify Service accounts & Click Next

16) Add User to work as SYSADMIN & Click Next

17) Set error reporting options & click Next

18) Click Next (In case of any failure, you need to fix that)

19) Check all configurations & Click Next

20) Installation Started

21) Installation Completed successfully

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Antivirus Exclusion Policy for SQL Server

Anti-virus & SQL Server on one system together are friends not enemies, if configured properly.

Anti-virus are very useful programs from security, audit & venerability detection & removal point of view. But if team managing anti-virus server did not configure anti-virus policies properly then your SQL Server is going to face the problem.

Here, we will discuss the file types that must be in exclusion list of anti-virus scanning policy. In other words, Let anti-virus programs deal with what they do best, and let SQL Server handle what it does best and avoid, at all possible costs, any interaction between the two

1. Binaries: Or the the paths to the actual executable for any of your running SQL Server Services (MSSQL, SQL Server Agent, SSAS, etc). Typically these are found, by default, in the C:\Program Files\Microsoft SQL Server folder – though this could easily be a different path on many production machines. (And, note, you’ll likely want to make sure that C:\Program Files (x86)\Microsoft SQL Server is included in any exclusions as well on x64 machines).

2. SQL Server Error Logs : Not your database log files, but the text files that SQL Server uses to keep its own ‘event logs’ running or up-to-date. (Which, in turn is also different than Windows’ system event logs as well.) By default the path to these files is, in turn, covered in the paths outlined above – or it’s part of the ‘program files’ data associated with your binaries – though you CAN move the location of these logs if desired (as an advanced operation via the startup parameters).)

3. Data And Log Files: Your actual .mdf, .ndf, and .ldf files – or the locations of your data files and log files. (Which you’ll want to make sure get excluded from anything that anti-virus monitors – otherwise creation of new databases, file-growth operations, and other normal ‘stuff’ can/will get blocked by anti-virus operations – which would be fatal in many cases.)

4. Backups: Yes, the path to any of your backups – or backup locations is also something you’ll want to make sure that anti-virus doesn’t monitor.

5. Others: Any other files related to SQL server & for its proper working. Like .TUF, .SS, .TRC etc.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

ERROR || The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

· This is not the case with non-clustered index.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg(http://mssqlfun.com/)

System Configuration Check – Without Starting SQL Server Installation

Question : Can we do system configuration check with starting SQL Server installation? One of my friend need to submit it to get installation approval. This is to ensure that no configuration issue occur at time of installation.

Answer : Yes, We can do it.

Step 1: Run Setup.exe

Step 2 : Go to Tool & click “System Configuration Checker”

Step 3 : You are done, You got configuration report. You can ignore the warnings But for healthy system start only on 100% green.

Support : It support SQL Server 2008/2008 R2/2012.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

%d bloggers like this: