Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm no good in SQL, but I've got a database to maintain.

There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had about 10 000 000 rows cleaned) and cleaning transaction log I've found out that my actions had no effect on database size. Is there anything else I have to do?

share|improve this question

migrated from superuser.com Nov 8 '12 at 6:20

This question came from our site for computer enthusiasts and power users.

4 Answers 4

While shrinking is dangerous indeed for the reasons mentioned here. There is a happy medium between Jimbo's answer and John's answer... You should always seriously consider whether or not you want to shrink your database.

In an ideal world - you'd create your DB with plenty of free space to grow into. I call this "Right Sizing" your database. You would allow this free space to be there and not strive to give it back and keep your total size right at your used size.. Why? Because your database will eventually grow again.. Then you'll shrink again.. And you'll be stuck in this horrible pattern of useless shrinks followed by growths - and the entire time, as a few have pointed out, you'll be increasing your index fragmentation.

I've blogged about this where I admonished folks to "Don't touch that shrink button!" but sometimes... Sometimes you need to. If you have a large database, just freed significant space and don't expect to grow back into it ever - well then it is okay to consider shrinking as a one time operation as long as you can take care of your index fragmentation afterwards through rebuilding them. The shrink operation can be time consuming so you'd want to plan it for a time where you can pay that price of a shrink running. The approach of creating an empty DB and copying data into it works - but that can become very difficult with larger databases and a lot of data.

If you plan on adding that space back to the DB through normal usage and growth patterns into the future, then you may just want to leave the space there.

Also You said you "cleared" your transaction log. I'd be curious to know how you did this but as you read the post I shared and the others in the series you'll see some tips on transaction log management. But in short - if you are in Full Recovery mode you should be taking regular log backups to keep the log reusing itself. Otherwise - with no log backups while in Full Mode - the log file keeps growing and growing and growing and always saves what you've done because you told SQL you don't just want to maintain that log for crash recovery but want to keep a manual backup of it to replay transactions/undo transactions to recover to a specific point in time for recovery purposes... If you are in simple and seeing the log grow excessively, this can be a sign (typically) that you are doing a LOT of work in one transaction (whether you said BEGIN TRAN ... do work.... COMMIT TRAN or whether you just issued one big DELETE statement and deleted a whole mess of data in one implicit transaction.)

I am also assuming that you are looking for this free space on your file system. If you are looking for it within SQL and within that large file you have - it could be that you are waiting on ghost cleanup to complete if looking immediately after your operation. Paul Randal blogs about Ghost Cleanup.

share|improve this answer
    
What i have looks like this: Almost full partition, no place to write new logs, and lot's of old unneeded info (from 2008, for ex). Here's how my algorythm looked like after reading all your useful advices (from which I took only "shrink the base" part, sorry, that's the cost of war): 1. Delete a part of info, just as much as left free space let's us. Query i used: delete from dbNameHere where logTime >= 'some_date' and logTime <= 'some_date' 2. Shrink the database to release unused space from db itself and clear transaction log (no command lines, used MS SQL 2005 interface) –  Marat Nov 16 '12 at 12:03
    
3. Repeat until there's just enough space to forget about this problem for a couple of years. Thanks guys, you've become real help to me. –  Marat Nov 16 '12 at 12:07
    
Sounds good and don't forget to rebuild those indexes when done. –  Mike Walsh Nov 16 '12 at 16:16

Deleting rows in a database will not decrease the actual database file.

You need to compact the database after row deletion.

MS SQL 2005 DBCC SHRINKDATABASE (Transact-SQL)

share|improve this answer
    
The T-SQL to do this is DBCC SHRINKDATABASE DatabaseNameHere –  Scott Chamberlain Nov 8 '12 at 5:35
    
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'DataBaseName'. Query was: DBCC SHRINKDATABASE 'DatabaseNameHere' –  Marat Nov 8 '12 at 6:15
    
Thx to Scott, I updated answer with link to SQL SRV 2005 syntax. –  John Siu Nov 8 '12 at 6:20
3  
After running this, you'll want to rebuild indexes. Shrinking typically causes index fragmentation, and that could be a significant performance cost. I would also recommend that after you shrink, you re-grow the files so that you have some free space. That way, when new rows come in, they don't trigger autogrowth. Autogrowth has a performance cost and is something you would like to avoid (through proper database sizing) whenever possible. –  Kevin Feasel Nov 8 '12 at 12:24
    
I downvoted simply because of the lack of any warning or notes on the downside of shrinking.. Kevin added that in a comment - which I upvoted ;) –  Mike Walsh Nov 10 '12 at 4:43

When you delete data, SQL Server reserves its space for later use for inserting new data. You need to shrink the database. You can find more information here.

share|improve this answer

DO NOT SHRINK YOUR DATABASE!

"Why does this happen? A data file shrink operation works on a single file at a time, and uses the GAM bitmaps (see Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps) to find the highest page allocated in the file. It then moves it as far towards the front of the file as it can, and so on, and so on. In the case above, it completely reversed the order of the clustered index, taking it from perfectly defragmented to perfectly fragmented."

http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

"Look at irony of the Shrinking database. One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually."

http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/

share|improve this answer
1  
You should move the data to a new filegroup then delete the old filegroup. This way you get no fragmentation and can shrink your database. –  Ali Razeghi Nov 8 '12 at 19:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.