Database cleanup?

Question

Database cleanup?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

sqlservercenter (7/7/2008)
shrink it at each weekend.

Absolutely not... it's just going to grow again and all you're doing by shrinking it is cause fragmentation of the underlying files at the OS level.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

sqlservercenter (7/7/2008)
shrink it at each weekend.

Did you read the link I posted earlier in the thread before recommending this action? The link doesn't go into the details about why shrinking is disasterous for fragmentation, but you can find that online easily.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Carl Federl One Orange Chip Points: 25384 More actions · Answer 3

With an objective have having a reasonable size files that have sufficient free space to run the defragementation and support future needs, here are the detailed steps. The database will not be usable during this process.

1. Disable any maintenance jobs and any application access.

2. Backup the database.

3. Record the database recovery option and, if not simple, change to simple.

4. If there are any archiving or purge routines, run them now.

5. Rebuild the indexes.

6. Check the space used.

7. Get the space used by the largest table.

8. If the file sizes are significantly larger than the space used, shrink the file and then, run the rebuild index again. The final file size should be space used plus the size of the largest table plus future growth needs (anywhere from 3 to 6 months is about right).

9. Check the level of windows file fragementation and defragement if needed.

10. Change the database recovery option back to the original.

11 Enable any maintenance jobs

Commands are:

To defragment the indexes, run from a Windows command prompt:

sqlmaint.exe -RebldIdx -SupportComputedColumn -S server_name[\instance_name -D database_name

To get the total space used, run:

select sysfilegroups.groupid

,Files.FileId

,sysfilegroups.groupname

,Files.FileName

,Files.AllocatedMb

,Files.SpaceUsedMb

, Files.AllocatedMb - Files.SpaceUsedMb as SpaceFreeMb

fromdbo.sysfilegroups

JOIN(

SELECTsysfiles.FileId

,sysfiles.name AS FileName

,sysfiles.groupid

, (sysfiles.size * 8) / 1024 AS AllocatedMb

,( (CAST(FILEPROPERTY(sysfiles.name, 'SpaceUsed' ) AS int) * 8 ) / 1024) AS SpaceUsedMb

FROM dbo.sysfiles

) as Files

on sysfilegroups.groupid = Files.groupid

order by sysfilegroups.groupid

,Files.FileId

SQL = Scarcely Qualifies as a Language

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) prior to doing a windows file defrag it is strongly recommended to shut down sql server.

2) I sure would want to do a DBCC CHECKDB after all that work under simple recovery mode prior to releasing the database back to production ops.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

kellyrmartin-805750 SSChasing Mays Points: 645 More actions · Answer 5

Thanks once again for the breakdown Carl. Would the windows defrag untility work ok or should we look at another product?

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

Here are a few changes to the order of operations:

1. Disable any maintenance jobs and any application access.

2. Backup all databases

New steps:

3. For tempdb, note the actual size and if different than the specified, change the specified to the actual. An autogrow on tempdb is an expensive operation and can cause calls complaining "the system is slow"

4. Shutdown SQL Server and run the Window defrag utility - you may need to run more than once.

5. Run CHECKDB on all databases - if there are any errors, contact Microsoft.

The perform the remainder of steps starting with:

3. Record the database recovery option and, if not simple, change to simple.

SQL = Scarcely Qualifies as a Language

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

Article "Top Tips for Effective Database Maintenance" was just posted by

Paul S. Randal, SQL Server MVP, at http://technet.microsoft.com/en-us/magazine/cc671165.aspx

Provides a good background on physical optimization.

SQL = Scarcely Qualifies as a Language