 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008Issue with unclaimed space
Hi all, I am not sure whether someone has experienced the same issue as I do. I have a table which has a column with text data type and a few other regular columns (int, datetime etc). There are inserts / deletes on this table, but the weird thing is the table is keeping on growing, but the row number is not (because of delete actions), for example, when the table has 10000 rows, it has 150GB, but when it has 6500 rows, it has 179GB, actualy when I execute sp_spaceused 'mytable' to monitor, I can see the rows are dynamically changing, i.e. sometimes more rows, sometimes fewer rows, but the data column in the sp_spaceused result set has the value growing continously. My environment is SQL2K5 EE (ver 9.0.3054). In my test environment, I cannot repeat this production issue. Does anyone encounter the same issue before? TIA, Jeff_Yao Dear Jeff, Thank you for posting here. As the data type in this table in question is "Text" , the size of the Text type column may not be proportional to the number of the rows because it depends on the data size in each field of the Text type column. For example, if one row contains the extreme large data in the Text column, the size of the whole table may grow even when the number of rows are less than before. So, it is an expected behavior in some situations. At this stage, you can first use the following sp_spaceused command to check the whole database to see if there is some unallocated space we can release. use dbname go sp_spaceused @updateusage = N'TRUE' If so, please first backup the database and then run the DBCC SHRINKFILE to shrink the data file. For more information, please refer to the following website: DBCC SHRINKFILE (Transact-SQL) http://msdn2.microsoft.com/en-us/library/ms189493.aspx If anything is unclear in my post, please don't hesitate to let me know and I will be glad to help. Have a nice day! Best regards, Adams Qu, MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. =====================================================  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: "jeff_yao" <jeff_yao@community.nospam> | Subject: Issue with unclaimed space [quoted text clipped - 33 lines] | TIA, | Jeff_Yao Using SHRINKFILE to free up unused space that is allocated to a table seems like an extreme and inefficient approach. The question was posed about a specific table, and it would seem better to look for a solution that deals with the table and not the entire database. Are you saying that ALTER INDEX with REBUILD or REORGANIZE (and LOB_COMPACTION = ON) would not free the unused space? Roy Harvey Beacon Falls, CT >Dear Jeff, > [quoted text clipped - 82 lines] >| TIA, >| Jeff_Yao Thanks Roy and Adams for the reply. This table has a clustered index, but after I drop and recreate it, it does not help either. LOB_COMPACTION = ON is the default option and I did not explicitly specify it. On the other hand, the index is based on an interger column, so it does not suprise me if reindex does not solve the issue. To Adams Qu [MSFT], I'd like to answer your suggestions / concerns here too. As a dba starting with ver 6.5, I of course understand "the size of the Text type column may not be proportional to the number of the rows", what I observed is even when a row is deleted (with no new rows inserted), the sp_spaceused (with @updateusage='true') reports no decreased data size, but as soon as a new row is inserted, sp_spaceused will show increased data size. The puzzle here is the table size has NEVER decreased over a long time and in terms of each row, the text column should be more or less identical in size. I have recorded the table size for the last 30 days, see following for details (size data are in KB) TblName rows reserved data index_size unused LogDate MyTable 697999 72518648 72330272 53152 135224 17-Jan-2008 MyTable 702841 75228552 75039912 53192 135448 18-Jan-2008 MyTable 706909 77616144 77427256 53208 135680 19-Jan-2008 MyTable 708712 78533968 78345048 53208 135712 20-Jan-2008 MyTable 711520 80033232 79844232 53216 135784 21-Jan-2008 MyTable 715487 82224280 82035128 53224 135928 22-Jan-2008 MyTable 722378 85085920 84896432 53248 136240 23-Jan-2008 MyTable 729030 87924840 87735112 53272 136456 24-Jan-2008 MyTable 735067 90753064 90563008 53280 136776 25-Jan-2008 MyTable 742325 93237808 93047496 53304 137008 26-Jan-2008 MyTable 745192 94787448 94596960 53312 137176 27-Jan-2008 MyTable 750447 97794424 97603648 53328 137448 28-Jan-2008 MyTable 759321 102154296 101963176 53360 137760 29-Jan-2008 MyTable 768464 106502824 106311240 53440 138144 30-Jan-2008 MyTable 777361 111264944 111072712 53480 138752 31-Jan-2008 MyTable 785317 115374392 115181664 53512 139216 1-Feb-2008 MyTable 790329 118687736 118494720 53520 139496 2-Feb-2008 MyTable 793007 120391928 120198856 53528 139544 3-Feb-2008 MyTable 797381 123205944 123012592 53544 139808 4-Feb-2008 MyTable 805268 127963832 127770056 53568 140208 5-Feb-2008 MyTable 812031 131762168 131568032 53592 140544 6-Feb-2008 MyTable 817958 135148280 134953872 53608 140800 7-Feb-2008 MyTable 823068 138176064 137981360 53624 141080 8-Feb-2008 MyTable 826878 140412488 140217496 53640 141352 9-Feb-2008 MyTable 829234 141999752 141804592 53648 141512 10-Feb-2008 MyTable 833843 145251208 145055840 53656 141712 11-Feb-2008 MyTable 840203 149432784 149237104 53688 141992 12-Feb-2008 MyTable 846171 153050064 152854104 53704 142256 13-Feb-2008 MyTable 852032 156654488 156458232 53736 142520 14-Feb-2008 MyTable 11670 159944352 159671584 82320 190448 15-Feb-2008 MyTable 11776 163975840 163674936 78920 221984 16-Feb-2008 MyTable 8573 165295776 164999280 76680 219816 17-Feb-2008 MyTable 6477 167538536 167259824 73208 205504 18-Feb-2008 MyTable 10171 170841784 170769184 824 71776 19-Feb-2008 Notice the last row, the index size is smaller, but it was a result of my index rebuild (drop & recreate for clustered index). But on the other hand data size keeps on growing no matter how many rows inside the table. I think there is one solution which I will implement later when a maintenance window comes, i.e. 1. select * into <TempTable> from MyTable 2. truncate table MyTable 3. insert into MyTable Select * from <TempTable> 4. drop table <TempTable> 5. check the MyTable size again I come to this proposed solution because I have calculated the sum of the datalength of the text column (stores an XML file), and it returns me only 760 MB, giving some overhead (the other four interger, datetime columns can be omitted here), I think 8GB should be sufficient for the whole table (i.e. 760 MB x 10 = 8 GB) If this works, I guess there must be something wrong with SQL Server engine, and at that time I hope Adams can provide us some internal whys :-) I will update you here once I implemented my proposal. Thanks, Jeff > Using SHRINKFILE to free up unused space that is allocated to a table > seems like an extreme and inefficient approach. The question was [quoted text clipped - 99 lines] >>| TIA, >>| Jeff_Yao Dear Jeff, Thank you for your detail response and clarifying the issue for me. According to my experience, there is an known issue in SQL Server that the space is not released after you use a DELETE statement to delete data from a table. This kind of behavior is normally caused by the row versioning-based isolation level, or Row-level lock. If pages are not deallocated, other objects in the database cannot reuse the pages. If it is the situation, one method we can try to correct the space is truncating table and re-importing the data (just as what you intend to do). However, I also suggest trying another method that including a "TABLOCK" hint in the DELETE statement to make a table-level lock being held (if a row versioning-based isolation level is not enabled) to test the issue again. For example, use a statement that is similar to the following: DELETE FROM <TableName> WITH (TABLOCK) where <condition> If it is convenient, you can try to delete a test row via above command and then verify the space again via the sp_spaceused. This method can help us to determine if the issue is caused by this factor. Please let me know the output at your convenience and I am glad to continue to work with you. Have a nice day! Best regards, Adams Qu MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: "jeff_yao" <jeff_yao@community.nospam> | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> | Subject: Re: Issue with unclaimed space | Date: Tue, 19 Feb 2008 14:57:52 -0800 [quoted text clipped - 196 lines] | >>| TIA, | >>| Jeff_Yao Dear Jeff, We wanted to see if the information provided was helpful. Please keep us posted on your progress and let us know if you have any additional questions or concerns. We are looking forward to your response. Have a nice day! Best regards, Adams Qu MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | X-Tomcat-ID: 112810464 | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> <#HHOdr0cIHA.148@TK2MSFTNGP04.phx.gbl> | MIME-Version: 1.0 | Content-Type: text/plain [quoted text clipped - 278 lines] | | >>| TIA, | | >>| Jeff_Yao Hi Adams, Thanks for the follow-up, really appreciate it. I finally got the chance to set up a test environment where I restored the problematic production db, which is 1400 GB large. So here is what I have found. Issue: a table called MyTable with a column of ntext data type does not decrease its size even after big chunk of deletion. Solution: 1. select * into <tempTable> from MyTable -- MyTable is the table not releasing space after rows are deleted 2. Truncate table MyTable 3. Insert into MyTable select * from <tempTable> After this, when I do the sp_spaceused 'MyTable', I can find the table size has been shunk from 14 GB to 40MB. (The table has 102 rows) There is one interesting question to be ansered here, originally when I first posted in this forum (Feb 19, 2008), it was about 170+GB size, but today it is only 14GB, which is amazing to me. How does this happen? But still 14GB is huge compared to 40MB. After solving this issue with MyTable, I find there are other two similar tables (each has a ntext column), let me call them MyTable2 and MyTable3, that also have the similar issue, i.e. after huge chunk of deletes (here huge means millions of records, i.e. 90% of the table rows), the tables still keep their sizes when checked with sp_spaceused. I tried to use the same solution as mentioned above, however, the two tables are so huge, MyTable2 currently is 157GB, and MyTable3 is 330GB, so doing a "select into" craps my test environment because tlog grows too large. I know I can overcome this by some chunk of rows in a loop but it will take too long, which means the solution may not be applicable to our prod environment. However I got some unexpected results, here are my exact steps with pseudo scripts 1. create table tempTable (pk_id int primary key, blob_col ntext) 2. insert int tempTable (pk_id, blob_col) select pk_id, blob_col from MyTable2 3. after 8 hrs running, my disk has no space for log file, I cancelled the step, which took another 3 hrs 4. after fully rolled back, tempTable has no rows because the insert does not succeed, and now I run: execute sp_spaceused 'tempTable', 'true', and here is the result name rows reserved data index_size unused tempTable 0 173200 KB 162496 KB 968 KB 9736 KB As you can see, rows = 0, while data colum is around 160MB, how can this be? Does this also prove in some way that the table is not releasing its space after deletion? If I do a truncate table tempTable, then everything is reset, i.e. all columns are 0. So Adams, could you please provide some insights on this table behaviour? Thanks in advance, Jeff_Yao > Dear Jeff, > [quoted text clipped - 351 lines] > | | >>| TIA, > | | >>| Jeff_Yao Dear Jeff, Thank you for your detail response. After checking your latest post, there are two different issues here: 1. The tempTable which has no rows is a heap table and it seems to be growing because of lack of clustered indexes. By design, SQL Server only releases all the pages that a heap table (no indx) uses when the following conditions are true: - A deletion on this table occurs. - A table-level lock is being held. In such case, if we do a truncate table tempTable, everything will be reset. For more information about this topic, you can refer to the following KB article: Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server http://support.microsoft.com/kb/913399 2. The main table MyTable or MyTable2 and MyTable3 is growing because SQL Server reserves more space for the text field. To further track the issue and determine the root cause, would you please if the issue can still be reproduced on the MyTable which is just rebuilt in the test environment? If not, please let me know how the data is inserted (via SP or manually)? Have a nice day! Best regards, Adams Qu, MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: "jeff_yao" <jeff_yao@community.nospam> | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> <#HHOdr0cIHA.148@TK2MSFTNGP04.phx.gbl> <RA1ghQ7cIHA.6844@TK2MSFTNGHUB02.phx.gbl> <h99$zKCeIHA.4200@TK2MSFTNGHUB02.phx.gbl> | Subject: Re: Issue with unclaimed space | Date: Tue, 11 Mar 2008 16:55:07 -0700 [quoted text clipped - 431 lines] | > | | >>| TIA, | > | | >>| Jeff_Yao >1. The tempTable which has no rows is a heap table and it seems to be >growing because of lack of clustered indexes. By design, SQL Server only [quoted text clipped - 11 lines] >statement to delete data from the table in SQL Server >http://support.microsoft.com/kb/913399 Adams, Interesting KB article. I have some questions about the first workaround, which says: ====== Include a TABLOCK hint in the DELETE statement if a row versioning-based isolation level is not enabled. For example, use a statement that is similar to the following: DELETE FROM <TableName> WITH (TABLOCK) ====== Will that work for just the rows deleted in that DELETE? Or does it also free up space from past DELETE commands? Along the same lines, if there is a WHERE clause that results in 0 rows being deleted, would the space from past DELETEs be freed? Roy Harvey Beacon Falls, CT Dear Roy, Thank you for your question. No, the TABLOCK hint will not free up the space from the past delete and it only make the SQL Server releases the pages for the current deletion on a heap table. If you experience such issue described in this KB article and would like to ensure that the space from the past delete commands have been freed up, we can export the data, and then TRUNCATE table, re-import the data to workaround this problem. Have a nice day! Best regards, Adams Qu MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: "Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> | Subject: Re: Issue with unclaimed space | Date: Wed, 12 Mar 2008 12:50:12 -0400 | Message-ID: <k32gt31imeioibmqvjfcn0aa5pkucaocos@4ax.com> | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> <#HHOdr0cIHA.148@TK2MSFTNGP04.phx.gbl> <RA1ghQ7cIHA.6844@TK2MSFTNGHUB02.phx.gbl> <h99$zKCeIHA.4200@TK2MSFTNGHUB02.phx.gbl> <OKxZYN9gIHA.6084@TK2MSFTNGP06.phx.gbl> <3iGyoWChIHA.3660@TK2MSFTNGHUB02.phx.gbl> | X-Newsreader: Forte Agent 4.2/32.1118 | MIME-Version: 1.0 [quoted text clipped - 43 lines] | Roy Harvey | Beacon Falls, CT Thanks for the clarification. I believe an alternate workaround to save/truncate/reload would be to add, and then drop, a clustered index. Roy Harvey Beacon Falls, CT >Dear Roy, > [quoted text clipped - 86 lines] >| Roy Harvey >| Beacon Falls, CT Dear Roy, Thank you for your suggestion. Have a nice day! Best regards, Adams Qu, MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: "Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> | Subject: Re: Issue with unclaimed space | Date: Thu, 13 Mar 2008 15:57:30 -0400 | Message-ID: <9n1jt31tl5pjln2d5tu92k4u8rbm9hm87i@4ax.com> | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> <#HHOdr0cIHA.148@TK2MSFTNGP04.phx.gbl> <RA1ghQ7cIHA.6844@TK2MSFTNGHUB02.phx.gbl> <h99$zKCeIHA.4200@TK2MSFTNGHUB02.phx.gbl> <OKxZYN9gIHA.6084@TK2MSFTNGP06.phx.gbl> <3iGyoWChIHA.3660@TK2MSFTNGHUB02.phx.gbl> <k32gt31imeioibmqvjfcn0aa5pkucaocos@4ax.com> <y2AkmjNhIHA.4672@TK2MSFTNGHUB02.phx.gbl> | X-Newsreader: Forte Agent 4.2/32.1118 | MIME-Version: 1.0 [quoted text clipped - 105 lines] | >| Roy Harvey | >| Beacon Falls, CT No, not in my case. I did a rebuild of clustered index and even move it to another filegroup, but it does not help. I believe this is because in my case, it is the BLOB columns that are not stored in the regular data pages where the clustered index resides. I will do more research and try to replicate this in my test environment. There is one fact here, we store all the blob columns in a secondary filegroup (other than the primary), which has one data file there. The data file is about 660GB and by using fileproperty(filename, 'spaceused'), I can see that only 140GB is used. When I try to do a dbcc shrinkfile(), after 12 hrs running, it is still not done and I have to cancel it and do not see my problematic tables shrinked in any way. > Thanks for the clarification. > [quoted text clipped - 98 lines] >>| Roy Harvey >>| Beacon Falls, CT Dear Jeff, Yes, the KB 913399 does not apply to our original main problem. The purpose of providing this article in my previous post is used to address your another space issue on 'TempTable' because I believe that they are very different problems here. Regarding our original main table issue, would you please if the issue can still be reproduced on the MyTable which is just rebuilt in the test environment? If not, please let me know how the data is inserted (via SP or manually) in the Production environment? Additionally, just as you suspect , DBCC SHRINKFILE may not work in some situations because of the BLOB columns . The following KB Articles may be useful: DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns http://support.microsoft.com/kb/324432 Hope it helps. Have a nice day! Best regards, Adams Qu MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | From: "jeff_y" <jeffrey_ya@abc.com> | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> <#HHOdr0cIHA.148@TK2MSFTNGP04.phx.gbl> <RA1ghQ7cIHA.6844@TK2MSFTNGHUB02.phx.gbl> <h99$zKCeIHA.4200@TK2MSFTNGHUB02.phx.gbl> <OKxZYN9gIHA.6084@TK2MSFTNGP06.phx.gbl> <3iGyoWChIHA.3660@TK2MSFTNGHUB02.phx.gbl> <k32gt31imeioibmqvjfcn0aa5pkucaocos@4ax.com> <y2AkmjNhIHA.4672@TK2MSFTNGHUB02.phx.gbl> <9n1jt31tl5pjln2d5tu92k4u8rbm9hm87i@4ax.com> | Subject: Re: Issue with unclaimed space | Date: Fri, 14 Mar 2008 11:15:23 -0700 [quoted text clipped - 127 lines] | >>| Roy Harvey | >>| Beacon Falls, CT Dear Jeff, How's everything going? I'm wondering if you have any further questions. Please feel free to respond to the newsgroups if you need any additional help. Have a nice day! Best regards, Adams Qu, MCSE, MCDBA, MCTS Microsoft Online Support Microsoft Global Technical Support Center Get Secure! - www.microsoft.com/security ===================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | X-Tomcat-ID: 76388858 | References: <eAwbKPtcIHA.1376@TK2MSFTNGP02.phx.gbl> <$Org8MucIHA.5204@TK2MSFTNGHUB02.phx.gbl> <vuimr39kk22u54rtleji0hb3lu0jhr5k9b@4ax.com> <#HHOdr0cIHA.148@TK2MSFTNGP04.phx.gbl> <RA1ghQ7cIHA.6844@TK2MSFTNGHUB02.phx.gbl> <h99$zKCeIHA.4200@TK2MSFTNGHUB02.phx.gbl> <OKxZYN9gIHA.6084@TK2MSFTNGP06.phx.gbl> <3iGyoWChIHA.3660@TK2MSFTNGHUB02.phx.gbl> <k32gt31imeioibmqvjfcn0aa5pkucaocos@4ax.com> <y2AkmjNhIHA.4672@TK2MSFTNGHUB02.phx.gbl> <9n1jt31tl5pjln2d5tu92k4u8rbm9hm87i@4ax.com> <eu27g9fhIHA.5280@TK2MSFTNGP02.phx.gbl> | MIME-Version: 1.0 | Content-Type: text/plain [quoted text clipped - 202 lines] | | >>| Roy Harvey | | >>| Beacon Falls, CT Hi Jeffrey, DBCC SHRINKFILE may not help for this issue. Let me try to explain more about the issues you had seen. Generally only the data pages in a heaped table will not be compressed after a delete operation. A heaped table means a table which is not associated with a clustered index key. Regarding this issue, we first recommend that you check whether or not your problematic table is a heaped table. If it is a heaped table, please import your data to a physical table with clustered index. After that if you explicitly execute DELETE statements, you should be able to see the decrease of the data value in the result of sp_spaceused. However for the problem you saw in your original test with tempTable, it should not be related to heaped table since it had been created a heaped table, the problem should be related to ROLLBACK statement. It seemed that some LOB data were not cleaned up from pages after the ROLLBACK statement was executed. You can check this via running the following undocumented DBCC command: DBCC IND ( <database name>, <table name>, 1) Go From the result, we can see which pages are still left there. However this issue actually will not happen on DELETE operation for the table with clustered index. I performed a test at my side and actually I reproduced this issue. Indeed I found that some data with LOB data type were not cleaned up after ROLLBACK operation. It sounds not reasonable. I am afraid that I need to consult the product team to see what the mystery is here. I will reply you as soon as possible. If you have any other questions or concerns, please feel free to let me know. Best regards, Charles Wang Microsoft Online Community Support =========================================================== Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. =========================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ============================================================ SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Thanks, Charles and I appreciate all the help coming from MS online support team. Best regards, Jeffrey > Hi Jeffrey, > DBCC SHRINKFILE may not help for this issue. Let me try to explain more [quoted text clipped - 62 lines] > rights. > ========================================================= Hi Jeffrey, This is a quick note to let you know that we are still discussing this issue internally. I may need more time to get back to you. Appreciate your patience! Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi Jeffrey, I have reported this issue as a product issue to the product team and currently the product team is investigating this issue now. If there is any update, I will let you know. Best regards, Charles Wang Microsoft Online Community Support ========================================================= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ========================================================= SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. ========================================================= Hi all, I think I need to post a result for this orginal post here. It seems I can hardly reproduce this issue in my test enviornment, but it did exist in my production due to the huge concurrencies in production which cannot be easily reproduced in a test environment. What we finanlly decide is to partition this table based on date, and there will be a partition for each day, and the each partition is on a different file, and the next day, we will auto delete the yesterday's partition and create a new partition for tomorrow, and this is what is termed as the "sliding window" mangement of the partition. The benefits are two folds here 1. We reduce the delete tractions on the table, and this may improve our system performance 2. by dropping a partition, we also dropped the file on which the partition resides, and this guarantees there is no space unclaimed. Jeff > Hi all, > [quoted text clipped - 17 lines] > TIA, > Jeff_Yao I appreciate your providing closure on this. Thanks! Roy Harvey Beacon Falls, CT >Hi all, > [quoted text clipped - 38 lines] >> TIA, >> Jeff_Yao |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.