Solved

Decrease database file SQL server 2008

Posted on 2010-08-17
14
289 Views
Last Modified: 2012-05-10
My database is very big, is it possible shrink it if I for example delete alot of rows in it?
0
Comment
Question by:Hocke_sweden
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33453082
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33453130
How big is "very big"?

Lee
0
 

Author Comment

by:Hocke_sweden
ID: 33453223
30gb is the datafile and the log file is 1,5gb, I delete the log file every night!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33453228
>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33453231
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
 

Author Comment

by:Hocke_sweden
ID: 33453297
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33453349
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33453381
>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
 

Author Comment

by:Hocke_sweden
ID: 33453470
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33453480
If performance is a problem, then you may consider looking at reindexing the database.
0
 

Author Comment

by:Hocke_sweden
ID: 33453587
Yes its a performance problem
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 250 total points
ID: 33454549
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33460600
>>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
 

Author Closing Comment

by:Hocke_sweden
ID: 33461298
Thanks for the help!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now