Home » SQL Server

Category Archives: SQL Server

How to Rebuild MSDB database?


I suggest to have regular full backup of your system database along with user database. But if you do not have system database & your MSDB got corrupted then you have to rebuild it. You can do it by instmsdb.sql script comes with installation. It will result in loss of SQL Agent jobs, backup & restore history, database mail configuration etc. Let’s have a demo for that :-

1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server

2. Start SQL Server from the command prompt using the command: NET START MSSQLSERVER /T3608

3. Detach the msdb database using SQLCMD : SQLCMD -E -S<servername> -dmaster -Q"EXEC sp_detach_db msdb"

4. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally.

5. Now you need to execute instmsdb.sql. You can locate it in <Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql

You can execute it from SSMS as well as from SQLCMD.

For SQLCMD from command prompt use command :

SQLCMD -E -S<servername> -i"<Installation Directory><Instance Name>MSSQLInstallinstmsdb.sql" -o" <Installation Directory><Instance Name>MSSQLInstallinstmsdb.out"

6. Re-apply any service packs or hotfix installed on the instance.

7. Recreate the user content stored in the msdb database, such as jobs, alert, etc.

8. Backup the msdb database.

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

How to install standalone SQL Server 2012 instance ?


Steps to install standalone SQL Server 2012 instance

1) Start the setup > Go to Installation from Left > Click on 1st option of New SQL Server standalone edition from right

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

3) Enter Product Key & click Next

4) Accept EULA & Click Next

5) New Feature of SQL Server 2012 installation : Automatically check product update & include them in single installation if available

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

7) Select the role of SQL Server instance, you are going to install

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

9) Click Next to continue

10) Specify SQL Server instance name & Click Next

11) Check Disk space requirement & Click Next

12) Specify Service accounts & Click Next

13) Add User to work as SYSADMIN & Click Next

14) Set error reporting options & click Next

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

16) Check all configurations & Click Next

17) Installation progress will appear to complete the installation. Installation Completed successfully

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

A Very Happy New 2015


Wishing you a very happy new year to you.

May this year bring success & happiness in your life.

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

2014 – One more Year with you


The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 73,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 3 days for that many people to see it.

Click here to see the complete report.

Cumulative Update – 5 for SQL Server 2014 RTM Is Now Available !


The 5th cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 5 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

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

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

KB Article For CU5 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

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

Use of RPC in Cluster


Today, My one of friend come back from SQL DBA interview & share his review. One question about which he is looking for was “What is the use of RPC in cluster” ?

The Remote Procedure Call (RPC) [http://support.microsoft.com/kb/832017] system service is an interprocess communication (IPC) mechanism that enables data exchange and invocation of functionality that is located in a different process. The different process can be on the same computer, on the LAN, or in a remote location, and it can be accessed over a WAN connection or over a VPN connection. The RPC service serves as the RPC Endpoint Mapper and Component Object Model (COM) Service Control Manager. Many services depend on the RPC service to start successfully. RPC did not use fixed ports, it worked on port ranges. RPC also works on Named pipe for “File and Printer Sharing” services.

Windows Cluster use multiple port (at least 100 ports) for different process via RPC. This process can be available over same system, depended system, over LAN or WAN, Domain Controller etc. RPC works on range of ports instead of fix port. This feature of RPC makes it very useful. Sometime critical ports can be already in user by some other process at that time RPC can use other ports from available range for connectivity.

There is long list of services that depends on RPC [http://technet.microsoft.com/en-us/library/cc787851(v=ws.10).aspx] like : Telnet, DHCP, DNS, COM+, DTC, WINS etc.

You may face multiple error when RPC is not working like

1) Cluster name mention not found

2) Node1 not able to communicate to Node 2

3) File Share not working

4) MSDTC issues

5) Etc.

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 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

Follow

Get every new post delivered to your Inbox.

Join 338 other followers