Solved

Decrease database file SQL server 2008

Posted on 2010-08-17
14
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +1
14 Comments
 
LVL 143

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 143

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 143

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
 
LVL 143

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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