Hi All ,
data base size of our orachestrator is full now. we are trying to delete the logs table . but the size gets increased and decreased simultaneously. infact none of the processes are running now. no developer is connected and doing development. but why it is increasing ?? infact the rate of increasing is high than decreasing. how come ? delte older reords in dbo.logs table is in progress but still this phenomena is there…!
Hi All ,
Review the logs that are actively being generated this will indicate the credential being used as well as the Robot. Do they have anything in common?
Robots can be adjusted to have different log levels within their NLog configuration, Orchestrator Robot setting, and from the UiRobot Tray / Agent Desktop has any of your robots been bumped up to trace level and over logging when it does need to?
Take a look at your Orchestrator’s
Web.config and look to what your minimum log level is configured for
Robot.*. If you need to use this to control the log levels that make it into the database.
The other question would be, is the data size strictly increasing due to the dbo.logs or are other tables increasing your data size or possible the data base log file itself (temp log space allocated etc). Depending on if you are deleting records, truncating, creating new table, rebuilding index etc, your temp log space could be growing as you delete the records in the table.
I used the following SQL to help me identify and trim down table sizes last year when we were limited on space.
sp_helpdb UiPath; DBCC SQLPERF(LOGSPACE); exec sp_spaceused; SELECT SUBSTRING(a.FILENAME, 1, 1) Drive, [FILE_SIZE_MB] = convert(decimal(12,2), round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2), round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2), round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , [FREE_SPACE_%] = convert(decimal(12,2), (convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) * 100)), a.NAME, a.FILENAME FROM dbo.sysfiles a ORDER BY Drive, [Name]; SELECT (SELECT COUNT(*) FROM dbo.Logs) AS Logs, (SELECT COUNT(*) FROM dbo.QueueItems) AS QueueItems, (SELECT COUNT(*) FROM dbo.UserNotifications) AS UserNotification, (SELECT COUNT(*) FROM dbo.TenantNotifications) AS TenantNotification, (SELECT COUNT(*) FROM dbo.AuditLogs) AS AuditLogs;
Also remember to create some maintenance tasks that will keep your tables in a good state that is suitable for your need.
those were excellent insight and i would even say it answers my question. to be precise , as i mentioend earlier , its log table ie dbo.logs is the 95 % space holder. but from your answer i still have couple of questions , below is my Nlog node of the web.config
Nlog config can you recommend some part to modify on this ? to reduce the log traffic ?
can u little bit elaborate on this ? my assumption is you are talking about SQL database log file itself . ans yes we initially deleted the records it did not work , size decreased but increased as well !! (some one pointed out in my team that there is a redo logs for sql which will instruct the DB to put back those deleted records unless you commit ! ) . finally we truncated the table so that the size came to 1 GB.
waiting for your observation
Please provide the configuration inline with your topic, unfortunately sites like dpaste are blocked for me.
Yes that is correct. Databases are not my subject of expertise so take it with a grain of salt… but from what I understand DELETE is a Data Manipulation Language (DML) command while TRUNCATE is a Data Definition Language (DDL) command. Delete will mark a record delete but doesn’t actually free up pages right away. The deletion will also be recorded in the transaction logs (I’m not sure how much depending on the recovery model). TRUNCATE can be transactional, but the logging is less and I believe removed the pages at the time of the truncate.
In our scenario the file system was provisioned with wrong storage type and unable to be expanded while the database was created with a database file starting at 64MB and unlimited growth and the database’s logfile was 64MB, and a fairly high limit. It went unchecked for a while expanding until it couldn’t grow any further.
SQL server was setup with mirroring, but as the disk filled up the transactions were not able to be replicated to the secondary instance which also meant the log did not truncate, the log file was small in comparison to the data file, shrinking the log file didn’t provide much additional space.
We were going to shrink and rebuilding the data file, but again due to the file system constraints we were unable to do that either.
We ended up having to delete some temporary / unessential files from the file system which gave us enough room to take a snapshot of the database. After which we reprovisioned the file system with adequate space that could be expanded in the future if needed at which point we restored the database and proceeded to delete records from the dbo.logs table among others, after which point we added some scheduled jobs to keep the various tables in check for different time ranges and types, delete small amounts at a time to be manageable without growing the log file too much.
We did consider shrinking the files and rebuilding the indexes, but decided not to as we had the appropriate amount of space for the File system now and the size of the data files was not a concern.
welcome to the uipath community
before manage it just clear your log file (.ldf).Shrink the .ldf file and clear it first
then if you are worring about robots log , just make it log level in to limited , i hope your current log level is in information
and then , you can run the report on of “Disk usage by table” on SQL server on your orchestrator
most probably the log usage as follows
dbo.queueitems and …
so you can clear the tables by using following sql commands
makes sure not to delete all the data and before delete , archive your table.
** by clearing your log file will be solve your most of the issue i hope
if you having more trouble regarding this , just contact me .