Solved

Decrease database file SQL server 2008

Posted on 2010-08-17
14
300 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Service Statictic 11 30
xpath sql query 2008 8 43
MS SQL - Rotating Values in SQL 9 53
SQL Query Conversion of IIF statement into CASE - Syntax issue 17 33
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…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

920 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

13 Experts available now in Live!

Get 1:1 Help Now