Shrink data base SQL Server 2008

views:

answers:

Shrink data base SQL Server 2008

HI I have made a maintenance package in that have used shrink database task for specific database, it ran successfully, found slight increase in previous db size. Initial size(129 gb) after running the package(130gb).

I am expecting after shrinkning it should shrink? what might be happen? am sure package scheduled to run and check the history found run successfully.

Any help/ please advise any special care required, Thanks in advance.

+1 A:

There are a couple of things you can do the check on this. In SQL Server Management Studio (SSMS), Object Explorer, right-click on the database name and select Properties. On the General tab you'll find the Space Available value. Is there any available space?

Note that the space available includes space in the transaction log file. You need that space, so you don't want to shrink the database too much.

bobs 2010-10-07 00:02:43

Thanks, great Suggestion,so how to control it.

rmdussa 2010-10-07 00:54:24

Generally, databases grow in size over time and rarely shrink. You should shrink a database only after special events, such as after removing historical information and when you need to recover disk space for other uses. Shrinking the database should be a special event for the DBA.

bobs 2010-10-07 01:15:04

Thanks for your response

rmdussa 2010-10-07 02:02:22

+9 A:

Do not shrink the database during maintenance. There is probably no other more damaging action you can do. Read more at Auto-shrink – turn it OFF. IF a database has grown to a certain size, then it will likely grow back if you shrink it. Shrinking the database is tremendously damaging to the index fragmentation and will slow down your reporting and analytic workloads. Once shrink, when the database will grow back during normal operations the auto-growth events will interrupt and freeze the database during the growth.

There is one thing to shrink a database that had got out of control due to some rogue action that increased it. But to have the shrink in maintenance task means you will constantly do it on a scheduled interval, and this is very bad.

Remus Rusanu 2010-10-07 00:24:25

Great answer. I wish I can gave you more pts.

Wonde 2010-10-07 00:29:35

Thanks for you suggestion, what is the solution/suggestion to control db size in better fashion.

rmdussa 2010-10-07 00:51:58

What does it mean to 'control db size'? If your database has 129Gb then it means it needs 129Gb. *Why* do you believe that it needs to shrink?

Remus Rusanu 2010-10-07 01:04:46

rmdussa 2010-10-07 02:00:25

found why the database size is increasing, in my case ,because there is unwanted data was sitting in heap tables(example:nomatch, failed,bad records,unformatted)(more than 100 millions records in diff tables need to truncate to freeup the space)

rmdussa 2010-10-07 03:23:51

`sp_spaceused`: http://msdn.microsoft.com/en-us/library/ms188776.aspx. Is it acceptable to shrink the database once, after you remove the unwanted data. Is it also acceptable to shrink the log, if the process of removal increased the log. But both shrinks are *extraordinary* operations you perform now, because you discovered the database is overweight. My objection was to having the shrink as an *scheduled* maintenance.

Remus Rusanu 2010-10-07 04:38:28

Excellent suggestion,Yes, I have done shrink database after truncation of the unwanted data. Now the database size is 88GB. Thanks.

rmdussa 2010-10-07 07:08:03

I do not know Why I got -ve mark, Please let me know,anything wrong

rmdussa 2010-10-07 07:10:03

+1 A:

Also, keep in mind that you're database is probably in full recovery mode. what this means is that, as data is being inserted, updated, and deleted in the database, sql server logs it in the database log. This log can become quite large on a busy database. You can reduce the size of the log by performing full backups. Remember, the point of the log is so that you can do log backups and do point in time restores. If you're not doing this, or don't need to do this, you might consider having the database turned to simple recovery mode.

DForck42 2010-10-07 13:39:10

ansaurus

tags:

views:

answers:

Shrink data base SQL Server 2008

related questions