Below is a command to check Vertica cluster status via admintools.
admintools -t view_cluster
Cheers!
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;
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!
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!
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 mysql
database 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!
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
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
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