Help Me to find the following ?

Hello Every one,

Please help!!!

1.I have scheduled a Rebuild index task to my db which is 90 GB .I am running this on daily basis for testing.Today I found my db is increased upto 1 Terabyte.Why?
2.Please help me how to schedule maintainance tasks in sequence ? My db size is 100 GB.
3.My users are complaining that application is running slow ?PLease help me how to start anaylsing. what are the steps to be taken care.

Thanks ,
Vijay

asked Jan 28 '11 at 06:07 AM in Default

vijay 2
236 ● 12 ● 18 ● 20

(comments are locked)

4 answers:

Just a quick guess here. You are rebuilding all your indexes on a daily basis and not doing any transaction log backups. Can you share with us the size of your data file and the size of your log? Is it your Log file that has increased by 750 to 800 percent?

First, no need to rebuild all indexes if a REORG is all you need. Ola Hallengren and Michell Uford have great scripts that can accomplish your index maintenance much more effectively than a maintenance plan. Actually Ola's blog contains one of the best if not the best maintenance plan there is.

Check out the two blogs I have referenced and you will have all you need keep your indexes in check. If you need help with getting your DB back into proper size let us know.

answered Jan 28 '11 at 06:31 AM

Tim
31.5k ● 20 ● 31 ● 116

Hello Thanks Magnus,

Yes my log file is increased a lot 70%.

I understood the problem but i need the sequence of the maintainance tasks which needs to be run in my db.

Thanks.

Jan 28 '11 at 06:37 AM vijay 2

@veejay_mca - it wasn't my answer, I just corrected the links that TRAD provided.

But to answer: If you rebuild all your indexes each night, and your log file grows a lot because of this task, you shouldn't shrink the logfile. It will harm your performance, both for the actual task but also the overall performance of you DB to autogrow and shrink your logfile.

I'd suggest following TRADs advice to look at eg Michell Ufords index maintenance scripts, which rebuilds whichever indexes needs rebuilding, reorganizes those that needs it, and leaves the other ones alone. And leave the transaction log alone, unless it grows due to a one-off job.

Jan 28 '11 at 06:43 AM Magnus Ahlkvist

Both Ola and Michelle's index scripts can be ran daily. They both look for percent of fragmentation to determine if an index rebuild needs to be ran or a reorganization. However if you can run them daily depends on your version of SQL and or your maintenance window. There is no one size fits all maintenance plan for everyone. As with everything SQL Server "it depends" on your environment. I can give you some great tips though.
1) BACKUPS - I would at least do a full backup each night esp since your DB is only 90 GB. Then depending on your SLA (Service Level Agreement) I would be doing Transaction log backups every 15 minutes or 30 or every hour. Every hour at least. Keep in mind that database backups do not truncate your transaction log, transaction log backups do. Backup up your log will keep your tlog from growing out of control.

Jan 28 '11 at 06:48 AM Tim

2) Index maintenance. If you don't have enterprise edition of SQL server you can't do online index rebuilds do those need to be done during maintenance windows or when users are not heavily using the system. I personally use Michelle's script. The first time you run it, it might take a good while to finish, then after that it is pretty quick because index fragmentation is not as high (the script took care of it the first time it ran).
3) Database Consistency Checks. How often do you run DBCC CHECKDB? This is very important. If you encounter corruption and are not checking for it, you might find yourself with some considerable database loss. I run DBCC daily.

This should get you going in the right direction.

Jan 28 '11 at 06:48 AM Tim

@veejay_mca, did you get everything straighten out? Is your tlog growth under control now?

Jan 30 '11 at 12:13 PM Tim

(comments are locked)

You seem to have 3 questions that are relatively unrelated. To the first, I think TRAD nailed it, but let me reemphasize the point in his comments about making your transaction log backups. You need this both to keep your transaction log reasonably sized and to ensure you have point in time restore capabilities.

Your second question is very dependent on your particular needs and your particular situation, but TRAD's outline in his comments is a very good place to start.

As to the third question, it would really help to have more details, but there are some general attempts to address it. I wrote one as An Informal Look at Database Performance ( http://www.sqlservercentral.com/articles/Performance/71001/ ) and Gail Shaw has one that is far better than mine at Finding the Causes of Poor Performance in SQL Server (http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/)

answered Jan 28 '11 at 11:11 AM

TimothyAWiseman
14.3k ● 17 ● 20 ● 29

Thanks Timothy...I am learning now!!

Jan 31 '11 at 09:24 AM vijay 2

(comments are locked)

Is that backing up your database at the same time as Rebuilding the index? (or) inserting data or any transaction occur at the same time while rebuilding the index....
What do you mean by shedule maintennace tasks in sequence? Back Up, rebuild, integrity check, etc..?
Use SQL profiler to trace the database transaction. You can filter user login, SPID, etc.. in Profiler if you want to look at more specific.

answered Jan 28 '11 at 06:21 AM

Leo
1.6k ● 48 ● 54 ● 58

Thanks Leo,

I am working on test db .There is no user activities on this DB. Yes, I mean rebuild,reorganise index,Integrity check,Update statistics,Shrinking of the db.

Jan 28 '11 at 06:39 AM vijay 2

Shrinking of the DB?

Jan 31 '11 at 12:57 AM ThomasRushton ♦

(comments are locked)

In response to part 3 - performance. You mention in a comment about shrinking of the DB.

Are you doing that regularly? If so, why? Consider stopping it immediately, and turning auto_shrink off on all your databases. Shrink is evil. Seriously evil. If you absolutely must do it, then make sure you do the index maintenance after the shrink.

answered Jan 31 '11 at 12:59 AM