databasemonitordlg.cpp

监控SQL Server2000数据库

		iReturn = GetFirstLongField("SELECT TOP 1 [size] * (8192/1024) / 1024 FROM SYSFILES WHERE [name] = 'washing'", &iMainFileSize);
		CString csDisk;
		char szDisk;
		for(szDisk = 'D'; szDisk < 'Z'; szDisk++)
		{
			csDisk = szDisk;
			csDisk = csDisk + ":";
			if(GetFreeDiskSpace(csDisk) > iLogFileSize + iMainFileSize + 100 )
			{
				if(::GetDriveType(csDisk)== DRIVE_FIXED || ::GetDriveType(csDisk)==DRIVE_REMOVABLE)
				{
					break;
				}
			}
			csDisk = "";
		}
		if(csDisk == "")
		{
			AfxMessageBox("无法找到一个可用空间大于数据库文件大小总和的分区用于备份数据库。\n请用U盘先剪切一部分文件到别的电脑。或者外挂硬盘。");
			return false;
		}
		ExecuteSQLWithoutRecordset("exec SP_DBOption 'washing','trunc. log on chkpt.','false'");
		ExecuteSQLWithoutRecordset("BACKUP LOG washing WITH TRUNCATE_ONLY");
		pCmd.CreateInstance( __uuidof(Command) );
		pCmd->ActiveConnection = m_pConnection;
		pCmd->CommandText = "sp_backupdb";
		pCmd->CommandType = adCmdStoredProc;
		pCmd->CommandTimeout = 300;
		
		_variant_t vtPrm;
		pPrm = pCmd->CreateParameter("return",	adInteger,	adParamReturnValue,sizeof(int));
		pCmd->Parameters->Append(pPrm);				
		pCmd->Parameters->Refresh();

		pCmd->Parameters->Item[ _variant_t( _bstr_t("@filepath") ) ]->Value = csDisk.GetBuffer(0);		
		pCmd->Parameters->Item[ _variant_t( _bstr_t("@dbname") ) ]->Value = "washing";		
		pCmd->Execute(NULL, NULL, adCmdStoredProc);
		
		//先判断是否成功		
//	pCmd->Parameters->Refresh();
		long retvalue = pCmd->Parameters->Item[(short)0]->Value;
		if(pPrm)
			pPrm.Release();
		if(pCmd)
			pCmd.Release();
		if (retvalue != 0)
		{
			AfxMessageBox("备份数据库失败。");
			ExecuteSQLWithoutRecordset("exec SP_DBOption 'washing','trunc. log on chkpt.','true'");
			return false;
		}
		csDisk = "washing数据库已经备份到" + csDisk;
		csDisk = csDisk + "。名字为年月日时分秒.thunder。";
		AfxMessageBox(csDisk);
	}
	catch(...)
	{
		AfxMessageBox("数据库备份失败。");
		if(pPrm)
			pPrm.Release();
		if(pCmd)
			pCmd.Release();
		ExecuteSQLWithoutRecordset("exec SP_DBOption 'washing','trunc. log on chkpt.','true'");
		return false;
	}
	ExecuteSQLWithoutRecordset("exec SP_DBOption 'washing','trunc. log on chkpt.','true'");
	return true;
}

//按照SQL获取返回单一字段整型记录集的第一个字段的值
//用于得到例如COUNT,MIN,MAX,AVG的返回值
//##ModelId=42967B7C031C
int CDatabaseMonitorDlg::GetFirstLongField(const char *i_pszSQL, long *o_plValue)
{
	*o_plValue = 0;
	//判断连接的合法性
	if(m_pConnection==NULL)
		return ERROR_NOT_CONNECT_DATABASE;
	//判断传入参数的合法性
	if(strlen(i_pszSQL)<1)
		return ERROR_SQL_STRING_IS_EMPTY;
	
	_RecordsetPtr pRecordset;
	unsigned int iReturnCode = HAVE_RECORDS;
	try
	{
		pRecordset.CreateInstance(__uuidof(Recordset));
		pRecordset->Open(i_pszSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
		if(!pRecordset->adoEOF)
		{
			_variant_t vt;
			vt = pRecordset->GetCollect(_variant_t((long)0));
			if(vt.vt == VT_BSTR)
				vt.ChangeType(VT_I4);
			*o_plValue = vt.lVal;
			iReturnCode = HAVE_RECORDS;
		}
		else
			iReturnCode = NO_RECORDS;

		pRecordset.Release();
	}
	catch ( _com_error &e )
	{
		iReturnCode = ERROR_COM;
	}
    catch (...)
    {
		iReturnCode = ERROR_UNKONWN;
    }

	return iReturnCode;
}


//##ModelId=42967B7C030D
int CDatabaseMonitorDlg::ExecuteSQLWithoutRecordset(const char *i_pszSQL)
{
	// TODO: Add your implementation code here
	//判断连接的合法性
	if(m_pConnection==NULL)
		return ERROR_NOT_CONNECT_DATABASE;
	//判断传入参数的合法性
	if(strlen(i_pszSQL)<1)
		return ERROR_SQL_STRING_IS_EMPTY;

	try
	{
		m_pConnection->Execute(i_pszSQL,NULL,adCmdText);
	}
	catch (_com_error &e)
	{
		return ERROR_COM;
	}
    catch (...)
	{
		return ERROR_UNKONWN;
	}

	return true;
}

//##ModelId=42967B7C02F0
int CDatabaseMonitorDlg::ExecuteSQLWithRecordset(const char *i_pszSQL, _Recordset **o_pRecordset)
{
	//判断连接的合法性
	if(m_pConnection==NULL)
		return ERROR_NOT_CONNECT_DATABASE;
	//判断传入参数的合法性
	if(strlen(i_pszSQL)<1)
		return ERROR_SQL_STRING_IS_EMPTY;

	_RecordsetPtr pRecordset;
	try
	{
		pRecordset.CreateInstance(__uuidof(Recordset));
		pRecordset->Open(i_pszSQL,m_pConnection.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdText);
		*o_pRecordset = pRecordset;
		pRecordset->AddRef();
	}
	catch ( _com_error &e )
	{
		return ERROR_COM;
	}
    catch (...)
	{
		return ERROR_UNKONWN;
	}

	return EVERYTHING_OK;
}

long CDatabaseMonitorDlg::GetLogFileSize()
{
	long iReturn, iLogFileSize = 0;
	iReturn = GetFirstLongField("SELECT TOP 1 [size] * (8192/1024) / 1024 FROM SYSFILES WHERE [name] = 'washing_log'", &iLogFileSize);
	if(iReturn == HAVE_RECORDS)
	{
		if(iLogFileSize > 1)
			m_csLogFileSize.Format("%d",iLogFileSize);
		else
			m_csLogFileSize.Format("小于1");
		UpdateData(FALSE);
	}
	if(iLogFileSize >= 1000)
	{
		m_csHint = "您的数据库日志文件大小已经超过1G，是否压缩？\n必须在停止营业后压缩。";
		ShrinkDB();
	}
	return iLogFileSize;
}

void CDatabaseMonitorDlg::GetInfo()
{
	GetLogFileSize();
	GetFreeDiskSpace("C:");
}

long CDatabaseMonitorDlg::GetFreeDiskSpace(CString i_csDriver)
{
	ULONGLONG ullFreeBytesAvailable = 0;
	ULARGE_INTEGER dwFreeBytesAvailable, dwTotalNumberOfBytes, dwTotalNumberOfFreeBytes ;
	BOOL bGetDiskFreeSpace = GetDiskFreeSpaceEx(i_csDriver, &dwFreeBytesAvailable, &dwTotalNumberOfBytes, &dwTotalNumberOfFreeBytes) ;
	if(bGetDiskFreeSpace == TRUE)
	{
		ullFreeBytesAvailable = dwFreeBytesAvailable.QuadPart / 1024 /1024;
		i_csDriver.MakeLower();
		if(i_csDriver == "c:")
			m_csCAvailableSize.Format("%ld",ullFreeBytesAvailable);
		UpdateData(FALSE);
	}
	return ullFreeBytesAvailable;
}

void CDatabaseMonitorDlg::SetDBOption()
{
	ExecuteSQLWithoutRecordset("exec SP_DBOption 'washing','autoshrink','true'");
	ExecuteSQLWithoutRecordset("exec SP_DBOption 'washing','trunc. log on chkpt.','true'");
	ExecuteSQLWithoutRecordset("ALTER DATABASE [washing] MODIFY FILE (NAME = N'washing_log', MAXSIZE = UNLIMITED)");
	ExecuteSQLWithoutRecordset("ALTER DATABASE [washing] SET RECOVERY FULL");	
	long iReturn, iGrowthSize = 0;
	iReturn = GetFirstLongField("SELECT TOP 1 growth / 1024 FROM SYSFILES WHERE [name] = 'washing_log'", &iGrowthSize);
	if(iReturn == HAVE_RECORDS)
	{
		if(iGrowthSize <= 100)
			ExecuteSQLWithoutRecordset("ALTER DATABASE washing MODIFY FILE (name = 'washing_log', FILEGROWTH = 300MB)");
	}
}

void CDatabaseMonitorDlg::ShrinkDB()
{
	KillTimer(UINT(this));
	if(AfxMessageBox(m_csHint, MB_YESNO) ==IDNO)
	{
		SetTimer(UINT(this),m_iMonitorInterval, NULL);
		return;
	}
	if(GetFreeDiskSpace("C:")<=500)
	{
		AfxMessageBox("C盘可用空间已经小于500MB，\n请用WORD打开《设置虚拟内存》，按照其中的步骤腾出空间再进行压缩。");
		SetTimer(UINT(this),m_iMonitorInterval, NULL);
		return;
	}

	if(BackupDatabase()==false)
	{
		AfxMessageBox("无法继续压缩数据库。");
		SetTimer(UINT(this),m_iMonitorInterval, NULL);
		return;
	} 

	AfxMessageBox("压缩数据库可能要花20分钟的时间，请耐心等候，勿关闭程序。");
	_CommandPtr pCmd		= NULL;
	_ParameterPtr pPrm		= NULL;	
	FieldPtr pTmp;
	try
	{
		pCmd.CreateInstance( __uuidof(Command) );
		pCmd->ActiveConnection = m_pConnection;
		pCmd->CommandText = "SP_ShrinkDB";
		pCmd->CommandType = adCmdStoredProc;
		pCmd->CommandTimeout = 3600;
		
		_variant_t vtPrm;
		pPrm = pCmd->CreateParameter("return",	adInteger,	adParamReturnValue,sizeof(int));
		pCmd->Parameters->Append(pPrm);				

		pCmd->Execute(NULL, NULL, adCmdStoredProc);
		
		//先判断是否成功		
		long retvalue = pCmd->Parameters->Item[(short)0]->Value;
		if(pPrm)
			pPrm.Release();
		if(pCmd)
			pCmd.Release();
		if (retvalue != 0)
		{
			AfxMessageBox("washing数据库压缩失败。");
			SetTimer(UINT(this),m_iMonitorInterval, NULL);
			return ;
		}
		AfxMessageBox("washing数据库压缩成功。");
		GetInfo();
	}
	catch (_com_error &e) 
	{
		AfxMessageBox(e.Description());
	}
	catch(...)
	{
		AfxMessageBox("washing数据库压缩失败。");
		if(pPrm)
			pPrm.Release();
		if(pCmd)
			pCmd.Release();
		SetTimer(UINT(this),m_iMonitorInterval, NULL);
		return ;
	}
	SetTimer(UINT(this),m_iMonitorInterval, NULL);
	return ;
}

bool CDatabaseMonitorDlg::RestoreDatabase()
{
//restore database karaok from disk = 'F:\bugs\数据库大\karaok0402' with replace, move 'karaok' to 'e:\karaok.mdf', move 'karaok_log' to 'e:\karaok_log.ldf'
	return true;
}