Help Me to find the following ?

Question

question

vijay 2 asked Jan 28, '11

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

maintenance-plans log-backup log-file-size

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-01-28T06:21:52Z

Leo answered Jan 28, '11

1. 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.... 2. What do you mean by shedule maintennace tasks in sequence? Back Up, rebuild, integrity check, etc..? 3. 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.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vijay 2 commented · Jan 28, 2011 at 06:39 AM

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.

0 ·

ThomasRushton ♦♦ commented · Jan 31, 2011 at 12:57 AM

Shrinking of the DB?

0 ·

Answer 2 · 2011-01-28T06:31:02Z

Tim answered Jan 28, '11

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]( http://www.ola.hallengren.com) and [Michell Uford]( http://www.sqlfool.com) 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.

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim commented · Jan 28, 2011 at 06:48 AM

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.

4 ·

vijay 2 commented · Jan 28, 2011 at 06:37 AM

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.

0 ·

Magnus Ahlkvist commented · Jan 28, 2011 at 06:43 AM

@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.

0 ·

Tim commented · Jan 28, 2011 at 06:48 AM

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.

0 ·

Tim commented · Jan 30, 2011 at 12:13 PM

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

0 ·

Answer 3 · 2011-01-28T11:11:29Z

TimothyAWiseman answered Jan 28, '11

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 (https://www.sqlservercentral.com/articles/an-informal-look-at-database-performance) and Gail Shaw has one that is far better than mine at Finding the Causes of Poor Performance in SQL Server (https://www.red-gate.com/simple-talk/sql/performance/finding-the-causes-of-poor-performance-in-sql-server-part-1/).

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vijay 2 commented · Jan 31, 2011 at 09:24 AM

Thanks Timothy...I am learning now!!

0 ·

Answer 4 · 2011-01-31T00:59:49Z

ThomasRushton answered Jan 31, '11

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.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim commented · Jan 31, 2011 at 06:02 AM

I really think shrink needs to be a little more hidden and auto shrink removed from future versions of SQL.

0 ·

ThomasRushton ♦♦ commented · Jan 31, 2011 at 06:16 AM

Oh, agreed, absolutely. Got to love Paul Randal's Mythbuster on it...

0 ·

question