VBScripts BLOG

We all have our weak moments and do stupid things. Just like I did with our virtual center database transaction log. I did not set a maximum growth limit to which the transaction log could grow and so it ran out of disc space. And our Virtual Center server stop running because of it. So I needed to trash the transaction log and fast. I made this little SQL script that can be run with SQL Query Analyzer.

The very simpel solution to this problem:

USE [YouVirtualCenterDB]

BACKUP LOG [YouVirtualCenterDB] WITH TRUNCATE_ONLY

DBCC SHRINKFILE([YouVirtualCenterDB]_log,2)

Remeber this will trash your transaction log. It will not be backuped to disc it will be written to dev NULL. This script is only to be used if your sure you don’t need the transaction log or you need the database running again in a very short time and there is no other way.

Hope it helps some of you.

The last few weeks it wasn’t fun having a VMWare ESX environment. SQL transaction logs that were out of control, ESX server that lost there iSCSI connection, etc.

One of the thing I was investigating was the size of our virtual center database. What I found was that data (events, tasks, etc) is being stored in the database but never deleted.

When browsing the VMWare site I found this SQL script in the knowledgebase of VMWare. The script inspects the database for data older than a certain date. The script works for virtual center 2.0 and 2.5.

———————————————————–
– Use one of these methods to specify the data cutoff date
– SET @CUTOFF_DATE = GETUTCDATE()-180
SET @CUTOFF_DATE = ‘2008/01/01′ <– cut off date
———————————————————–

Default the script count the rows that can be deleted. If you set deletedata to 1 than the script will purge the data from the database.I removed 2.5 million rows from our Virtual Center database and we noticed an increase of performance.

Use it carefully!!! Make your backups.