Search This Blog

Wednesday, June 25, 2014

Database lookup not fetching the latest database field in Penthao - Kettle

Today I was developing a transformation in Kettle & using "Database lookup" step in it. During development I realised that one more column need to be added into the lookup table and then I added that column in database using ALTER TABLE statement.

But in transformation, when I clicked on "Get Lookup Fields" button of "Database lookup" it was not showing the newly added column. I removed the "Database lookup" step from the transformation and then added it back (hoping that it will now show all the columns present in database) but still it was not showing the newly added column.

After googling, I found the following option to handle the above issue:

Go to the main menu action, select Tools -> Database -> Clear Cache

After clearing cache, the newly added column start appearing into the "Database lookup" step.

Thursday, March 27, 2014

Shrink the data or log files using DBCC SHRINKFILE

DBCC SHRINKFILE shrinks the size of database/log file of the current database either to the specific size or as small as it can.

Below query gives you the name of the database/log file, Total size(MB), Used Space(MB), %Used, Physical location of file and the Shrink SQL statement with the specific size which you can execute on the database to shrink the respective file:

SELECT file_id ,name ,size_mb=size/128.0 ,UsedSpace_MB=fileproperty(name, 'spaceused')/128.0 ,UsedSpace_Pct = CAST(fileproperty(name, 'spaceused')*1./size as decimal(5,2)) * 100 ,physical_name ,shrink_stmt='dbcc shrinkfile(' + CAST(file_id as varchar) + ',' + CAST((fileproperty(name, 'spaceused')/128 + 1) as varchar) + ')' FROM sys.database_files