Decrease database file SQL server 2008

My database is very big, is it possible shrink it if I for example delete alot of rows in it?
Hocke_swedenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, after a delete, you can attempt to shrink the database files:
DBCC SHRINKFILE
DBCC SHRINKDB

note that the transaction log requires, if the db is in full recovery mode, a regular transaction log backup before the shrinkfile for the logfile will be effective
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
How big is "very big"?

Lee
0
Hocke_swedenAuthor Commented:
30gb is the datafile and the log file is 1,5gb, I delete the log file every night!
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I delete the log file every night!
you should NOT do that, because if it is 1.5GB, it is that big for a reason.

so:
* is the db in full recovery mode, or in simple recovery mode?
* if it's in full recovery mode: do you have a regular (hourly or even more) transaction log backup in place and working?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note: instead of having 1 single big data file, you should consider adding filegroups and data files, and move (nonclustered) indexes to dedicated filegroups, as well as "hot" table(s) to dedicated filegroup(s)
this shall enhance your overall I/O and performance ...
0
Hocke_swedenAuthor Commented:
Its in full recovery mode

>do you have a regular (hourly or even more) transaction log backup in place and working?
No

Why should I have that?
0
Lee SavidgeCommented:
30gb is not very big. If you're running short on disk space, I would seriously consider getting a bigger disk.

As for why you should back up transaction logs... If you don't and the database fails you will only be able to go back to the last valid backup. Anything after that stored in the transaction logs will be lost. If you back the db up every night, then potentially you could lose a days worth of data as the transaction logs will contain that days database transactions. Backing those up allows you to restore almost to the point of failure.

Lee
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Why should I have that?

if you have full recovery mode, you can restore with the full+transaction log backups to any point in time since the full backup until the transaction log backup.
if you don't need that feature: change to simple recovery mode, but note:
IMPORTANT: you cannot change from simple to full "after the ooups", and expect to backup + restore to before your "ooups"

ooups can be "drop table", delete table, udpate all rows to the same value eetc ...
0
Hocke_swedenAuthor Commented:
We never use the full recovery mode feature, is it better to change to simple mode to better perfermance?

I have I big disk now, but Im thinking of maybe change to a SSD disk, and they are very expensive if I want I big one. Then I consider of shrink the databases (I have more 1 one, but the 30gb is the biggest, the other are 10gb, 6gb and 1gb!

The performance have getting worse the the latest time and I want to increase it!
0
Lee SavidgeCommented:
If performance is a problem, then you may consider looking at reindexing the database.
0
Hocke_swedenAuthor Commented:
Yes its a performance problem
0
Lee SavidgeCommented:
Then rather than shrinking the database which will probably not have a huge effect, you may want to consider performance tuning. This is a massive subject. Here are some pages that may help you:

http://blog.stevex.net/why-is-sql-server-so-slow/
http://www.simple-talk.com/sql/database-administration/fine-tuning-your-database-design-in-sql-2005/
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx
http://www.mssqltips.com/tip.asp?tip=1481 (See links at the bottom of this page)

Cheers,

Lee
0
Anthony PerkinsCommented:
>>Why should I have that?<<
Because you care about your data and/or your job and you wish to preserve one or both.  In other words by doing this "I delete the log file every night!" you risk corrupting your database.  Would you consider deleting the data file?  I did not think so.  In case you are not aware the Transaction Log file is an integral part of your database.
0
Hocke_swedenAuthor Commented:
Thanks for the help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.