1

Just moved from wrong forum i paste here my question:

I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor application is compatible only max to SQL Server 2005. I found that the database never got a maintenance plan: it's about 14 years old, always up and, fortunately, always backed up (daily full backup, 4 hour differential and 10 minutes transaction log backup).

At first glance DB is split in 4 .mdf files (10Gb, 30Gb, 2Gb, 2Gb) and one .ldf file of 35Gb. .ldf is quite big because transactions log are max 100 mb. For what office folks say during those years there were 2 - 3 big deletion, but they don't know anything else. Server is a bit old (IBM x346, 8Gb Ram and only 70gb of free hd space), running W2003 R2 32bit with /pae and SQL Server 2005 with AWE.

At first glance index fragmentation is HUGE.

What are the best practices to start 'revive' :-) this situation ?

Improve this question

1 Answer 1

Reset to default
1

It is certainly possible to use a Maintenance Plan to handle the fragmentation issues. However, many people use the free scripts at: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I am part of the 'many people'. These scripts by default implement the suggested standards for reducing the fragmentation of indexes. So, if you implement the script with one of the many sample settings further down the page, it should bring your indexes into better order.

Your database is not particularly huge, so scheduling an online index rebuild on a relatively quiet time, perhaps Saturday night if that is a low use time, and let it run.

If you link to the home page (http://ola.hallengren.com/) you will also find scripts for database backups and integrity checking. It seems that you have a good backup schedule overall, though I do not know your retention period, so you do not need to change unless you personal see the need.

Second, regarding the large LOG file, you can look into the DBCC SHRINKFILE() command to reset the size of the log file. On the other hand why bother unless you need the space.

DBCC SHRINKFILE ('LogFileName', );

If it does not shrink the first time, run CHECKPOINT, backup the log again, and try to shrink the file again. Once the high order portion of the log is empty (through the log backups) the file will be able to shrink.

Improve this answer
2
  • In a few months space will be a problem. So i need to think the best practice to shrink that log and be prepared. Actually there's no 'quiet' night cause the business run every day from 2.30 am to 4.00 pm (it's newspaper factory) so maybe i need to schedule something from 4.01 pm and 2.00 am :-) but how can be sure how much time it will take ?
    – Kartone
    Commented Mar 31, 2014 at 13:11
  • @Kartone - I reorganize indexes online once a week for a 1.5 terabyte server in less than 2 hours. Of course we have been maintaining it for a while and we have a fairly strong server. You can, however, set the TimeLimit on the script so that it will not start another step after the limit is reached.
    – RLF
    Commented Mar 31, 2014 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.