X_db to tempdb back to X_db - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

X_db to tempdb back to X_db

Author

Topic

willgates
Starting Member

3 Posts

Posted - 2007-01-05 : 17:28:13

i have written a stored procedures to resize the tempdb but i need to know how to run it and return back to the table. the dbcc shrink can't (for all i know) shrink the tempdb from another database, so i need to call the stored proc in tempdb, then shrink it , then somehow get back to my original table. Any suggestions will be appreciated.

Will

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 17:36:24

I assume you mean DBCC SHRINKDATABASE? If so, you can run it from anywhere (which is why it takes the database name as a parameter).

willgates
Starting Member

3 Posts

Posted - 2007-01-05 : 17:48:27

actually its shrinkfile..
sorry

dbcc shrinkfile (tempdev, 1)
go
dbcc shrinkfile (templog, 1)
go

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-05 : 19:10:27

You can do it like this:



exec tempdb..sp_executesql 
N'
print ''Database = ''+db_name()
dbcc shrinkfile (tempdev, 1000)
dbcc shrinkfile (templog, 1000)
select size,name from sysfiles
'

Results:


Database = tempdb
Cannot shrink file '1' in database 'tempdb' to 128000 pages as it only contains 12160 pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Cannot shrink file '2' in database 'tempdb' to 128000 pages as it only contains 96 pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
size        name 
----------- ------------------------------------
12160       tempdev 
96          templog 

(2 row(s) affected)

CODO ERGO SUM

willgates
Starting Member

3 Posts

Posted - 2007-01-07 : 14:13:32

Thanks, they both work perfectly...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources