Skip to content

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"

Apr 12

Auditing database restore in SQL Server instance

How to confirm if someone have restored a database? Which database backup file is restored? WHo did the restore? When was the database last restored?

To find the answer of all these questions you may have to run couple of queries shown below.
Please make sure you run these queries under MSDB database.

1) Below query will give us a details of which all databases has ben restored, when these were restored, who restored it and the backup set which was restored.

select restore_date,destination_database_name,user_name,
backup_set_id, restore_type, recovery
 from restorehistory

OUTPUT:

restore_date		destination_database_name	user_name	backup_set_id	restore_type	recovery

2012-08-07 13:35:29.013	TestDB_RC1		TestID_8181	358			D	1	1
2012-08-07 16:39:49.370	TestDB_RC1		TestID_5357	358			D	1	0
2012-08-07 16:41:19.680	TestDB_RC1		TestID_5357	372			I	0	1

D – Full database backup applied
I – DIfferential backup applied

2) Now to find when was these backups taken, just run the below query. Hre you may have to copy the desired backup set number from the first query.
NOTE: If no result is displayed then possibly the backup which was restored is not from this instance. That mean a backup was taken in some other environment and then restored in current.

 select backup_set_id,name, backup_start_date,backup_finish_date, 
 type, database_name from backupset
 where backup_set_id = 358 or backup_set_id = 372

OUTPUT:

backup_set_id	name						backup_start_date	backup_finish_date	type	database_name

358		TestDB_backup_2012_07_07_133002_2720281	2012-07-07 13:30:08.000	2012-07-07 13:30:22.000	D	TestDB
372		TestDB_backup_2012_07_08_230001_5563963	2012-07-08 23:00:17.000	2012-07-08 23:00:19.000	I	TestDB

3) Additionally you can look into the SQL Server error log to find when was database restored and the location where database backup file was placed for this restore.

2012-08-07 16:39:59.44 spid64      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:39:59.90 Backup      Database was restored: Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11037:12735:86, last LSN: 11037:12771:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_07_133002_2720281.bak'}). Informational message. No user action required.
2012-08-07 16:41:22.15 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:22.15 spid51      The database 'TestDB_RC1' is marked RESTORING and is in a state that does not allow recovery to be run.
2012-08-07 16:41:22.68 spid51      Starting up database 'TestDB_RC1'.
2012-08-07 16:41:23.16 spid51      CHECKDB for database 'TestDB_RC1' finished without errors on 2012-07-07 11:00:16.783 (local time). This is an informational message only; no user action is required.
2012-08-07 16:41:23.16 Backup      Restore is complete on database 'TestDB_RC1'.  The database is now available.
2012-08-07 16:41:23.18 Backup      Database changes were restored. Database: TestDB_RC1, creation date(time): 2012/05/14(12:51:48), first LSN: 11040:10549:56, last LSN: 11040:10573:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'TestServer\MSSQLSERVER\TestDB\TestDB_backup_2012_07_08_230001_5563963.diff'}). This is an informational message. No user action is required.

Apr 1

Query to find tables consuming more space in a disk – Netezza

Some times you may find that your Netezza data slice used space have grown a lot and now you want to check which all tables are consuming space on those data slices/disk so that you can try to find if those tables needs to be groomed, etc.

In order to make your task easy, you can use below query to find tables consuming more space in a disk. The below query will list all the tables in disk id 1,2,3,and 4 which are using more than 1000 MB on any of the disk. It will also provide valuable information like database name where that table exist, disk id as well as spu id in which this disk is residing.
You can alter the script by changing disk id as per your requirement.

select objname as table_name,
       database as db_name,
       hwid as spu_id, dsid,
       (allocated_bytes/1048576) as allocated_mbytes
  from _v_sys_relation_xdb sys,
       _v_sys_object_dslice_info ds
 where ds.tblid = sys.objid
   and dsid in (1,2,3,4)
and allocated_mbytes > 1000
 order by  allocated_mbytes desc, table_name,db_name, dsid;
← Older Entries