How to shrink your SCVMM database (Transactional Log)

(Generally speaking, this is not a ‘SCVMM-problem’. It`s linked to SQL, and the way the SQL server act when it comes to I/O, Memory, Data, - and log files. But since this is a virtualization-blog, I want to angle this toward the SCVMM server since it`s also relevant here)

Ok, you have had your SCVMM server running for a while now, all roles and services installed on a single server. Remember that the SCVMM 2008 R2 server installs a SQL 2005 Express if you don’t specify another instance for this purpose.

The storage limit for a database in SQL 2005 Express is 4GB, so what do you do when the VirtualManagerDB_log starts to grow out of control ?

First, we have to locate our databases.

SCVMMàAdministrationàGeneralàDatabase Connection

Here we`ll find the information we need

We`ll see the Database Server Name, Instance, and the name of our database.

We should also locate the file location for the databases.

Startàcmd

SQLCMD –S .\Microsoft$VMM$  (Log on to your SQL instance with trusted connection)

1.       Select * from sysdatabases where name = ‘VirtualManagerDB’

2.       Go

Now we`ve located the database, and we can see that the log file is growing (the reason for this, is that the VirtualManagerDB has the ‘Recovery Model’ set to full, which allows the log file to grow since you are able to run log-backups, and can restore the database to any point in time).

We can actually shrink this log file in two ways.

1)      In a heavy way (Fullbackup->log backup->shrink log file (log backup and shrink log file x2)

2)      In a awesome way (sp_detach_db, rename log file, sp_attach_single_file_db)

I`ll go for number 2.

(Be careful if you are planning to do this in production, and off course have a solid backup and make a copy of the log file before editing).

This procedure will cause the SCVMM server to be offline, since we are detaching the database from our SQL server.

Log on to your SQL server instance with SQLCMD –S .\Microsoft$VMM$

1.       Sp_detach_db virtualmanagerdb

2.       Go

3.       Rename the VirtualManagerDB_log to OLDVirtualManagerDB_log

4.       Sp_attach_single_file_db virtualmanagerdb, ‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\virtualmanagerdb.mdf’

5.       Go

6.       In the data folder for your SQL server, you will now have a newly created log file for VirtualManagerDB at the size of 512KB.

7.       Alter database VirtualManagerDB set recovery simple with no_wait

8.       Go

The database is now in simple recovery, meaning that the log file will not grow out of control again but on a regularly basis create a checkpoint to the data file. (The way SQL uses its log files does not changes, whether you run full, bulk, or simple).