Views:

1. Monitor Size of MSCRM DataFile and LogFile and the Infuse/Scribe Datafiles and logfiles if applicable.Check for any sudden large file growth. Check initial sizes of database is set to a reasonable size and autogrow is in MB and not in Percentage and set maximum size of file to a percentage of total diskspace

 

2. Monitor Space Available on the backup disk

 

3. Check for fragmentation and see if any particular table caused the size increase


Sp_spaceused tablename

 

Sp_helpdb master

 

To find fragmentation:

 

SHOWCONTIG

 

A density of less than 75% means that DB is fragmented

 

4. Back up transaction log and data files

 

5. DB MAINTENANCE PROCEDURE

 

·Set Database in Single user Mode

 

Alter database <dbname> set single_user with rollback immediate

 

To Set Database back to Multi User Mode,


Alter database db-name set MULTI_USER

 

 

·         Shrink Database and Files

 

DBCC Shrinkfile(logicalfilename, percentagetarget, OPTION)

 

[OPTION – NoTRUNCATE TRUNCATEONLY, EMPTYFILE]

 

To find the logical filename:

 

Select * from sysfiles.

 

DBCC shrinkdatabase

 

·         If this fails Right click Database>AllTasks>Shrink Files

 

·         DBCC INDEXDEFRAG databasename [tablename],[indexname]

This can be run when users are online and table will still be available

 

·         DBCC DBREINDEX database.owner.tablename
 

This cannot be run when users are online and table is not available
 

 

PERFORMANCE MONITORING

 

To list open transactions:

 

·         DBCC OPENTRAN


To view table level statistics

 

·         DBCC SHOW_STATISTICS tablename

Find a remedy.