RS First Dynamics NAV Blog


...from NAV 3.60 to NAV 2013
Archivio Posts
Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Shrink defrag databases - Test Script

Shrink defrag databases

An the end, only you can determine whether you want to shrink or not. With above information, you hopefully have enough to go on when making that decision. You probably know by now what I think of regular shrinking. Not to talk about autoshrink. :-)

Below you find the T-SQL code I ran to prove that shrinking of a database will generate the same amount of transaction log records as the amount of data which was moved. (The script uses the 'NUL' filename as backup destination. This will "write" the backup data to the "
bit bucket". I.e., the data isn't saved anywhere. This is very practical for testing but risking to state te obvious, do not use this backup destination for your production work!)

SCRIPT
--Script to show that shrink produces a lot of log record. 
SET NOCOUNT ON
USE 
master
IF DB_ID('shrink_test'IS NOT NULL DROP DATABASE shrink_test
GO

CREATE DATABASE shrink_test
ON PRIMARY 
(NAME shrink_test_dataFILENAME N'c:\shrink_test_data.mdf'
,SIZE 5MBMAXSIZE 200MBFILEGROWTH 10%) 
LOG ON  
(NAME shrink_test_logFILENAME N'c:\shrink_test_log.ldf'
,SIZE 3MBMAXSIZE 200MBFILEGROWTH 10%) 
GO 

ALTER DATABASE shrink_test SET RECOVERY FULL

--Make sure the database isn't in auto-truncate mode for the log
BACKUP DATABASE shrink_test TO DISK = 'NUL' 

USE shrink_test 
CREATE TABLE 
(
 
c1 INT IDENTITY CONSTRAINT PK_shrink_test PRIMARY KEY NONCLUSTERED
,c2 CHAR(3000DEFAULT 'hello'


DECLARE @i INT 
SET 
@i 
WHILE @i <= 40000  
BEGIN 
  INSERT INTO 
DEFAULT VALUES 
  IF 
@i%500 --Truncate log on every 500'th row 
    
BACKUP LOG shrink_test TO DISK = 'NUL' 
  
SET @i @i 
END 
SELECT 
COUNT(c1AS "Number of rows before delete, should be 40000"
FROM t  
GO 

--Delete some rows, in a loop so log doesn't grow! 
DECLARE @i INT 
SET 
@i 
WHILE @i <= 20000  
BEGIN 
  DELETE FROM 
WHERE c1 @i 
  
IF @i%500 --Truncate log on every 500'th row 
    
BACKUP LOG shrink_test TO DISK = 'NUL' 
  
SET @i @i 
END 
SELECT 
COUNT(c1AS "Number of rows after delete, shuld be 20000"
FROM t  

--Empty the log
BACKUP LOG shrink_test TO DISK = 'NUL' 

--Database file should be large (160MG on my machine), logfile small (3MB)
SELECT namesize*8192/(1024*1024AS sizeInMB FROM sysfiles 
GO 

--This shrink might now produce a lot of log record as 20000 rows will be moved! 
CHECKPOINT
DBCC 
SHRINKFILE (shrink_test_data40)

--Database file should now be small, but logfile large
SELECT namesize*8192/(1024*1024AS sizeInMB FROM shrink_test..sysfiles

--My result, the data file shrunk to 80MB and the log file grew from 3MB to 146MB!!!

USE master
GO
DROP DATABASE shrink_test

Categoria: Sql Server ALL
mercoledì, 01 set 2010 Ore. 09.20

Messaggi collegati


Statistiche
  • Views Home Page: 455.213
  • Views Posts: 869.387
  • Views Gallerie: 0
  • n° Posts: 343
  • n° Commenti: 0
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003