How to reduce the size of a SQL Server database?

Question

I have a database of almost 1.9 GB, and MSDE2000 does not allow DBs to exceed 2.0Gb

I need to compress this database (and many others like that in different places of the client).

I found and deleted a lot of 100 out of 1000 records that are considered unnecessary: ​​these records account for a large percentage of some of the main (largest) tables in the database. Therefore, it is reasonable to assume that a lot of space should now be restored.

So, now I need to compress the database to account for the missing records.

• DBCC ShrinkDatabase('MyDB') ... No effect.
• I tried the various shrinks provided in the MSSMS ... Anyway, no effect.
• I backed up the database and restored it ... Anyway, no effect.

Still 1.9Gb

Why?

No matter which procedure I ultimately discover should be played back on a client machine that has access only to OSql or the like.

Answer 1

 ALTER DATABASE MyDatabase SET RECOVERY SIMPLE GO DBCC SHRINKFILE (MyDatabase_Log, 5) GO ALTER DATABASE MyDatabase SET RECOVERY FULL GO 

Answer 2

It may seem strange, but it worked for me, and I wrote a C # program to automate this.

Step 1: Truncate the transaction log (back up only the transaction log, enable the option to delete inactive transactions)

Step 2. Starting the database is compressed, moving all pages to the beginning of the files.

Step 3: reset the transaction log again, as step 2 adds the log entries

Step 4. Run the database again.

My truncated code that uses the SQL DMO library looks like this:

 SQLDatabase.TransactionLog.Truncate(); SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_NoTruncate); SQLDatabase.TransactionLog.Truncate(); SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default); 

Answer 3

This is an old question, but I just pounced on it.

A really short and correct answer has already been given and has the most votes. This is how you shorten the transaction log, and this is probably a problem with the OP. And when the transaction log went out of control, it often needs to be put back, but care should be taken to prevent future situations when the log goes out of control. This question on dba.se explains this. In principle - do not let him get it in the first place thanks to the correct recovery model, transaction log maintenance, transaction management, etc.

But the big question that arises when I read this question about compressing a data file (or even a log file) is why? and what bad things happen when you try? Operations seem to be shrinking. Now in this case it makes sense in a way - because MSDE / Express editions are limited by the maximum database size. But the correct answer may be to view the correct version for your needs. And if you stumble upon this question while trying to collapse your production database, and that is not the reason, you should ask yourself the question why? .

I don’t want anyone searching on the Internet how to “compress the database”, coming across this and thinking that this is cool or acceptable.

Data reduction is a special task that must be reserved for special cases. Keep in mind that compressing your database effectively fragmentes your indexes. Keep in mind that when you shrink a database, you take away the free space that the database can ever grow back — effectively wasting your time and impacting the performance of the shrink operation just to see the DB grow again.

I wrote about this concept on several database reduction blogs. This call is called " Do not touch this squeeze button " first comes to mind. I am talking about these concepts outlined here, but also about the concept of "properly calibrating" your database. It is much better to decide what size your database should be, plan future growth and distribute it for this amount. With instant file initialization, available in SQL Server 2005 and later for data files, the cost of growth is lower - but I still prefer to have the right starting application - and I'm much less afraid of database gaps than I overall without thought . :)

Answer 4

You will also need to compress individual data files.

However, shrinking databases is not recommended. For example, see here .

Answer 5

DBCC SHRINKDATABASE works for me, but this is the full syntax:

 DBCC SHRINKDATABASE ( database_name, [target_percent], [truncate] ) 

where target_percent is the desired percentage of free space remaining in the database file after shrinking the database.

And the truncate parameter could be:

 NOTRUNCATE 

Causes freed file space to be stored in database files. If not specified, freed file space will be released to the operating system.

 TRUNCATEONLY 

Invokes any unused space in the data files that will be released to the operating system, and compresses the file to the last allocated area, reducing the file size without moving any data. No attempt is made to move lines to unallocated pages. target_percent is ignored when using TRUNCATEONLY.

... and yes no_one is right, reducing datbase is not a good practice, for example, for example:

compressing data files are great ways to introduce meaningful logical fragmentation, as it moves pages from the end of the selected range of the database file to somewhere in the front of the file ...

Shrinking a database can make a big difference to the database server .... think a lot about it before you do it!

There are many blogs and articles on the Internet about this.

Answer 6

You should use:

 dbcc shrinkdatabase (MyDB) 

It compresses the log file (keep Windows Explorer open and view it).

Answer 7

Late answer, but may be useful for someone else

If neither DBCC ShrinkDatabase / ShrinkFile nor SSMS (Tasks / Shrink / Database) help, there are tools from Quest and ApexSQL that can complete the task and even schedule periodic reductions if you need it.

I used the latter in a free trial to do this some time ago, following the brief description at the end of this article:

https://solutioncenter.apexsql.com/sql-server-database-shrink-how-and-when-to-schedule-and-perform-shrinking-of-database-files/

All you need to do is install ApexSQL Backup, click the "Shrink database" button on the main ribbon, select the database in the window that appears and click "Finish".

Answer 8

Here's another solution: use the Database Publishing Wizard to export your schema, security, and data to sql scripts. Then you can disconnect your current database and create it with scripts.

It sounds silly, but there are a couple of advantages. Firstly, there is no way to lose data. Your original db (if you do not delete your database when deleting it!) Is safe, the new database will be about as small as it can be, and you will have two different snapshots of your current database - one ready-made roll, one miniature - You can choose to backup.

Answer 9

"Therefore, it is reasonable to assume that a lot of space should now be restored."

Sorry if I misunderstood the question, but are you sure this is a database, not log files that use space? Check which recovery model is in the database. This is most likely a complete file, which means the log file is never truncated. If you do not need a complete record of each transaction, you should be able to change it to Simple, which will truncate the logs. You can compress the database during the process. Assuming everything is going right, the process is as follows:

• Database backup!
• Change to simple recovery
• Compress db (right click db, select all tasks> shrink db → set to 10% free space)
• Make sure the space has been fixed if you do not need to perform a full backup

If this does not work (or you get a message that the "log file is full" when you try to switch recovery modes), try the following:

• Backup
• Kill all db connections
• Detach db (right click> Detach or right click> All Tasks> Detach)
• Delete log file (ldf)
• Reload db
• Change recovery mode

and etc.

Answer 10

I came across this post, although I needed SHRINKFILE in the MSSQL 2012 version, which is a bit more complicated with the 2000 or 2005 versions. After studying all the risks and problems associated with this problem, I finished testing. In short, the best results I got are using MS SQL Server Management Studio .

 Right-Click the DB -> TASKS -> SHRINK -> FILES -> select the LOG file 

Answer 11

You also need to change the minimum size of data and log files. DBCC SHRINKDATABASE will reduce the data inside the files that you have already allocated. To reduce the file size to its minimum size, use DBCC SHRINKFILE and specify a new size.

Answer 12

Delete the data, make sure the recovery model is simple, then hide (shrink the database or shrink the files). If the data file is still too large and you use heaps to store data, that is, without a clustered index on large tables, then you may have a problem with deleting data from heaps: http://support.microsoft.com/kb/ 913399

Answer 13

I did it recently. I tried to make a compact version of my database for testing on the road, but I just could not smooth it, no matter how many lines I deleted. In the end, after many other commands in this thread, I found that my clustered indexes did not recover after deleting rows. Restoring my indices made it so that I could shrink normally.

Answer 14

Not sure how practical this is, and depending on the size of the database, the number of tables, and other complexities, but I:

• physical disk defragmentation
• create a new database according to my requirements, space, percentage growth, etc.
• use simple ssms task to import all tables from old db to new db
• script to derive indexes for all tables of the old database, and then recreate indexes in the new database. expand as needed for foreign keys, etc.
• rename databases as needed, confirm successful, delete old