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?
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.
Deleting rows in a database will not decrease the actual database file size.
You need to compact the database after row deletion.
SQL Server 2005 DBCC SHRINKDATABASE (Transact-SQL)
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.
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."
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.
I found this because I just deleted a bunch of backup tables because my database had "maxed out". I kept looking at the "Size" property, thinking why isn't this getting smaller?. After reading this, no, I don't want to shrink the database. What I want to do is "reclaim" the space for the junk I just deleted. What I needed to be looking at was "Space Available". I'm thinking maybe that's what someone else might need to be looking at that, too.?*
Worth noting as well that if the table has indexes on it, fragmentation can exist after deleting large swaths of data. I had a table today that had ~70M records in it taking about 13GB. I cleaned it down to 1639 records (the rest were generated by a single bug) but the table still took up around 4.5GB. After I rebuilt all indexes on the table, it was only taking 85 pages (680kb). After that, I used incremental shrinkfile to reclaim the space (and fixed the bug on the system to prevent a repeat).
I wrote this after being in the exact same scenario and needing to shrink the database. I did not want to use DBCC SHRINFKILE so I used Paul Randals method of shrinking the database.
https://gist.github.com/tcartwright/ea60e0a38fac25c847e39bced10ecd04
