Yet another reason to do backups even in a lab… (#SCOM, #SCVMM, #SCDPM)

Ok, I’ll admit it – I don’t like backing up my systems. There, I’ve said it. It has bit me multiple times and I’m sure that it will again in the future. I’ve been working in my private cloud lab and had an interesting gotcha which brought up yet another good reason to do backups even in just a lab environment.

Symptom #1: My OpsMgr and other System Center servers were running very sluggishly. According to SCVMM the memory on my SQL server was in a warning state and the processor was running much higher than it had historically.

Symptom #2a: OpsMgr is telling me that my log files are running out of space for my DWDataMart and my CMDWDataMart.

Watch the backups_03

Symptom #2b: Checking on the databases in question, the log files are HUGE.

Watch the backups

Symptom #3: Checking on the databases in question, the Space Available is minimal (3630.38 MB of 147382.19 MB below as an example).

Watch the backups_04

Symptom #4: The drive with the SQL databases was almost critical on space.

Based upon these, I knew that I needed to get some free space on the drive where the SQL server was storing the databases and to get a backup done to clear up the logs on the SQL server.

Resolution Part 1:

In the lab environment, the SQL server is virtualized and the drive which the SQL databases is on is a dynamic drive (I know, not good but very practical in a lab environment with limited space). I shut down the server and used Hyper-V to increase the size of the drive by another 100 GB. Next I restarted the server and extended the drive to use the newly available space. This at least bought me some breathing room but really want I wanted to accomplish was an effective backup of the SQL databases to clear out the logs.

Resolution Part 2:

Using System Center Data Protection Manager 2012 RC I added a backup for the SQL databases in my environment on this server. On my DPM environment I also use dynamically expanding drives (see my last thought on dynamic drives in resolution part 1). One of the benefits of this approach is I can expand the drives quickly and then use the new space as part of my storage for backups available in my DPM environment.

image

image

After these steps were completed, OpsMgr closed out the corresponding alerts.

Resolution Part 3:

Unfortunately for some reason the logs were not getting truncated for these databases as part of the DPM 2012 RC backups I was doing. To free up the disk space I manually performed the truncate of logs using these SQL queries (changing the query to the appropriate database name for each): (from http://www.jaxidian.org/update/2009/04/22/48/)

USE [OMDWDataMart]

GO

ALTER DATABASE [OMDWDataMart] SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE(SM_LOG)

GO

ALTER DATABASE [OMDWDataMart] SET RECOVERY FULL

GO

clip_image001

The corresponding alerts were closed automatically as shown below.

Watch the backups_05

Summary: When building a private cloud environment (even a lab) add System Center Data Protection Manager to backup the environment both from a Hyper-V level and from a SQL database level. Watch the log file sizes to make sure that they are being truncated.

Leave a Reply