shrink tempdb

DaPosh

I have a sql2000 server that has a 24 /7 uptime. The tempdb size is increasing rapidly. it was 4 Gbs and i restarted the sql server to shrink the tempdb size which worked fine. but now after 1 week then tempdb size is almost 1.5gbs.

Is there anyway that i can shrink the tempDb without restarting it. Asking the users to stop using for a certain timeframe after every month or so seems like a hassle for the users.

oj

it's documented here.
http://support.microsoft.com/kb/307487

DaPosh

Yes I have already had a look here. As I mentioned my db requires a 24 by 7 uptime. The first option in this doc requires a restart. The other 2 options require that no uses are using the Tempdb.
How can I know that no users are using tempdb.
Since its heavly used, i dont think there will be a moment when no users are logged in. Its the same thing as having users log off the application at a specific time which I and the business wants to avoid.

Is there any other method of clearing the Tempdb

oj

To shrink the database, it has to be exclusive locked. Just think about it, if the used location is at the end of the file, how would it be possible to shrink the file. it is only possible to move stuff around within the file (in order to shrink) is if the location/block is not currently used.

You will have to dedicate down time if you want to shrink the database.

bass_player

Do a sp_who2 to check whether users (or applications) are connected to tempdb. If you know there are no users, shrink the tempdb. I agree that downtime is required for you to do this. Also, for performance concerns, allocate a space for your tempdb which will not require it to grow frequently

Rahman Hadi

hai guys...
i've got the identical case but different problem. it is still about shrink tempdb.

my office sql server have job schedule in every 3 hours for shrinking tempdb. I am sure it is not the good practice. Then I have got the locking tempdb problem, it is happened while the task to shrink tempdb running on the same time with one of my store procedure (use: #tempTable , transaction , @temptable, like '%' ) and shrink process is blocked by store procedure(sp) for 4 hours till I kill the spid .

Now, I have task from my boss, to give practical/technical proof that is showed the process that can make shrink is unacceptable while there is running query on sqlserver.

anyone can help me to make some query that will make shrink process blocked and can not stop the process till we kill the spid Cause, whenever i try to run sp & shrink tempdb at the sametime. its OK!!!

Please help me ... ... .... .... ....

this is my SP, may be you can help me if there is wrong code or potentially locking process:

/*****************
Start of my SP
*******************/

SET NOCOUNT ON
DECLARE @LocList VARCHAR(10) , @UpdatedUser varchar(20) , @UpdatedTime datetime
SET @LocList = dbo.fn_get_loclist_string()
SET @UpdatedUser = 'FMI\auto'
SET @UpdatedTime = getdate()

-- get material definition detail as a combination of material origin, and destination
DECLARE @tabDefinitions TABLE (
def_code VARCHAR(20) NOT NULL
, def_name VARCHAR(100) NULL
, origin_id VARCHAR(20) NOT NULL
, destination_id VARCHAR(20) NOT NULL
, material_type VARCHAR(20) NULL)

INSERT INTO @tabDefinitions (def_code, def_name, origin_id, destination_id, material_type)
SELECT a.def_code
, a.def_name
, b.criteria_id as origin_id
, c.criteria_id as destination_id
, d.criteria_id as material_id
FROM vw_matdef_definitions_latest AS a WITH (NOLOCK)
INNER JOIN matdef_definition_criteria AS b WITH (NOLOCK) ON a.def_code=b.def_code AND a.def_seq=b.def_seq AND b.criteria_type='O'
INNER JOIN matdef_definition_criteria AS c WITH (NOLOCK) ON a.def_code=c.def_code AND a.def_seq=c.def_seq AND c.criteria_type='D'
INNER JOIN matdef_definition_criteria AS d WITH (NOLOCK) ON a.def_code=d.def_code AND a.def_seq=d.def_seq AND d.criteria_type='M'

-- translate material definition into material origin, and destination patterns
-- definition is a combination of super-origin, super-destination and material type
DECLARE @tabPatterns TABLE (
def_code VARCHAR(20) NOT NULL
, def_name VARCHAR(100) NULL
, origin_id VARCHAR(10) NOT NULL
, origin_pattern VARCHAR(50) NOT NULL
, destination_id VARCHAR(10) NOT NULL
, destination_pattern VARCHAR(50) NOT NULL
, material_type VARCHAR(20) NULL)

INSERT INTO @tabPatterns (def_code, def_name, origin_id, origin_pattern, destination_id, destination_pattern, material_type)
SELECT a.def_code
, a.def_name
, a.origin_id
, replace(c.od_pattern, '[Material]', a.material_type) as origin_pattern
, a.destination_id
, e.od_pattern as destination_pattern
, a.material_type
FROM @tabDefinitions AS a
INNER JOIN matdef_od_hierarchy AS b WITH (NOLOCK) ON a.origin_id=b.od_parent
INNER JOIN matdef_od_patterns AS c WITH (NOLOCK) ON b.od_child=c.od_id
INNER JOIN matdef_od_hierarchy AS d WITH (NOLOCK) ON a.destination_id=d.od_parent
INNER JOIN matdef_od_patterns AS e WITH (NOLOCK) ON d.od_child=e.od_id

-- *** special destination: loclist
IF EXISTS(SELECT * FROM @tabPatterns WHERE destination_pattern=@LocList)
BEGIN
-- insert all dump names (not pattern) from loclist into the definition
INSERT INTO @tabPatterns (def_code, def_name, origin_id, origin_pattern, destination_id, destination_pattern, material_type)
SELECT a.def_code
, a.def_name
, a.origin_id
, a.origin_pattern
, a.destination_id
, b.dumpname as destination_pattern
, a.material_type
FROM @tabPatterns AS a
INNER JOIN dbo.fn_get_destination_loclist() AS b ON 1=1 -- cross join
WHERE a.destination_pattern=@LocList

DELETE FROM @tabPatterns WHERE destination_pattern=@LocList
END

INSERT INTO @tabPatterns (def_code, def_name, origin_id, origin_pattern, destination_id, destination_pattern, material_type)
SELECT a.var_code
, b.def_name
, ''
, a.origin
, ''
, a.destination
, a.material
FROM grs_definition_map AS a
JOIN ( select distinct def_code, def_name from @tabPatterns ) as b ON a.var_code=b.def_code
WHERE a.var_code <> 'GRSDEFNEW' and a.update_by <> 'FMI\auto' and len(a.origin)>7

-- update grs_definition_map table only if it is required
BEGIN TRAN

-- only changed definition assigned to the origin-destination-material need to be updated, not all rows
Create table #tabDefinitionUpdate (
origin VARCHAR(50)
, destination VARCHAR(50)
, material VARCHAR(50)
, var_code VARCHAR(50)

CONSTRAINT [PK_grs_definition_map_tmp] PRIMARY KEY CLUSTERED
(
[material],
[origin],
[destination]
) ON [PRIMARY]
) ON [PRIMARY]

-- updated changed definition
INSERT INTO #tabDefinitionUpdate (origin, destination, material, var_code)
SELECT a.origin, a.destination, a.material, b.def_code
FROM grs_definition_map AS a (NOLOCK, index=PK_grs_definition_map)
INNER JOIN @tabPatterns AS b
ON a.origin LIKE b.origin_pattern AND a.destination LIKE b.destination_pattern
AND a.material=b.material_type
WHERE (b.def_code<>a.var_code) and len(a.Origin) > 7

-- update manual assignment definition to auto assign if the pattern have already defined before
INSERT INTO #tabDefinitionUpdate (origin, destination, material, var_code)
SELECT a.origin, a.destination, a.material, b.def_code
FROM grs_definition_map AS a (NOLOCK, index=PK_grs_definition_map)
INNER JOIN @tabPatterns AS b
ON a.origin LIKE b.origin_pattern AND a.destination LIKE b.destination_pattern
AND a.material=b.material_type
WHERE (b.def_code=a.var_code and a.update_by<>'FMI\auto' and @UpdatedUser = 'FMI\auto') and len(a.Origin) > 7

-- not defined definition in pattern but the combination O,D,M already exist
-- , but just update the auto assignment, do not include the manual assignment
INSERT INTO #tabDefinitionUpdate (origin, destination, material, var_code)
SELECT a.origin, a.destination, a.material, ISNULL(b.def_code, 'GRSDEFNEW')
FROM grs_definition_map AS a (NOLOCK, index=PK_grs_definition_map)
LEFT OUTER JOIN @tabPatterns AS b
ON a.origin LIKE b.origin_pattern AND a.destination LIKE b.destination_pattern
AND a.material=b.material_type
WHERE (b.def_code is null AND a.var_code<>'GRSDEFNEW' and a.update_by = 'FMI\auto' ) and len(a.Origin) > 7

-- update by = 'FMI\auto' >> update by system ; updte_by='FMI\@userlogon' >> manual assignment
-- update to table grsdefinition map
UPDATE dbo.grs_definition_map
SET var_code=b.var_code
, update_by=@UpdatedUser
, date_update=@UpdatedTime
FROM dbo.grs_definition_map a (NOLOCK),
#tabDefinitionUpdate b
where a.origin=b.origin
AND a.destination=b.destination
AND a.material=b.material
AND len(a.Origin) > 7

IF (@@ERROR = 0)
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END

-- return the definition pattern list only if required
SELECT def_code, def_name
, rtrim(origin_id) as origin_id
, rtrim(origin_pattern) as origin_pattern
, rtrim(destination_id) as destination_id
, rtrim(destination_pattern) as destination_pattern
, rtrim(material_type) as material_type
FROM @tabPatterns

SET NOCOUNT OFF