Cisco Unified MeetingPlace, Release 7.0 -- How to Manage the SQL Database Size

From DocWiki

Jump to: navigation, search

Main page: Cisco Unified MeetingPlace, Release 7.0

Up one level: Configuration



Contents

Modifying the SQL Database Properties to Manage Database Size

The MPWEB database that Cisco Unified MeetingPlace Web Conferencing creates is comprised of two files: MPWEB.mdf and MPWEB.ldf. The MDF file contains the actual data, while the LDF contains changes (both the content and timing) made to that data.


On a SQL server that has been actively and properly managed through regular database backup, this LDF file (also called Transaction Log) remains a reasonable size. However, if the SQL database has not been backed up in a while, this transaction log may become very large.


To help prevent the file from growing too large, configure the following three properties for the MPWEB database:

  • Recovery = Simple
  • Torn Page Detection = On
  • Auto Shrink = On


Caution! This procedure applies only to the MPWEB database. Do not apply this procedure to any MPWEB slave database (these databases have names that begin with "MPWEB_").


Procedure
  1. Open a DOS command window.
  2. Log in to SQL Server by entering C:\osql -U <userid> -P <password>, replacing <userid> and <password> with the applicable value.
  3. See the current properties of the database.
    1. Enter sp_helpdb MPWEB.
    2. Enter go.
  4. Modify properties.
    1. Enter alter database mpweb set auto_shrink on, recovery simple, torn_page_detection on.
    2. Enter go.
  5. If you are low on disk space because the database file is already large, force an immediate database shrink and remove empty space in the database files by entering dbcc shrinkdatabase ('mpweb', <percent>). where <percent> is the amount of free space that you want to allow.



Examples: Modifying the SQL Database Properties to Manage Database Size

In the following examples, the output is displayed for each command that is used in How to Manage the SQL Database Size.


Sample Output for Viewing Current Database Properties

In this example, Recovery Mode is set to FULL and Torn Page Detection and Auto Shrink are not configured on this database.

1> sp_helpdb MPWEB

2> go

name db_size owner dbid created status

compatibility_level

MPWEB 1254.00 MB sa 5 Oct 16 2003

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL,

Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,

IsAutoCreateStatistics, IsAutoUpdateStatistics



Sample Output for Modifying Database Properties

1> alter database mpweb set auto_shrink on, recovery simple, torn_page_detection on

2> go

1> sp_helpdb MPWEB

2> go

name db_size owner dbid created status

compatibility_level

MPWEB 1254.00 MB sa 5 Oct 16 2003

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE,

Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoShrink,

IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics



Sample Output for Decreasing File Size

In this example, the size of the files are decreased in the MPWEB database to allow 10 percent free space in the files of MPWEB.

1> dbcc shrinkdatabase ('mpweb', 10)

2> go

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

5 2 2912 1280 2912 1280

(1 row affected)

DBCC execution completed. If DBCC printed error messages, contact your system

administrator.

Rating: 0.0/5 (0 votes cast)

Personal tools