SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 AutoShrink
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  12:34:44  Show Profile  Reply with Quote
I have AUTOGROW and AUTOSHRINK db settings on . can anybody confirm how often AUTOSHRINK will run, Ive read that SQL will run SHRINK evrey 30 mins or so. I have had sql trace running since morning and no shrinks have take place on the DB although there is 1708MB still available.
If some one could shed some light on this I would appreciate it.
Thanks

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/07/2006 :  12:37:43  Show Profile  Reply with Quote
I strongly recommend that you do not use Auto Shrink.

What problem are you experiencing that causes you to have this option turned on?

Kristen
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  13:02:51  Show Profile  Reply with Quote
Yes thats what I have heard but just wanted to see how it impacts server performance for myself. My recovery Model is simple would that have someting to do with it.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/07/2006 :  13:25:26  Show Profile  Reply with Quote
"My recovery Model is simple would that have someting to do with it"

No, I doubt it.

Still can't imagine why this feature is even worth testing, but that's up to you!

Kristen
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  14:17:44  Show Profile  Reply with Quote
Kirsten,,,I need to track down the cause of the performance issues we are having.

Our main database is as follows

Size is 14737MB (from Enterprise Manager)
Unused Space is 1556 MB (from Enterprise Manager)
LOG ACTUAL size is 62.25 MB FROM DBCC SQLPERF ( LOGSPACE )
Log Space Used (%) is 20% DBCC SQLPERF ( LOGSPACE )
DB is set for automatic unlimited growth at 20%

I just wanted to find out when the AutoGrowth kicks in and the AutoShrink so I could monitor the perfomance. As the Site perofmance is really slow. It's a big issue with the business.
Could you give me a rough guid when each of these would kick in.
Thanks




Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/07/2006 :  14:41:11  Show Profile  Reply with Quote
I really think you should turn AUTOSHRINK off, I can't imaging any circumstances where it is a good idea to have that enabled.

I would change "unlimited growth at 20%" to a fixed numebr of MB. A database at 14GB with 20% growth may take a long time to actually make the extension (of 2.8GB) - it s a very resource intensive process IME.

Manually SHRINK the database just once and see if the space is reclaimed, then let it grow back to its normal working size, whence you should avoid shrinking it - there are a few exceptional circumstances when Shrinking is advisable - after an exceptional delete, or to minimise database size when migrating to a new server, or if somehow the logs have got to runaway size! (but that's about it)

If it won't shrink you may have a transaction which has failed to complete/checkpoint/or some other problem - and that transaction at the "end" of the log file is preventing the file being shrunk further. You can force SQL Server to truncate past that point - ask if that appears to be the problem.

Are you making transaction log backups at regular intervals (e.g. every 10 - 15 minutes)? If not your database Recovery model should be set to SIMPLE (which will ONLY allow you to recover from a Full backup) otherwise you should put frequent TLog backups in place - otherwise your transaction log will grow in a run-away fashion.

"As the Site perofmance is really slow"

All the time, or just some of the time?

If its some of the time I would change AutoGrowth to, say, 50MB or 100MB and see if that fixes it.

Your database files may be badly fragmented (physically fragmented) if the Log File has been shrunk often. You may want to physically defragment them (which requires stopping SQL server service)

Are you doing routine maintenance of your system (index defrag/rebuild and updating statistics)? If not then your system may be getting slower because the indexes and statistics are "out of shape"

Do you use Stored Procedures or parameterized queries? These may be using stale query plans, so if your SQL Server Service has not been restarted recently it would be worth restarting it (see the SQL Log file for the date when it was last started).

Do you have other processes on your server (such as Exchange or Small Business Server)? These may be competing for resources and trashing the machine / memory. Check Windows Task Manager [performance] that the Physical Memory Total is greater than the Commit Charge Total - otherwise your memory is being swapped to disk, which will be very inefficient.

See also:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Database%20Shrinking,Shrink,Shrinking,Rebuilding%20Indexes,DBCC%20CHECDB,Reindex,INDEXDEFRAG,Tara%20Blog%20Houskeeping%20Routines

Kristen
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  15:11:13  Show Profile  Reply with Quote
Thanks Kirsten.

The recovery model is simple.

Site Performance is always slow. So what should I set the AutoGrowth too?
I have set up IndexDefrag Jobs which run on the the weekend.

AutoCreate and AutoUpdate Stats is enabled. However I might disable them for the large tables. The biggest table in the DB is 8 Gig. I'm thinking of reindexing the largest tables on the weekend which will also update the stats.

There are 4 other databases on the server which are used by the site. All have thier AutoShrink settings on and also AUTO update Stats.

We mostly use Sp's


Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  15:23:00  Show Profile  Reply with Quote
I really think you should turn AUTOSHRINK off, I can't imaging any circumstances where it is a good idea to have that enabled.

I know you are right as I've read such bad things about it. I just needed to understand why rather than making adhoc changes and not really understanding the reason why.

So am i correct in assuming the AUtoshrink only acts on the transaction log. If so why would the log grow in simple recovery model. As I understood no transactions are logged in simple recovery.

Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  15:25:33  Show Profile  Reply with Quote
If its some of the time I would change AutoGrowth to, say, 50MB or 100MB and see if that fixes it

And Also is the above for the log or data file
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/07/2006 :  15:59:08  Show Profile  Reply with Quote
"I just needed to understand why rather than making adhoc changes and not really understanding the reason why"

Have a read of the link I posted

"is the above for the log or data file[i]"

I think it would be reasonable for both, but its only a guide. Whatever, 20% is going to be a HUGE increase each time it kicks in.

"[i]why would the log grow in simple recovery model
"

Probably the database maintenance, particularly if REINDEX (rather than INDEXDEFRAG) is used.

If you reindex the Clustered Index on an 8GB table the whole table will be moved elsewhere in the database, so that needs 8GB of extra Data File space and (if I understand it correctly) 8GB of log space.

Kristen
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  17:08:34  Show Profile  Reply with Quote
Thanks Again kirsten.
My Plan is as follows I'm going to monitor the size of the MFf and LDF file for the remaining part of the week. See How much it grows by. Get the average growth on a day basis. Multipy this by 30 days(i.e a month). Set the growth to this figure. SOo that the DB expands hopefully on a monthly basis. Would this be ok?
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/07/2006 :  17:10:21  Show Profile  Reply with Quote
Do you know roughly the Threshold where by expanding the DB by X amounts will be a performance bottleneck?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/08/2006 :  10:37:12  Show Profile  Reply with Quote
Received by private email:

"Sorry to bother you...But J have one more question with regards to this link. If you are saying to autogrow the db in smaller chunks i.e 100MB surley this will lead to data file fragmentation. The DB is groing at a rate of 300MB a day. If you can please assist in shedding some light on this."

miranwar: this is a public forum, not a private service I offer! Being a public forum other people will have ideas and opinions, so you will benefit from their advice. The fact that only I may have answered in this thread is irrelevant, many regulars here will be reading the thread, and if they have additional input they will provide it.

Having said that I can't do the work for you. You need to go away and make some tests and form your own views. You want to avoid fragmentation by allocating extensions to your database in 2.8GB lumps? Fine, but live with the performance degradation. You don't like the performance degradation? Fine, then extend the database in smaller pieces - and periodically defragment the physical files.

Folk cannot provide definitive answers to these questions, they are very subjective. I've given you what suggestions I can, you need to try some different things and see what works for you.

If that raises more questions come back and ask them, but I can't take the decisions for you.

Kristen
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/08/2006 :  13:00:45  Show Profile  Reply with Quote
>>Site Performance is always slow

It sounds like you have some fundamental configuration problem(s) or perhaps a poor application architecture. Have you just tested any calls directly in Query Analyzer to determine if it is purely a Sql Server problem or not?

Be One with the Optimizer
TG
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/09/2006 :  04:00:16  Show Profile  Reply with Quote
Kirsten, I am just trying to understand the matter cleary before I make my final decision. You see when you say that auto growing the database in 2.8Gig chunks will cause fragmentation. I would of thought making the autogrow smaller will cause more fragments. So it's best to grow the dB in larger chunks although there would be a performance hit.
Go to Top of Page

miranwar
Posting Yak Master

125 Posts

Posted - 11/09/2006 :  04:06:02  Show Profile  Reply with Quote
Kirsten
Sorry I understand what your point is now reading it for the second time. I did alot of reading around this topic yesterday and i agree with you.
Appreciate your help

Cheers
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 11/09/2006 :  04:34:58  Show Profile  Reply with Quote
"Appreciate your help"

No problem ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000