SQL ServerÈçºÎ½Ø¶Ï(Truncate)ºÍÊÕËõ(Shrink)ÊÂÎñÈÕÖ¾
µ±SQL Server½Ø¶ÏÊÂÎñÈÕ־ʱ£¬Ëü½ö½öÊÇÔÚÐéÄâÈÕÖ¾ÎļþÖÐ×ö¸ö±ê¼Ç£¬ÒԱ㲻ÔÙʹÓÃËü£¬È»ºó×¼±¸ÒÔÖØÓÃÐÎʽÀ´×ö±¸·Ý(¼ÙÈçÔËÔØÔÚÍêÕû»òÊÇÅúÁ¿ÈÕÖ¾»Ö¸´Ä£ÐÍ)¡£Ò²¾ÍÊÇ˵£¬ÔÚʹÓüòµ¥»Ö¸´Ä£ÐÍʱ£¬ÊÂÎñÈÕÖ¾°üÀ¨ÈçϵÄÈÕÖ¾¼Ç¼£º
µ±checkpoint·¢Éúʱ£¬ÐéÄâÈÕÖ¾Îļþ1¡¢2²»ÔÙ±»Ê¹Óã¬ÒòΪÊÂÎñ1¡¢2ÒѾ±»Ìá½»ÁË£¬¶øÇÒÈÕÖ¾¼Ç¼Ҳ²»ÔÙÐèÒª»Ø¹öÁË¡£È»ºóSQL ServerÖØÓÃÐéÄâÈÕÖ¾Îļþ1¡¢2£¬ÈçÏÂͼ£º
Õâ¾ÍÊÇÎÒÃÇËùÊìÖªµÄÊÂÎñÈÕÖ¾½Ø¶Ï¡£»ù±¾ÉÏ£¬ÊÂÎñÈÕÖ¾µÄ»î¶¯Çø¼äÒѾ±»½Ø¶ÏÁË£¬µ«ÊÇÊÂÎñÈÕÖ¾µÄÎïÀí´óС²»»á¸Ä±ä£¬³ý·ÇÊý¾Ý¿âʹÓÃ×Ô¶¯ÊÕËõµÄÊôÐÔÉèÖá£ÔÚÕâÖÖÇé¿öÏ£¬ÊÂÎñÈÕÖ¾¾Í»á¾¡¿ÉÄܵÄÔÚÎïÀíÉϽøÐÐÖÜÆÚÐÔµÄÊÕËõ¡£
ΪÁËÎïÀíÉϼõСÊÂÎñÈÕÖ¾µÄ´óС£¬ÊÕËõÊÂÎñÈÕÖ¾×÷ΪÒÑÖªµÄ·½·¨£¬ÄãÔÚʹÓÃʱ¿ÉÒÔÑ¡ÔñÏÂÃæÑ¡ÏîÖеÄÒ»ÖÖ£º
Ö´ÐÐ DBCC SHRINKDATABASEÃüÁî
Ö´ÐÐ DBCC SHRINKFILEÃüÁî
ÉèÖÃÊý¾Ý¿âµÄÊÂÎñÈÕÖ¾×Ô¶¯ÊÕËõÑ¡Ïî
ÐèҪעÒâµÄÊÇ£¬ÊÂÎñÈÕÖ¾½ö½öÄÜÊÕËõµ½ÐéÄâÈÕÖ¾ÎļþµÄ±ß½ç¡£ÏÂÃæÊǸöÀý×Ó¡£
ÎÒн¨ÁËÒ»¸öÊý¾Ý¿â£¬ËüÓÐ1MBµÄÊÂÎñÈÕÖ¾¿Õ¼ä£¬5MBµÄ×Ô¶¯Ôö³¤¿Õ¼ä¡£ÔËÐÐDBCC LOGINFOÏÔʾÈçÏ£º
ÕâÀïÓÐËĸö¿É±ä´óСµÄÐéÄâÈÕÖ¾Îļþ¡£È»ºóÎÒÊäÈëһЩÊý¾Ý£¬Õâ»áʹÊÂÎñÈÕÖ¾ Ôö³¤µ½5MB£º
ÔÚеÄ5MBÊÂÎñÈÕÖ¾Çø¼äÀïÃæн¨ÁË4¸öеÄÐéÄâÈÕÖ¾Îļþ¡£Ã¿Ò»¸öн¨µÄÐéÄâÈÕÖ¾Îļþ¶¼ÊÇ1310720bytes£¬Ã¿7¸öÐéÄâÈÕÖ¾ÎļþÕýÔÚʹÓÃʱ(״̬ÊÇ2)¡£ÎÒÏÖÔÚ±¸·ÝÊÂÎñÈÕÖ¾£¬Òò´Ë½«»á½Ø¶ÏÊÂÎñÈÕÖ¾£º
Ä¿Ç°½ö½öÓÐÒ»¸öÐéÄâÈÕÖ¾ÎļþÔÚʹÓÃ(µÚ7ÐУ¬×´Ì¬Îª2). ¼ÙÈçÎÒÏÖÔÚÓÃÏÂÃæµÄÃüÁÊÔ×Å°ÑÈÕÖ¾ÊÕËõµ½2M£º
DBCC SHRINKFILE ('AdventureWorks_log', 2)
ÒòΪ»î¶¯ÈÕÖ¾¼Ç¼ÊÇÐéÄâÈÕÖ¾Îļþ7£¬ËùÒÔSQL Server½ö½öɾ³ýÐéÄâÈÕÖ¾Îļþ8¡£Õâ´ÎÊÂÎñÈÕÖ¾´Ó7MBÊÕËõµ½4.7MB. SQL ServerÒ²ÔÚÊÂÎñÈÕÖ¾ÖÐн¨Á˼ٵÄÈë¿Ú£¬ÎªÁËÒƳý2MBµã֮ǰµÄ×î½ü»î¶¯ÈÕÖ¾¼Ç¼£¬ÒÔ±ãÓÚËü°ü¹üµ½ÐéÄâÈÕÖ¾Îļþ2£¨×¢Òâ״̬Ϊ2µÄÐУ©¡£
¼ÙÈçÏÖÔÚÔٴα¸·ÝÊÂÎñÈÕÖ¾µÄ»°£¬ÊÂÎñÈÕÖ¾»áÔٴα»½Ø¶Ï£¬ÏÖÔڻÇø¼ä¾ÍÊÇÐéÄâÈÕÖ¾Îļþ2ÁË¡£
Èç¹ûÎÒÏÖÔÚÔÙ³¢ÊÔÒ»´ÎÊÕËõÎļþµÄ»°£¬SQL ServerÔò»á³É¹¦µÄÊÕËõµ½2MB×óÓÒ£¬ÒòΪÈÕÖ¾µÄ»î¶¯Çø¼äÒѾ½Ó½ü2MBÁË¡£Îļþ±»ÊÕËõµ½×î½Ó½üÓÚÈÕÖ¾µÇ¼ÇʱµÄ´óС¡£ÕâʱDBCC LOGINFOµÄÊä³öÈçÏ£º
ÊÂÎñÈÕÖ¾Îļþ´óСΪ2359296bytes(ÐéÄâÈÕÖ¾Îļþ´óС×ÜÁ¿Òª¼ÓÉÏ8192×Ö½ÚµÄÍ·ÐÅÏ¢)
ËùÒÔÈç¹ûÄã·¢ÏÖÄã²»ÄÜÊÕËõÊÂÎñÈÕÖ¾µ½Ò»¸öÖ¸¶¨µÄ·¶Î§£¬ÔËÐÐDBCC LOGINFO£¬È»ºó¼ì²éÐéÄâÈÕÖ¾ÎļþµÄ·¶Î§£¬ÅªÇå³þÿһ¸
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃ棬ºóÃæ ......
select *
from
(select [id]=row_number() over (order by getdate()),
date=convert(varchar(8),dateadd(dd,number,'2010-01-01'),112)
from
(select number from master..spt ......
if exists(select * from sysobjects where name='atzk')--ÅжÏÊÇ·ñ´æÔڴ˱í
drop table atzk
go
create table atzk
(
nid int identity(1,1) primary key,--nid×Ô¶¯±àºÅ£¬²¢ÉèΪÖ÷¼ü¡£
mytitle varchar(50) not null,--֪ͨµÄ±êÌâ¡£
mycontents varchar(200)--·¢²¼Í¨ÖªµÄÄÚÈÝ¡£
) ......
Sql Server ÓÐÈçϼ¸Ö־ۺϺ¯ÊýSUM¡¢AVG¡¢COUNT¡¢COUNT(*)¡¢MAX ºÍ MIN£¬µ«ÊÇÕâЩº¯Êý¶¼Ö»ÄܾۺÏÊýÖµÀàÐÍ£¬ÎÞ·¨¾ÛºÏ×Ö·û´®¡£Èçϱí:AggregationTable
Id Name
1 ÕÔ
2 Ç®
1 Ëï
1 Àî
2 ÖÜ
Èç¹ûÏëµÃµ½ÏÂͼµÄ¾ÛºÏ½á¹û
Id Name
1 ÕÔËïÀî
2 Ç®ÖÜ
ÀûÓÃSUM¡¢AVG¡¢COUNT¡¢COUNT(*)¡¢MAX ºÍ MINÊÇÎÞ·¨×öµ½µ ......
×î½üÐèÒª²âÊÔÒ»¸öÏîÄ¿, ÐèÒªSQL SERVER 2008, ÔÚ΢ÈíÏÂÔØÆÀ¹À°æºó½øÐа²×°
Öм䷢ÏÖÁËÖÖÖÖ°²×°Ê§°ÜµÄÎÊÌ⣬¶Ô¸Ã¹ý³Ì½øÐÐÁ˼Ǽ£¬Ï£Íû¸øÓöµ½´ËÀàÎÊÌâµÄÅóÓÑÃǹ²Í¬Ì½ÌÖ£¬ÓÐËù°ïÖú£º
1. WINDOWS 2003, WINDOWS 7 ÐÂ×°µÄʱºò¿ÉÐУ¬µ«ÊÇÈç¹ûÓÐɾ³ýºóÖØа²×°ÔòÎÞ·¨Ôٴΰ²×°
2. ¸ù¾ÝÍøÉÏÎÞÊýÏÈÇýµÄʧ°ÜÌáʾ£¬ ½øÐÐÁËÎÞÊýÖØÊÔ£¬ ......