£¨Ò»£©¹ÒÆð²Ù×÷

ÔÚ°²×°Sql»òsp²¹¶¡µÄʱºòϵͳÌáʾ֮ǰÓйÒÆðµÄ°²×°²Ù×÷£¬ÒªÇóÖØÆô£¬ÕâÀïÍùÍùÖØÆôÎÞÓ㬽â¾ö°ì·¨£º

µ½HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager

ɾ³ýPendingFileRenameOperations

£¨¶þ£©ÊÕËõÊý¾Ý¿â

--Öؽ¨Ë÷Òý

DBCC REINDEX

DBCC INDEXDEFRAG

--ÊÕËõÊý¾ÝºÍÈÕÖ¾

DBCC SHRINKDB

DBCC SHRINKFILE

£¨Èý£©Ñ¹ËõÊý¾Ý¿â

dbcc shrinkdatabase(dbname)

£¨ËÄ£©×ªÒÆÊý¾Ý¿â¸øÐÂÓû§ÒÔÒÑ´æÔÚÓû§È¨ÏÞ

exec sp_change_users_login 'update_one','newname','oldname'

go

£¨Î壩¼ì²é±¸·Ý¼¯

RESTORE VERIFYONLY from disk='E:\dvbbs.bak'

£¨Áù£©ÐÞ¸´Êý¾Ý¿â

ALTER DATABASE [dvbbs] SET SINGLE_USER

GO

DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK

GO

ALTER DATABASE [dvbbs] SET MULTI_USER

GO

--CHECKDB ÓÐ3¸ö²ÎÊý:

--REPAIR_ALLOW_DATA_LOSS 

--  Ö´ÐÐÓÉ REPAIR_REBUILD Íê³ÉµÄËùÓÐÐÞ¸´£¬°üÀ¨¶ÔÐкÍÒ³½øÐзÖÅäºÍÈ¡Ïû·ÖÅäÒÔ¸ÄÕý·ÖÅä´íÎ󡢽ṹÐлòÒ³µÄ´íÎó£¬ÒÔ¼°É¾³ýÒÑË𻵵ÄÎı¾¶ÔÏó¡£ÕâЩÐÞ¸´¿ÉÄܻᵼÖÂһЩÊý¾Ý¶ªÊ§¡£ÐÞ¸´²Ù×÷¿ÉÒÔÔÚÓû§ÊÂÎñÏÂÍê³ÉÒÔÔÊÐíÓû§»Ø¹öËù×öµÄ¸ü¸Ä¡£Èç¹û»Ø¹öÐÞ¸´£¬ÔòÊý¾Ý¿âÈԻẬÓдíÎó£¬Ó¦¸Ã´Ó±¸·Ý½øÐлָ´¡£Èç¹ûÓÉÓÚËùÌṩÐÞ¸´µÈ¼¶µÄÔµ¹ÊÒÅ©ij¸ö´íÎóµÄÐÞ¸´£¬Ôò½«ÒÅ©ÈκÎÈ¡¾öÓÚ¸ÃÐÞ¸´µÄÐÞ¸´¡£ÐÞ¸´Íê³Éºó£¬±¸·ÝÊý¾Ý¿â¡£ 

--REPAIR_FAST ½øÐÐСµÄ¡¢²»ºÄʱµÄÐÞ¸´²Ù×÷£¬ÈçÐÞ¸´·Ç¾Û¼¯Ë÷ÒýÖеĸ½¼Ó¼ü¡£ÕâЩÐÞ¸´¿ÉÒԺܿìÍê³É£¬²¢ÇÒ²»»áÓжªÊ§Êý¾ÝµÄΣÏÕ¡£ 

--REPAIR_REBUILD Ö´ÐÐÓÉ REPAIR_FAST Íê³ÉµÄËùÓÐÐÞ¸´£¬°üÀ¨ÐèÒª½Ï³¤Ê±¼äµÄÐÞ¸´£¨ÈçÖؽ¨Ë÷Òý£©¡£Ö´ÐÐÕâЩÐÞ¸´Ê±²»»áÓжªÊ§Êý¾ÝµÄΣÏÕ¡£

--DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY

SQL SERVERÈÕÖ¾Çå³ýµÄÁ½ÖÖ·½·¨

ÔÚʹÓùý³ÌÖдó¼Ò¾­³£Åöµ½Êý¾Ý¿âÈÕÖ¾·Ç³£´óµÄÇé¿ö£¬ÔÚÕâÀï½éÉÜÁËÁ½ÖÖ´¦Àí·½·¨¡­¡­

·½·¨Ò»

Ò»°ãÇé¿öÏ£¬SQLÊý¾Ý¿âµÄÊÕËõ²¢²»Äܴܺó³Ì¶ÈÉϼõСÊý¾Ý¿â´óС£¬ÆäÖ÷Òª×÷ÓÃÊÇÊÕËõÈÕÖ¾´óС£¬Ó¦µ±¶¨ÆÚ½øÐд˲Ù×÷ÒÔÃâÊý¾Ý¿âÈÕÖ¾¹ý´ó

1¡¢ÉèÖÃÊý¾Ý¿âģʽΪ¼òµ¥Ä£Ê½£º´ò¿ªSQLÆóÒµ¹ÜÀíÆ÷£¬ÔÚ¿ØÖÆ̨¸ùĿ¼ÖÐÒÀ´Îµã¿ªMicrosoft SQL Server-->SQL Server×é-->Ë«»÷´ò¿ªÄãµÄ·þÎñÆ÷-->Ë«»÷´ò¿ªÊý¾Ý¿âĿ¼-->Ñ¡ÔñÄãµÄÊý¾Ý¿âÃû³Æ£¨ÈçÂÛ̳Êý¾Ý¿âForum£©-->È»ºóµã»÷ÓÒ¼üÑ¡ÔñÊôÐÔ-->Ñ¡ÔñÑ¡Ïî-->ÔÚ¹ÊÕÏ»¹Ô­µÄģʽÖÐÑ¡Ôñ¡°¼òµ¥¡±£¬È»ºó°´È·¶¨±£´æ

2¡¢ÔÚµ±Ç°Êý¾Ý¿âÉϵãÓÒ¼ü£¬¿´ËùÓÐÈÎÎñÖеÄÊÕËõÊý¾Ý¿â£¬Ò»°ãÀïÃæµÄĬÈÏÉèÖò»Óõ÷Õû£¬Ö±½ÓµãÈ·¶¨

3¡¢ÊÕËõÊý¾Ý¿âÍê³Éºó£¬½¨Ò齫ÄúµÄÊý¾Ý¿âÊôÐÔÖØÐÂÉèÖÃΪ±ê׼ģʽ£¬²Ù×÷·½·¨Í¬µÚÒ»µã£¬ÒòΪÈÕÖ¾ÔÚһЩÒì³£Çé¿öÏÂÍùÍùÊǻָ´Êý¾Ý¿âµÄÖØÒªÒÀ¾Ý

·½·¨¶þ

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

        @MaxMinutes INT,

        @NewSize INT

USE     tablename             -- Òª²Ù×÷µÄÊý¾Ý¿âÃû

SELECT  @LogicalFileName = 'tablename_log',  -- ÈÕÖ¾ÎļþÃû

@MaxMinutes = 10,               -- Limit on time allowed to wrap log.

        @NewSize = 1                  -- ÄãÏëÉ趨µÄÈÕÖ¾ÎļþµÄ´óС(M)

-- Setup / initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size 

  FROM sysfiles

  WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' + 

        CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 

        CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

  FROM sysfiles

  WHERE name = @LogicalFileName

CREATE TABLE DummyTrans

  (DummyColumn char (8000) not null)

DECLARE @Counter   INT,

        @StartTime DATETIME,

        @TruncLog  VARCHAR(255)

SELECT  @StartTime = GETDATE(),

        @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

DBCC SHRINKFILE (@LogicalFileName, @NewSize)

EXEC (@TruncLog)

-- Wrap the log if necessary.

WHILE     @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired

      AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)  

      AND (@OriginalSize * 8 /1024) > @NewSize  

  BEGIN -- Outer loop.

    SELECT @Counter = 0

    WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

      BEGIN -- update

        INSERT DummyTrans VALUES ('Fill Log')  

        DELETE DummyTrans

        SELECT @Counter = @Counter + 1

      END   

    EXEC (@TruncLog)  

  END   

SELECT 'Final Size of ' + db_name() + ' LOG is ' +

        CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 

        CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'

  FROM sysfiles 

  WHERE name = @LogicalFileName

DROP TABLE DummyTrans

SET NOCOUNT OFF