SSIS Database with lots of space

Question

SSIS Database with lots of space

Viewing 15 posts - 31 through 45 (of 50 total)

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

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

Thom A - Wednesday, November 29, 2017 2:30 AM
Indeed. You need to know what is right for you. For example, like I said, mine is set to 7 days. The reason being that we run a LOT of SSIS tasks. If I stored 365 days of logs for SSIS, I wouldn't be surprised if SSISDB was over 150GB~ in size just for the logs. What you need is up to you or whoever makes those decisions in your business; they know better for your environment.

Yes. Absolutely. Thank you very much.

I still have more questions for you.

I don't understand why we log so much data. According to my little (or very little understandying) we should not keep so many information volume.

Here is the tables size in PROD:

We have a huge amount of information in internal event message and message context as well as in internal mesages. BUT we have the parameter in the catalog set to not log as per below:

The server wide default logging level is set to none. Based on this. Should we still get logging if this parameters is set to none?

If yes, then it is probably because of some of the costum components that we have that are logging.

How can I see inside a package which components are logging?

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

WE would realy like to have minimal logging in our packages and because have the server wide default logging level set to nothing , we don't understand why do we have so much log infromation.

I went into the SSDT and saw that packages under the SSIS menu , on logging have several option, like this:

But the first node of the left tree is not ticked (StrtoStg_TransferData)

Is it still doing log? even with the first opetion not activated?

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

river1

SSC-Insane

Points: 24309

November 29, 2017 at 5:42 am

#1969872

Another example:

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

Even with the logging level set to None you still get logging although it is very minimal logging.

Thom A SSC Guru Points: 99130 More actions · Answer 5

Going to be honest, but the sea of images and a mad morning at work, have made me completely loss track what your aim is now. We've identified that the "problem" is your log file, and that it's set to 8GB (if i recall?). What is the problem now? Please don't post images that don't explain anything, tell us what you want, and what you need to achieve. Apologies, but it seems that you're posting lots of pictures of your Server's properties, without understanding what they mean; which in turn doesn't help us.

I believe, a while a go, I asked if you'd backed up your SSISDB and although i didn't get an answer, it there seemed to be an implied "no". Is that the case? If you don't do a back up, your transaction log isn't going to get smaller.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

If 14GB for your SSISDB is to big reduce your retention period, let it purge, then shrink it. Once shrunk rebuild your indexes.

Even with logging set to None, you still get logging.

If you concerned the cleanup isn't happening, run the clean up job manually and run some checks.

If you see no reduction in size query the SSISDB, see what the earliest dates are and if they should be purged, purge them. Then raise a ticket with Microsoft to help you investigate why your SSISDB is not purging correctly. You can easily see the relationships between the tables, so build a query and check the contents.

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

I just did this query:

select distinct(context_source_name), context_source_id, property_name , count(*) contador from [internal].[event_message_context]

group by context_source_name, context_source_id, property_name

order by contador desc

And got the following resulst:

vLoadID {0A78B447-DA43-4B07-B85B-70D11AE85EF5} Value 1559
vLoadStartDateTime {F144C73C-81B5-4283-8D92-67FD2D2CC3BF} Value 1559
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} ConnectionString 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} CreationName 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} Description 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} ID 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} InitialCatalog 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} Name 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} ProtectionLevel 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} RetainSameConnection 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} Scope 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} ServerName 882
dwhCore {F6C903F7-DE16-48A6-A8CF-16C5390FC0E2} SupportsDTCTransactions 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} ConnectionString 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} CreationName 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} Description 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} ID 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} InitialCatalog 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} Name 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} ProtectionLevel 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} RetainSameConnection 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} Scope 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} ServerName 882
dwhCubes {72E82C16-C5DB-4B5A-98A3-CD9DD7DA14DC} SupportsDTCTransactions 882

It seems that a good part of the logging is related with connection string and initialization of the package.

BUT I would like to get ride of this infromation to be loggeg.

What can I do?

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

Build the entity relationship diagram from within the SSISDB, find the relationships, and look at the root internal.operations and query the child tables, you don't want to purge anything with an active operation. If everything has an active operation, then reduce the overall retention limit.

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

Ok. After reading you last posts, let me try to explain what I am trying to achive.

1) understand why I had the logs so big. This was already achieved with your help. (I was able to understand).

2) Now my concern is: I have lots of records in this tables even having logging set to None.

So what I would like to understand is why are the packages still logging if the server is set to no log. On the other hand, I would like to understand what are the packages that are logging infromation to this tables (probably not all) and if I can tell to those packages to NOT log information.

Can you please help me understand?

Thanks

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

Because I have huge amount of rows in this tables, probably, if my packages don't write this infromation I will be faster in executing my SSIS packages.

So I need to understand, from all the packages, which ones are logging infromation and for each of those, which information is logged.

At the end, I would like to understand if I can disable the logging for those packages or at least, if there is any minimal logging concept where I can just log the minimal infromation possible.

Maybe, some packages have already minimal information while others are logging several.

Can you help me understand the scenario and how can I change it?

Thank you very very much.

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

As I have already said a number of times.

Even with logging set to None, you still have logging. It is very minimal logging, with enough information to see if the package was a success or a failure.

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

If you want to know what is logging, go and query the database.

Go build the ER diagram.

Go query internal.operations, join it to all of its child tables.

If you have data which is in the database which is older than your retention period speak with Microsoft to fix your SSISDB

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

For instance, I have executed below query:

select distinct(package_name), count(*) total from internal.event_messages

group by package_name

order by total desc

Which returned below results:

CoreToCalc_Population.dtsx 19572
StgToCore_LoadDataVault_HSPAD.dtsx 16522
SrcToStg_GenericData_FileLoad_AVALOQTEXI.dtsx 13549
StgToCore_LoadDataVault_GenericData.dtsx 11990
StgToCore_LoadDataVault_ICRM.dtsx 10740
StgToCore_LoadDataVault_MUM.dtsx 10560
StgToCore_SingleLoad.dtsx 10472
StgToCore_LoadDataVault_MDS.dtsx 8580
SrcToStg_GenericData_FileLoad_BBEQEURO.dtsx 8103
SrcToStg_GenericData_FileLoad_BBEQ1.dtsx 6977
SrcToStg_TransferData.dtsx 6084
SrcToStg_SingleLoad.dtsx 5508
CoreToDM_Dimensions_MIS.dtsx 3330
CoreToDM_Single.dtsx 2810
StgToCore_Master.dtsx 2293
SrcToStg_Master.dtsx 1854
DMToCube_SmartProcessingMaster.dtsx 1665
SrcToStg_GenericData_Master.dtsx 1656
StgToCore_LoadDataVault_MDM.dtsx 1580
SrcToStg_GenericData_FileLoad_BBEQ2.dtsx 1249
SrcToStg_GenericData_FileLoad_POSLSMO.dtsx 965
SrcToStg_SingleLoad_AGT.dtsx 705
SrcToStg_GenericData_FileLoad_RBCPOSSECMGR.dtsx 462
CoreToDM_Dimensions_PE.dtsx 420
CoreToDM_Facts_PE.dtsx 284
CoreToDM_Facts_MIS.dtsx 127
CoreToDM_Dimensions_UPDB.dtsx 124
SrcToStg_GenericData_FileLoad_NAVCNAV.dtsx 56

At first site, seems that first 10 packages are the ones logging more infromation , but I don't know if it is true because they may have been executed more times during the day than others

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

Start with internal.operations, then work down.

You know what you need to do now, go check the data, if it is older than 28 days go speak to Microsoft. If it is 28days or earlier reduce your retention period.

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

Looking at the package with more loogging (in the SSIS menu, and then logging) I see following:

But I see that CoretoCalc_Population is not checked. So this means that the below trees are not logging, correct?