April 27, 2013 at 8:53 am
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!
April 27, 2013 at 9:14 am
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.
---------------------------------------------------------------------
April 27, 2013 at 9:46 am
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.
April 27, 2013 at 9:59 am
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.
---------------------------------------------------------------------
April 29, 2013 at 7:26 am
Thanks a lot, I appreciate your help.
April 29, 2013 at 7:31 am
no probs
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply