How can I shrink the CASRMCentralDB database in SQL Server used with Web Reporter?

Products

• Activator
• eTrust ALERT
• eTrust ALERT for CICS
• ALERT
• Archiver
• ASM/ARCHIVE VSE-ESA OPTION
• ASM/Archive
• AutoAction
• AutoSys Job Management Adapter for AutoXfer
• Process Automation Manager
• Portal
• ARCserve Backup Client
• CICSORT
• Clipper
• COBOLVISION Analyzer
• CTO Distributed
• Dart
• Datesolver/2000
• Distribulink
• EUREKA:Object
• EPIC VM
• Enterprise
• Endevor for Unix
• Archiver Disk Option
• EPIC Backup
• EPIC Access Option
• EPIC SUPER SYS
• EPIC for CMS System
• EPIC TAPE MANAGEMENT
• eTrust Antivirus Gateway for CVP
• eTrust Content Inspection Gateway
• eTrust Content Inspection Gateway Option for MSP/ISA EE
• eTrust Antivirus Gateway for Netscape
• eTrust VPN Enterprise Edition
• FAQS VM
• FAQS Automated Systems Operation
• Global Subsystem (GSS)
• ANDYNE
• MAINFRAME MISCELLANEOUS
• Inventory Discovery Server
• ARS Resource Management
• JARS Resource Management ACC Option
• JOBDOC
• JOBWATCH
• Look
• MAZDAMON
• Service Management Affiliate Technology Partner Fee
• Service Management Basic Technology Partner Fee
• Metanet
• MF - MISC OLD CODES
• Omniguard for CICS
• Omniguard
• Opera
• Connect
• PREVAIL
• Paramount XP CAM FOR DIST SYS
• Paramount FOR NAVIGRAPH/HOST
• Research Planmacs Unlimited
• Project Planner
• Remote Console PREVAIL/XP-AUTOMATION PHASE 1
• Remote Console PREVAIL/XP AUTOMATION-RDF/SSM
• Raps
• Research Raveler
• Generic Non Product
• SOLVE:EPS
• Unicenter NetMaster Network Management for SNA
• Alexandria
• Sort System
• SPOOLMAN
• Storage Mainframe
• SYSTEM/SCHEDULER
• SYSTEM/SCHEDULER VSE-ESA OPTION
• System/Manager
• Tape/Manager VSE-ESA OPTION
• Output Management Document Server
• TNG Network and Systems Management Agent for CA-Datacom
• DFSMShsm Workstation
• Network and Systems Management UNIX SMTP
• Unicenter Management for Microsoft Exchange SMTP Option
• UNICENTER II
• Unicenter Network and Systems Management Database Performance Monitor Option
• UNICENTER OSM
• Database Command Center Mnt
• VM:DBA
• VM:DB Admin
• VM:DB Center
• VM:DB Developer
• VM:DB Editor
• VM:DB Monitor
• VM:DB Reorganizer
• VM:DB Reporter
• VM:DB Restore
• VM:DB REXX
• VM:DB SQLMAP
• VM:DB Suite
• VM:Migrate
• VM:Monitor
• VM:Notekeeper
• V/Solver
• VM:Webserver for Officevision
• VM:Webserver Secure
• VM:Webgateway
• VM:Webserver
• XMENU for z/VM
• VISION:COMBO for TSO
• VISION:DAGER
• VISION:ELEVEN
• VISION:EIGHTY
• VISION:Online
• VISION:IQ for Informix
• Automate
• STORAGE RESOURCE MANAGEMENT SUITE

Issue/Introduction

Description:

When you perform the Add AS/Delete AS/Modify AS operations using the Web Reporter Control Panel UI, both the .mdf & .ldf files size increase, and in case of .ldf file, it grows drastically since you perform bulk operations like Add AS/Delete AS at a single go.

Solution:

To shrink the size of the database Before implementing below solutions please refer to the following Microsoft KBASE article: http://support.microsoft.com/kb/315512

1. Database level: You can perform the following two options by enabling shrinking option as provided by MS SQL Server by executing the below commands.
   
   
   • Option # 1: ALTER DATABASE [CASRMCentralDB] SET AUTO_SHRINK ON By enabling this option the database engine periodically examines the space usage in each database and reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.
     
     
   • Option # 2: DBCC SHRINKDATABASE ([CASRMCentralDB], 10, TRUNCATEONLY) This option enables manual shrink of database or files. Pros:
     
     
     • The SQL Server takes care of automatic shrinking.
       
       
     Cons:
     
     
     • Disk fragmentations issues may creep up due to which, in long run there might be chances of corrupting the operating system.
       
       
     • Due to the fragmentation SQL Server performance might slow down.
       
       
2. File Level: You can manually execute the below SQL commands in frequent time intervals: BACKUP LOG [CASRMCentralDB] WITH TRUNCATE_ONLY Cons:
   
   
   • The administrator has to track and check the log size, and perform this activity from time to time.
     
     
3. Truncating: When you perform regular SQL Server back-ups, enable truncate log option so that the backup software will truncate the logs after it is backed up. Pros:
   
   
   • This is a very common practice in every application

Environment

Release: BRMAPS20100-11.7-Storage Resource Manager-Application Server
Component: