Month: January 2019

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!

 

 

 

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

MySQL – Upgrading MySQL 5.5 to 5.7

  By ,

In this blog post, I will enumerate the steps to upgrade MySQL 5.5 to 5.7.

This is also known as the Logical Upgrade which involves exporting SQL from the old MySQL instance using a backup utility (mysqldump), installing the new MySQL server, and applying the SQL to your new MySQL instance.

1. Backup all your databases

 mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > /backup/data-for-upgrade.sql

Note: Use the --routines and --events options with mysqldump (as shown above) if your databases include stored programs. The --all-databases option includes all databases in the dump, including the mysqldatabase that holds the system tables.

2.  Stop MySQL 5.5 instance

service mysqld stop

3. Move old datadir

mv /var/lib/mysql/ /var/lib/mysql-55

4. Install MySQL 5.7

5. Start MySQL

service mysqld start

6. Login to mysql with new root password

# Get temporary root password
grep 'temporary password' /var/log/mysqld.log

# Login to MySQL
mysql –u root –p

# Change root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password’;

7. Restore databases

mysql -u root -p < /backup/data-for-upgrade.sql

 

We have just performed the most cleanest and most stable upgrade path.

Cheers!

 

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