SQL Server 2012内存泄漏 [英] sql server 2012 memory leak

问题描述

我正在使用带有ADODB连接的VB6连接到sql server 2012数据库并在数据库上执行查询，我面临着内存泄漏的问题，即使我终止程序，查询结束后内存使用量也不会减少.内存使用量不会减少.

I'm using VB6 with ADODB connection to connect to sql server 2012 database and execute queries on the database, I'm facing memory leak where I see that memory usage does not decrease after query finished even after I terminate my program the memory usage dose not decrease.

下面是我的代码:

Public adoCon As New ADODB.Connection

公共服务器名作为字符串，数据库名作为字符串，表名作为字符串 公用sqlCmd作为字符串

Public serverName As String, databaseName As String, tableName As String Public sqlCmd As String

公共Sub adodb_Connect()

Public Sub adodb_Connect()

If Tenzfrm.adoCon.State = adStateOpen Then Tenzfrm.adoCon.Close
serverName = Tenzfrm.server_Name.Text
databaseName = Tenzfrm.database_Name.Text
Tenzfrm.adoCon.Provider = "sqloledb"
Tenzfrm.adoCon.Properties("Data Source").Value = serverName
Tenzfrm.adoCon.Properties("Initial Catalog").Value = databaseName
Tenzfrm.adoCon.Properties("Integrated Security").Value = "SSPI"
Tenzfrm.adoCon.CommandTimeout = 0
Tenzfrm.adoCon.Open

结束子

公共子adodb_Close()

Public Sub adodb_Close()

If Tenzfrm.adoCon.State = adStateOpen Then Tenzfrm.adoCon.Close
Set Tenzfrm.adoCon = Nothing

结束子

Private Sub appHourlySTS_Click()

Call adodb_Connect
proccess.Caption = "Preparing tables !"
proBar2.Width = 2 * proBar1.Width / 100
sqlCmd = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tenz_Cell_STS_Hourly_Temp') DROP TABLE Tenz_Cell_STS_Hourly_Temp;"
adoCon.Execute (sqlCmd)
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'CREATE TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp](';"
sqlCmd = sqlCmd & "SELECT @sqlCmd+='['+Headers+'] FLOAT,' FROM [dbo].[Tenz_colReg];"
sqlCmd = sqlCmd & "SET @sqlCmd=LEFT(@sqlCmd,LEN(@sqlCmd)-1)+')';"
sqlCmd = sqlCmd & "EXEC(@sqlCmd);"
adoCon.Execute (sqlCmd)
sqlCmd = "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [Date] VARCHAR(23);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [Time] VARCHAR(10);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [NE Name] VARCHAR(15);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [Cell Name] VARCHAR(64);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [CI] VARCHAR(64);"
sqlCmd = sqlCmd & "ALTER TABLE [dbo].[Tenz_Cell_STS_Hourly_Temp] ALTER COLUMN [LAC] VARCHAR(10);"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' table from CS1 !"
proBar2.Width = 5 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'INSERT INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] '; "
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=[dbo].[Tenz_CS1].['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND ([Name]='Date' OR [Name]='Time' OR [Name]='Cell Name' OR [Name]='NE Name' OR [Name]='CI' OR [Name]='LAC');"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_CS1].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_CS1].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1);"
sqlCmd = sqlCmd & "DECLARE @dc NVARCHAR(MAX) = ''; "
sqlCmd = sqlCmd & "SELECT @dc+='['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND ([Name]='Date' OR [Name]='Time' OR [Name]='Cell Name' OR [Name]='NE Name' OR [Name]='CI' OR [Name]='LAC');"
sqlCmd = sqlCmd & "SELECT @dc+='['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS1') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @dc=LEFT(@dc,LEN(@dc)-1);"
sqlCmd = sqlCmd & "SET @sqlCmd+='('+@dc+') SELECT '+@sc+' FROM [dbo].[Tenz_CS1]';"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' from CS2 !"
proBar2.Width = 35 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'MERGE INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] USING [dbo].[Tenz_CS2] ON [dbo].[Tenz_Cell_STS_Hourly_Temp].Date = [dbo].[Tenz_CS2].Date AND [dbo].[Tenz_Cell_STS_Hourly_Temp].Time = [dbo].[Tenz_CS2].Time AND [dbo].[Tenz_Cell_STS_Hourly_Temp].[Cell Name] = [dbo].[Tenz_CS2].[Cell Name] WHEN MATCHED THEN UPDATE SET ';"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_CS2].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_CS2].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_CS2') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1)+';';"
sqlCmd = sqlCmd & "SET @sqlCmd+=@sc;"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' from PS1 !"
proBar2.Width = 50 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'MERGE INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] USING [dbo].[Tenz_PS1] ON [dbo].[Tenz_Cell_STS_Hourly_Temp].Date = [dbo].[Tenz_PS1].Date AND [dbo].[Tenz_Cell_STS_Hourly_Temp].Time = [dbo].[Tenz_PS1].Time AND [dbo].[Tenz_Cell_STS_Hourly_Temp].[Cell Name] = [dbo].[Tenz_PS1].[Cell Name] WHEN MATCHED THEN UPDATE SET ';"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_PS1].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_PS1].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_PS1') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1)+';';"
sqlCmd = sqlCmd & "SET @sqlCmd+=@sc;"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Appending statistics to 'Tenz_STS_Cell_Hourly' from PS2 !"
proBar2.Width = 65 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'MERGE INTO [dbo].[Tenz_Cell_STS_Hourly_Temp] USING [dbo].[Tenz_PS2] ON [dbo].[Tenz_Cell_STS_Hourly_Temp].Date = [dbo].[Tenz_PS2].Date AND [dbo].[Tenz_Cell_STS_Hourly_Temp].Time = [dbo].[Tenz_PS2].Time AND [dbo].[Tenz_Cell_STS_Hourly_Temp].[Cell Name] = [dbo].[Tenz_PS2].[Cell Name] WHEN MATCHED THEN UPDATE SET ';"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = '';"
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+']=CASE WHEN TRY_CONVERT(FLOAT,[dbo].[Tenz_PS2].['+Name+']) IS NULL THEN ''0'' ELSE [dbo].[Tenz_PS2].['+Name+'] END,' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_PS2') AND [Name]<>'Date' AND [Name]<>'Time' AND [Name]<>'Cell Name' AND [Name]<>'NE Name' AND [Name]<>'CI' AND [Name]<>'LAC';"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1)+';';"
sqlCmd = sqlCmd & "SET @sqlCmd+=@sc;"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proccess.Caption = "Finishing !"
proBar2.Width = 80 * proBar1.Width / 100
sqlCmd = "DECLARE @sqlCmd NVARCHAR(MAX) = 'INSERT INTO [dbo].[Tenz_Cell_STS_Hourly] '"
sqlCmd = sqlCmd & "DECLARE @sc NVARCHAR(MAX) = ''; "
sqlCmd = sqlCmd & "SELECT @sc+='['+Name+'],' FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'Tenz_Cell_STS_Hourly');"
sqlCmd = sqlCmd & "SET @sc=LEFT(@sc,LEN(@sc)-1);"
sqlCmd = sqlCmd & "SET @sqlCmd+='('+@sc+') SELECT '+@sc+'FROM [dbo].[Tenz_Cell_STS_Hourly_Temp]';"
sqlCmd = sqlCmd & "EXEC sp_executesql @sqlCmd;"
adoCon.Execute (sqlCmd)
proBar2.Width = 100 * proBar1.Width / 100
MsgBox ("Statistics appended to 'Tenz_STS_Cell_Hourly' successfully !")
proBar2.Width = 0
proccess.Caption = "Idle ! "
sqlCmd = "IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tenz_Cell_STS_Hourly_Temp') DROP TABLE Tenz_Cell_STS_Hourly_Temp;"
adoCon.Execute (sqlCmd)
Call adodb_Close

结束子

推荐答案

我只是遇到了同样的问题. SQL Server将始终使用您允许的尽可能多的内存.您必须设置一个限制.

I just had the same problem. SQL server will always use as much memory as you allow it to. You have to set a limit.

SSMS无法释放系统内存 https://dba.stackexchange.com/questions/47431/为什么sql-server消耗更多的服务器内存

右键单击SSMS中的服务器，然后选择属性.在内存"选项卡中，设置最大服务器内存"选项以限制SQL的内存使用.

Right click on the server in SSMS and select properties. In the memory tab set the Maximum server memory option to limit SQLs memory usage.

这篇关于SQL Server 2012内存泄漏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！