Home » SQL Server

Category Archives: SQL Server

SQL Server Configuration Manager giving Error : The remote procedure call failed. 0x800706be


Issue : I am facing error “The remote procedure call failed. [0x800706be]” while trying to view SQL Services from SQL Server Configuration Manager.

Steps I perform to resolve the issue, Seems to be a WMI error :-

1) If using Window Server 2008 & above, Run command from command prompt to verify WMI repository : winmgmt /verifyrepository

If you got any error, Please contact your OS team for WMI rebuild.

2) Recompiled the MOF file –> mofcomp.exe "C:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof"

3) Re-registered the SQL Server provider DLL –> regsvr32 "C:Program FilesMicrosoft SQL Server90Sharedsqlmgmprovider.dll"

4) Your issue should be resolved now. If you still face same issue that means there are chances that you may be running with different versions of SQL Server. If yes, Please try to patch SQL Server Management Studio (SSMS) with latest available Service pack.

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

SQL Server 20082008 R22012 || Remove Node from failover cluster


Steps to remove node from failover cluster for SQL Server 2008 onwards :-

Note :-

a) Same process will be used to remove node from failover cluster

b) Same process will be used when you left with only one node, this will clear cluster group as well

c) Old process of uninstallation from Add and Remove program in Control Panel will not work

1) Open SQL Server installation Center > Go to Maintenance Option from Left > Click on “Remove Node from a SQL Server failover cluster” from right

2) Click on Run to start uninstallation process

3) Click “OK”. In case of any failure, you need to fix that before uninstallation

4) Click Next

5) Select Instance Name & click Next

6) Click Remove to start uninstallation

7) Uninstallation in progress

8) Uninstallation successfully completed.

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

TempDB Configuration Best Practices


1. Do not change collation of TempDB from the SQL Server instance collation.

2. Do not change the TempDB database owner from sa.

3. Do not drop the TempDB database.

4. Do not drop or revoke the guest user from the database.

5. Do not change the recovery model from SIMPLE.

6. Allow for tempdb files to automatically grow as required.

7. Ensure the disk drives TempDB resides on have RAID protection in order to prevent a single disk failure from shutting down SQL Server. Keep in mind that if TempDB is not available then SQL Server cannot operate.

8. Move the TempDB database to seprate set of disks.

9. Size the TempDB database appropriately.

10. Configure no. of TempDB database data files as per available C.P.U. Cores

✔ If no of cores < 8 then use the same number of data files as logical processors
✔ if no. of cores between 8 to 32 inclusive then 1/2 data files as logical processors
✔ if no. of cores > 32 then 1/4 data files as logical processors

11. Make each data file the same size; this allows for optimal proportional-fill performance.

12. Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

13. Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance.

14. General guidelines for setting the FILEGROWTH increment for tempdb files

✔ If TempDB file size > 0 and < 100 MB then the recommended filegroup increment can be 10 MB
✔ If TempDB file size > 100 and < 200 MB then the recommended filegroup increment can be 20 MB
✔ If TempDB file size >= 200 MB then the recommended filegroup increment can be 10% OR any fix value depending on requirement or on basis of I/O system capabilities

15. Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.

16. Keep auto create statistics & auto update statistics OFF. It will create tempDB objects faster.

17. Keep auto close OFF

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

Cumulative Update – 13 for SQL Server 2012 Service Pack 1 Is Now Available !


The 13th cumulative update release for SQL Server 2012 Service Pack 1 is now available for download at the Microsoft Support site. Cumulative Update 13 contains all the hotfixes released since the initial release of SQL Server 2012 SP1.

Those who are facing severe issues with their environment, they can plan to test CU13 in test environment & then move to Production after satisfactory results.

Although, I prefer to have SP2 with latest CU in your environment.

KB Article For CU13 of SQL Server 2012 SP1

Previous Cumulative Update KB Articles:

· CU#12 KB Article: http://support.microsoft.com/kb/2991533

· CU#11 KB Article: http://support.microsoft.com/kb/2975396

· CU#10 KB Article: http://support.microsoft.com/kb/2954099

· CU#9 KB Article: http://support.microsoft.com/kb/2931078

· CU#8 KB Article: http://support.microsoft.com/kb/2917531

· CU#7 KB Article: http://support.microsoft.com/kb/2894115

· CU#6 KB Article: http://support.microsoft.com/kb/2874879

· CU#5 KB Article: http://support.microsoft.com/kb/2861107

· CU#4 KB Article: http://support.microsoft.com/kb/2833645

· CU#3 KB Article: http://support.microsoft.com/kb/2812412

· CU#2 KB Article: http://support.microsoft.com/kb/2790947

· CU#1 KB Article: http://support.microsoft.com/kb/2765331

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

Cumulative Update – 3 for SQL Server 2012 Service Pack 2 Is Now Available !


The 3rdcumulative update release for SQL Server 2012 Service Pack 2 is now available for download at the Microsoft Support site. Cumulative Update 3 contains all the hotfixes released since the initial release of SQL Server 2012 SP2.

Those who are facing severe issues with their environment, they can plan to test CU3 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP3 final release to deploy on your production environment, to have consolidate build.

KB Article For CU2 of SQL Server 2012 SP2

Previous Cumulative Update KB Articles:

Download Link of SQL Server 2012 SP2 :

SQL Server 2012 SP2 : http://www.microsoft.com/en-us/download/details.aspx?id=43340

SQL Server 2012 SP2 EXPRESS : http://www.microsoft.com/en-us/download/details.aspx?id=43351

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

Interview Questions on SQL Server Database Mirroring


1) What is default port of Database Mirroring Endpoint?

Ans : 5022

2) Database Mirroring comes with which edition?

Ans: SQL Server 2005 SP1. Or SQL Server 2005 RTM with trace flag 1400

3) When I configure mirroring I’m receiving the below errror,
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again

Ans: The fully qualified computer name of each server can be found running the following from the command prompt:
IPCONFIG /ALL
Concatenate the “Host Name” and “Primary DNS Suffix”

Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com
Then FQDN of your computer name is just A.corp.mycompany.com.

4) How to enable mirroring by Script ?

Ans: – Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://A.corp.mycompany.com:5022′;

– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://B.corp.mycompany.com:5022′;

Note: Replace the dbname before using the above script

5) How to disable mirroring by script?

Ans: ALTER DATABASE [AdventureWorks] SET PARTNER OFF

Note: Replace the dbname before using the above script

6) How to do manual failover to Mirror when principle is working fine?

Ans: ALTER DATABASE <DB Name> SET PARTNER FAILOVER

7) Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring

Ans : You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

8) Can we configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

Ans: Nope its not possible, both principal and mirror should have same edition

9) Is it possible to take backup of mirrored database in mirror server?

Ans: No

10) Is it possible to perform readonly operation at mirrored database in mirror server?

Ans: Yes, You can create database snapshot for the same

11) Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Ans: No

12) Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping

Ans: No

13) How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec.

Ans: ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

Note: Before using the script change the dbname.

14) What status of mirroring has if secondary is down?

Ans: If secondary is down principle or Mirror show status disconnected

15) What status of mirroring has if principle is down?

Ans: If principle is down mirror will be disconnected with in recovery instead of synchronized with restoring

16) What status of mirroring has if mirroring is paused?

Ans: Is mirroring is set to paused from principle then then both principle & mirror in suspending

17) How to bring mirror DB online if Principle is down?

Ans: ALTER DATABASE <DB Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

If you try to do failover like normal situation when principle is online [ALTER DATABASE <DB Name> SET PARTNER FAILOVER] then you will receive below error.

ALTER DATABASE <DB Name> SET PARTNER FAILOVER

Msg 1404, Level 16, State 10, Line 1

The command failed because the database mirror is busy. Reissue the command later.

18) System Store Procedure to monitor Mirroring?

Ans: MSDB.SYS.SP_DBMMONITORRESULTS

19) What are different possible Mirroring Stats?

Ans:

SYNCHRONIZING

SYNCHRONIZED

SUSPENDED

PENDING FAILOVER

DISCONNECTED

Refer for more on SQL Server Database Mirroring

http://mssqlfun.com/2014/11/13/script-to-monitor-sql-server-database-mirroring-status/

http://mssqlfun.com/2014/11/10/add-witness-to-existing-sql-server-database-mirroring/

http://mssqlfun.com/2014/11/03/sql-server-database-mirroring-monitoring/

http://mssqlfun.com/2014/10/29/add-database-file-on-database-invloved-in-db-mirroring-3/

http://mssqlfun.com/2014/10/27/remove-witness-from-sql-server-database-mirroring/

http://mssqlfun.com/2014/10/14/database-mirroring-states-sql-server/

http://mssqlfun.com/2014/10/13/configure-sql-server-database-mirroring/

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

Script to Monitor SQL Server Database Mirroring Status


SQL Server Database Mirroring is fantastic HA solution developed by Microsoft. Microsoft lunch database mirroring with SQL Server 2005. Database Mirroring send logs to mirror from principle. We need to monitor Database Mirroring status continuously to ensure that everything is working fine. We can use MSDB.SYS.SP_DBMMONITORRESULTS system store procedure to monitor database mirroring.

Below script will convert number to actual values during monitoring. You can use below script to monitor the database mirroring status.

CREATE TABLE #DBMRESULTS

(

DATABASE_NAME VARCHAR(255),

ROLE INT,

MIRRORING_STATE TINYINT,

WITNESS_STATUS TINYINT,

LOG_GENERAT_RATE INT,

UNSENT_LOG INT,

SENT_RATE INT,

UNRESTORED_LOG INT,

RECOVERY_RATE INT,

TRANSACTION_DELAY INT,

TRANSACTION_PER_SEC INT,

AVERAGE_DELAY INT,

TIME_RECORDED DATETIME,

TIME_BEHIND DATETIME,

LOCAL_TIME DATETIME

)

INSERT INTO #DBMRESULTS

EXEC MSDB.SYS.SP_DBMMONITORRESULTS ‘USERDB1′, 0,1

SELECT DATABASE_NAME,

(CASE ROLE

WHEN 1 THEN ‘PRINCIPAL’

WHEN 2 THEN ‘MIRROR’

END) ROLE_OFDB_QUERY_FIRED,

(CASE MIRRORING_STATE

WHEN 0 THEN ‘SUSPENDED’

WHEN 1 THEN ‘DISCONNECTED’

WHEN 2 THEN ‘SYNCHRONIZING’

WHEN 3 THEN ‘PENDING FAILOVER’

WHEN 4 THEN ‘SYNCHRONIZED’

END) MIRRORING_STATE,

(CASE WITNESS_STATUS

WHEN 0 THEN ‘UNKNOWN’

WHEN 1 THEN ‘CONNECTED’

WHEN 2 THEN ‘DISCONNECTED’

END) WITNESS_STATUS,

LOG_GENERAT_RATE,

UNSENT_LOG,

SENT_RATE,

UNRESTORED_LOG,

RECOVERY_RATE,

TRANSACTION_DELAY,

TRANSACTION_PER_SEC,

AVERAGE_DELAY,

TIME_RECORDED,

TIME_BEHIND,

LOCAL_TIME

FROM #DBMRESULTS

DROP TABLE #DBMRESULTS

http://gallery.technet.microsoft.com/Script-to-Monitor-SQL-3d25e132

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

Follow

Get every new post delivered to your Inbox.

Join 248 other followers