Cisco Unified MeetingPlace, Release 6.x -- How to Modify the SQL Database Properties to Manage MPWEB Database Size

From DocWiki

Revision as of 23:28, 28 January 2008 by MeetingPlace Moderator (Talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Cisco Unified MeetingPlace, Release 6.x > Web Conferencing > Configuring > Web Conferencing and SQL Server




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 important properties for the MPWEB database:

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

Contents

To Modify the SQL Database Properties to Manage Database Size

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_").


  1. Open a DOS command window.
  2. To log in to SQL Server, enter C:\osql -U userid -P password , replacing userid and password with the applicable value.
  3. To see the current properties of the database, enter sp_helpdb MPWEB , then enter go .
  4. To modify properties, enter alter database mpweb set auto_shrink on, recovery simple, torn_page_detection on , then 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 the To Modify the SQL Database Properties to Manage 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