SSIS Database with lots of space

Question

Post reply

SSIS Database with lots of space

Viewing 6 posts - 46 through 50 (of 50 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Ant-Green

SSC Guru

Points: 113507

November 29, 2017 at 6:29 am

#1969898

Go check the database to see

Eric M Russell SSC Guru Points: 125561 More actions · Answer 2

river1 - Tuesday, November 28, 2017 9:07 AM
Understood. What about maintenace tasks? What are you using for this database?
I am using OLA jobs for all the user databases but not this one.
do you do maintenance tasks in this database? (CheckDBcc and Index defrag)?
What are the best practices for maintaining a SSISDB? Any idea?
Thanks,
Pedro

Yes, SSISDB would need to be on a regular index reorg schedule just like any other database. Also, for those servers where SSISDB tends to grow into the multiple GB size, I will enabled page compression for all tables and indexes.

Run the following in SSISDB to get an overview of data/index sizes, row counts, fragmentation and page fill.

IF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T; SELECT @@servername AS ServerName , DB_NAME() AS DatabaseName , s.name AS SchemaName , o.name AS ObjectName , MIN(o.create_date) AS CreateDate , ISNULL(i.name,'-') AS IndexName , i.type_desc AS IndexType , pp.data_compression_desc AS CompressionType , MAX(CAST(i.is_primary_key AS TINYINT)) AS IsPrimary , MAX(CAST(i.is_unique AS TINYINT)) AS IsUnique , MAX(CAST(i.is_padded AS TINYINT)) AS IsPadded , AVG(i.fill_factor) AS FillFactorPct , MAX(STATS_DATE(i.object_id,i.index_id)) AS StatsLastUpdated , CASE WHEN SUM(pp2.partition_number) = 2 THEN 'Y' ELSE 'N' END AS IsPartitioned , ps.partition_number AS PartitionNumber , MAX( ps.record_count ) AS RowsCount , (SUM(ps.page_count) * 8192 ) / 1024000 AS SizeTotalMB , CAST( AVG( ps.avg_page_space_used_in_percent ) AS SMALLINT ) AS AvgPageFullPct , CAST( AVG( ps.avg_fragmentation_in_percent ) AS SMALLINT ) AS AvgFragmentationPct INTO #T FROM sys.dm_db_index_physical_stats( DB_ID() -- database_id , NULL -- object_id , NULL -- index_id , NULL -- partition_number , 'SAMPLED' -- LIMITED | SAMPLED | DETAILED ) ps INNER JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id INNER JOIN sys.partitions AS pp ON pp.object_id = ps.object_id AND pp.index_id = ps.index_id AND pp.partition_number = ps.partition_number LEFT OUTER JOIN sys.partitions AS pp2 ON pp2.object_id = ps.object_id AND pp2.index_id = ps.index_id AND pp2.partition_number = 2 INNER JOIN sys.objects o ON o.object_id = ps.object_id INNER JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 GROUP BY s.name , o.name , i.name , i.type_desc , i.index_id , ps.partition_number , pp.data_compression_desc;

SELECT * FROM #T ORDER BY SchemaName, ObjectName, IndexName, PartitionNumber;

SELECT SchemaName, ObjectName, IndexName, PartitionNumber , MAX(AvgFragmentationPct)MaxFragmentationPct , MAX(FillFactorPct)MaxFillFactorPct , SUM(RowsCount)RowsCount , SUM(SizeTotalMB)SizeTotalMB FROM #T GROUP BY SchemaName, ObjectName, IndexName, PartitionNumber HAVING MAX(AvgFragmentationPct) > 30 ORDER BY SchemaName, ObjectName, IndexName, PartitionNumber; GO

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

river1 SSC-Insane Points: 24309 More actions · Answer 3

anthony.green - Wednesday, November 29, 2017 6:29 AM
Go check the database to see

I was able to retrieve all the required information. I am now able to see per package the rows loggeg. I went a bit fare and added the event name.

As you will be able to see per below results table (PackageName, Event, Count inserted rows) I am logging several infromation, that in our case we don't need.

For example, OnInfromation and Prevalidation and PostValidation. I don't need to have this information logged. What I need is on error and on warning only.

CAn you please let me know how can I ask the server to NOT log this events (On information, OnPrevalidation and On Post validation)?

Thanks

StgToCore_LoadDataVault_GenericData.dtsx

OnPreValidate

1001500

StgToCore_LoadDataVault_GenericData.dtsx

OnPostValidate

1001500

SrcToStg_GenericData_FileLoad_NAVCRDB.dtsx

OnInformation

971646

SrcToStg_GenericData_FileLoad_BBEQEURO.dtsx

OnInformation

704357

CoreToCalc_Population.dtsx

OnPostValidate

594782

CoreToCalc_Population.dtsx

OnPreValidate

594782

StgToCore_LoadDataVault_GenericData.dtsx

OnPostExecute

347424

StgToCore_LoadDataVault_GenericData.dtsx

OnPreExecute

347424

StgToCore_SingleLoad.dtsx

OnPreValidate

287355

StgToCore_SingleLoad.dtsx

OnPostValidate

287355

SrcToStg_GenericData_FileLoad_AVALOQTEXI.dtsx

OnInformation

258873

CoreToCalc_Population.dtsx

OnPreExecute

210478

CoreToCalc_Population.dtsx

OnPostExecute

210472

SrcToStg_GenericData_FileLoad_BBEQ2.dtsx

OnInformation

188123

SrcToStg_GenericData_Master.dtsx

OnPreExecute

118120

SrcToStg_GenericData_Master.dtsx

OnPostExecute

118120

StgToCore_SingleLoad.dtsx

OnPreExecute

95721

StgToCore_SingleLoad.dtsx

OnPostExecute

95712

SrcToStg_GenericData_FileLoad_BBBONDOTHER.dtsx

OnInformation

78016

SrcToStg_GenericData_Master.dtsx

OnPreValidate

77604

SrcToStg_GenericData_Master.dtsx

OnPostValidate

77604

SrcToStg_GenericData_FileLoad_BBEQ1.dtsx

OnInformation

65121

StgToCore_LoadDataVault_MDS.dtsx

OnPreValidate

47925

StgToCore_LoadDataVault_MDS.dtsx

OnPostValidate

47925

SrcToStg_GenericData_FileLoad_BNPPOSSHCMGR.dtsx

OnInformation

42064

SrcToStg_GenericData_FileLoad_AVALOQEXCHRATE.dtsx

OnInformation

33628

StgToCore_LoadDataVault_INVESTRAN.dtsx

OnPostValidate

30387

StgToCore_LoadDataVault_INVESTRAN.dtsx

OnPreValidate

30387

SrcToStg_GenericData_FileLoad_BNPPOSADV.dtsx

OnInformation

30276

DMToCube_SmartProcessingMaster.dtsx

OnInformation

27340

SrcToStg_GenericData_FileLoad_BNPPOS.dtsx

OnInformation

25882

SrcToStg_GenericData_FileLoad_RBCPOSSECMGR.dtsx

OnInformation

24207

SrcToStg_GenericData_FileLoad_PRODBNAMES.dtsx

OnInformation

22764

StgToCore_LoadDataVault_MUM.dtsx

OnPreValidate

22386

StgToCore_LoadDataVault_MUM.dtsx

OnPostValidate

22386

SrcToStg_GenericData_FileLoad_RBCPOSSEC.dtsx

OnInformation

20450

SrcToStg_GenericData_FileLoad_CSLUXPOS.dtsx

OnInformation

20116

SrcToStg_GenericData_FileLoad_NAVCRDB.dtsx

OnWarning

18051

StgToCore_LoadDataVault_MDS.dtsx

OnPostExecute

16425

StgToCore_LoadDataVault_MDS.dtsx

OnPreExecute

16425

river1 SSC-Insane Points: 24309 More actions · Answer 4

I think I found it.

Our SSIS packages run using Jobs. I was seing the jobs and I found that the level of logging is set to basic as per below image, so probably it is overwritting the other property.

Can this be the situation?

Thank you

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Go try it, but yes that is most likely the cause of you issue.

river1 SSC-Insane Points: 24309 More actions · Answer 6

It was the problem. Solved .

Let me thank you for your time and your effort. I was really lost on this situation and you clarified lots of things. Really appreciated.