Skip to content

Tag: SQL Server

System database 0

Resource Database

Till now you are aware about the 4 main system databases. However, there is one more important, but hidden system database is exists. Yes, it is called as Resource Database or mssqlsystemesource database.

Today, we will explore this hidden database in SQL Server.

  • This is hidden and read only system database.
  • It does not contain user data or user metadata.
  • It contains all the system objects that are included with SQL Server.
  • SQL Server system objects like sys.objects are resides in this database, however, they logically appear in the sys schema of all the databases

Physical Properties of Resource Database

  • Below are the physical file names of the resource database
    • mssqlsystemresource.mdf
    • mssqlsystemresource.ldf
  • Below is the location of these files.
    <drive>;\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\
  • Each instance has only one associated mssqlsystemresource.mdf file.
  • Changing the location of resource database files is not at all recommended.
  • The Id of this Database is always 32767.
  • Other important values associated with this database are as below
    • Version Number
      SELECT SERVERPROPERTY('ResourceVersion');
      GO
    • Last time the database was updated
      SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
      GO

Backup and Restore of Resource Database

  • We can not backup this database using SQL server like other system or user databases.
  • If you want to backup, you can take the backup of the resource database files directly like file backup.

Please check below Microsoft article for further information.

Resource Database

Check all related articles

By Tushar Kothawade Posted on

Backup 2

How to troubleshoot SQL Server VSS Writer getting failed/Timed Out issue.

As we know, backup is very critical. In many environments, database backups are taken by third-party tools like Avamar, Evault, Netapp, and ComVault. In such cases, many times backup fails due to SQL Server VSS Writer error. You will see errors like Timed Out or Non-retryable error.

SQL VSS Writer Error
SQL VSS Writer Error

To troubleshoot the issue, you can follow below given steps.

  1. To check the ‘Writer status’ you need start the Command Prompt with “Run as Administrator” and execute below command.
VSSadmin List writers

2. You will see number of Writers with their status.

3. Check the status of “SqlServerWriter“. If it shows “Failed“, go to the services. Restart the service “SQL Server VSS Writer”.

4. After that restart the service “Volume Shadow Copy” and then check “SqlServerWriter” status again.

If you are still observing the error, then you need to reinstall the SQL Server Writer. To do that please follow below steps.

  1. Go to “Program and Features” and uninstall “Microsoft VSS Writer for SQL Server”.
SQL Server VSS Writer Name

2. Go to your SQL Server Setup and install SQL Writer using “SqlWriter.msi” file.

3. Now check the Writer state again and you should be capable of seeing it as “Stable”.

Please refer below Microsoft article for further details.

Backup issue because of SQL Server VSS writer

Check all related articles

By Tushar Kothawade Posted on

SQL Server 0

Rename The SQL Server

Often during a side by side upgrade, the application team refuses to change the connection string. They want to avoid altering it from the application end. They are unable to point the application to the new SQL server. In such a scenario, they ask you to rename the SQL server with the existing server. This way, when the application comes up, it will connect with the new SQL server without any issue.

Recently I came across the same situation. The application team was not allowing us the side by side upgrade approach. They don’t want to make any changes on the application end. So we provided them the solution. We decided to build the new server first. At the time of final cutover, we will rename the SQL server with the old name.

At the time of final cutover, we rename the SQL server from windows side and started the application. Unfortunately application was not able to connect to the database. Application was throwing error that unable to connect to the database. After investigation we found that only renaming of server is not enough. You need to rename the SQL server from SQL side as well. Yes, you read it correct. You need to alter or update the New name in System Metadata that is stored in “sys. servers” and reported by the system function “@@SERVERNAME” to rename SQL server.

Rename the SQL server:

To change or rename the SQL server, please follow below steps.

  1. If the server hosts the Default instance of SQL Server then, execute below queries.
sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO

To get these changes effective you need to restart the SQL Services.

2. If the server hosts the Named instance of SQL Server then, execute below queries.

sp_dropserver <old_name\instancename>;  
GO  
sp_addserver <new_name\instancename>, local;  
GO

To get these changes effective you need to restart the SQL Services.

Note: – In Named instance the server name is in format of ServerName\InstanceName. You can change it to NewServerName\InstanceName, but you cannot change it to ServerName\NewInstanceName.

Story don’t end here, sometime even if you execute above queries, it will not work or you will get the error.

In below scenarios you get errors after running above queries.

  1. When Remote logins are present
Error Message:
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, 
Line 44 There are still remote logins for the server 'SERVER A'

2. When Logins associated with Linked server are present

Error Message:
Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, 
Line 44 There are still remote logins or linked logins for the server 'SERVER A'

Check all related articles

By Tushar Kothawade Posted on

SQL Server 2

Change The SQL Server Name Where Logins Associated With Linked Server Are Present. Or Fix Error : Msg 15190 – There are still remote logins or linked logins for the server

If you go and change the SQL server name wher it has Linked server and logins associated with it. In such cases, if you attempt to drop the old server name for renaming SQL server, then you will get below error.

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44 
There are still remote logins or linked logins for the server 'SERVER A'

In this scenario, first you need to find out which are logins associated with Linked server. Below stored procedure will help you to check this.

EXEC sp_helplinkedsrvlogin

In the result of this Stored Procedure you will get the list of logins associated with linked server. Now you need to drop these logins one by one.

To drop these logins, please use below query.

sp_droplinkedsrvlogin 'old_name', 'linked_login';

After successful removal of these logins, you can drop the old server easily. Now go ahead and rename the SQL server. You can perform below steps to do this.

  1. If the server hosts the default instance of SQL Server then, execute below queries.
sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO

To get these changes effective you need to restart the SQL Services.

2. If the server hosts the named instance of SQL Server then, execute below queries.

sp_dropserver <old_name\instancename>;  
GO  
sp_addserver <new_name\instancename>, local;  
GO

To get these changes effective you need to restart the SQL Services.

Check all related articles

By Tushar Kothawade Posted on

SQL Server 2

Change The SQL Server Name Where Remote Logins Are Present. Or Fix the Error: Msg 15190 – There are still remote logins for the server

If the SQL Server has the Remote Logins, then at the time of changing the SQL Server name you will receive below error.

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, 
Line 44 There are still remote logins for the server 'SERVER A'

This is because some remote logins are present on the server and they are not allowed to modify the Server name in System metadata. To solve this problem, first you need to drop these remote logins. In order to drop these logins, please follow below procedure.

  1. If the SQL server instance is default instance. Please run below query.
sp_dropremotelogin old_name;  
GO

2. If the SQL Server Instance is Named instance, then please run below query.

sp_dropremotelogin old_name\instancename;  
Go

After removal of all remote logins you can easily rename the SQL Server using below queries.

  1. If the server hosts the default instance of SQL Server then, execute below queries.
sp_dropserver <old_name>;  
GO  
sp_addserver <new_name>, local;  
GO

To get these changes effective you need to restart the SQL Services.

2. If the server hosts the named instance of SQL Server then, execute below queries.

sp_dropserver <old_name\instancename>;  
GO  
sp_addserver <new_name\instancename>, local;  
GO

To get these changes effective you need to restart the SQL Services.

Check all related articles

By Tushar Kothawade Posted on

SQL Server 9

Recover lost or forget “sa”/Sysadmin password in SQL Server

We all know that “sa” is the account with highest administrative access, which is Sysadmin access. Sometimes we all come in a situation where we lose our access to the SQL instance. In order to manage the SQL databases and perform administrative activities we need to get the access back. In some situations the server loses their domain connectivity and due to this, we lose our administrative access which was associated with Windows logon. So, in those situations we try to crack the password of “sa” account. Here, I am going to tell you how to crack the password or how to get access even when we do not know the password for “sa” account.

  1. Login to the server with administrator account.
  2. Go to SQL Server Configuration Manager.
  3. You will see SQL services running on that server. Right click on the SQL Server service and go to properties.
SQL Server Configuration manager
SQL Server Configuration Manager

4. In properties, go to the Startup Parameters and add “-m” to start the SQL Server in Single user mode.

SQL server Properties
SQ Server Properties

5. Stop the SQL Server and Start it. Now SQL server will start in Singe user mode.

6. Now open the SQL Server Management Studio as administrator and now you can connect to the SQL Server instance without any issue.

Now go ahead and reset the password for “sa” account or create a login for yourself. Once again go to the properties and remove that -m from parameter and start SQL server normally (or in multiuser mode). Consequently login with newly created login and perform your activity.

Check All Related Articles

By Tushar Kothawade Posted on