September 23, 2008 at 9:13 am
We have SQL2005 SP2 installed with MS 2003 SP2. The database is updated everyday but the file size in windows explorer did not show latest time stamp and file size.
Can anyone help?
Thanks.
September 23, 2008 at 9:30 am
When you create the database, you specify an initial size and autogrowth parameter.
For example, when you create your database, you .mdf size will be 50Mb and it will increase by 10 Mb when the 50 Mb will be full...
So, the file size / date does not change until it is fully used by SQL Server.
The file size will be incremented by the file growth when it will be full....
Not sure if this is clear but hope it will help...
Patrick
September 23, 2008 at 12:27 pm
I did change Autogrowth to "By 1 MB, unrestricted growth" and I did some sql jobs. However on the windows explorer, the data file did not update.
Anything I can do?
Thanks.
Dave
September 24, 2008 at 12:41 am
It will only update when the file will be full...
So, I guess that if your file is not updated, it only means that there is still some space for SQL Server to store your data...
September 24, 2008 at 3:36 am
Load Management Studio
Expand Databases
Right-click on your database
Choose Reports, Standard Reports, Disk Usage
Check out how much "unallocated" space (as a %) is in the file. Until this is basically zero the file won't grow in Windows Exporer.
If you want to get actual figures use the following:
Open QA against your database
-- Get the name of the data file
SELECT * FROM sysfiles
-- Get the file size from here
SELECT /128 FROM sysfiles WHERE NAME = '{datafilename}'
-- Get the amount used from here
SELECT FILEPROPERTY('{datafilename}','SpaceUsed')/128
September 25, 2008 at 3:18 am
The file size on disk and the file size shown by Windows may not always be the same.
The information shown to the end-user by the DIR command or in Windows Explorer comes from the file cache. The file cache gets updated every 15 seconds with what is actually on the disk. However, if a file is open the cached information does not always match reality. When the file gets closed, then the cached information will get corrected.
If you see that the information in Windows Explorer is showing a smaller file than is shown by SQL Server, then stop SQL Server, wait until the next cache update, then look again - you should see Windows now shows the same size as SQL Server.
This is not a bug, and it is not a risk to data integrity. It is a design decision in Windows that the information shown to the end user does not need to be 100% up to date for open files. Making this information 100% right would add a lot of extra processing and most of the time no-one is looking at the result.
Now to the next issue. Growing a database file is a bad thing. Every time the file grows, the query that is requiring the extra space is halted until the file growth is complete. This is bad for your database users. Every time the file grows, you get another NTFS disk fragment, which is affect SQL Server performance from that point on.
You should make sure your database files are large enough to deal with your queries. You should never shrink a database file unless the reduced size is likely to be permanent. I often say never shrink a database file if you expect it to grow again within the next 3 months.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 25, 2008 at 8:15 pm
so what I need to do make sure database files are large enough to deal with the queries.
Thanks.
DP
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply