INF: SQL Server 7.0 ????????? ??? ?? ???? ???? ?? ??? ????

• Microsoft SQL Server 7.0, SHRINKFILE ?? SHRINKDATABASE ???? ??? shrinking ?? ??? ???? ?????? ?? ???? ???? ???????? ??? ????? ?? ????????? ?? ?????? ??????? ???? ??, ????? ?? ?? ?????? ??? ??? ????? ?? ??? truncation ?? ?? ??????? ?? ???? ???? ?? ?????? ???? ??? ?????, SHRINKFILE ?? ????? ???? ?? SHRINKDATABASE ???? ?????? ?????? ?? ????? ???? ?? truncates ???? ?? ???? ??? ???? ?? ??? ?? ??? ???? ?? ?? ???? ?????
• ???? ?? ???????? ?? ?????? ??????? ?? ?? ???? ???? ?? ??? ???? ??? ???? ???? ?? ????:
  
  
  • ???? ??? ???? ?? ???? ??? ?? ???? ???? ?? ??? ?? ???-??? ??????? ?? DBCC SHRINKFILE ?? ???? ???? ?????? ?? ??? ?? ???? ??? ?? ?????? ??? ?? ????????? ???? ?? ???? ???? ???? ???? ?? ??? DBCC SHRINKDATABASE ?? ????? ???? ???? ?? ???? ??? ??? ???? ?? ??????? ?? ????? ?? ??????? ???????? ?? ?????? ???? ?? ???? ??? ???? ?? ??? ??? ???????? ?? ???? ??? ??? ???? ??? ??? ?? ???????? ?????? ????? ???? ???
    
    
  • ????? ??? ???? ?? ??????? ??? ??? ????? ??? ????? ??? ?? ????? ?? ?????? ?? ??? ???? ?? ???? ???? ????? ??? ?? ????? ?? ?????? ?? ??????? ???? ?? ??? ?? DBCC SQLPERF (LOGSPACE) ???? ?? ????? ?? ???? ????
    
    
  • ??????? ???? ???? ??????? ??? ?? ?? ?? ????? ?? ??? ??????? ??? ?? ?? ??? ??????? ???? ??? ???????? ??? ??, ???? ??????? ??? 1 MB ?? ?? ???? ?? ???
    
    
  • ??????? ?? ??? ???? ???? ??????? ??? ????? (VLF) ???? ?? ??? shrunk ?? ???? ???, ?? ???? ???? ?? ???? ???? ?? ??? ?? ??? ????? ??? VLF ?? ???? ???? ?? ??? ??? ?? ????? ????? ???? ???? ?? ??? ??? Likewise, ??? ??? VLF ?? ?? ?????? ????? ??? ??, ?? ?? ???? ???? ??? VLF ?? ????? ?? ??? ??? ???? ??????? ?? ??? SQL Server ???????? ?????? ??? "??? ?????? ???????" ?? "????????? ??? ????? ???????????" ???? ?? ??????
  
  
  
• ????????? ??? ??? ?? "??????-??" ??? ??? ???? ???? ?? ?? ?? ???? ?? ??? ?? ??? ?? ?? ???? ?? VLFs ????, ????, ??/?? ??? ?? ??? ??? "?????" ?? "reusable" ????? ?? ???? ??? ??? ?? ???? ???? ?? ??? ???? ????? "?????" ??? ?? ??? ??? ????? ??, ? ???? ??? ??? ???? ?? ????? ????? ????? ??? ??, ?? ?? ???? ??? ???? ???? ???? VLFs. ????????? ?? ???? ???? ?? ??? ??? VLFs ??? ????? ?? ??? ??? ???? ?? truncated ????????? ??? ???? ??????? ??????? ?? ??? SQL Server ???????? ?????? ??? "????????? ??? Truncating" ???? ?? ??????

• ????? ????? ?????? ??????? ?? ?????????? ??????? ????? ?? ???? ?? ?? ???????? ?? ?????? ?? ???????? ???? ?? ???? DBCC SHRINKFILE ?? DBCC SHRINKDATABASE ???? ??? ??? ???????? ????, ?? ?????? ?? ??? invalidates ??? ????????? ??? ?????? ?? DBCC SHRINKFILE ?? DBCC SHRINKDATABASE ???? ????? ?? ??? ???? ??????? ?? ????? ????? ????? ?????? ???
  
  
• ????????? ???? ?? ??? ??? ????? ???? ?? ??? shrink ?? supposed ??? ???? ?? ??? ??????? ???? ??? ???
  
  
• ????????? ???? ?? ??? ??????, ???? ????, ?? unreplicated ????????? ???? ??? ??? ???? ?? ???, ??? ?? ??? ?? ????? ????:

  DBCC OPENTRAN (database_name)
  					

• ??? shrinkpoint ??????? ???? ?? ??? DBCC SHRINKFILE ?? DBCC SHRINKDATABASE ???? ?????? DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to members of the sysadmin fixed server role or thedb_ownerfixed database role, and are not transferable. For information about the differences between these commands, refer to the following topics in SQL Books Online (note the different parameters):
  
  

  DBCC SHRINKFILE     (file_name, target_size)
  DBCC SHRINKDATABASE (database_name, target_percent)
  					

• Create some dummy transactions to make the log wrap around and then issue a BACKUP command to truncate the log. The BACKUP statement is what actually attempts to shrink the log to the marked target size.
  
  Here is a sample of how to create a dummy transactions that wraps the log for a single logical log file and causes it to truncate, allowing for shrinkage. Modify the sample as needed for your environment.

     SET NOCOUNT ON
     DECLARE @LogicalFileName sysname,
             @MaxMinutes INT,
             @NewSize INT
  
     -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
     USE     [Test DB]              -- This is the name of the database
                                    -- for which the log will be shrunk.
     SELECT  @LogicalFileName = 'Test DB Log',  -- Use sp_helpfile to 
        -- identify the logical file 
        -- name that you want to shrink.
             @MaxMinutes = 10,      -- Limit on time allowed to wrap log.
             @NewSize    = 10       -- in MB
  
     -- Setup / initialize
     DECLARE @OriginalSize int
     SELECT @OriginalSize = size -- in 8K pages
       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)
  
     -- Wrap log and truncate it.
     DECLARE @Counter   INT,
             @StartTime DATETIME,
             @TruncLog  VARCHAR(255)
     SELECT  @StartTime = GETDATE(),
             @TruncLog = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
     -- Try an initial shrink.
     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)  -- the log has not shrunk    
           AND (@OriginalSize * 8 /1024) > @NewSize  -- The value passed in for new size is smaller than the current size.
       BEGIN -- Outer loop.
         SELECT @Counter = 0
         WHILE  ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
           BEGIN -- update
             INSERT DummyTrans VALUES ('Fill Log')  -- Because it is a char field it inserts 8000 bytes.
             DELETE DummyTrans
             SELECT @Counter = @Counter + 1
           END   -- update
         EXEC (@TruncLog)  -- See if a trunc of the log shrinks it.
       END   -- outer loop
     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
     PRINT '*** Perform a full database backup ***'
     SET NOCOUNT OFF
  					

  Check to see if the log has shrunk from its original size.Repeat the preceding steps if necessary. If the log is not shrinking, re-check the summary at the top of the article to see if you are encountering any of the common issues with shrinking the log.

1. Perform a full database backup of the master database.
2. Perform a full database backup of the user database. This is necessary because the SHRINK command is not logged and invalidates future transaction log backups unless a full database backup is completed.

????? ?? ???? ????

??????

????? ?? ???? ????