Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Decrease database file SQL server 2008

Posted on 2010-08-17
14
Medium Priority
?
342 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 750 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 750 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

722 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