How to stop running endless DS batch job?

1) From inside Designer: Go to the Monitor tab in Project Area window, right click your running batch job (with status green) and ‘Kill Job’. If not able to do that, you can get this done in Data Services Management Console. 2) Login to DS Management Console. Go to Administrator > Batch Choose your repository, select the running job and click ‘Abort’ at the bottom. … Continue reading How to stop running endless DS batch job?

How to find the slow running objects in DI?

SQL Server query: SELECT AL_LANG.NAME, AL_HISTORY.START_TIME, AL_HISTORY.END_TIME, AL_HISTORY.EXECUTION_TIME, ObjName.VALUE as Object_Name, ObjType.VALUE as Object_Type, cast(ExecTime.VALUE as numeric(10,0)) as Object_Execution_Time FROM AL_LANG JOIN AL_HISTORY ON (AL_HISTORY.INST_EXEC_KEY = AL_LANG.OBJECT_KEY) JOIN AL_STATISTICS ExecTime ON (ExecTime.OBJECT_KEY = AL_HISTORY.OBJECT_KEY AND ExecTime.NAME = ‘EXECUTION_TIME’) JOIN AL_STATISTICS ObjType ON (ObjType.OBJECT_KEY = ExecTime.OBJECT_KEY AND ObjType.KEY2 = ExecTime.KEY2 AND ObjType.NAME = ‘OBJECT_TYPE’ AND ObjType.VALUE IN (‘Workflow’, ‘Dataflow’)) JOIN AL_STATISTICS ObjName ON (ObjName.OBJECT_KEY = ExecTime.OBJECT_KEY … Continue reading How to find the slow running objects in DI?

How to find if the specific column existed in specified table SQL SERVER 2008?

I have to find all the table names which is having a specific column and to know the no of records available in those tables. Here is the query. When executing the query should remove the UNION at the end of the query. SELECT ‘SELECT ‘ + ””+ t.name +””+’,’+ ‘ MAX(<column_name>) FROM ‘+ t.name + ‘ UNION ‘ AS TableName FROM sys.columns c JOIN … Continue reading How to find if the specific column existed in specified table SQL SERVER 2008?

Finding a column used by the tables in SQL Server database

How to find a column, has been used by the tables in SQL Server database? SELECT TABLE_CATALOG ,TABLE_SCHEMA ,TABLE_NAME ,COLUMN_NAME ,DATA_TYPE ,CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = ”INT” AND COLUMN_NAME LIKE ”%<COLUMN_NAME>%” SELECT T.NAME AS TABLE_NAME, SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME, C.NAME AS COLUMN_NAME FROM SYS.TABLES AS T INNER JOIN SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID WHERE C.NAME LIKE ‘%<COLUMN_NAME>%’ ORDER BY SCHEMA_NAME, TABLE_NAME; Continue reading Finding a column used by the tables in SQL Server database

How to track the data of N no of tables manually

How to track the data of N no of tables manually? If we want to know how data has been loading into DWH tables, then we can use the below query: A query to track the data of tables: select ‘TABLE1’ Table_Name,count(1) as Row_Count from TABLE1 UNION select ‘TABLE2’ Table_Name,count(1) from TABLE2 UNION select ‘TABLE3’ Table_Name,count(1) from TABLE3 UNION select ‘TABLE4’ Table_Name,count(1) from TABLE4 UNION … Continue reading How to track the data of N no of tables manually

TABLENAME and INDEXNAME from the database

Find TABLENAME and INDEXNAME for fragmented indexes Create a function: CREATE FUNCTION dbo.INDEX_NAME (@object_id int, @index_id int) RETURNS sysname AS BEGIN RETURN(SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id) END; GO Run the below query: SELECT OBJECT_NAME(object_id) AS TABLENAME ,dbo.INDEX_NAME(object_id, index_id) AS INDEXNAME ,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) WHERE avg_fragmentation_in_percent > 20 AND index_type_desc IN(‘CLUSTERED INDEX’, ‘NONCLUSTERED INDEX’) Continue reading TABLENAME and INDEXNAME from the database