Reduce database size

Question

Reduce database size

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

bdeboer Valued Member Points: 65 More actions · Answer 1

When looking at our database it's 226 Gb, but the data is 167 Gb and the logfiles 59 Gb. Does this mean that I can reduce the size of the database by deleting the logfile?

I'm very new in this so can someone advise me what to do?

Thanks,

Bert

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Lowell (12/5/2012)
http://sqlserverpedia.com/wiki/Heaps#Deletes_and_Heaps
Deletes and Heaps
When data is deleted from a heap using a DELETE statement, SQL Server will not release the space; it remains allocated to the heap. This leads to space bloat that wastes valuable resources. To address this problem, you can do any of the following:

That's not entirely true. Mostly true, just not completely true.

When data is deleted from a heap using a DELETE statement, SQL Server will not release the space, unless the delete has taken a table lock.

The cited page has a lot of 'mostly true' statements, so be a little careful with it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

bdeboer (12/12/2012)
When looking at our database it's 226 Gb, but the data is 167 Gb and the logfiles 59 Gb. Does this mean that I can reduce the size of the database by deleting the logfile?

No. Not unless you want to potentially destroy the entire database. The log is not an optional file. It's a critical and essential part of the database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

bdeboer (12/12/2012)
When looking at our database it's 226 Gb, but the data is 167 Gb and the logfiles 59 Gb. Does this mean that I can reduce the size of the database by deleting the logfile?
I'm very new in this so can someone advise me what to do?
Thanks,
Bert

first of all welcome to Sqlservercentral , you always need to start a new thread for your problem , never add in the existing one , it often dont attract many eyes (old thread less visibility). now coming to your question ? log files is mandate part or i would say unavoidable part of any database.first spend sometime on reading these links then you will come to know why we can/dont play with log file

http://msdn.microsoft.com/en-us/library/ms190925.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

itsmemegamind SSC Veteran Points: 244 More actions · Answer 5

there are few ways with the help of them you can able to reduce your database size

1. Defrag you database

2. Reorganising the indexes

3. Partitioning the database or also table

these operations will definitely improve your server performance and also the database space

//ADMIN: Removed unrelated link

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

itsmemegamind (12/12/2012)
1. Defrag you database
2. Reorganising the indexes
3. Partitioning the database or also table
these operations will definitely improve your server performance and also the database space

Err, no, they won't.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125522 More actions · Answer 7

A lot of the advise here is appropriate for a production database (don't shrink the files, etc.) where the goal is to preserve restore history and optimize performance.

However, for Dev and UAT where disk storage is tight, backups and performance are not critical, and the database is periodically wiped down and restored from production anyhow, then you may want to consider setting the database recovery model to Simple and also shrink the database and transaction logs when they expand with unused space. You may only need to do this once after each restore from production.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Eric M Russell (12/12/2012)
You may only need to do this once after each restore from production.

+ 1

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)