/*
** Copyright Anthony Mandic, 27/10/1998.
*/

/*
** Suss out where we are.
*/
if (select count(*)
    from   master..sysdatabases
    where  name = 'sybsystemprocs') = 1
    use sybsystemprocs
else
    use master
go

/*
** Drop an existing one. Yadda, yadda, yadda.
*/
if object_id('sp_reset_tempdb') is not null
    drop procedure sp_reset_tempdb
go

/*
** Well, its 1 for the inheritance...
*/
execute sp_configure 'allow updates', 1
go

create procedure sp_reset_tempdb
as

declare	@tid	smallint,
	@mid	smallint,
	@tvs	int,
	@count	int,
	@msm	int,
	@mlow	int,
	@mhigh	int

if @@trancount > 0
begin
	print "Can't run from within a transaction."
	return 1
end

if proc_role("sa_role") != 1
begin
	print "You need to have sa role to run this sproc."
	return 2
end

set chained off

set transaction isolation level 1

/*
** Get tempdb's dbid.
*/
select	@tid	= dbid
from	master..sysdatabases
where	name	= "tempdb"

/*
** Get model's dbid.
*/
select	@mid	= dbid
from	master..sysdatabases
where	name	= "model"

/*
** Get master's size range.
*/
select	@mlow	= low,
	@mhigh	= high
from	master..sysdevices
where	name	= "master"

/*
** Find out how many devices tempdb uses.
*/
select	@count	= count(*)
from	master..sysusages
where	dbid	= @tid

/*
** Get model's segmap.
*/
select	@msm	= segmap
from	master..sysusages
where	dbid	= @mid

if (@count = 1)
begin
	/*
	** No need to do anything. There is only one tempdb device.
	** Note that it may not be on master. However, we can't do
	** anything about it here.
	**
	** Regardless, reset the segmap in case it was changed.
	*/
	update	master..sysusages
	set	segmap	 = @msm
	where	dbid	 = @tid
	and	segmap	!= @msm

	return 0
end

select	@tvs	= vstart
from	master..sysusages
where	dbid	= @tid
and	lstart	= 0

if @@rowcount != 1
begin
    print "What the hey!"
    return 3
end

if not (@tvs between @mlow and @mhigh)
begin
    print "First fragment of tempdb is not on the master device!"
    return 4
end

/*
** At this point we know all that we need. So just do it.
*/
delete	master..sysusages
where	dbid	= @tid
and	lstart	> 0

update	master..sysusages
set	segmap	 = @msm
where	dbid	 = @tid
and	segmap	!= @msm

/*
** There are other things we can reset if we like - like tempdb's status
** in sysdatabases. But leave that for someone else to ponder over.
*/
print "The tempdb database has been reset to its default size."
print "Please bounce your server now."

return 0

go

/*
** This probably isn't a good idea.
*/
grant execute on sp_reset_tempdb to public
go

execute sp_configure "allow updates", 0
go