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.
| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-04-08 : 10:32:28
|
| Dear All,My data base size is having 500GB space apart from tempDB itself 100GB how can we reduce the temp db space without restarting the server.Thanks,Gangadhar |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-08 : 10:48:27
|
To shrink it down to 50MB, use this:USE [tempdb]GODBCC SHRINKFILE (N'tempdev' , 50)GO There are 10 types of people in the world, those that understand binary, and those that don't. |
 |
|
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
Posted - 2010-04-08 : 10:50:25
|
| Hi,I am doing this on the production server is there any thing i need to take care before doing this.precautionary measures pls tell me i don't want to see any surprises after doing this Thanks for your helpGangadhar |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-08 : 11:00:49
|
quote:
Originally posted by gangadhara.ms
Hi,I am doing this on the production server is there any thing i need to take care before doing this.precautionary measures pls tell me i don't want to see any surprises after doing this Thanks for your helpGangadhar
If it were me, I'd wait till there was a period when the server wasn't very busy.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-08 : 11:02:15
|
| don't do that unless you know nothing is using tempdb. (which is probably likely)Read this and schedule some changes in your downtime window:http://support.microsoft.com/kb/307487Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-08 : 11:04:03
|
| Effects of Execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE While Tempdb Is In UseIf tempdb is in use and you attempt to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors similar to the following type and the shrink operation may fail:Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.-or-Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.Although error 2501 may not be indicative of any corruption in tempdb, it causes the shrink operation to fail. On the other hand, error 8909 could indicate corruption in the tempdb database. Restart SQL Server to re-create tempdb and clean up the consistency errors. However, keep in mind that there could be other reasons for physical data corruption errors like error 8909 and those include input/output subsystem problems.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 11:34:57
|
quote:
Originally posted by DBA in the makingIf it were me, I'd wait till there was a period when the server wasn't very busy.
I wouldn't do it at all whilst the system was "up", no ifs-buts-or-maybes. Possible risk of corrupting things, just restart SQL Service (don't need to restart the server) and that should set TEMPDB back to its configured "initial startup" size.Does the O/P need the disk space urgently? If not I suggest just leaving it until the next SQL Restart |
|
|
|
|
|
|
|
|
Temp DB space