September 28, 2006 at 6:08 am
I wondered what is the pro and con of the auto shrink. Also how can I find the unused space in the transaction log and database file. Thanks.
September 28, 2006 at 7:23 am
Auto Shrink need to be used with utmost caution.
This is off by default but in any case if you switch this on then server will periodically check, if shrink is required. The problem with this is during business hours this will keep using resources. CPU time and IO will go extremely high and this will cause unexpected bottleneck to regular work.
If you have enough free disk space then this is not recommended. If you must use then use it with commands such as DBCC ShrinkDatabase or DBCC ShrinkFile at your scheduled time.
Easy way to find unused DB and Log size is using taskpad View.
Hope this helps.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
September 28, 2006 at 7:30 am
Will you please give more detail on unused DB and Log size is using taskpad View ? Thanks.
October 2, 2006 at 7:19 am
Frances
To see the Taskpad View, click on the database in Enterprise Manager, then right-click and choose View and then Taskpad. Unused DB and log space are the amount of space in your data and log files respectively that don't have data in them. This will become clearer once you have seen the Taskpad View.
John
October 2, 2006 at 7:30 am
Task view did not give me Unused DB and log space are the amount of space in your data and log files respectively that don't have data in them.
Why ? Thx.
October 4, 2006 at 12:58 am
sp_spaceused may give you desired info.
October 4, 2006 at 3:50 am
run DBCC SQLPERF(LOGSPACE) command to view the total transaction log space and %used for every database.
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
October 4, 2006 at 7:43 am
any regular shrinking ( or to the same effect growth ) will cause ntfs fragmentation which will degrade performance. Try to balance device size to allow minimal size changes.
Autoshrink should never be used on a production system.
running dbcc updateusage will assist in the acuracy of the reported free space. I normally publish a tabular report of device size and free space, however viewing in EM is acceptable to see how much free space you have.
A database needs a certain amount of free space if you're running any maint jobs.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply