Category: DBA Utilities

DBA Utilities, Errors and Exceptions, SQL

SQL Server Configuration Manager – The remote procedure call failed. [0x800706be]

Problem Statement:

Recently, I encountered an issue as below with SQL Server Configuration Manager which is a very common issue for most of us. Most of the time, this type of error is related to not having enough permission to see the SQL Server Configuration Manager. However, the case was different for me as I am the administrator for my system.

Observations:

In my laptop, I have multiple SQL versions like 2008 R2, 2014, 2016 and 2017.For some reason, SQL Server Configuration Manager (by default it was pointing to oldest one, nothing but 2008 R2 version) was not able to correctly get the information for all the installed versions. This was causing an issue.

To resolve the issue,

1. we can identify the file location as “C:\windows\syswow64\” OR “C:\Windows\System32\”.
The SQL Server Configuration manager file name should be similar to SQLServerManager**.msc where ** denotes the version of the SQL Server.

2. Open the latest version of SQL Server configuration manager file. This will work as expected.

However, the above is not a complete solution while we open the configuration manager from the start window. This is because, the default configuration manager will still be pointing to the older version of the SQL Server. To change this behavior, we need to do the following steps.

1. Open the “SQL Server Configuration” in start and right click on the icon.


2. Right Click on “SQL Server Configuration Manager” and change the Target file to the latest mmc file.(You should have administrator privilege to do so)


3. Close the Window

From then, you will be successfully able to see the SQL Server Configuration manager that fetch the data as expected.

DBA Utilities, Linked Server, NON SQL

How to identify NetBIOS Name of a computer?

Its not a SQL Server related, but I found it difficult to identify a way to get the NetBIOS name of a computer, hence sharing the same.

Recently, while troubleshooting an issue with MSDTC, I had to use DTCPing tool. I do not cover the DTCPing tool here, but will share more about it in another post. DTCPing tool was expecting NetBIOS name instead of IP address.

So, here are few options to identify the NetBIOS Name of a computer. 


Using T-SQL

Select serverproperty('ComputerNamePhysicalNetBIOS')
(Or)
exec master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\','ComputerName'

Using Windows

nbtstat -n  (-n   -- Lists local NetBIOS names.)

Thats it for now, but if you think there is any other method, post it in the comment section.

DBA Utilities, SQL

A quick look at SQL Server Logs

Logs are always important for anyone who works with any application. SQL Server has its own Logs that can be accessed to get the required information. Often, people call it as SQL Server Error logs. However, I personally would like to refer as SQL Server logs instead of Error Logs. The reason is that it does not only report the errors or exceptions, but also other important information.

What are the different types of logs in SQL Server?

Each one represents the logs for different purposes.
1. SQL Server – Logs SQL Server specific information
2. SQL Server Agent – Logs SQL Server Agent information
3. Database Mail – Logs Database Mail related information
4. Windows NT – Logs System related information

How do we see these log information?

There are multiple ways to get the log information as below.

using TSQL command using XP_READERRORLOG

XP_ReadErrorlog is an extended procedure available in SQL server. Please note that this is an undocumented procedure.

Parameter details:

There is also another method using sp_readerrorlog. However, if you take the definition of the procedure, it uses XP_readerrorlog internally.

Using Object Explorer

Management provides two options for us to view the logs as below.
1. Management -> SQL Server Logs

2. SQL Server Agent -> Error Logs

How to find the location of SQL Server log file?

The logs are not storing in SQL Server anywhere, but in the disk. When you access the logs in any of the above methods, it reads from a file from the predefined location. We can identify the location of the log files by any of the below methods.

using XP_Readerrorlog

using SQL Server configuration Manager

– SQL Server Configuration Manager -> Right click on SQL Service, properties -> Select “Startup Parameters”
– Parameter that starts with “-e” represent the SQL Server log path

using SERVERPROPERTY


How do we clear the Logs from SQL Server?

To clear the Logs, there are two options as below. To be clear, there is no concept of clearing the log *immediately*, but closing the most recent one and clearing the oldest one as per the configuration.

sp_cycle_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Customize the number of maximum archives that you want to keep. The range is between 6(Min) to 99(Max).
– You can configure the number of error logs by Right click on “SQL Server Logs” under Management -> Configure -> specify the number of files. By default, the value is 6.
sp_cycle_agent_errorlog
– Closes the current log for SQL Server logs and flush the oldest one.
– Make sure the current database is msdb.
– Make sure the SQL Server Agent is up and running.
– Can have maximum of 9 archives for agent logs.

Hope this post helps you to understand the Logs in SQL Server, Looking for your feedback if I missed anything.

DBA Utilities, SCRIPT GALLERY, SQL

How to get last running query based on SPID in SQL Server

We often need to identify the SQL text that was executed last time for a spid in SQL Server. Recently one of my friend was asking to get these information for his troubleshooting purpose. I have come up with a very simple query to help him as below.

Hope this will be helpful for those looking for similar information.


SELECT
sysprc.spid,sysprc.waittime,sysprc.lastwaittype,DB_NAME(sysprc.dbid) AS database_name,
sysprc.cpu,sysprc.physical_io,sysprc.login_time,sysprc.last_batch,sysprc.status,
sysprc.hostname,sysprc.[program_name],sysprc.cmd,sysprc.loginame,
OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text
FROM sys.sysprocesses sysprc
OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt
DBA Utilities, Errors and Exceptions, SQL

Error : MDF file cannot be overwritten in SQL Server
Today one of my colleague asked me that he was trying to replace an existing database with another database, while trying to do so , he has encountered the below error , though he was using same restore scripts which were working with out any error  in another machine of same configuration.

SQL Scripts used: 

USE master
BACKUP DATABASE [Test_master] TO DISK = N'C:\Backups\Test_master.bak' WITH NOFORMAT, INIT, NAME = N'Test_master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

Error Message: 


Msg 1834, Level 16, State 1, Line 6 
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master.mdf' cannot be overwritten. It is being used by database 'Test_master'.

Msg 3156, Level 16, State 4, Line 6 File 'Test_master' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master.mdf'. 
Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 6
The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master_log.ldf' cannot be overwritten. It is being used by database 'Test_master'.
Msg 3156, Level 16, State 4, Line 6 File 'Test_master_log' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_master_log.ldf'.
 Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 6 Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

Solution:

As the Error message clearly says that restore should happen WITH MOVE. We were able to restore Successfully by using WITH MOVE. 

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' 
WITH MOVE 'Test_master' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf', 
MOVE 'Test_master_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test_log.ldf', REPLACE, STATS = 10;

Output of this query: 


14 percent processed.
23 percent processed. 
32 percent processed. 
42 percent processed. 
51 percent processed. 
61 percent processed. 
70 percent processed. 
80 percent processed. 
94 percent processed. 
100 percent processed. 
Processed 168 pages for database 'Test', file 'Test_master' on file 1. Processed 2 pages for database 'Test', file 'Test_master_log' on file 1. RESTORE DATABASE successfully processed 170 pages in 0.023 seconds (57.659 MB/sec).
To answer the question of my colleague on why he didn’t receive the same error on another machine with same set of scripts.

lets try with same scripts which he has used. i.e. WITH OUT using MOVE 

USE master BACKUP DATABASE [Test_master] TO DISK = N'C:\Backups\Test_master.bak' WITH NOFORMAT, INIT, NAME = N'Test_master-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10 

ALTER DATABASE [Test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

RESTORE DATABASE [Test] FROM DISK = N'C:\Backups\Test_master.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

As we could see that we are able to restore with No Error 🙂 


4 percent processed.
23 percent processed. 
33 percent processed. 
42 percent processed.
52 percent processed.
61 percent processed.
71 percent processed.
80 percent processed. 
94 percent processed.
Processed 168 pages for database 'Test_master', 
file 'Test_master' on file 1. 100 percent processed.
Processed 1 pages for database 'Test_master', file 'Test_master_log' on file 1. BACKUP DATABASE successfully processed 169 pages in 0.021 seconds (62.872 MB/sec). 
14 percent processed. 
23 percent processed. 
33 percent processed. 
42 percent processed. 
52 percent processed. 
61 percent processed. 
80 percent processed. 
94 percent processed. 
100 percent processed. 
Processed 168 pages for database 'Test', file 'Test_master' on file 1. Processed 1 pages for database 'Test', file 'Test_master_log' on file 1. RESTORE DATABASE successfully processed 169 pages in 0.021 seconds (62.872 MB/sec).
Conclusion:

As long as the database being restored already has the same number and type of files to match those contained in the backup set being restored, the MOVE clause is not required.