SHRINKFILE DBCC και SHRINKDATABASE εντολές ενδέχεται να μην λειτουργεί εξαιτίας της συμπληρωμένης sparsely κειμένου, ntext ή στήλες εικόνας

Μεταφράσεις άρθρων Μεταφράσεις άρθρων
Αναγν. άρθρου: 324432 - Δείτε τα προϊόντα στα οποία αναφέρεται το συγκεκριμένο άρθρο.
Ανάπτυξη όλων | Σύμπτυξη όλων


Οι εντολές SHRINKFILE DBCC και SHRINKDATABASE DBCC δεν μετακινήσετε δεδομένα και μειώστε το μέγεθος του αρχείου, αν τοEstimatedPagesη τιμή ισούται με τοMinimumSizeη τιμή που αναφέρεται, όταν ολοκληρώνεται η εντολή. Λόγω των ερωτημάτων τροποποίηση δεδομένων σε πίνακες με δυαδικό μεγάλο αντικείμενο (BLOB) δεδομένων τύποι)text,ntext,image), it is possible to have many uniform extents allocated that are sparsely populated with data (for example, only 1 of the 8 pages that the extent controls is allocated), which will cause the DBCC SHRINKFILE and the DBCC SHRINKDATABASE commands to over-estimate the number ofEstimatedPages, and the commands do not perform the shrink operations. Typically, in cases where this behavior is experienced, the size of a full database backup is up to 8 times smaller than the sizes of the files of the database after you run a DBCC SHRINKFILE on the database files.

Προτεινόμενη αντιμετώπιση

If the affected tables do not have to be continuously online while you correct this situation, you can use any one of the following workarounds to resolve this problem:
  • Use a SELECT INTO statement to transfer the whole table to a new table in the same database. Drop the original table, and then run a DBCC SHRINKFILE statement. Rename the new table to the original table name.
  • Use a SELECT INTO statement to transfer the whole table to a new table in a different database. Truncate the original table, and then run a DBCC SHRINKFILE statement. Transfer the data back to the original table.
  • Use the bulk copy program to copy the table out in native mode. Script out the table, and then drop the existing table. Run a DBCC SHRINKFILE statement. Create a new table, and then use the bulk copy program to copy the data back to the table.
  • Use Data Transformation Services (DTS) to transfer the whole database to a new database. SQL Server 7.0 Data Transformation Services may not transfer text and image data longer that is than 64 KB correctly. This problem does not apply to the SQL Server 2000 version of Data Transformation Services.Για πρόσθετες πληροφορίες, κάντε κλικ στον αριθμό του άρθρου παρακάτω, για να προβάλετε το άρθρο της Γνωσιακής Βάσης της Microsoft (Knowledge Base):
    257425FIX: DTS Object Transfer Does Not Transfer BLOB Data Greater Than 64
    You can look for text and image columns that are greater than 64 KB with the following query:
    SELECT MAX (DATALENGTH (TextImageColumn)) FROM TableName
    If the text and image data is greater than 64 KB, the transfer truncates the data and does not raise any error except in the DTS log files. If you have text or image data greater than 64 KB, use the information in the third bullet item for the tables that have text or image data. Next, use DTS for the rest of the items in the database.
If the database must be online or the size of the database is too large to move the data into an external file or another database, you can use the DBCC SHRINKFILE command with the EMPTYFILE option. To use this work around, follow these steps:
  1. If you are running Microsoft SQL Server 7.0 Service Pack 3, turn on trace flags 1180 and 1197.
  2. If you running Microsoft SQL Server 7.0 Service Pack 3 build 7.00.987, or later, including Service Pack 4, turn on trace flag 1180.
  3. If you have spread the database across several files for either drive space issues or performance reasons, create 1 or more additional files in the database by using the ALTER DATABASE command with a total size comparable to the results of the following query.
    select 'size in MB'=ceiling(sum(ei.pg_alloc)/128.000), sfg.groupname
    from extentinfo ei inner join sysfiles sf on ei.[file_id] = sf.fileid and ei.[file_id] != 1
     inner join sysfilegroups sfg on sf.groupid = sfg.groupid
    group by sfg.groupname
    ΣΗΜΕΙΩΣΗ: The preceding query uses the table that is created by the query in the "More Information" section of this article. Additionally, the file size totals are broken down by filegroup.

  4. Run a DBCC SHRINKFILE query, with the EMPTYFILE option as the second parameter, on each file that has sparsely populated blob uniform extents.
  5. You can track the progress of theDBCC SRHINKFILEcommand by creating one or more tables with the same schema as theextentinfotable from the "More Information" section of this article. Then run an "INSERT .. EXECimport_extentinfo" query to populate the data to the new tables. Use the following query to analyze the results:
    select [file_id], 'page count'=sum(pg_alloc), 'min page'=min(page_id), 'max page'=max(page_id) from extentinfo group by [file_id]
    Additionally, you can use the following query to keep track of when the DBCC EXTENTINFO command was run if the tables start with the same pattern of letters and are created near the time as the DBCC EXTENTINFO command:
    select crdate, [name] from sysobjects where type = 'U' and [name] like 'extent%' order by crdate
  6. When the DBCC SHRINKFILE query completes, run an ALTER DATABASE query to remove the old files from the database.
  7. You can possibly remove additional space by running a DBCC SHRINKFILE query with the TRUNCATEONLY option on the new files that were created in step 3.
  8. If you are running Microsoft SQL Server 7.0, turn off the trace flags you turned on in step 1 or 2.

Περισσότερες πληροφορίες

You can run the following Transact-SQL query from any query tool, such as Query Analyzer, if you are logged in as a user who hassysadminrights. You run the Transact-SQL query on SQL Server to determine if the DBCC SHRINKDATABASE command and the DBCC SHRINKFILE command cannot shrink files because of sparsely populated BLOB uniform extents.

ΣΗΜΕΙΩΣΗ: You must update the string '<database name="">' to the name of the problem database, and enclose the name in single quotation marks.</database>

use pubs
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8
   create table extentinfo 
   [file_id] smallint,
   page_id int,
   pg_alloc int,
   ext_size tinyint,
   obj_id int,
   index_id tinyint,
   pfs_bytes varbinary(10)
if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7
   create table extentinfo 
   [file_id] smallint,
   page_id int,
   pg_alloc int,
   ext_size tinyint,
   obj_id int,
   index_id tinyint,
   pfs_bytes varbinary(10),
   avg_used tinyint
create procedure import_extentinfo as
dbcc extentinfo('<database name>')
insert extentinfo exec import_extentinfo

select [file_id],obj_id, index_id, ext_size, 'actual extent count'=count(*), 'actual page count'=sum(pg_alloc),
 'possible extent count'=ceiling(sum(pg_alloc)*1.0/ext_size),
 'possible extents / actual extents' = (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*)
 from extentinfo where ext_size != 1 and index_id = 255
 group by [file_id],obj_id, index_id, ext_size
 having count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0
 order by obj_id, index_id, [file_id]
If you see result rows from the last query that haveindex_idvalues of 255, and thepossible extent / actual extentcolumn is less than 75, this database does have sparsely populated BLOB extents and can be reduced in size by the methods described in the "Resolution" section of this article.

ΣΗΜΕΙΩΣΗ: This query relies on the functionality of the DBCC EXTENTINFO command. Do not use the DBCC EXTENTINFO command outside this situation because the functionality may change or even be removed from future versions of Microsoft SQL Server.


Για πρόσθετες πληροφορίες, κάντε κλικ στους αριθμούς των άρθρων παρακάτω, για να προβάλετε τα άρθρα της Γνωσιακής Βάσης της Microsoft (Knowledge Base):
272220FIX: TEXT/IMAGE Data Storage Space Not Reclaimed Correctly
308627FIX: DBCC SHRINKDATABASE or DBCC SHRINKFILE May Expand Database with Text or Image Data


Αναγν. άρθρου: 324432 - Τελευταία αναθεώρηση: Τρίτη, 21 Δεκεμβρίου 2010 - Αναθεώρηση: 2.0
Οι πληροφορίες σε αυτό το άρθρο ισχύουν για:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
kbprb kbmt KB324432 KbMtel
Μηχανικά μεταφρασμένο
ΣΗΜΑΝΤΙΚΟ: Αυτό το άρθρο είναι προϊόν λογισμικού μηχανικής μετάφρασης της Microsoft και όχι ανθρώπινης μετάφρασης. Η Microsoft σάς προσφέρει άρθρα που είναι προϊόντα ανθρώπινης αλλά και μηχανικής μετάφρασης έτσι ώστε να έχετε πρόσβαση σε όλα τα άρθρα της Γνωσιακής Βάσης μας στη δική σας γλώσσα. Ωστόσο, ένα άρθρο που έχει προκύψει από μηχανική μετάφραση δεν είναι πάντα άριστης ποιότητας. Ενδέχεται να περιέχει λεξιλογικά, συντακτικά ή γραμματικά λάθη, όπως ακριβώς τα λάθη που θα έκανε ένας μη φυσικός ομιλητής επιχειρώντας να μιλήσει τη γλώσσα σας. Η Microsoft δεν φέρει καμία ευθύνη για τυχόν ανακρίβειες, σφάλματα ή ζημίες που προκύψουν λόγω τυχόν παρερμηνειών στη μετάφραση του περιεχομένου ή χρήσης του από τους πελάτες της. Επίσης, η Microsoft πραγματοποιεί συχνά ενημερώσεις στο λογισμικό μηχανικής μετάφρασης.
Η αγγλική έκδοση αυτού του άρθρου είναι η ακόλουθη:324432

Αποστολή σχολίων


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from