You can click on the number below the Initial Size (MB) header and type in a new number. You should try to do the math before doing this. To find how much of the space is actually in use, you could use the below. You can either run this as a query in SSMS or if your application has the ability to run queries directoy, you can copy and paste it into that window.
Begin
select
name
, filename
, convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB
, convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB
, convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB
from dbo.sysfiles a
End
Setting Initial Database Sizes
Setting initial database sizes is easy to do and results in better SQL performance. Steps below to accomplish this.
As seen above, SQL created the additional files with an ndf extension. We created the files with the same size and growth patterns so that SQL will use them equally. If we don't do this, SQL will make some choices based on the size settings that may result in the additional files not being used. If you look at them using the code from the previous page, they are unlikely to be the same size, but over time you should see different ones being used the most. Once you have had SQL up for a while, (I usually try for at least a week.) I would probably add at least 10 percent to whatever the largest file value is to all the files equally for the reasons outlined above. The Tempdb is deleted and starts over when you restart SQL server, so if this has just been restarted, the data isn't generally going to be indicative of where this is likely to wind up. Thanks for reading and I hope this helps!
You would then right click on tempdb and follow the steps you did to set the Initial Size on your database to get to the Files screen as shown below. I already started the file add process as will be explained below.
The Tempdb presents a special case. Out of the box, there is one data file and one log file. SQL Server essentially uses these date files as scratch pads. Each logical processor you allow SQL Server to use can independently perform tasks. If there is only one data file, they share this. Just like a shared scratch pad, a processor might be waiting for another processor to be done with it before they get to use it.
At PASS 2011 Bob Ward, one of the Sr Escalation Engineers for SQL, made the following recommendation:
"As a general rule, if the number of logical processors is less than 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code."
Please note that the recommendation only applies to the data files, not the log files. You only need 1 of those. How do I add the extra files? We will start by clicking on the + next to the System Database folder as shown below.
You should now see something like the screen below. I stretched mine sideways a bit, so I could show the full file path. As you can see, my Initial Size settings are pretty low.
This brings up the Database Properties window. Click on the Files page on the left.
Open SSMS. (SQL Server Management Studio) It should look something like the shot below.
Clicking on the + next to Databases and then right clicking on ERPSQLPro brings up a context based popup as shown below. Click on properties.
When this came up I clicked on the Add button at the bottom of the screen and edited the Logical Name to be Tempdev1. I also changed the Initial Size to be the same as the original file and changed the Autogrowth / Maximize settings to be the same. If you look to the far right, there is no actual file name yet. I have 8 logical processors, so I am going to add 6 more files and then click on the Ok button. Results after doing this and coming back to this screen below.
I've only had this database up and running for a month and to be honest, in terms of SQL server, this is so small, that I could do just about anything and it would work well. <G> That being said, the same best practice principles apply. My DB is 2.56 MB and I am likely to keep this computer for 3 years. 2.56 x 12 x 3 = 92.16 I sometimes keep them a year out of warranty if they are running well, so I will extend this out to 4 years and round it off at 125 MB. If possible, the very best solution would be to figure the life of the server and set the file size now. That way I minimize fragmentation. My recovery model on this is set to simple and the log file essentially recycles data at some point, so I will leave it alone and perhaps check back on it at some point. We will start the Tempdb stuff at the top of the next column.