unallocated space无法释放问题[紧急]

ChengKing 2012-10-19 09:38:48
sql server 2008 r2


使用sp_spaceused命令发现:
unallocated space

非常大,50G中有35G是 unallocated space 这个应当怎么释放掉?


用一般的数据库和文件收缩无法释放。

谢谢!
...全文
1075 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
SQL77 2012-10-22
  • 打赏
  • 举报
回复
[Quote=引用 17 楼 的回复:]
已经解决了此问题。 原因是数据文件 中有一个page坏掉了,通过pageid查询不到这个页的所属(table/index etc),这个页是一个死页。

把数据全部导到新库(根据pageid得不到是哪个对象的数据,所以只能全导了),不受那个坏页的限制了,这样解决就好。

shrinkfile执行过程中出错是因为这个操作试图移动extent时报错,而extend中包含那个坏页。导数据的方式是……
[/Quote]
GX。。。

所以一般都要定期DBCC CHECKDB()进行检查。如果有出错。可进行适当修复。
ChengKing 2012-10-22
  • 打赏
  • 举报
回复
已经解决了此问题。 原因是数据文件 中有一个page坏掉了,通过pageid查询不到这个页的所属(table/index etc),这个页是一个死页。

把数据全部导到新库(根据pageid得不到是哪个对象的数据,所以只能全导了),不受那个坏页的限制了,这样解决就好。

shrinkfile执行过程中出错是因为这个操作试图移动extent时报错,而extend中包含那个坏页。导数据的方式是复制,而不是移动所以不会出错。

lyhabc桦仔 2012-10-20
  • 打赏
  • 举报
回复
如果数据库有完整备份,使用备份来恢复数据库
如果没有备份使用DBCC CHECKDB
使用dbcc checkdb 的repair_rebuild参数:不丢失数据
不行的话使用repair_allow_data_loss :有可能丢失数据
lyhabc桦仔 2012-10-20
  • 打赏
  • 举报
回复
运行DBCC CHECKDB检查一下数据库
ChengKing 2012-10-20
  • 打赏
  • 举报
回复
在使用中 unesed空间一般比 unallocated 空间难 回收,要涉及到对表进行聚焦索引整理。 但这里问题比较奇怪,在SHRINKFILE执行过程中又出现一个下面错误:

---------------------------------------------------------------------------

DBCC SHRINKFILE: 无法移动页1:5362183,因为该页所属的分区已删除。

(1 行受影响)

DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。


(1 行受影响)

DBCC 执行完毕。如果DBCC 输出了错误信息,请与系统管理员联系。

---------------------------------------------------------------


运行了几秒钟就提示如上错误。根据 我现在的分析怀疑很可能是 文件损坏导致错误了。 这个页所在分区找不到,致后面的所有 分区称动都没有执行。 导致这个移动事务失败。














汤姆克鲁斯 2012-10-19
  • 打赏
  • 举报
回复
[Quote=引用 10 楼 的回复:]

Reserved下面的unused可以通过重建聚焦索引解决,但对unallocated space没有用。这些方法都试过了。


现在想释放掉:unallocated space,还有没有好的办法?
[/Quote]
整理完索引碎片了吗
写个循环多次运行DBCC SHRINKFILE

declare @i int=1
while @i<500
begin
DBCC SHRINKFILE(1,100)
DBCC SHRINKFILE(2,0)
set @i=@i+1
waitfor delay '00:00:10'
end
發糞塗牆 2012-10-19
  • 打赏
  • 举报
回复
重建了索引,能把数据重组。这样有可能空出某些区,由于收缩是基于区一级的操作。所以收缩起来才有意义,如果本来可以集中在一个区的数据零散分布于多个区,收缩是没有效果的。
KevinLiu 2012-10-19
  • 打赏
  • 举报
回复
你指定收缩的TARGET了吗?
ChengKing 2012-10-19
  • 打赏
  • 举报
回复
Reserved下面的unused可以通过重建聚焦索引解决,但对unallocated space没有用。这些方法都试过了。


现在想释放掉:unallocated space,还有没有好的办法?
KevinLiu 2012-10-19
  • 打赏
  • 举报
回复
不重建索引的话可能根本收缩不了的,SQL Server管理数据库文件的级别是区,重建索引可以腾出很多空区,这样就可以收缩掉了。

如果是HEAP表的话建议重新创建表然后将数据导入到新表然后改名,否则可能无法收缩。

汤姆克鲁斯 2012-10-19
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 的回复:]

重建一下聚集索引再收缩咯
[/Quote]
应该是先收缩,在重建索引吧,收缩之后会产生索引碎片的
百年树人 2012-10-19
  • 打赏
  • 举报
回复
在频繁进行增删的表上重建索引试试

dbcc dbreindex('表名','',90)
發糞塗牆 2012-10-19
  • 打赏
  • 举报
回复
重建一下聚集索引再收缩咯
汤姆克鲁斯 2012-10-19
  • 打赏
  • 举报
回复
EXEC sp_spaceused @updateusage='true'
汤姆克鲁斯 2012-10-19
  • 打赏
  • 举报
回复
dbcc shrinkfile
对数据库文件和日志文件分别收缩

另外使用sp_spaceused的时候
exec sp_spaceused true 这样会更新一下统计信息
不加true你看到的可能不是最新的
ChengKing 2012-10-19
  • 打赏
  • 举报
回复
sql server 2008 r2


使用sp_spaceused命令发现:
unallocated space

非常大,50G中有35G是 unallocated space 这个应当怎么释放掉?

[分配的保留空间只占25%左右,但未分配空间太大了,怎么释放掉]


用一般的数据库和文件收缩无法释放。

比较急,各位有没有好的办法



--------------------------------------------------------------------------------
xuam 2012-10-19
  • 打赏
  • 举报
回复
DBCC SHRINKFILE (DB_log, n)
极品老土豆 2012-10-19
  • 打赏
  • 举报
回复
....那是未分配的空间。。。
查询数据库的状态信息: ------------------------------Data file size---------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') drop table #dbsize create table #dbsize (Dbname varchar(30),dbstatus varchar(20),Recovery_Model varchar(10) default ('NA'), file_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),Free_Space_MB decimal(20,2) default (0)) go insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) exec sp_msforeachdb 'use [?]; select DB_NAME() AS DbName, CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), sum(size)/128.0 AS File_Size_MB, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB from sys.database_files where type=0 group by type' go -------------------log size-------------------------------------- if exists (select * from tempdb.sys.all_objects where name like '#logsize%') drop table #logsize create table #logsize (Dbname varchar(30), Log_File_Size_MB decimal(20,2)default (0),log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0)) go insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) exec sp_msforeachdb 'use [?]; select DB_NAME() AS DbName, sum(size)/128.0 AS Log_File_Size_MB, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB from sys.database_files where type=1 group by type' go --------------------------------database free size if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') drop table #dbfreesize create table #dbfreesize (name varchar(50), database_size varchar(50), Freespace varchar(50)default (0.00)) insert into #dbfreesize(name,database_size,Freespace) exec sp_msforeachdb 'use ?;SELECT database_name = db_name() ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') ,''unallocated space'' = ltrim(str(( CASE WHEN dbsize >= reservedpages THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 ELSE 0 END ), 15, 2) + '' MB'') FROM ( SELECT dbsize = sum(convert(BIGINT, CASE WHEN type = 0 THEN size ELSE 0 END)) ,logsize = sum(convert(BIGINT, CASE WHEN type <> 0 THEN size ELSE 0 END)) FROM sys.database_files ) AS files ,( SELECT reservedpages = sum(a.total_pages) ,usedpages = sum(a.used_pages) ,pages = sum(CASE WHEN it.internal_type IN ( 202 ,204 ,211 ,212 ,213 ,214 ,215 ,216 ) THEN 0 WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id ) AS partitions' ----------------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') drop table #alldbstate create table #alldbstate (dbname varchar(25), DBstatus varchar(25), R_model Varchar(20)) --select * from sys.master_files insert into #alldbstate (dbname,DBstatus,R_model) select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases --select * from #dbsize insert into #dbsize(Dbname,dbstatus,Recovery_Model) select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' insert into #logsize(Dbname) select dbname from #alldbstate where DBstatus <> 'online' insert into #dbfreesize(name) select dbname from #alldbstate where DBstatus <> 'online' select d.Dbname,d.dbstatus,d.Recovery_Model, (file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace from #dbsize d join #logsize l on d.Dbname=l.Dbname join #dbfreesize fs on d.Dbname=fs.name order by Dbname
比较简短的C语言教程,英文资料 1.0 Introduction 1.1 The main() Function 1.2 Include Files 1.3 Whitespace 1.4 The Preprocessor 1.4.1 The #define Directive 1.4.2 Comments 1.4.3 The #include Directive 1.4.4 The #ifdef/#else/#endif Directives 1.4.5 More Directives 1.4.6 Predefined Macros 2.0 Basic Data Types 2.1 Signed Integer Types 2.2 Unsigned Integer Types 2.3 Integer Overflow 2.4 Real Data Types 2.5 BCC32 Implementation 2.6 The void Type 3.0 Control Flow 3.1 The if/else/endif Statements 3.2 Compound Statements 3.3 Nested if Statements 3.4 The switch/case Statement 3.5 The for Statement 3.6 The while Statement 3.7 The do/while Statement 3.8 The break Statement 3.9 The continue Statement 3.10 The goto Statement 3.11 The return Statement 4.0 Expressions and Operators 4.1 Basic Arithmetic Operators 4.2 Promotion and Casting 4.3 More Arithmetic Operators 4.4 Assignment Operators 4.5 Bitwise Operators 4.6 Relational Operators 4.7 Logical Operators 4.8 The Conditional Operator The C Cheat Sheet Revision 1 Copyright ? 2000 Andrew Sterian iv 4.9 The Comma Operator 4.10 Operator Precedence and Association 5.0 Program Structure 5.1 Declaring Functions 5.2 Calling Functions 5.3 Local Variables 5.4 Global Variables 6.0 Advanced Data Types 6.1 Arrays 6.2 Structures 6.3 Pointers 6.4 Strings 6.4.1 String Pointers 6.4.2 String Operations 6.5 Enumerated Types 6.6 Bitfields 6.7 Unions 7.0 Advanced Programming Concepts 7.1 Standard I/O 7.1.1 Opening Files 7.1.2 Writing to File Pointers 7.1.3 Reading from File Pointers 7.1.4 The stdin/stdout/stderr Streams 7.2 Dynamic Memory Allocation 7.3 Memory Manipulation 7.4 Declaring New Types 7.5 Pointers to Functions 7.6 Command-Line Parameters 8.0 Multi-File Programs 8.1 Basic Concepts 8.2 Include Files as Interfaces 8.3 Object Files and Linking 8.4 The Details of the Compilation Process 9.0 The Standard C Library 9.1 Assertion Checking 9.2 Character Classification 9.3 Error Reporting 9.4 Buffer Manipulation 9.5 Non-Local Jumps 9.6 Event Signalling 9.7 Variable-Length Argument Lists 9.8 Miscellaneous Functions 9.9 String Handling 9.10 Time Functions 9.11 Floating-Point Math 9.12 Standard I/O 10.0 Tips, Tricks, and Caveats 10.1 Infinite Loops 10.2 Unallocated Storage 10.3 The Null Statement 10.4 Extraneous Semicolons 10.5 strcmp is Backwards 10.6 Unterminated Comments 10.7 Equality and Assignment 10.8 Assertion Checking 10.9 Error Checking 10.10 Programming Style 11.0 Differences between Java and C
Introduction The usual implementation of malloc and free are unforgiving to errors in their callers' code, including cases where the programmer overflows an array, forgets to free memory, or frees a memory block twice. This often does not affect the program immediately, waiting until the corrupted memory is used later (in the case of overwrites) or gradually accumulating allocated but unused blocks. Thus, debugging can be extremely difficult. In this assignment, you will write a wrapper for the malloc package that will catch errors in the code that calls malloc and free. The skills you will have learned upon the completion of this exercise are pointer arithmetic and a greater understanding of the consequences of subtle memory mistakes. Logistics Unzip debugging_malloc.zip into an empty directory. The files contained are as follows: File(s): Function: debugmalloc.c Contains the implementation of the three functions you will be writing. This is the one file you will be editing and handing in. debugmalloc.h Contains the declaration of the functions, as well as the macros that will call them. driver.c Contains main procedure and the code that will be calling the functions in the malloc package dmhelper.c, dmhelper.h Contain the helper functions and macros that you will be calling from your code grader.pl Perl script that runs your code for the various test cases and gives you feedback based on your current code debugmalloc.dsp Exercise 3 project file debugmalloc.dsw Exercise 3 workspace file tailor.h, getopt.c, getopt.h Tools that are used only by the driver program for I/O purposes. You will not need to know what the code in these files do. Others Required by Visual C++. You do not need to understand their purpose Specification Programs that use this package will call the macros MALLOC and FREE. MALLOC and FREE are used exactly the same way as the malloc() and free() functions in the standard C malloc package. That is, the line void *ptr = MALLOC ( n ) ;will allocate a payload of at least n bytes, and ptr will point to the front of this block. The line FREE(ptr);will cause the payload pointed to by ptr to be deallocated and become available for later use. The macros are defined as follows: #define MALLOC(s) MyMalloc(s, __FILE__, __LINE__) #define FREE(p) MyFree(p, __FILE__, __LINE__) The __FILE__ macro resolves to the filename and __LINE__ resolves to the current line number. The debugmalloc.c file contains three functions that you are required to implement, as shown: void *MyMalloc(size_t size, char *filename, int linenumber); void MyFree(void *ptr, char *filename, int linenumber); int AllocatedSize(); Using the macros above allow MyMalloc and MyFree to be called with the filename and line number of the actual MALLOC and FREE calls, while retaining the same form as the usual malloc package. By default, MyMalloc and MyFree() simply call malloc() and free(), respectively, and return immediately. AllocatedSize() should return the number of bytes currently allocated by the user: the sum of the requested bytes through MALLOC minus the bytes freed using FREE. By default, it simply returns 0 and thus is unimplemented. The definitions are shown below: void *MyMalloc(size_t size, char *filename, int linenumber) { return (malloc(size)); } void MyFree(void *ptr, char *filename, int linenumber) { free(ptr); } int AllocatedSize() { return 0; } Your job is to modify these functions so that they will catch a number of errors that will be described in the next section. There are also two optional functions in the debugmalloc.c file that you can implement: void PrintAllocatedBlocks(); int HeapCheck(); PrintAllocatedBlocks should print out information about all currently allocated blocks. HeapCheck should check all the blocks for possible memory overwrites. Implementation Details To catch the errors, you will allocate a slightly larger amount of space and insert a header and a footer around the "requested payload". MyMalloc() will insert information into this area, and MyFree() will check to see if the information has not changed. The organization of the complete memory block is as shown below: Header Checksum ... Fence Payload Footer Fence Note:MyMalloc() returns a pointer to the payload, not the beginning of the whole block. Also, the ptr parameter passed into MyFree(void *ptr) will point to the payload, not the beginning of the block. Information that you might want to store in this extra (header, footer) area include: a "fence" immediately around the requested payload with a known value like 0xCCDEADCC, so that you can check if it has been changed when the block is freed. the size of the block a checksum for the header to ensure that it has not been corrupted (A checksum of a sequence of bits is calculated by counting the number of "1" bits in the stream. For example, the checksum for "1000100010001000" is 4. It is a simple error detection mechanism.) the filename and line number of the MALLOC() call The errors that can occur are: Error #1: Writing past the beginning of the user's block (through the fence) Error #2: Writing past the end of the user's block (through the fence) Error #3: Corrupting the header information Error #4: Attempting to free an unallocated or already-freed block Error #5: Memory leak detection (user can use ALLOCATEDSIZE to check for leaks at the end of the program) To report the first four errors, call one of these two functions: void error(int errorcode, char *filename, int linenumber); errorcode is the number assigned to the error as stated above. filename and linenumber contain the filename and line number of the line (the free call) in which the error is invoked. For example, call error(2, filename, linenumber) if you come across a situation where the footer fence has been changed. void errorfl(int errorcode, char *filename_malloc, int linenumber_malloc, char *filename_free, int linenumber_free); This is the same as the error(), except there are two sets of filenames and line numbers, one for the statement in which the block was malloc'd, and the other for the statement in which the block was free'd (and the error was invoked). The fact that MyMalloc() and MyFree() are given the filename and line number of the MALLOC() and FREE() call can prove to be very useful when you are reporting errors. The more information you print out, the easier it will be for the programmer to locate the error. Use errorfl() instead of error() whenever possible. errorfl() obviously cannot be used on situations where FREE() is called on an unallocated block, since it was not ever MALLOC'd. Note: You will only be reporting errors from MyFree(). None of the errors can be caught in MyMalloc() In the case of memory leaks, the driver program will call AllocatedSize(), and the grader will look at its return value and possible output. AllocatedSize() should return the number of bytes currently allocated from MALLOC and FREE calls. For example, the code segment: void *ptr1 = MALLOC(10), *ptr2 = MALLOC(8); FREE(ptr2); printf("%d\n", AllocatedSize()); should print out "10". Once you have gotten to the point where you can catch all of the errors, you can go an optional step further and create a global list of allocated blocks. This will allow you to perform analysis of memory leaks and currently allocated memory. You can implement the void PrintAllocatedBlocks() function, which prints out the filename and line number where all currently allocated blocks were MALLOC()'d. A macro is provided for you to use to print out information about a single block in a readable and gradeable format: PRINTBLOCK(int size, char *filename, int linenumber) Also, you can implement the int HeapCheck() function. This should check all of the currently allocated blocks and return -1 if there is an error and 0 if all blocks are valid. In addition, it should print out the information about all of the corrupted blocks, using the macro #define PRINTERROR(int errorcode, char *filename, int linenumber), with errorcode equal to the error number (according to the list described earlier) the block has gone through. You may find that this global list can also allow you to be more specific in your error messages, as it is otherwise difficult to determine the difference between an overwrite of a non-payload area and an attempted FREE() of an unallocated block. Evaluation You are given 7 test cases to work with, plus 1 extra for testing a global list. You can type "debugmalloc -t n" to run the n-th test. You can see the code that is being run in driver.c. If you have Perl installed on your machine, use grader.pl to run all the tests and print out a table of results. There are a total of 100 possible points. Here is a rundown of the test cases and desired output (do not worry about the path of the filename): Test case #1 Code char *str = (char *) MALLOC(12); strcpy(str, "123456789"); FREE(str); printf("Size: %d\n", AllocatedSize()); PrintAllocatedBlocks(); Error # None Correct Output Size: 0 Points worth 10 Details 10 points for not reporting an error and returning 0 in AllocatedSize() Test case #2 Code char *str = (char *) MALLOC(8); strcpy(str, "12345678"); FREE(str); Error # 2 Correct Output Error: Ending edge of the payload has been overwritten. in block allocated at driver.c, line 21 and freed at driver.c, line 23 Points worth 15 Details 6 pts for catching error 3 pts for printing the filename/line numbers 6 pts for correct error message Test case #3 Code char *str = (char *) MALLOC(2); strcpy(str, "12"); FREE(str); Error # 2 Correct Output Error: Ending edge of the payload has been overwritten. in block allocated at driver.c, line 28 and freed at driver.c, line 30 Points worth 15 Details 6 pts for catching error 3 pts for printing the filename/line numbers 6 pts for correct error message Test case #4 Code void *ptr = MALLOC(4); *ptr2 = MALLOC(6); FREE(ptr); printf("Size: %d\n", AllocatedSize()); PrintAllocatedBlocks(); Error # None Correct Output Size: 6 Currently allocated blocks: 6 bytes, created at driver.c, line 34 Points worth 15 Details 15 pts for not reporting an error and returning 6 from AllocatedSize Extra for printing out the extra block Test case #5 Code void *ptr = MALLOC(4); FREE(ptr); FREE(ptr); Error # 4 Correct Output Error: Attempting to free an unallocated block. in block freed at driver.c, line 43 Points worth 15 Details 15 pts for catching error Extra for correct error message Test case #6 Code char *ptr = (char *) MALLOC(4); *((int *) (ptr - 8)) = 8 + (1 << 31); FREE(ptr); Error # 1 or 3 Correct Output Error: Header has been corrupted.or Error: Starting edge of the payload has been overwritten. in block allocated at driver.c, line 47 and freed at driver.c, line 49 Points worth 15 Details 9 pts for catching error 6 pts for a correct error message Test case #7 Code char ptr[5]; FREE(ptr); Error # 4 Correct Output Error: Attempting to free an unallocated block. in block freed at driver.c, line 54 Points worth 15 Details 15 pts for recognizing error Extra for printing correct error message Test case #8 (Optional) Code int i; int *intptr = (int *) MALLOC(6); char *str = (char *) MALLOC(12); for(i = 0; i < 6; i++) { intptr[i] = i; } if (HeapCheck() == -1) { printf("\nCaught Errors\n"); } Error # None Correct Output Error: Ending edge of the payload has been overwritten. Invalid block created at driver.c, line 59 Caught Errors Points worth Extra Details "Caught Errors" indicates that the HeapCheck() function worked correctly. Extra points possible. Your instructor may give you extra credit for implementing a global list and the PrintAllocatedBlocks() and HeapCheck() functions.

22,209

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧