Skip to content

Jun 15

Find all the materialized views in all databases – Netezza

You can make use of this shell script to list all the materialized views in alt the Netezza databases. Just save this script as .sh and execute. It will automatically connect to all the databases and pull the list of materialized views in each database.

# This script will list all the matearilized views in all databases

PATH=`dirname \`which $0\``:$PATH

DATABASE=""
DATABASES=""
TABLES=""

if [ "$DATABASE" = "" ]; then
        DATABASES=`nz_get_database_names`
else
        DATABASES=$DATABASE
fi

for DATABASE in $DATABASES; do

                TABLES=`nzsql -d \"${DATABASE}\" -A -t -c "select VIEWNAME from _v_view where objtype = 'MATERIALIZED VIEW';"`

if [ "$TABLES" = "" ]; then

echo "No Materilized View found in" $DATABASE

else

echo "Materilized Views in" $DATABASE
echo "---------------------------------------------------------"
echo $TABLES
echo "---------------------------------------------------------"

fi

done

Jun 1

Finding SQL Server log file location

Here are couples of ways to find the location of SQL Server log file:-

1) Connect to the SQL Server instance from SSMS and then run below query in query analyzer

SELECT SERVERPROPERTY('ErrorLogFileName')
--------------------------------------------------------------
E:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
(1 row(s) affected)

2) Connect to SQL Server instance from SSMS and navigate to Management -> SQL Server Logs and open the current log file. Then scroll down to the beginning of the log and somewhere around 7th line from bottom which will read like “Logging SQL Server messages in file ‘E:\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG’.”

3) Open SQL Server configuration manager then navigate to SQL Server service and open the properties. You will find the log location against Startup parameter field under advanced tab.

4) Go to Windows event viewer then application log and look for event id number 17111 and you can find the log location under General details.

May 20

Query to find permissions granted directly at to specific objects for a specific user – Netezza

You can run below query to find permissions granted directly at to specific objects for a specific user. This query will also report the object name, object type and the database name where this access is granted.
Here we will pull data from various system table and system views in system database.

select u.usename as "User Name", D.DATABASE, o.objname as "Object Name", oc.classname as "Object Type" from _t_object o, _t_user u,  
_t_object_classes oc, _v_database D where u.usename='TEST_ID' and                     
u.usesysid=o.objowner and o.objclass=oc.objclass and o.OBJDB=D.objid;

May 1

SQL Server Agent job history getting lost or not populating

You may come across a scenario when all of a sudden you are not able to see the job history for almost all the jobs except few. Here are few things that will help you in analyzing the issue. Recently we have faced this issue in our environment below id how I manage to find the cause of this issue:-

• Connect to SSMS and open the job history by going to SQL Server Agent → Job → right click and view history, for all the jobs and check how many rows are returned. Like in this case it returned only 1188 rows.

• Now Go to SQL Server Agent → Right Click → Properties → history tab. Here check what is the maximum job history log size and maximum job history per job. In this example it is set to 15000 each. Default for maximum job history log size is 1000 and maximum job history rows per job is 100.

• Now connect to msdb database and run below query to find the row count in jobhistory table.

select COUNT(*) from sysjobhistory

-----------
15000

(1 row(s) affected)

• We have found that the history table is full. So now we need to check why it’s full and which job is writing huge data in history table. In order to find that you may have to run below query in msdb database

select b.name, a.job_id, a.run_date, COUNT(*) NumberofRun from 
sysjobhistory a, sysjobs b 
where a.job_id = b.job_id
group by a.job_id, b.name, run_date

name   job_id                               run_date    NumberofRun
----   ------					  --------    -----------
Job1   R14UIUJI-C164-5FUI-BDAD-54C3UIUJIA2B 20130328    84
Job2   269UIUJI-5902-4E0C-5FUI-57AABAUIUJI2 20130328    12217
Job3   8UIU5FUI-6B0B-446C-9B64-6805FUIIUJIB 20130328    125
Job4   UIUJIB0F-EB63-5FUI-B03D-98B235FUIUJI 20130328    2480
Job5   43CUIUJI-5FUI-4CE2-B068-AEUIUJI19E3A 20130328    84
Job6   AUIUJI5A-8166-42ED-5FUI-FFUIUJI02CD8 20130328    10

(6 row(s) affected)

• The output of above query will help you find the jobs which have more rows in jobhistory table. Like in above example you will see that Job2 is adding almost 12K rows in history table in a day. So now we need to go to this job and see what it is doing.
You can alter above query to find the actual step which is sending huge data to history table. This will help you pin pointing the step which needs attention

select b.name, a.job_id, a.step_id, a.run_date, COUNT(*) NumberofRun from 
sysjobhistory a, sysjobs b 
where a.job_id = b.job_id
group by a.job_id, b.name, a.step_id, run_date

• Now we know that the job history is getting lost because Job2 is just sending huge data to history table. In order to fix this we have to either look into the job step and modify the job so that it will not send huge number of rows to history table or consider changing the maximum job history log size and maximum job history rows per job to higher values, based on above analysis and frequency of troublesome job runs.

Apr 25

Query to find the size of each database in Netezza environment

Please run below query to find the size of each database in Netezza environment. The output of this query will be the database name and total space allocated to the database.
This data is stored in system views under system database.

SELECT ORX.database::nvarchar(64) AS "DatabaseName",
case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS "AllocatedSpace_GB"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
GROUP BY "DatabaseName"
ORDER BY "DatabaseName"

Now if you want to know the free space available to the database as well then try below query:

SELECT ORX.database::nvarchar(64) AS "DatabaseName" ,
case when sum(SOD.used_bytes) is null then 0 else SUM(SOD.used_bytes)/1073741824 end AS "UseSpace_GB",
case when sum(SOD.allocated_bytes) is null then 0 else SUM(SOD.allocated_bytes)/1073741824 end AS "AllocatedSpace_GB",
current_timestamp as "CurrentTime"
FROM _V_SYS_OBJECT_DSLICE_INFO SOD INNER JOIN _V_OBJ_RELATION_XDB ORX ON ORX.objid = SOD.tblid
GROUP BY "CurrentTime", "DatabaseName"
ORDER BY "DatabaseName"
← Older Entries