StevensNet.com

...Because someone's gotta tell the story...

Blogs

To return to the main Blog List, click Full Blog Listing.

Tags

SQL
Technical

TSQL: Defragment and Shrink a MSSQL Database Automagically

Friday, March 21, 2008 in Technical Articles (Views: 4585)
One thing people seem to overlook a lot is maintenance of our systems. Most people are aware of the concept of defragmenting disks (and files), but how many people think about defragmenting SQL databases? They become fragmented as they are used.

Here is a simple query you can run as a scheduled task to enumerate all tables in a SQL database and then defrag each table.

DECLARE @DatabaseName varchar(255)
DECLARE @TableName varchar(255)
SET @DatabaseName='Master'
USE @DatabaseName
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

Additionally, you can add the following command to shrink the database.

dbcc shrinkdatabase (@DatabaseName, 1)

You can run this from OSQL as a scheduled task or as a SQL Job in SQL 2005.

Happy "Databas-ing"... :)

 

Related Blogs You May Be Interested In:


To leave a comment, please log in and/or register.