Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question about clearing tempdb Expand / Collapse
Author
Message
Posted Saturday, April 27, 2013 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 11:26 AM
Points: 17, Visits: 22
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!
Post #1447273
Posted Saturday, April 27, 2013 9:14 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 5,871, Visits: 12,971
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.


---------------------------------------------------------------------

Post #1447274
Posted Saturday, April 27, 2013 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 11:26 AM
Points: 17, Visits: 22
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.
Post #1447281
Posted Saturday, April 27, 2013 9:59 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 5,871, Visits: 12,971
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.


---------------------------------------------------------------------

Post #1447282
Posted Monday, April 29, 2013 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 11:26 AM
Points: 17, Visits: 22
Thanks a lot, I appreciate your help.
Post #1447506
Posted Monday, April 29, 2013 7:31 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 5,871, Visits: 12,971
no probs

---------------------------------------------------------------------

Post #1447508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse