Sql server Blog Forum

Helping SQL server DBAs and Developers

INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

INDEX DEFRAGMENTATION SCRIPT SQL 2000

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

Use the DBCC SHOWCONTIG statement. It will display the fragmentation information.

Here I used 3 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild

2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics

3. If the above conditions have false then updated the statistics

Before you are going to run the procedure creates the tables provided for history propose.

Note: This Index Defragmentation script only works for SQL server 2000. You can also get defragmentation script for SQL server 2005/2008 here.

USE MSDB;
go
CREATE TABLE [DBO].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[TABLE_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[actiON_taken] [VARCHAR](35) NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go
--Archive the data's in master DB
USE MASTER;
go
CREATE TABLE [DBO].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[TABLE_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[actiON_taken] [VARCHAR](35) NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go

– Sproc

USE msdb
go
CREATE  PROC [DBO].[indexdefragmentatiON] @p_dbname SYSNAME
/*
Summary:        Remove the Index Fragmentation to improve the query performance
Contact:           Muthukkumaran Kaliyamoorhty SQL DBA
Description:      This Sproc will take the fragmentation details and do three kinds of work.
                       1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
                       2. Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
                       3. Update the statistics the first two conditions is false
ChangeLog:
Date                             Coder                                                                           Description
2011-03-11                    Muthukkumaran Kaliyamoorhty                                      created
*************************All the SQL keywords should be written in upper case*************************
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT
SET @db_name=@p_dbname
--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
   id INT identity,
   ObjectName char(255),
   ObjectId INT,
   IndexName varchar(1000),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL);
INSERT INTO #tempfrag
EXEC ('use ['+@db_name+'];DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES');
CREATE TABLE #tempschema
(
obj SYSNAME,
ind SYSNAME,
TABLE_schema SYSNAME,
frag FLOAT,
page INT
)
INSERT INTO #tempschema
EXEC('
SELECT
d.objectname,
d.indexname ,
i.TABLE_schema,
d.logicalfrag ,
d.countpages
FROM #tempfrag d JOIN ['+@db_name+'].INFORMATION_SCHEMA.TABLES i ON (d.OBJECTNAME=i.TABLE_NAME)
')
SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag
TRUNCATE TABLE msdb.DBO.dba_defrag_maintenance_history
WHILE (@min_id<=@max_id)
BEGIN
SELECT
@tab_name=d.objectname,
@ind_name=d.indexname ,
@schema_name=t.TABLE_schema,
@frag=d.logicalfrag ,
@pages=d.countpages
FROM #tempfrag d JOIN #tempschema t ON(d.objectname=t.obj)
WHERE id=@min_id
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL )
BEGIN
IF (@frag>=30 AND @pages>1000)
BEGIN
EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;DBCC DBREINDEX("['+@db_name+'].[DBO].['+@tab_name +']",['+@ind_name+'])')
INSERT INTO msdb.DBO.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 15 AND 29) AND @pages>1000 )
BEGIN
EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;DBCC INDEXDEFRAG( ['+@db_name+'],['+@tab_name +'], ['+@ind_name+'] )')
EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']' )
INSERT INTO msdb.DBO.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics if the first two conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
ELSE
BEGIN
EXEC ('USE ['+@db_name+'];SET QUOTED_IDENTIFIER OFF;UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']'  )
INSERT INTO msdb.DBO.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',GETDATE())
END
END
SET @min_id=@min_id+1
END
--------------------------------------------------------------------------------------------------------------------------------------
--Archive the fragmentation details for future reference
--------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO master.DBO.dba_defrag_maintenance_history
SELECT * FROM msdb.DBO.dba_defrag_maintenance_history
END

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Difference between truncating and shrinking the transaction log file

by Muthukkumaran kaliyamoorthy Published on: March 7, 2011
Comments: 20 Comments
Tags: SQL truncating and shrinking, truncating and shrinking, truncating log file
Categories:DBA

Difference between truncating and shrinking the transaction log file

I have noticed in many forum the frequently asked question is “Truncating and shrinking the log file both are same”

The answer is obviously NO. These both are totally different. Before going into discussion, let’s read the transaction log file architecture.

Truncating the transaction log:

Truncating a log file will change one or more VLF file’s status from active to inactive and mark the space for reuse. SQL server will change the VLF from active to inactive and reuses it automatically (Just like a circular).

When does it truncate and reuse it automatically?

It depends upon your setting: recovery model and backup type.

SQL server runs a CHECKPOINT an every time truncate/mark the inactive portion of the VLF files and reuse it automatically, When a database is in simple recovery model.

SQL server runs the CHECKPOINT an every time truncate/mark the inactive portion of the VLF files and reuse it automatically, When you perform the log backup in under the FULL or BULK LOGGED Recovery model.

You can truncate the log file (VLF) manually by using the TRUNCATE_ONLY or NO_LOG commands.

Here is the test:

Create DATABASE test

ALTER DATABASEtest SET RECOVERY full

BACKUP DATABASE test TO DISK ='e:\backup\test.trn'
BACKUP LOG test WITH TRUNCATE_ONLY

Now run the log backup.

BACKUP LOG test TODISK='e:\backup\test.trn'

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

Check the error log by using below command

SP_READERRORLOG 0,1,'BACKUP LOG WITH TRUNCATE_ONLY'

BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.

The simple recovery model should be used to automatically truncate the transaction log.

Note: When you use this command you can’t do regular log backup and point in time recovery too. This command will truncate the log files (VLF) and break the log chain. To maintain the log chain again you have to run full or differential backup.

Microsoft has removed this command in SQL server 2008 onwards.

What to do in SQL server 2000 and 2005?

You can use the ‘3031’ trace flag to stop the user to perform the deprecated option.

DBCC TRACEON (3031,1)

Let’s check,

BACKUP LOG test WITH TRUNCATE_ONLY

Ha…SQL server throwing the following error
Msg 8309, Level 16, State 1, Line 1BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated. The simple recovery model should be used to automatically truncate the transaction log.

Shrinking the transaction log:

Shrinking the log file will reduce the physical log file size. Shrinking the log file is not best practice until you don’t have any other option. OK, How to avoid the shrinking?

Frequently run the log backup and maintain the log size as small as possible.

Alter the database and point the log file to another drive/Add new drive.

If your company ready to accept the data loss (minimum like 2 to 3 hours) then puts the database in simple recovery mode and run the differential backup every 2 to 3 hours. It will maintain the log file as much as but not for the continuous huge transaction database.

You can manually shrink the log file by using the below command.

Important note: Shrinking the data/log file will increase the fragmentation. Try to truncate the log file instead of shrinking. This means running the log backup more frequently.

SELECT name FROM SYS.MASTER_FILES WHERE DB_NAME(database_id)='test'

Test_log is the logical name of “test DB”.

DBCC SHRINKFILE (Test_Log)

Sometimes your shrink command will not shrink the database. When there is an active portion of log is available OR the transaction is not closed (not yet committed).

Here is the code to find you can able to shrink your database files or not. It will give you a clear picture about your database files used and free space.

use Databasename

SELECT DB_NAME() AS DBNAME,
NAME AS FILENAME,
SIZE/128.0 AS CURRENTSIZE_MB,
SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)  AS USEDSPACE_MB,
SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,
PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC,
CASE WHEN IS_PERCENT_GROWTH = 0
THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '
ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +
CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'
ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'
END AS AUTO_GROW
FROM SYS.MASTER_FILES
WHERE DATABASE_ID = DB_ID()

See the Shrink result :

DbId	FileId	CurrentSize	MinimumSize	UsedPages	EstimatedPages
5	2	305760	70	305760	64

Alright the shrinking doesn’t reduce the file size.

Let’s check 1) open transaction by using DBCC OPENTRAN.2) Check the virtual log file status using DBCC loginfo().

Step:1

DBCC OPENTRAN()

Transaction information for database ‘test’.

Oldest active transaction:

SPID (server process ID): 54

UID (user ID) : -1

Name : INSERT

LSN : (197:11774:2)

Start time : Mar 7 2011 4:18:44:240PM

SID : 0×01

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

‘Sys.master_files’ will return the size of each file.

SELECT size/128,type_desc FROM SYS.MASTER_FILES WHERE DB_NAME(database_id)='test'

Step:2

‘DBCC LOGINFO()’ will return the status of the log file.

If the status is 2 VLF in use (Active)

If the status is 0 VLF not in use (Inactive)

Look the BOL for more about DBCC loginfo().Then, How to shrink the log file?

Ok…Wait until the open transaction finished.

Check the log_reuse_wait_descsys.databases by running below command and see what value it returns. If you want you can automate the script to your Non Prod servers. It will help to minimize the database running out of space.

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name='test'

--Look the BOL for more about  log_reuse_wait_desc.

ALTER DATABASE [Test] SET RECOVERY SIMPLE WITH rollback immediate
DBCC SHRINKFILE(Test_Log)
ALTER DATABASE [Test] SET RECOVERY FULL WITH rollback immediate

Note: When you use this command you can’t do regular log backup and point in time recovery too. This command will truncate the log files (VLF) and break the log chain. To maintain the log chain again you have to run full or differential backup.

Conclusion

Shrinking the log file will reduce the physical log file size and truncating the log file will change one or more VLF files from active to inactive and marked the space for reuse.

Thanks to Paul Randal and Kalen Delaney.

My special thanks go to GilaMonster (Gail Shaw).