I currently work for a company that runs Senior Living, Memcare, etc. type facilities. At some of these locations Sara is used for Pendant alerts, roam tags, wander gaurd, door, pull chain, etc. type alerts and notifications for the residents. One of the main features is a phone messaging system that texts the alerts to to staff phones when a pendant is triggered, etc.<\/p>\n
Generically speaking; those phone messages are delivered to our CUCM server via: SARA (notified)->NIPA message to IPCelerate->IPCelerate to CUCM->Phone<\/p>\n
Recently these notifications were randomly getting delayed or dropped. After looking everything over I noticed the IPCelerate transaction log was almost 2\u00a0gigs and clearly the DB had never been maintained since it\u2019s installation (almost 5 years). I was getting ready to go through an annoying (and potentially software breaking) routine of installing SP1 for 2008, updating .NET, etc. in order to get SSMS installed on a non-SP1 2008 R2 server. However, with some deep googling and some archive.org help, I found this guy:<\/p>\n
https:\/\/webmaxtor.blogspot.com\/2015\/03\/ipcelerate-ipsession-database-cleanup.html<\/a><\/p>\n The only guy on the internets that basically had the same issue was good enough to document it. I\u2019m going to update it. It\u2019s basically correct, but there are some things to correct just so someone less experienced can still make it through.<\/p>\n Keep in mind, these are about legacy installations. My version of Sara is 4.4 and this information should apply all the way up to 4.7, maybe 4.8 and up.. but I do not have that enviroment so cannot confirm. \u00a0Our IPCelerate version is up to date (as is JTAPI) with our CUCM version (10.5(2.x)). One difference between my situation and \u201cwebmaxtor\u201d\u2018s (I believe) is that we have a separate servers; One running IPCelerate and another running Sara. IPCelerate is located @ our DC that has our CUCM server and the Sara server is installed @ the location.<\/p>\n I\u2019m going to replicate his information below, with my notes added in red.<\/span><\/p>\n Before continuing !<\/strong><\/span> This is likely dated material as I haven\u2019t had the chance to work with IPCelerate based solutions in years, but the following just helped me out of a jam.\u00a0 The issue was communication between a Status Solutions TAP paging interface, the IPCelerate IPSession server and ultimately Cisco 7925 handsets was delayed.<\/p>\n 1. Open up Windows Service and stop the following services: 2. Open command prompt and type osql -E<\/p>\n 3. Then type the following commands Before you can shrink a transaction log, a backup HAS to be performed.<\/span> Note: This may take a few minutes before you receive a message similar to this: 4. Once you see this message type in the following.<\/p>\n 1> dbcc loginfo(nipa) Example of 0:\u00a0\u00a0\u00a0 \u00a00\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 5570560\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 224526336\u00a0\u00a0\u00a0 200 \u201cwhere you will either see 0 or 2\u201d is just a description. This information isn\u2019t needed specifically for a later step.<\/span> 5. After running this wait about 5 minutes and then type exit to logout of the SQL.<\/p>\n Waiting won\u2019t really help, so just logout go to #6. \u00a0People say to wait when dealing with this because sometimes the loginfo is still running in the background and you can\u2019t perform the shrink, but\u2026just keep going. No need to wait.<\/span><\/p>\n 6. Go to Windows Services and stop the MSSQLSERVER service.<\/p>\n 7. Wait about 2 minutes and then Start the MSSQLSERVER service<\/p>\n 8. repeat steps 2 through 4<\/p>\n Don\u2019t repeat steps 2 through 4. \u00a0Repeat steps 2-3. Running another loginfo will just prevent you from performing the shrink. So go through step 3 and perform the \u201cfake\u201d backup, then move on to step 9.<\/span><\/p>\n 9. No wait is needed this time, begin typing the following: (1 row affected) (I deleted a copy and paste that was here as a note)\u00a0<\/span><\/p>\n 10. Verify the NIAP_log.LDF file under C:\\Program Files\\Microsoft SQL Server\\MSSQL\\Data is rougly 10MB, if not repeat step 9 again.<\/i><\/p>\n 11. Type the following to exit SQL: 12. Start the following services in this order: Summary:<\/span> Additional notes: Final notes \u2013 I will say I had very little expectation for this to solve our issues (at least entirely). Also, our transaction log was just over 1.4GB\u2026which isn\u2019t insane or unheard of. However, performing this DB maintenance totally did fix the delays we were experiencing.. other issues aside.<\/span><\/p>\n Also, if you\u2019ve ever.. EVER.. google for information regarding the shrinking of a db transaction log (or db for that matter) then you\u2019ll know that there are shit ton of DBA know-it-alls that act like using shrink for anything, ever, is a horrible idea and will make your life hell. I\u2019m here to say that is BULLLLLLLLLLLLLLLLSHIT. It\u2019s 100% ok to shrink your\u00a0transaction log files<\/strong>\u00a0for small db systems like this given you\u2019ve performed other basic maintenance like disk de-fragmentation. \u00a0There is very little in the way of something going wrong with shrinking a transaction log file and if there were something to go wrong it\u2019s easy to fix. Besides\u2026 are you REALLY telling me you\u2019re going to migrate and re-build\/organize your entire DB that\u2019s associated with the log\u2026 just to clean it up? Are these people out of their\u00a0minds?<\/p>\n DB\u2019s on the other hand, no matter the size or utilization, I would never shrink\u2026ever\u2026the idea of it makes me wanna shit my pants.<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":" I currently work for a company that runs Senior Living, Memcare, etc. type facilities. At some of these locations Sara is used for Pendant alerts, roam tags, wander gaurd, door, pull chain, etc. type alerts and notifications for the residents. One of the main features is a phone messaging system that texts the alerts to\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":358,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[99,87],"tags":[102,104,103,101,100],"jetpack_featured_media_url":"http:\/\/www.lazytrap.com\/trapped\/wp-content\/uploads\/2018\/05\/broken-200x140.png","_links":{"self":[{"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/posts\/382"}],"collection":[{"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=382"}],"version-history":[{"count":3,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/posts\/382\/revisions"}],"predecessor-version":[{"id":414,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/posts\/382\/revisions\/414"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=\/wp\/v2\/media\/358"}],"wp:attachment":[{"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=382"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.lazytrap.com\/trapped\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n*PERFORM A WINDOWS DISK DE-FRAGMENTATION FIRST ON THE DRIVE CONTAINING YOUR DB FILES.\u00a0*IF YOU HAVE A SEPARATE DISK FOR YOUR DB, GO AHEAD AND DEFRAG YOUR SYSTEM DRIVE AS WELL.\u00a0*WHY? Because if you\u2019re in this state with a Sara or IPCelerate system, it\u2019s because there has been no maintenance plan on these servers.\u00a0*You want, nay, NEED, those transaction log files externally de-fragmented before you go and try to shrink them..\u00a0<\/span><\/p>\n
\nApache Tomcat Tomcat5
\nNipa
\nNipads<\/i><\/p>\n
\n1> use nipa
\n2> backup log NIPA TO DISK =\u2019NUL\u2019
\n3> go<\/p>\n
\nThis here is a \u2018fake\u2019 backup as you\u2019re directing the output to a null device.<\/span><\/p>\n
\nProcessed 33136 pages for database \u2018NIPA\u2019, file \u2018NIPA_log\u2019 on file 1.
\nBACKUP LOG successfully processed 33136 pages in 3.697 seconds (73.423 MB\/sec).<\/p>\n
\n2> go
\nThis will display a long list and the far left column is where you will either see 0 or 2.
\nExample of 2:\u00a0\u00a0\u00a0 \u00a02\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 5570560\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 224526336\u00a0\u00a0\u00a0 200
\n0\u00a0\u00a0\u00a0 64\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 1960000000961100001<\/p>\n
\n0\u00a0\u00a0\u00a0 64\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 1960000000961100001<\/p>\n
\nAlso, this step accesses the log which will later prevent you from shrinking it due to \u201call transaction files are in use\u201d type errors. So, this step, while good (it gives you an idea how many virtual logs are contained in the single transaction log file \u2026 and verifies it is actually there) \u2014 is out of order when you hit the \u201crepeat\u201d stage. I\u2019ll clarify further ahead.<\/span><\/p>\n
\ndbcc shrinkfile(NIPA_log, 10) means, shrink my transaction logfile to around 10 mb<\/span>
\n1> dbcc shrinkfile (NIPA_log,10)
\n2> go<\/p>\nNote: You should get the following message below with no errors.\u00a0<\/del>
\nExample output with no errors:<\/span>
\nDbId\u00a0\u00a0 FileId CurrentSize MinimumSize UsedPages\u00a0\u00a0 EstimatedPages
\n\u2014\u2014 \u2014\u2014 \u2014\u2014\u2014\u2013 \u2014\u2014\u2014\u2013 \u2014\u2014\u2014\u2013 \u2014\u2014\u2014\u2014\u2013
\n5\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1303\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 128\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1296\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 128<\/p>\n
\nDBCC execution completed. If DBCC printed error messages, contact your system
\nadministrator.<\/p>\n
\n1> exit<\/p>\n
\nApache Tomcat Tomcat5
\nNipa
\nNipads<\/p>\n
\n1. open sql command line<\/span>
\n2. create \u201cfake backup\u201d to null device<\/span>
\n3. list entries in transaction file using loginfo. Good to keep a record of.<\/span>
\n4. Stop\/Restart SQL Server service (aka MSSQLSERVER)<\/span>
\n5. Repeat steps 2 & 3 (just for verification and to see the difference in processed time\/pages)<\/span>
\n6. After repeating step 3 (fake backup) \u2013 Shrink the transaction log (step 9)<\/span>
\n7. Verify the shrink took place.<\/span>
\n8. Exit & restart your services in the appropriate order<\/span><\/p>\n
\nIf this is a VM, take a snapshot of your server so you have something to go back to if you mess up.
\nI rebooted my server after all was said and done
\nCheck your event viewer for any \u201cnew\u201d MSSQL errors. There shouldn\u2019t be any.
\nCheck IPCelerate to make sure the services are started. Easiest just to login to the webpage as admin and check connections.
\netc. Due\u00a0diligence.<\/p>\n