1. Home
  2. Cloud & Dedicated Servers
  3. MS SQL
  4. How do I Defrag my MS SQL database?

How do I Defrag my MS SQL database?

Step 1


Because of the inserts, updates and deletes, the data and the index pages can get fragmented (just as your PC). The follwoing script can help you defrag your database:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 
--Re-indexes the specified database CREATE PROCEDURE usp_DefragDatabase -- We don't use sysname because it might not be long enough. -- sysname is 128 chars, so we use double that. @dbname nvarchar(256) AS BEGIN -- Quote the database name with brackets DECLARE @quoteddbname nvarchar(256) SET @quoteddbname = quotename( @dbname )   -- The outer EXEC is so we can do USE, not allowed in stored procs -- The inner EXEC does the actual reindex on each table in the -- specified database   EXEC(' USE '+ @quoteddbname +' DECLARE @sTableName sysname DECLARE PKMS_Tables CURSOR LOCAL FOR select table_name from information_schema.tables where table_type = ''base table'' order by 1 OPEN PKMS_Tables FETCH NEXT FROM PKMS_Tables INTO @sTableName   WHILE @@FETCH_STATUS = 0 BEGIN select @sTablename = quotename(@sTablename, ''[]'') EXEC('' DBCC DBREINDEX ( ''+@sTableName+'') WITH NO_INFOMSGS'') FETCH NEXT FROM PKMS_Tables INTO @sTableName END CLOSE PKMS_Tables') END GO

Step2


Once you have created the stored procedure (by executing the script above).
You will need to run ‘usp_DefragDatabase’ against your database in order to defrag it.

Content retrieved from: https://support.appliedi.net/kb/a291/how-do-i-defrag-my-ms-sql-database.aspx.

Updated on November 11, 2019

Was this article helpful?

Related Articles

Need Support?
Can't find the answer you're looking for? Don't worry we're here to help!
CONTACT SUPPORT

Leave a Comment