Post Skype Installation SQL tasks part 2 – Maintenance

As mentioned in Part 1, not performing SQL maintenance can lead to strange behavior issues when the RTCXDS database hits its size limit of 16 Gb, such as;  login issues, conferencing issue, adding/removing users, modifying groups lists, etc..   I’m not sure if anyone is aware of a reason that there is still a 16 Gb limit on this one Skype database, where the rest are unlimited, however, the rest of the database do not appear to infinitely grow like this one does.  Some Admins suggest removing the limit, problem solved, except much of the used space is reclaimable white space.  So much like and Exchange database needs to have a backup run before whitespace can be reused, so to must SQL.

Discuss with your SQL Admins, if you have them, before checking and or making the following changes.  And it should go without saying, backups are important and below is what is minimally necessary if you do not have a full SQL backup solution, and the backup files created by this method should at least be part of a nightly File backup process, scheduled accordingly.

  1. Log onto the SQL server, recommended with an account that has SYSADMIN privileges.
  2. Expand the Management container and Maintenance Plans.  If none exist, then we have some work to do.  If there are some present, then investigate before continuing.
  3. Right-click on Maintenance Plans and select New Maintenance Plan Wizard, click Next.
  4. Pick a Name of your choosing or leave as default, but a little information is always helpful.  Click on the Change button to create a schedule for this Plan.  Pick a Daily, non-busy time of day for this Recurring Activity.  Click Ok. Click Next.
  5. Under Select Maintenance Tasks, we want the following; Shrink Database, Back Up Database (Full), Back Up Database (Transaction Log), and Maintenance Cleanup Task.  Some get adventurous and also work on the Indexes; I do not.  Click Next.
  6. Task order: 1-Backup Full, 2-Backup TransLog, 3-Shrink DB, 4-Maintenance cleanup.  Click Next.
  7. Full Backup Database Task.
    • Databases:  All User Databases
    • Backup Set will expire:  After 2 days
    • Back up to:  Disk
    • Create a backup file for every database:
      • Folder: E:\MSSQL\Backup
      • Backup file extension:  BAK
  8. Transaction Log Backup
    • Databases:  All User Databases
    • Backup Set will expire:  After 2 days
    • Back up to:  Disk
    • Create a backup file for every database:
      • Folder: E:\MSSQL\Backup
      • Backup file extension:  TRN
  9. Shrink Database.  The debate is open on free space to keep.  There is overhead when the database has to expand again, this should hopefully find a good balance.
    • Databases:  All User Databases
    • Shrink database when it grows beyond: 250 MB
    • Amount of free Space to remain after shrink: 20%
    • Return freed space to operating system
  10. Maintenance Cleanup Task
    • Delete files of the following type:  Backup files
    • Search folder and delete files based on an extension:
      • Folder:  E:\MSSQL\Backup
      • File extension: *.*
      • Delete files based on the age of the file at task run time: 3 days
  11. Select Report Options
    • Write a report to a text file:  E:\MSSQL\Backup
  12. Click Next and Click Finish.  Assuming success on the task creation process, click Close.
  13. Test the new plan by right-click the DatabaseMaintenancePlan and select Execute.
  14. Assuming Success, check out your backup folder.  IF you are performing these above tasks because you have maxed out your RTCXDS database, you may need to Execute 2 or 3 time before you gain significant space returns.

The RTCXDS databases do have minimum database sizes, so do not be alarmed when the backup files are a fraction of the database and transaction log size.

Check the backup folders after a couple of days to confirm that clean up is happening.  You do not want to inadvertently fill up a drive due to a typo or missed setting.

If you have Mirrored SQL server, you should create the above tasks on both the primary and mirror, but not at the same scheduled time.  Only the server running as Principal for that Database will actually backup, but if you have an unknown failover to the mirror node, maintenance will still continue.

Additional relevant information on the topic:  http://www.confusedamused.com/notebook/lync-2013-and-the-rtcxds-16-gb-transaction-log-limit

4 thoughts on “Post Skype Installation SQL tasks part 2 – Maintenance”

  1. Hi,
    Thanks for the backup task, I’m facing the database size growth issue.
    This task will truncate rtcxds log and shrink to database? I see it only go to all users datatbase , all user databases included all database?
    Thanks.

  2. Great with thanks, after run this maintenance task.
    it reduce 50% space . I only perform backup rtcxds transation log to fix 16GB issue before.

    I saw some SQL article say shrink database will have perform and need re-index?

Leave a Reply

Your email address will not be published. Required fields are marked *