SQL Server Performance Forum – Threads Archive
Data File size
Why is it that I have a data file of about 60GB data used (where the actual data file is 85GB) and 400,000 records and in deleting 100,000 records, my data used still remains at 60GB?This is an educated guess. I believe SQL Server reports space used based on the HWM (High Water Mark) which basically is the location of the last record in a table. Therefore if you have 1 mil records in a table, you delete 999,999 records but not the last record in the table, the space used reported will be unchanged. The HWM did not shift down, as the last record was not deleted. There is an option in Database Maintenance Plan in EM to ‘remove used space in database files’ which basically shrinks the database. You may want to consider this option. However, take note that this is an resource intensive operation. The plus side is that your backup will be smaller (and maybe faster) as the HWM is shifted downwards. I believe this is how Oracle works, can someone else confirm whether SQL Server works this way too?
The file size of your sql database will continue to grow as you keep adding data to it, and will stop growing when it hits the preset limit. However, when you delete data from sql, the file size doesn’t automatically get smaller. You can only make the file size smaller (after deleting data) by shrinking the files manually
Check whether auto shrink option is enabled or not…
I feel its enabled as default.
I assume you did already try to shrink the file?
Was the data you’ve deleted BLOB data? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de
Run DBCC UPDATEUSAGE(0) in the dataabse and check the numbers again.
Hello Everybody, Yes marlboro.Sql server also has similar functionality.
when new records inserted, SQL server checks for the free page space and if not found then SQL server create new extend which is equal to 64K(8K per Page means 8 Pages).Due to addition of new pages,mdf file size increases.
Now when deletion happens which may or may not be at the end of the mdf file. So rows which is delete i.e. data stored on the pages is empty,SQL server marked that pages as ghost but SQL server never gives back deleted page space to OS.Same can happen only when shrink command is used. Information related to the Free space and used space is maintain by Global Allocation Map (GAM) and information related to individual pages is records by Page Free Space (PFS) I hope richard you got the answer of your query. Thanks and Regards
Ravi Kumar
Thanks Ravi.
Thanks Marlboro. Perhaps you, or somebody else can help me walk through this problem. My datafile is at 80GB. The data used in the datafile is about 70GB and growing, with approximately 4,000,000 records in a table that contains a Text field. I have a weekly purge that deletes about 500,000 records. (Disk space is at a premium.) My understanding is that shrinking the data file is not the best thing to do, but if I have to, then I will. So essentially, to keep the data file comfortably below 80GB I would: 1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" Would the above 2 steps be the means of keeping my datafile comfortably below 80GB?
quote:Originally posted by dineshasanka
The file size of your sql database will continue to grow as you keep adding data to it, and will stop growing when it hits the preset limit. However, when you delete data from sql, the file size doesn’t automatically get smaller. You can only make the file size smaller (after deleting data) by shrinking the files manually
How is it then that when I delete records in my test environment that the "data used" within the data file decreases, but not so when I delete records from my production environment?
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" What I think you should do is: 1. Perform the weekly purge (deleting approximately 500,000 records)
2. Shrink the datafile "dbcc shrinkfile ( filename, NOTRUNCATE)"
3. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" The 2nd command compacts the scattered records in your datafiles to the front of the file. The 3rd command truncates the file beyond the HWM, which would have been lowered when you run the 2nd command. I expect that this 2 processes will take a long time completed. Actually I found out that if your data file is 80GB but you use only 1GB, the backup size is only 1GB. What I’m trying to say here is there is no harm to retain your allocated file size to be 80GB. Therefore the 3rd command above is not really needed. How is it then that when I delete records in my test environment that the "data used" within the data file decreases, but not so when I delete records from my production environment? I think the difference is that in the test env, you’re deleting records that were inserted last in the data file, therefore occupying the end of the file. Removing them will lower the HWM. In the prod env., this is not so.
quote:Originally posted by marlboro
1. Perform the weekly purge (deleting approximately 500,000 records).
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" What I think you should do is: 1. Perform the weekly purge (deleting approximately 500,000 records)
2. Shrink the datafile "dbcc shrinkfile ( filename, NOTRUNCATE)"
3. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" The 2nd command compacts the scattered records in your datafiles to the front of the file. The 3rd command truncates the file beyond the HWM, which would have been lowered when you run the 2nd command. I expect that this 2 processes will take a long time completed. Actually I found out that if your data file is 80GB but you use only 1GB, the backup size is only 1GB. What I’m trying to say here is there is no harm to retain your allocated file size to be 80GB. Therefore the 3rd command above is not really needed. How is it then that when I delete records in my test environment that the "data used" within the data file decreases, but not so when I delete records from my production environment? I think the difference is that in the test env, you’re deleting records that were inserted last in the data file, therefore occupying the end of the file. Removing them will lower the HWM. In the prod env., this is not so.
2. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" What I think you should do is: 1. Perform the weekly purge (deleting approximately 500,000 records)
2. Shrink the datafile "dbcc shrinkfile ( filename, NOTRUNCATE)"
3. Shrink the datafile "dbcc shrinkfile ( filename, TRUNCATEONLY)" The 2nd command compacts the scattered records in your datafiles to the front of the file. The 3rd command truncates the file beyond the HWM, which would have been lowered when you run the 2nd command. I expect that this 2 processes will take a long time completed. Actually I found out that if your data file is 80GB but you use only 1GB, the backup size is only 1GB. What I’m trying to say here is there is no harm to retain your allocated file size to be 80GB. Therefore the 3rd command above is not really needed. How is it then that when I delete records in my test environment that the "data used" within the data file decreases, but not so when I delete records from my production environment? I think the difference is that in the test env, you’re deleting records that were inserted last in the data file, therefore occupying the end of the file. Removing them will lower the HWM. In the prod env., this is not so.
Thanks again Marlboro. Things are getting more clear. I was evaluating the 2nd command in your most recent response (quoted above), and it almost seems if I want to see the "data used" decrease, then wouldn’t I first run "dbcc shrinkfile ( filename, NOTRUNCATE)" to compact scattered records, and then perform my purge?
quote:Originally posted by dineshasanka
The file size of your sql database will continue to grow as you keep adding data to it, and will stop growing when it hits the preset limit. However, when you delete data from sql, the file size doesn’t automatically get smaller. You can only make the file size smaller (after deleting data) by shrinking the files manually
Yes, but as far as reducing the data used within the data file, is where I am experiencing a problem. In my Test environment (a greatly reduced subset of data) when I delete from the table, the data used in the data file decreases. This decrease in data used does not decrease in my Production table. Presently I am leaning toward Marlboro’s resolution of performing my weekly purge, followed by "dbcc shrinkfile ( filename, TRUNCATEONLY)". But I am trying to avoid the shrinkfile. Would a dbcc checktable be of any use, or a reorganization of data and index pages be of any potential use? It just seems like I should be able to delete and reduce the data used in the data file. Any suggestions beyond what Marlboro has suggested?
quote:Originally posted by FrankKalis
I assume you did already try to shrink the file?
Was the data you’ve deleted BLOB data?
Was the data you’ve deleted BLOB data?
Yes, Frank, the data deleted contains a text column. I have not performed a shrink yet, but it appears that is about my only alternative to reduce the data used within the data file.
]]>