sql server ÈÕÖ¾ÎļþµÄÊÕËõ
ÍêÈ«±¸·Ý»òÈÕÖ¾±¸·ÝËä˵¶¼ÓнضÏÈÕÖ¾µÄ¹¦ÄÜ,µ«ÊDz»»áÊÕËõÈÕÖ¾ÎļþµÄ¿Õ¼ä·µ»Ø¸ø²Ù×÷ϵͳ.
Èç¹ûÄãÏ뽫ÈÕÖ¾ÎļþµÄ¿Õ¼ä·µ»Ø¸ø²Ù×÷ϵͳµÄ»°,Ö»ÓÐÒ»ÖÖ·½·¨,¾ÍÊÇÊÕËõÊý¾Ý¿â(Ñ¡ÔñÈÕÖ¾Îļþ)
ZTÒ»¸öÏà¹ØÌû×Ó,¹©²Î¿¼!
ÎïÀíÈÕÖ¾Îļþ£º
Õâ¸ö±È½ÏºÃÀí½â£¬ÊµÊµÔÚÔڵĶ«Î÷£¬Êý¾Ý¿âĿ¼ÏÂÃæµÄ.ldfÎļþ¾ÍÊÇ£¬ÓÐЩÈËϲ»¶¸Äºó׺£¬¸Ð¾õ²»´óºÃ£¬Êý¾Ý¿âµÄÊÂÎñÈÕÖ¾¼Ç¼¾ÍÔÚÕâÀïÃæ
ÐéÄâÈÕÖ¾£º
ÏàÐŶàÊýÈËÓÐÕâ¸ö¸Ð¾õ£¬ÐéÄâÕâ¸ö×ÖÑÛ×ÜÊÇÉñÃصĴúÃû´Ê£¬ÐéÄâ¸ö·¹µº°®ÎÒϲ»¶£¬µ«ÐéÄâÈÕÖ¾£¬ÐéÄâÄڴ棬ÐéÄâ¡£¡£¡£¡££¬¿´Á˾ÍÌÖÑá¡£½âÊÍÓ¦¸ÃÊÇÕâÑùµÄ£¬¶ÔÓÚÒ»¸ö»ò¶à¸öÁ¬ÐøµÄÎïÀíÈÕÖ¾Îļþ£¬SQL SERVERÔÚÕâЩÎļþµÄÄÚ²¿ÓÖ»®·Ö³ÉÁ˶à¸öСµÄÎļþ£¬³ÆΪÐéÄâÈÕÖ¾Îļþ£¬ËûÊÇÈÕÖ¾ÎļþÊÕËõºÍÈÕÖ¾½Ø¶ÏµÄ×îСµ¥Î»£¬±ÈÈçÎïÀíÈÕÖ¾ÎļþÊÇ400M£¬ÄÚ²¿»®·ÖÁË4¸ö100MµÄÐéÄâÎļþ£¬ÊÕËõʱÄãµÃµ½µÄÊÇ300M£¬200M£¬²»¿ÉÄܵõ½239M£¬¶ÔÓÚÒ»¸öÎïÀíÎļþ£¬»á»®·Ö³É¶àÉÙ¸öÐéÄâÎļþ£¬Õâ¸öÓÉSQL×Ô¼ºÎ¬»¤£¬Î¨Ò»¿ÉÒÔÈ˹¤¸ÉÔ¤µÄÊÇÖ¸¶¨½Ï´óµÄÎïÀíÈÕÖ¾Îļþ£¬²¢Ö¸¶¨½Ï´óµÄÔö³¤±ÈÀý£¬ÕâÑù¿ÉÄÜÐéÄâÎļþµÄ¿éÍ·»á´óµã£¬ÊýÁ¿»áÉٵ㣬ϵͳµÄά»¤¿ªÏú»áµÍÒ»µã
Âß¼ÈÕÖ¾£º
²»ÒªÍ·ÔΣ¬Ó²×ÅͷƤ¿´°É£¡£¡£¡¸Ð¾õÕâ¸öÓ¦¸ÃÊÇÊý¾Ý¿âÊÂÎñÈÕÖ¾µÄÕæʵдÕÕ£¬ÎïÀíÈÕÖ¾ÎļþºÃ±ÈÊÇÒ»¸öÈÝÆ÷£¬ÀïÃæÈÝÄɵÄÊÇÈÕÖ¾¼Ç¼£¬ÕâЩ¼Ç¼¾Í³ÆΪÂß¼ÈÕÖ¾£¬´ÓÎïÀíÈÕÖ¾ÎļþµÄÆðµã¿ªÊ¼£¬Âß¼ÈÕ־˳ÐòµÄÉú³É£¬¼Ç¼ÏÂÊý¾Ý¿âÀï·¢ÉúµÄÿ¸öÊÂÎñ£¬ÕâЩÊÂÎñ±»´òÉÏÒ»¸ö±êÇ©£¬LSN£¬Ë³ÐòµÄÅÅÁÐÏÂÀ´£¬ÕâÑùÂß¼ÈÕÖ¾¾ÍÔÚÎïÀíÈÕÖ¾ÎļþÄÚÂýÂýµÄ³É³¤£¬Ö±µ½³äÂúÁËËû£¬Õâ¸öʱºòÎïÀíÈÕÖ¾Îļþ¾Í»á×Ô¶¯Ìí¼ÓеĿռ䣬ÒÔ¼ÌÐøÇ°ÃæµÄ²½Ö裬ÕâÖÖÇé¿öÊÇ×îÖ±½ÓµÄÒ»ÖÖ£¨´ÓÀ´²»½Ø¶ÏÈÕÖ¾£¬»ù±¾ÉϾÍÊÇÕâÑùµÄ£©£¬µ«ÊÂʵÉÏÍùÍùÊǸ´ÔӵĶà
¼ì²âµã(checkpoint)ºÍ»Ö¸´ÖÜÆÚ(recovery interval)£º
checkpoint²»ÊÇÓÃÓÚ¼ì²éÊý¾ÝÊÇ·ñÍêÕû£¬Ò³ÃæÁ¬½ÓÊÇ·ñÕýÈ·µÄ£¬ËûÊÇÓÉϵͳά»¤µÄÒ»¸ö½ø³Ì£¨ÄãÒ²¿ÉÒÔÊÖ¹¤µÄÖ´ÐУ©£¬ÓÃÓÚ½«¸ßËÙ»º´æÀïµÄÔàҳˢе½´ÅÅÌ£¬Á½ÕßµÄÅäºÏËãÊÇΩÃîΩФ£¬µ±»º´æÖеÄÔàÒ³»ýÀÛµ½Ò»¶¨µÄÊýÁ¿£¬SQL¹À¼ÆÑÝËãÕâЩÔàÒ³Òª»¨µÄʱ¼ä¿ìÒª½Ó½üÉ趨µÄrecovery interval£¨·ÖÖÓ£©Ê±£¬ÏµÍ³¾Í»á²úÉúÒ»¸öcheckpoint£¬ËùÒÔcheckpointµÄ²úÉú²»ÊǶ¨Ê±µÄ£¬ËüÓÉrecovery intervalºÍÊý¾Ý¿âµÄ¸üÐÂƵ·±¶È¾ö¶¨¡£Èç¹ûÄãµÄÊý¾Ý¿âÓÀÔ¶²»ÓÃÖØÆô£¬ÓÀÔ¶²»»á³öÏÖʲô¹ÊÕÏ£¬¾ÍÕâôһֱÔËÐÐÏÂÈ¥£¬ÄÇôcheckpointºÍrecovery interval¾ÍûÓÐÏëÏóÖеÄÖØÒªÁË£¬SQL×ÜÊ
Ïà¹ØÎĵµ£º
drop table father;
create table father(
id int identity(1,1) primary key,
name varchar(20) not null,
age int not null
)
drop table mother;
create table mother(
id int identity(1,1),
name varchar(20) not null,
age int not null,
husban ......
ʹÓà CONVERT£º
CONVERT (data_type[(length)], expression [, style])
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),\'-\',\'\'),\' \',\'\'),\':\',\'\')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 ) ......
--3.3.1 ʹÓÃÓα귨½øÐÐ×Ö·û´®ºÏ²¢´¦ÀíµÄʾÀý¡£
--´¦ÀíµÄÊý¾Ý
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
--ºÏ²¢´¦Àí
--¶¨Òå½á¹û¼¯±í±äÁ¿
DECLARE @t TABLE(col1 varchar(10),col2 varch ......
Use DatabaseName
--DB shrink
--»ñÈ¡database ¿ÕÓà¿Õ¼ä£¬¡¡¾ö¶¨ÊÇ·ñ×÷shrinkDB
exec [DBNAME].dbo.sp_spaceused
DBCC ShrinkDB(DBNAME)
--Log file shrink
Use DatabaseName
GO
Alter Database DatabaseName Set Recovery Simple
GO
Alter Database DatabaseName Set Recovery Full
GO
DBCC SHRINKFILE ('Log ......