| Author |
Topic |
|
faisals
Starting Member
4 Posts |
 Posted - 2007-12-04 : 12:39:13
|
| I have a stored proc that processes large amounts of data. I have used temp tables in this stored proc. After I drop these temp tables, it doesn't release the disc space and my hard drive runs out of space before the process ends. When I stop the sql service and restarts it, it releases the disc space. How can I force the release of disc space inside of my stored procedure? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-04 : 12:42:02
|
| Post your code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
faisals
Starting Member
4 Posts |
Posted - 2007-12-04 : 12:53:18
|
| This is a dynamic stored procedure. Let me create a sample script this stored proc creates and I will post that in 5 minutes.Thanks for the help. |
|
|
|
faisals
Starting Member
4 Posts |
Posted - 2007-12-04 : 13:00:23
|
| The following is a part of script that is produced by the dynamic stored procedure. The stored proc goes in loops and could create as many as 20 or 30 of the following scripts and it involves anywhere from 0.5 gb to 25 gb of data.CREATE TABLE [dbo].[ARC_DATA_1] ( [ARCO_ID] [bigint] IDENTITY (1, 1) NOT NULL , [ARCO_Platform] [nvarchar] (50) COLLATE database_default NULL , [ARCO_Host] [nvarchar] (50) COLLATE database_default NULL , [ARCO_Level] [nvarchar] (50) COLLATE database_default NULL , [ARCO_LevelKey] [nvarchar] (max) COLLATE database_default NULL , [ARCO_LevelIdentifier] [nvarchar] (250) COLLATE database_default NULL , [ARCO_LevelField] [nvarchar] (50) COLLATE database_default NULL , [ARCO_Run_1] [nvarchar] (max) COLLATE database_default NULL, [ARCO_Run_2] [nvarchar] (max) COLLATE database_default NULL) ON [PRIMARY]ALTER TABLE [dbo].[ARC_DATA_1] WITH NOCHECK ADD CONSTRAINT [PK_ARC_DATA_1] PRIMARY KEY CLUSTERED ( [ARCO_ID] ) ON [PRIMARY]CREATE INDEX [IX_ARC_DATA_1] ON [dbo].[ARC_DATA_1]([ARCO_Platform], [ARCO_Host], [ARCO_Level], [ARCO_LevelIdentifier], [ARCO_LevelField]) ON [PRIMARY]CREATE TABLE #TempVW_ALL_AHOSTS (Host_Key BIGINT, Key_Field_ID bigint, TRUN_ID BIGINT, Host_Name nvarchar(50) COLLATE database_default, Host_Platform nvarchar(50) COLLATE database_default, Level_Name nvarchar(50) COLLATE database_default, Key_Field nvarchar(max) COLLATE database_default, Identifier_Field nvarchar(255) COLLATE database_default, Field_Name nvarchar(50) COLLATE database_default, Field_Value nvarchar(max) COLLATE database_default, RUN_2 nvarchar(max) COLLATE database_default, RUN_3 nvarchar(max) COLLATE database_default)CREATE TABLE #Temp1VW_ALL_AHOSTS (Host_Key BIGINT, Key_Field_ID bigint, TRUN_ID BIGINT, Host_Name nvarchar(50) COLLATE database_default, Host_Platform nvarchar(50) COLLATE database_default, Level_Name nvarchar(50) COLLATE database_default, Key_Field nvarchar(max) COLLATE database_default, Identifier_Field nvarchar(255) COLLATE database_default, Field_Name nvarchar(50) COLLATE database_default, Field_Value nvarchar(max) COLLATE database_default, RUN_2 nvarchar(max) COLLATE database_default, RUN_3 nvarchar(max) COLLATE database_default)CREATE TABLE #VW_ALL_AHOSTS (Host_Key BIGINT, Key_Field_ID BIGINT, Host_Name NVARCHAR(50) COLLATE database_default,Domain_Name NVARCHAR(50) COLLATE database_default,Host_Platform NVARCHAR(50) COLLATE database_default,TRUN_ID BIGINT,Host_ServerFlag SMALLINT,Host_WMIUsed SMALLINT,OS_Name NVARCHAR(255) COLLATE database_default,OS_Version NVARCHAR(255) COLLATE database_default,OS_KernelVersion NVARCHAR(255) COLLATE database_default,OS_InstallPath NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS,Host_UserAssigned SMALLINT,Host_Model NVARCHAR(255) COLLATE database_default,Host_Manufacturer NVARCHAR(255) COLLATE database_default,Host_InstallDate DATETIME,Host_PhysicalMemoryMB INT,Processor_Count INT,PhysicalProcessorCount INT,Processor_SpeedMHz INT,Processor_L1CacheKB INT,Processor_L2CacheKB INT,Processor_ID NVARCHAR(255) COLLATE database_default,Processor_Type NVARCHAR(255) COLLATE database_default,HyperThreading_Capable SMALLINT,HyperThreading_Enabled SMALLINT,BIOS_Date DATETIME,BIOS_Version NVARCHAR(255) COLLATE database_default,BIOS_Manufacturer NVARCHAR(255) COLLATE database_default,BIOS_Name NVARCHAR(255) COLLATE database_default,Host_ServicePackLevel NVARCHAR(255) COLLATE database_default,Host_UniqueIdentifier NVARCHAR(255) COLLATE database_default,Host_UserID NVARCHAR(255) COLLATE database_default,Host_SerialNumber NVARCHAR(255) COLLATE database_default,Host_EncryptionLevel NVARCHAR(255) COLLATE database_default,CurrentTimeZone NVARCHAR(255) COLLATE database_default,DayLightSavings NVARCHAR(255) COLLATE database_default)INSERT INTO #VW_ALL_AHOSTS SELECT DISTINCT Host_Key, NULL, Host_Name,Domain_Name,Host_Platform,TRUN_ID,Host_ServerFlag,Host_WMIUsed,OS_Name,OS_Version,OS_KernelVersion,OS_InstallPath,Host_UserAssigned,Host_Model,Host_Manufacturer,Host_InstallDate,Host_PhysicalMemoryMB,Processor_Count,PhysicalProcessorCount,Processor_SpeedMHz,Processor_L1CacheKB,Processor_L2CacheKB,Processor_ID,Processor_Type,HyperThreading_Capable,HyperThreading_Enabled,BIOS_Date,BIOS_Version,BIOS_Manufacturer,BIOS_Name,Host_ServicePackLevel,Host_UniqueIdentifier,Host_UserID,Host_SerialNumber,Host_EncryptionLevel,CurrentTimeZone,DayLightSavings FROM VW_ALL_AHOSTS WHERE TCLN_ID = 1 AND TRUN_ID IN (2, 3)CREATE TABLE #TempKey_VW_ALL_AHOSTS (ID BIGINT IDENTITY(1,1), KeyField nvarchar(max) COLLATE database_default)INSERT INTO #TempKey_VW_ALL_AHOSTS SELECT DISTINCT Host_Name+Host_Platform FROM #VW_ALL_AHOSTSUPDATE tmp SET Key_Field_ID = vw.ID FROM #VW_ALL_AHOSTS tmp, #TempKey_VW_ALL_AHOSTS vw WHERE tmp.Host_Name+Host_Platform = vw.KeyFieldDROP TABLE #TempKey_VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_Name' AS Field_Name, Host_Name AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Domain_Name' AS Field_Name, Domain_Name AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_ServerFlag' AS Field_Name, Host_ServerFlag AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_WMIUsed' AS Field_Name, Host_WMIUsed AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'OS_Name' AS Field_Name, OS_Name AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'OS_Version' AS Field_Name, OS_Version AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'OS_KernelVersion' AS Field_Name, OS_KernelVersion AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'OS_InstallPath' AS Field_Name, OS_InstallPath AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_UserAssigned' AS Field_Name, Host_UserAssigned AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_Model' AS Field_Name, Host_Model AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_Manufacturer' AS Field_Name, Host_Manufacturer AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_InstallDate' AS Field_Name, Host_InstallDate AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_PhysicalMemoryMB' AS Field_Name, Host_PhysicalMemoryMB AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Processor_Count' AS Field_Name, Processor_Count AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'PhysicalProcessorCount' AS Field_Name, PhysicalProcessorCount AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Processor_SpeedMHz' AS Field_Name, Processor_SpeedMHz AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Processor_L1CacheKB' AS Field_Name, Processor_L1CacheKB AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Processor_L2CacheKB' AS Field_Name, Processor_L2CacheKB AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Processor_ID' AS Field_Name, Processor_ID AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Processor_Type' AS Field_Name, Processor_Type AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'HyperThreading_Capable' AS Field_Name, HyperThreading_Capable AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'HyperThreading_Enabled' AS Field_Name, HyperThreading_Enabled AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'BIOS_Date' AS Field_Name, BIOS_Date AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'BIOS_Version' AS Field_Name, BIOS_Version AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'BIOS_Manufacturer' AS Field_Name, BIOS_Manufacturer AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'BIOS_Name' AS Field_Name, BIOS_Name AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_ServicePackLevel' AS Field_Name, Host_ServicePackLevel AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_UniqueIdentifier' AS Field_Name, Host_UniqueIdentifier AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_UserID' AS Field_Name, Host_UserID AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_SerialNumber' AS Field_Name, Host_SerialNumber AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'Host_EncryptionLevel' AS Field_Name, Host_EncryptionLevel AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'CurrentTimeZone' AS Field_Name, CurrentTimeZone AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSINSERT INTO #TempVW_ALL_AHOSTS SELECT DISTINCT Host_Key, Key_Field_ID, TRUN_ID, Host_Name, Host_Platform, 'Host Configuration' AS Level_Name, Host_Name+Host_Platform AS Key_Field, Host_Name AS Identifier_Field, 'DayLightSavings' AS Field_Name, DayLightSavings AS Field_Value, NULL, NULL FROM #VW_ALL_AHOSTSDROP TABLE #VW_ALL_AHOSTSUPDATE #TempVW_ALL_AHOSTS SET RUN_2 = Field_Value WHERE TRUN_ID = 2UPDATE #TempVW_ALL_AHOSTS SET RUN_3 = Field_Value WHERE TRUN_ID = 3INSERT INTO #Temp1VW_ALL_AHOSTS SELECT * FROM #TempVW_ALL_AHOSTSUPDATE tmp SET RUN_2 = vw.RUN_2 FROM #TempVW_ALL_AHOSTS tmp, #Temp1VW_ALL_AHOSTS vw WHERE tmp.RUN_2 IS NULL AND vw.TRUN_ID = 2 AND tmp.Host_Key = vw.Host_Key AND tmp.Field_Name = vw.Field_Name AND tmp.Key_Field_ID = vw.Key_Field_IDUPDATE #TempVW_ALL_AHOSTS SET RUN_2 = '' WHERE RUN_2 IN ('None', 'Unknown')UPDATE tmp SET RUN_3 = vw.RUN_3 FROM #TempVW_ALL_AHOSTS tmp, #Temp1VW_ALL_AHOSTS vw WHERE tmp.RUN_3 IS NULL AND vw.TRUN_ID = 3 AND tmp.Host_Key = vw.Host_Key AND tmp.Field_Name = vw.Field_Name AND tmp.Key_Field_ID = vw.Key_Field_IDUPDATE #TempVW_ALL_AHOSTS SET RUN_3 = '' WHERE RUN_3 IN ('None', 'Unknown')INSERT INTO ARC_DATA_1 SELECT DISTINCT Host_Platform, Host_Name, Level_Name, Key_Field, Identifier_Field, Field_Name, ISNULL(RUN_2, '') AS ARCO_Run_1, ISNULL(RUN_3, '') AS ARCO_Run_2 FROM #TempVW_ALL_AHOSTSDROP TABLE #TempVW_ALL_AHOSTSDROP TABLE #Temp1VW_ALL_AHOSTS |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-04 : 13:03:54
|
| Why do you need to clear up the tempdb disk space after the stored procedure completes? The data isn't any longer inside tempdb, it's just that the file has grown to accomodate the data when it was in use. You shouldn't be restarting the SQL Server to fix this issue, but rather add more disk space so that tempdb can always have this amount.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
faisals
Starting Member
4 Posts |
Posted - 2007-12-04 : 13:12:11
|
| The problem starts before the stored proc ends. Let me explain this is in a little bit more detail. The dynamic stored proc has a loop and it creates let's say 10 of these scripts and sends it in a table back to the client. The client creates a long string from the table and sends it back to the db for processing. While processing the script, I notice that my hard drive space is running out. The process takes forever to complete and for one of my users who has very little disc space, the process never completes. His drive runs out of space. When we drop the temporary tables, it doesn't free up the physical space on the drive. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-04 : 13:16:08
|
| You need to add more disk space to the system then as your code is using a ton of tempdb space. There is no way for us to fix this. Shrinking the physical files down is only temporarily giving you back the disk space. Next time it runs though, it'll use it all up again. Add enough disk space to the system to support your code.Oh and a redesign of your stored procedure is probably the only way to use less tempdb.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Temp Table & Disc Space