MS SQL Db Growing - UserInfoHistory Table - Self Inflicted!
Hello Guru's!
With some help, I've created Powershell scripts (Thanks Érica!!) to add/edit users in my RDM MS SQL Data Source automagically and hit a problem... my database has grown to over 500gb. After a little digging, I've found that the UserInfoHistory table is the culprit holding almost a million rows and 536gb of history that I probably shouldn't be keeping... and/or I need to cool my jets with the scripts running every 15 min's... I'd prefer to flush these logs if possible and adjust retention. Thoughts?
Thanks!!
B
All Comments (5)
Hello,
My recommendation would be to create a ""RDM Service account"" (double double quotes coz previous mixup with another customer), e.g. RDMPowershellRunner to run these automated tasks.
This would allow you to create SQL Statements that would delete only the historical data related to that account.
We can send a SQL Statement that you can schedule, we'll do that shortly
Maurice
Thanks Maurice! Érica mentioned this the other day and I figured I was alright. I currently run the scripts under my account which should meet your req's. I can change it of course... will do that moving forward because I'm not really testing anymore. Appreciate the lightning fast response!!!
B
Hi Maurice,
Happy Monday! My alerting on SQL has been trying to prevent coffee from happening this morning. I'm still quite low on space and it doesn't appear to be leveling off any time soon. Did ya'll have a chance to work on that SQL Statement?
B
Hi Brian,
The first step will be to delete the rows in the dbo.UserInfoHistory table.delete from dbo.UserInfoHistory where ModifiedUserName = 'SMARTDATA\bkrontzadmin'The second step will be to release the now "unused space" from the database file. There are many resources to do such an action.
Here for example: https://dba.stackexchange.com/questions/86295/releasing-free-space
Keep in mind must DBA's will tell you to not release free space back to the OS. In the case I would say it is very much justified.
Good read: https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Best regards,
Stéfane Lavergne
Right on Stefane... Brent Ozar would be unhappy, I'm sure... but I'll defrag the db after the shrink... familiar with all of that stuff, but can't write a query to save my life :)
THANK YOU!
B