VERTICA – View Cluster Status

Below is a command to check Vertica cluster status via admintools.

admintools -t view_cluster

Cheers!

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

Vertica – Check System Resources

Below is a script to check on System Resources. The system resource usage system table provides history about system resources, such as memory, CPU, network, disk, I/O.

SELECT * FROM v_monitor.system_resource_usage ORDER BY end_time DESC;

Cheers!

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

VERTICA – View the Number of ROS Containers Per Projection Per Mode

Below is the command to view the number of ROS containers per projection per node. A high number (over 500) of ROS containers indicates a problem with the PARTITION BY clauses of your tables.  It can also indicate that the merging process is not functioning.

For more information, see Vertica Partitions: The FAQs in the Vertica Knowledge Base.

SELECT node_name, projection_schema, projection_name, SUM(ros_count) AS ros_count 
FROM v_monitor.projection_storage GROUP BY node_name, projection_schema, projection_name ORDER BY ros_count DESC;

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

VERTICA – Check Node Status

Below is a query to monitor the Node status in Vertica. a node in the DOWN state does not participate in any transactions committed after the time the node came down.

SELECT node_name, node_state FROM nodes ORDER BY 1

Cheers!

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

SQL SERVER – Partitioning Table

In this blog post, I will demonstrate how to partition your tables. I have created a sample database called deptstore.

There are 4 general steps in partitioning tables in SQL Server.

You can create two types of partition: RANGE partition and LIST partition.

In this post, I will only enumerate the procedures in creating RANGE partition but the process is essentially the same for creating LIST partition.

1. Create FileGroups for each of your partition.

USE [master]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q12008]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q12008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q12008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q12008]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q22008]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q22008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q22008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q22008]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q32008]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q32008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q32008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q32008]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q42008]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q42008', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q42008.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q42008]
GO

2. Create a Partition Function. This defines how your table is going to be partitioned. You have to specify the data type that your column is dealing with. Also you have to specify the partition/dividing points.

CREATE PARTITION FUNCTION date_part_func (DATETIME)
AS
RANGE RIGHT FOR VALUES('20080401','20080701','20081001')

Note: The word RIGHT  means that if data we are dealing with is exactly 01 July, then it will go to the right    partition. In other words it will go to the partition that is in the 3rd Quarter.

If we say LEFT values, then the records will go to quarter 2.

The screenshot below shows you where you can find the create partition function in SSMS.

3. Create a Partition Scheme. This will bind particular paritions with the appropriate FileGroups.

CREATE PARTITION SCHEME date_part_scheme
AS 
PARTITION date_part_func
TO (q1,q2,q3,q4)

Below is where you can find the created partition scheme in SSMS

4. Create a table that is going to use that Partition Scheme. Ensure that the table you are creating has a column with the data type that you mentioned above (DATETIME).

CREATE TABLE invoices
(
	id INT NOT NULL
	,inv_date DATETIME
	,balance NUMERIC
)
ON date_part_scheme(inv_date)  --specify partitioning column

To test, insert records to the table

INSERT INTO invoices  VALUES(1,'20081204',5000)
INSERT INTO invoices  VALUES(2,'20080202',3000)
INSERT INTO invoices  VALUES(3,'20080803',5000)

Check which records went in which partition.

SELECT $PARTITION.date_part_func(inv_date) partition,
[id], inv_date, balance
FROM invoices;

Note: If we insert data that is not in year 2008? That data will just go to the last partition. It is considered best practice to have your partitions on the left side and right hand (both sides) to be like a catch all partition.

Check partition info

SELECT  pf.name AS pf_name ,
            ps.name AS partition_scheme_name ,
            p.partition_number ,
            ds.name AS partition_filegroup ,
            OBJECT_NAME(si.object_id) AS object_name ,
            rv.value AS range_value ,
            SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows
                     ELSE 0
                END) AS num_rows
    FROM    sys.destination_data_spaces AS dds
            JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
            JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
            JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
            LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id
                                                          AND dds.destination_id = CASE pf.boundary_value_on_right
                                                                                     WHEN 0 THEN rv.boundary_id
                                                                                     ELSE rv.boundary_id + 1
                                                                                   END
            LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id
            LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id
                                             AND si.index_id = p.index_id
                                             AND dds.destination_id = p.partition_number
            LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id
                                                           AND p.partition_id = dbps.partition_id
    GROUP BY ds.name ,
            p.partition_number ,
            pf.name ,
            pf.type_desc ,
            pf.fanout ,
            pf.boundary_value_on_right ,
            ps.name ,
            si.object_id ,
            rv.value
	ORDER BY 
		p.partition_number
GO


SPLITTING PARTITIONS

Before we split any partitions, we need to actually create new FileGroups to hold the new partition.

USE [master]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q12009]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q12009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q12009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q12009]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q22009]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q22009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q22009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q22009]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q32009]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q32009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q32009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q32009]
GO
ALTER DATABASE [deptstore] ADD FILEGROUP [q42009]
GO
ALTER DATABASE [deptstore] ADD FILE ( NAME = N'q42009', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\q42009.ndf' , SIZE = 4096KB , FILEGROWTH = 204800KB ) TO FILEGROUP [q42009]
GO

Alter the partition scheme. Tell it which partition to use next. And then you are going to alter the Partition Function to specify the new split value.

ALTER PARTITION SCHEME date_part_scheme
NEXT USED q12009

ALTER PARTITION FUNCTION date_part_func()
SPLIT RANGE('20090101')


ALTER PARTITION SCHEME date_part_scheme
NEXT USED q22009

ALTER PARTITION FUNCTION date_part_func()
SPLIT RANGE('20090401')


ALTER PARTITION SCHEME date_part_scheme
NEXT USED q32009

ALTER PARTITION FUNCTION date_part_func()
SPLIT RANGE('20090701')


ALTER PARTITION SCHEME date_part_scheme
NEXT USED q42009

ALTER PARTITION FUNCTION date_part_func()
SPLIT RANGE('20091001')

NOTE: It is considered a best practice to have your right and left most partitions to be empty. This is so that when you split it, it will not be a very intensive operation. In other words, the end partitions you want to have those empty so that the split can happen very quickly.

Insert records to the table to see if it goes to the new partitions

INSERT INTO invoices  VALUES(1,'20091204',5000)
INSERT INTO invoices  VALUES(2,'20090202',3000)
INSERT INTO invoices  VALUES(3,'20090803',5000)

 

Cheers!

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

SQL SERVER – Transaction Log Full Due to ‘CHECKPOINT’

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

MySQL – Upgrading MySQL 5.5 to 5.7

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

ORACLE – Different Ways to Start and Stop Oracle RAC Instances

In this blog post, I will show you the different methods to stop and start an Oracle RAC instance and an Oracle RAC database.

1. Check the status of the database by using srvctl utility. (execute srvctl on unix prompt)

srvctl status database -d orclrac

2. Stop the instance instrac

srvctl stop instance -d orclrac -i instrac -o immediate

3. Verify that the instance instrac is down

# check out the status of instracin the output of the following command:
srvctl status database -d orclrac 

# check out the processes in the OS level
ps -ef | grep pmon

# connect to the database and verify to which instance the session is connected
sqlplus system/oracle@rac
SELECT INSTANCE_NAME FROM V$INSTANCE;

4. To stop two instances (e.g. instrac1, instrac2) in a single srvctl utility command.

srvctl stop instance -d rac -i instrac1,instrac2 -o immediate

5. Startup the database using the srvctl utility

Note: the startoption is optional and it defaults to open. With start command, it accepts the open, mount and nomout options.

srvctl stop instance -d rac -i instrac1,instrac2 -o immediate

6. Checkout the status of the database

srvctl status database -d orclrac

 

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

SQL Server – Check Database Mirroring Status

Below is a script to check the database mirroring synchronization state.

SELECT 
	@@SERVERNAME as Server_Name,
	DB_NAME(database_id) as Database_Name,  
	mirroring_state_desc,
	mirroring_role_desc,
	mirroring_safety_level_desc
FROM 
	sys.database_mirroring
WHERE  
	mirroring_role IS NOT NULL

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

SQL Server – TSQL Script to Check Job Run Status

Below is a T-SQL script to check SQL job​​ run​​ status.

 

SET​​ NOCOUNT​​ ON

--Checking for SQL Server verion

IF​​ CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1)))​​ <>​​ 8

BEGIN

---This is for SQL 2k5 and SQL2k8 servers

SET​​ NOCOUNT​​ ON

SELECT​​ Convert(varchar(20),SERVERPROPERTY('ServerName'))​​ AS​​ ServerName,

j.name​​ AS​​ job_name,

CASE​​ j.enabled​​ WHEN​​ 1​​ THEN​​ 'Enabled'​​ Else​​ 'Disabled'​​ END​​ AS​​ job_status,

CASE​​ jh.run_status​​ WHEN​​ 0​​ THEN​​ 'Error Failed'

WHEN​​ 1​​ THEN​​ 'Succeeded'

WHEN​​ 2​​ THEN​​ 'Retry'

WHEN​​ 3​​ THEN​​ 'Cancelled'

WHEN​​ 4​​ THEN​​ 'In Progress'​​ ELSE

'Status Unknown'​​ END​​ AS​​ 'last_run_status',

ja.run_requested_date​​ as​​ last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration​​ *​​ 9​​ +​​ jh.run_duration​​ %​​ 10000​​ *​​ 6​​ +​​ jh.run_duration​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4,108)​​ AS​​ run_duration,

ja.next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message)​​ AS​​ step_description

FROM

(msdb.dbo.sysjobactivity ja​​ LEFT​​ JOIN​​ msdb.dbo.sysjobhistory jh​​ ON​​ ja.job_history_id​​ =​​ jh.instance_id)

join​​ msdb.dbo.sysjobs_view​​ j​​ on​​ ja.job_id​​ =​​ j.job_id

WHERE​​ ja.session_id=(SELECT​​ MAX(session_id) ​​​​ from​​ msdb.dbo.sysjobactivity)​​ ORDER​​ BY​​ job_name,job_status

END

ELSE

BEGIN

--This is for​​ SQL2k servers

SET​​ NOCOUNT​​ ON

DECLARE​​ @SQL​​ VARCHAR(5000)

--Getting information from sp_help_job to a temp table

SET​​ @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,

CASE last_run_outcome WHEN 0 THEN ''Error Failed''

 WHEN 1 THEN ''Succeeded''

 WHEN 2 THEN ''Retry''

 WHEN 3 THEN ''Cancelled''

 WHEN 4 THEN ''In Progress'' ELSE

 ''Status Unknown'' END AS ​​ last_run_status,

CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE​​ last_run_date END last_run_date,

CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,​​ 

CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,​​ 

CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,

last_run_date AS lrd, last_run_time AS lrt

INTO ##jobdetails

FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'

exec​​ (@SQL)

--Merging run date & time format, adding run duration and adding step description

select​​ Convert(varchar(20),SERVERPROPERTY('ServerName'))​​ AS​​ ServerName,jd.job_name,jd.job_status,jd.last_run_status,

CONVERT(DATETIME,RTRIM(jd.last_run_date))​​ +(jd.last_run_time​​ *​​ 9​​ +​​ jd.last_run_time​​ %​​ 10000​​ *​​ 6​​ +​​ jd.last_run_time​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4​​ AS​​ last_run_date,

CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration​​ *​​ 9​​ +​​ jh.run_duration​​ %​​ 10000​​ *​​ 6​​ +​​ jh.run_duration​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4,108)​​ AS​​ run_duration,

CONVERT(DATETIME,RTRIM(jd.next_run_date))​​ +(jd.next_run_time​​ *​​ 9​​ +​​ jd.next_run_time​​ %​​ 10000​​ *​​ 6​​ +​​ jd.next_run_time​​ %​​ 100​​ *​​ 10)​​ /​​ 216e4​​ AS​​ next_scheduled_run_date,

CONVERT(VARCHAR(500),jh.message)​​ AS​​ step_description

from​​ (##jobdetails jd ​​ LEFT​​ JOIN ​​​​ msdb.dbo.sysjobhistory jh​​ ON​​ jd.job_id=jh.job_id​​ AND​​ jd.lrd=jh.run_date​​ AND​​ jd.lrt=jh.run_time)​​ where​​ step_id=0​​ or​​ step_id​​ is​​ null

order​​ by​​ jd.job_name,jd.job_status

--dropping the temp table

drop​​ table​​ ###jobdetails

END

 

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