In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-16 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Some summaries in practical work
1. In practice, shrinking data files is more common than shrinking the database, so it is not recommended to shrink the database directly.
2. Shrinking can easily lead to waiting. The waitresource value of shrinking session corresponding to sys.sysprocesses is similar to 15 PAGEIOLATCH_EX, which is similar to the field lastwaittype value of PAGEIOLATCH_SH or PAGEIOLATCH_EX.
3. When shrinking data files, do not shrink them all at once. You can shrink about 5G at a time. For example, if DataFile1 has 32G, each contraction is as follows.
USE UserDB
DBCC SHRINKFILE (DataFile1, 27000)
GO
DBCC SHRINKFILE (DataFile1, 22000)
GO
4. The free space of the data file can be viewed by combining sys.master_files and FILEPROPERTY (name,'SpaceUsed').
5. The 100% progress of contraction can be seen in the field percent_complete of sys.dm_exec_requests.
6. Remember to rebuild the index after shrinking
Alter index all on table_name rebuild with (>
Shrink the official document https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-2017 of the database
DBCC SHRINKDATABASE
(database_name | database_id | 0
[, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY}]
)
[WITH NO_INFOMSGS]
Database_name | database_id | 0
The name of the database or ID to shrink. 0 specifies that the current database is used.
Target_percent
An integer, the percentage of the remaining free space required in the database file after the database is shrunk.
NOTRUNCATE
Move the assigned page from the end of the file to the unassigned page in front of the file. This operation compresses the data in the file.
The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when you specify NOTRUNCATE, the database does not seem to shrink.
NOTRUNCATE applies only to data files. NOTRUNCATE does not affect log files.
TRUNCATEONLY
Release all available space at the end of the file to the operating system. Do not move any pages within the file. The data file shrinks only to the last specified extent. If specified using TRUNCATEONLY, target_percent is ignored.
TRUNCATEONLY will affect the log file. To truncate only the data file, use DBCC SHRINKFILE.
The following example reduces the size of the data and log files in the UserDB database to leave 10% free space in the database.
DBCC SHRINKDATABASE (UserDB, 10)
GO
Shrink the official document https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017 of the data file
DBCC SHRINKFILE
(
{file_name | file_id}
{[, EMPTYFILE]
| | [, target_size] [, {NOTRUNCATE | TRUNCATEONLY}]] |
}
)
[WITH NO_INFOMSGS]
File_name | file_id
The logical name or identification (ID) number of the file to shrink, and the name or file_id field that participates in the sys.master_files view.
Target_size
Integer, the new size of the file in MB. If not specified, DBCC SHRINKFILE zooms out to the file creation size.
NOTRUNCATE
Move the allocated page at the end of the data file to the unallocated page area at the beginning of the file, whether or not target_percent is specified. The operating system does not reclaim the free space at the end of the file, and the physical size of the file does not change. Therefore, if you specify NOTRUNCATE, the file looks as if it did not shrink. NOTRUNCATE applies only to data files. Log files are not affected. The FILESTREAM filegroup container does not support this option.
TRUNCATEONLY
Frees up all available space at the end of the file to the operating system, but does not move any pages within the file. The data file shrinks only to the last allocated area. If specified using TRUNCATEONLY, target_size is ignored.
The TRUNCATEONLY option does not move the information in the log, but removes the invalid VLF at the end of the log file. The FILESTREAM filegroup container does not support this option.
The following example shrinks the size of the data file named DataFile1 in the UserDB database to 10 MB.
USE UserDB
DBCC SHRINKFILE (DataFile1, 10)
GO
View the size of the data file
Select name,size*8/1024 MB from sys.master_files where database_id=db_id (NumberDBNAME')
View the shrinking space of the data file. For the result, see the Availabesize_MB field value.
Select name, size*8/1024 as Totalsize_MB, CAST (FILEPROPERTY (name,'SpaceUsed') AS int) * 8 Universe 1024 as Usedsize_MB
Size*8/1024-CAST (FILEPROPERTY (name, 'SpaceUsed') AS int) * 8 take 1024 AS Availabesize_MB
From sys.master_files where database_id=db_id (NumberDBNAME')
Check the progress of contraction by 100%. This statement is to be executed under the specified database.
SELECT DB_NAME (database_id) AS Exec_DB
, percent_complete
, CASE WHEN estimated_completion_time < 36000000
THEN'0' ELSE''END + RTRIM (estimated_completion_time/1000/3600)
+':'+ RIGHT ('0' + RTRIM ((estimated_completion_time/1000)% 3600Universe 60), 2)
+':'+ RIGHT ('0' + RTRIM ((estimated_completion_time/1000)% 60), 2) AS [Time Remaining]
, b.text as tsql
, *
FROM SYS.DM_EXEC_REQUESTS
Cross apply sys.dm_exec_sql_text (sql_handle) as b
WHERE command LIKE 'DbccFilesCompact%'-- and database_id=db_id (' cardorder')
ORDER BY 2 DESC
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.