Category: Troubleshooting

One Comment

SQL SERVER – Deadlock on ALTER DATABASE to MULTI_USER mode

  By ,

I was trying to alter the database to MULTI_USER mode, but faced this error.

Msg 1205, Level 13, State 68, Line 10 Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Msg 5069, Level 16, State 1, Line 10 ALTER DATABASE statement failed.

Instinctively, you would run sp_who2, and then try to kill the processes connected to that particular database. But what if the SPID is less than 50? You tried to kill it, but you get the the following error.

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

Basically, it says that you cannot kill a system process.

So, to resolve this problem, here is the secret Ninja move. Simply execute the command below.

SET DEADLOCK_PRIORITY HIGH 
ALTER DATABASE YourDBName SET MULTI_USER WITH ROLLBACK IMMEDIATE

Cheers!

Knowledge worth sharing...Share on linkedin
Linkedin
Share on facebook
Facebook
Share on google
Google
Share on twitter
Twitter
No comments

SQL SERVER – The logical file name is already in use

  By , ,

I just encountered the error below.

Msg 1828, Level 16, State 4, Line 3
The logical file name “OTDB_FG_WRTestResults_01” is already in use. Choose a different

I was able to solve it by executing the following scripts. Essentially, you just have to rename the logical file name to different one, then rename it back to your preferred name.

ALTER DATABASE [DBName] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01', NEWNAME=N'OTDB_FG_WRTestResults_01_Temp')

The file name ‘OTDB_FG_WRTestResults_03_Temp’ has been set.

Then change it back to the original name

ALTER DATABASE [OptimalTestDB] MODIFY FILE (NAME=N'OTDB_FG_WRTestResults_01_Temp', NEWNAME=N'OTDB_FG_WRTestResults_01')

Done.

Cheers!

No comments

SQL SERVER – Trace Flag 1117 and 1118

  By ,

Trace flag 1117 will allow us to take care of tempdb contention. It permits us to grow our data files at the same time.

We also use 1118 to prevent tempdb contention. Trace flag 1118  deals with how we allocate extents whether it be mixed extents or uniform extents

These trace flags are enabled by default in SQL Server 2016. For prior versions, you have to manually turn-on these trace flags.

Below are the ways to enable Trace Flags:

To Enable Trace Flags at Startup

Go to Configuration Manager > Select your instance > Choose Properties > Go to Startup Parameters tab.

Enable Trace Flags with DBCC TRACEON/TRACEOFF

Add the -1 to enable it globally on the instance

DBCC TRACEON( 1117, -1)

DBCC TRACEON( 1118, -1)

To see which trace are enabled:

DBCC TRACESTATUS

 

 

2 Comments

SQL SERVER – Transaction Log Full Due to ‘CHECKPOINT’

  By , ,

Problem:

We encountered the Error 9002 ansaction Log Full Due to ‘CHECKPOINT’.

Solution:

I was able to resolve it by rebuilding the log. Below are the scripts that I used.

ALTER DATABASE DatabaseName  set  EMERGENCY 
ALTER DATABASE DatabaseName  REBUILD LOG ON (NAME='DatabaseName_log',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DatabaseName_Log.LDF')
ALTER DATABASE DatabaseName  set ONLINE
ALTER DATABASE DatabaseName  SET MULTI_USER

 

Below is a script to get the logical name and filepath.

SELECT 
	DB_NAME ([database_id]) AS [Database Name]
	,[file_id] 
	,name
	,physical_name
	,type_desc
	,state_desc
	,CONVERT(bigint, size/128.0) AS [Total Size in MB]
	,CONVERT(decimal(18,2), size/131072.0) AS [Total Size in GB]
FROM
	sys.master_files WITH(NOLOCK)
WHERE
	[database_id] > 4
	AND [database_id] <> 32767
	OR [database_id] = 2
ORDER BY DB_NAME ([database_id]) OPTION(RECOMPILE)

 

Cheers!

 

 

 

No comments

SQL Server – syspolicy_purge_history Job Failed to Run

  By ,

I just installed an Enterprise Editon of SQL Server 2016 on Windows 2012 R2. I noticed the syspolicy_purge_history is failing.

 

Below is the error message. I observed the line set-ExecutionPolicy RemoteSigned -Scope process -Force

I tried to set the execution policy to RemoteSigned in Powershell as what was suggested in the error message but was not successful even when I run powershell as Administrator.

What is syspolicy_purge_history?

This job removes policies evaluation history. When a policy runs, it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

CAUSE:

The ‘Execution Policy’ at the Machine Policy scope is currently set to Unrestricted. This may override the configuration at the ‘Process’ scope.

SOLUTION:

This issue may be resolved by setting the ‘ExecutionPolicy’ to ‘RemoteSigned‘. But I could not change the ‘ExecutionPolicy’ in Powershell. So what I did was to change it directly in the registry.

I verified the change I made in Powershell.

The job syspolicy_purge_history now runs successfully.

No comments

MySQL – Solution to Dependency Issues During MySQL Installation Using RPM

  By ,

For complete steps on how to install MySQL 5.7 on Redhat, you may refer to this link

In this blog, I will show you specifically how simple it is to resolve the dependency issues that you might encounter while installing MySQL 5.7 on RedHat 6 using RPM.

Below are the dependency errors I encountered during installation.

The solution is simple. Just put the option –nodep at the end of the rpm command.

# rpm -ivh mysql-commercial-embedded-devel-5.7.21-1.1.el6.x86_64.rpm --nodeps

That’s it.

Cheers

No comments

SQL Server – Troubleshoot a Transaction Log Full Transaction Error (Msg 9002)

  By ,

I encountered the error below.

Msg 9002, Level 17, State 2, Line 1
The transaction log for database ‘DBName’ is full due to ‘LOG_BACKUP’.

The following are the corrective actions that can be done.

  • Take a log backup (The #1 cause of full transaction log is the lack of backups)
  • Kill a long-running transaction
  • Alter database mirroring, availability groups, or replication to allow the transaction log to clear
  • Either manually grow the existing log file(s) or add another log file (temporarily)
  • As a last resort, switch to SIMPLE recovery model

Execute the script below to know what is the reason why the log cannot be cleared.

SELECT [log_reuse_wait_desc]
	FROM [master].[sys].[databases]
	WHERE [name] = N'DB_Name';
GO

Some examples of why the log is not clearing are LOG_BACKUP, DATABASE_MIRRORING, NOTHING.

You may check the full list in Books Online at http://bit.ly/PoX2xe

 

No comments

SQL Server – MAXIMUMERRORCOUNTREACHED Error (Maintenance Plan)

  By ,

I setup a maintenance plan to backup my databases. I ran it  and I hit the error below.

The problem is because there are a couple of databases that are having an error -transaction log full due to CHECKPOINT (This is a different story). Hence, I’m not able to backup these databases. If you look at the log closely, you will notice the errors (refer to the screenshot below).

So what I did was to deselect these databases to make the job run successfully.

Alternatively though, you increase the maximum error count to make the job go thru successfully despite hitting errors.

  1. Right-click on the Backup Task then go to Properties.

2. Increase the number of the “MaximumErrorCount” parameter.

Cheers!

No comments

SQL Server – The EXECUTE permission was denied on the object ‘xp_instance_regread’, database ‘mssqlsystemresource’,schema ‘sys’ (Error 229)

  By ,

One of our users encountered the error below.

I was able to fix the problem by granting the user direct access to the procedure.

 

USE master
GO

GRANT EXECUTE ON [sys].[xp_instance_regread] TO [DOMAIN\USER];

--To veriy that the user has been granted the privilege
EXECUTE AS USER = 'DOMAIN\USER';Select * from fn_my_permissions('xp_instance_regread','Object')

No comments

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication

  By ,

I encountered the error below during Transaction Replication setup between SQL Server 2008 R2 (Publication) and SQL Server 2012 (Subscriber). When you hit this error, what you need to do​​ is to set up the Subscriber from SQL Server 2012 Management studio in the ‘Subscriber’ database server.