SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question about clearing tempdb


Question about clearing tempdb

Author
Message
defiantclass
defiantclass
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 30
Hello,

My question is simply, once I have cleared the temp database either by rebooting or restarting the service, should the tempdb.mdf file located at Drive:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf go to the size I have allocated for it in the tempdb properties, or should it go closer to a 0 file size?

I have cleared the tempdb, both by rebooting and restarting the services, I can see in the log where it said it started the operation, but I don't know how to tell for sure if it is emptied since the tempdb.mdf file is always at the allocated size I set for it when I look at the file.

I guess I'm just not sure if setting the allocation size means that is the size it is allowed to grow to, or if it is the size the file (database) should physically be by default, at all times.

Thanks!
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25828 Visits: 13701
If you have explicitly amended the size of a tempdb database file via an alter database command that is the size it will recreated at on restart.

Any data in tempdb will be lost on restart.

In the GUI the initial size shown for the file is the size on creation, not the size it has grown to if there have been any autogrowths since restart.

---------------------------------------------------------------------
defiantclass
defiantclass
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 30
Ok, so when I drill down in Windows Explorer to the physical file, the size of it there is not reflecting the amount of data in it (meaning it is full) but that it is that size because a allocated that much size to it in my sql server configuration.

So if I made my allocated file size for the tempdb 4.5 GB, that physical file, located at Drive:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf, is always going to be that size. Or bigger perhaps, but never smaller.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25828 Visits: 13701
defiantclass (4/27/2013)
Ok, so when I drill down in Windows Explorer to the physical file, the size of it there is not reflecting the amount of data in it (meaning it is full) but that it is that size because a allocated that much size to it in my sql server configuration.


correct

So if I made my allocated file size for the tempdb 4.5 GB, that physical file, located at Drive:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf, is always going to be that size. Or bigger perhaps, but never smaller
.

correct, though strictly speaking it could get smaller if you were to shrink the file to less than its original size, but thats unlikely to happen.

---------------------------------------------------------------------
defiantclass
defiantclass
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 30
Thanks a lot, I appreciate your help.
george sibbald
george sibbald
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25828 Visits: 13701
no probs

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search