Sql-server – Shrink a database below its initial size

shrinksql serversql-server-2005

I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% unused.

I need to reclaim the space, which will allow us to have a second dev DB on the server (based on the cut down version); however, I cannot reclaim the space, I have done the following:

The initial size of the file SMS2_Data is 30GB.

DBCC SHRINKFILE (N'SMS2_Data' , 0, TRUNCATEONLY)

followed by

DBCC SHRINKFILE (N'SMS2_Data' , 19500)

No joy. I have tried making a backup, creating a new DB with a low initial size then restoring, no joy as the initial size gets overwritten. Have also tried:

ALTER DATABASE SMS2HazSub MODIFY FILE (NAME = 'SMS2_Data', SIZE = 20000)

This errored, saying:

MODIFY FILE failed. Specified size is less than current size.

I tried 20800 and then kept going up til 29000 (29GB) and it still will not let me change it.

Have done the shrink then changed the recovery mode from FULL to SIMPLE and back again. No joy.

I thought it was to do with some TEXT fields. We have about 6 across the system. So as a test I dropped them all and then did a shrink of the file and still no change.

The only option left is to reimport the data to another DB. This is not practical, as it would have to be done on the live DB, which carries too much risk. We semi-regularly grab a copy of the live DB and overwrite dev/test. We have something like 500 tables. I would like a way of doing it that would not have the risk of exporting data to a new DB.

I tried moving the data to another file, and it copied all but 5% of the data. This is what lead me to try and drop all the text columns.

The server is on compatibility mode 90, but is SP2. I've now done the following 3 times: reindex all tables, backup database, shrink file, shrink database. Still no joy.

EXECUTE sp_spaceused returns:

database_name   database_size   unallocated space
SMS2Tests       31453.94 MB     13903.16 MB

reserved     data         index_size   unused
16545568 KB  10602264 KB  4254360 KB   1688944 KB

Best Answer

As some people already mentioned you could create new database and "copy" stuff over from old database. This would be the best option for you. However I've noticed that you want to do it quite regularly. So your best option is Redgate Data Compare and Redgate Compare. Both are part of Redgate SqlToolbelt package.

So what you do:

Create an empty DB with small initial size.
Use Redgate Compare to copy db structure, functions etc from old db
Use Redgate Data Compare to copy data from old database to new one
You work on dev database and then at any time you either do just Data Compare and update the Dev DB regularly, or if you do any changes to db you can deploy those changes using Redgate Compare and then doing Redgate Data Compare.

What is good with Data Compare is that after you copy those 30gb of data (you can do it starting with some tables only) after a while it just needs to 'recompare' only some changes and not whole 30gb of data. Which means it will do a lot less impact on both databases then it would by copying it normally.

Related Solutions

Sql-server – Shrink the database only up to its initial size which is set after creating database

Initial size is not just 3MB, it is taken from the model database (if not specified during the creation of your user database.) So assuming you haven't specified a initial size during the creation of your user db and you haven't altered the model database file sizes after you have created your userdb you can do the following:

--Create testDB
CREATE DATABASE [TEST_100]
GO


--grow your database file size
ALTER DATABASE [Test] MODIFY FILE ( NAME = N'Test', SIZE = 100MB )
GO


--switch context
USE [TEST_100]
GO



--Find size of modelDB mdf file, this is your initial file size used for the userdb
DECLARE @TargetFileSize int

SELECT  @TargetFileSize = (size * 8 / 1024)
FROM    sys.master_files 
WHERE   database_id = 3 --model database
AND     file_id= 1 --first file is mdf, assuming you have a model with just one mdf. If you have multiple files, change for the one you need to find.


--shrink the the first file of your current database to the target size that you just found.
DBCC SHRINKFILE (1,@TargetFileSize)

EDIT

Okay, some extra info is needed after your edits and comments.

First of all. I feel that the "Initial size" label that you see when you look at the file properties in SSMS is a misnomer. Basically, your intial size is just a concept. It's the first size that is used during creation of the database. You can either explicitly specify this in the CREATE DATABASE statement, or you can have SQL Server implicitly copy it from the model database by ommiting that information during creation.

However, once the database is created, from a DBA perpective there is no such thing as a "initial size" there is only one property visible for a DBA and that is: the actual size. Even the "Initial size" property in SSMS just shows actual size, not the initial size.

Well how come that DBCC SHRINKFILE or DBCC SHRINKDATABASE "know" the initial size then? Even after you have changed the size. Interesting question.

The first page of a dattabase file is the file header page. In there you have, amongst others, 2 properties: size and minsize.

At creation of the file, both file header properties get filled with the inital value:

DBCC TRACEON(3604)
--parameters for DBCC PAGE: (Dbname, fileID, pageID, outputTypeID)
DBCC PAGE('Test_100',1,0,3)with tableresults

enter image description here

Both sizes are in the amount of data pages. In this case. 288 data pages.

Now if I alter the file size:

ALTER DATABASE [test_100] MODIFY FILE ( NAME = N'test', SIZE = 50MB )

enter image description here

You can see that the "size" property is changed to reflect the new size. However, the "MinSize" property still contains the "Initial" size. It's the minimal size to which the shrink command will go.

However, having said all this. I still don't understand why you want to complicate things by first altering the initial size and then shrink to that initial size. Instead of just shrinking directly to a targetsize.

Anyway, to answer your question. The "initial" size is not exposed as a property to the user/dba.

Sql-server – TempDB will not shrink. No Open Transactions

Note: this post might be useful too:

Issues with TempDB mdf file ever increasing

Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb.

A different question has dealt with figuring this out for #temp tables; I don't know if it can be adapted for work tables:

Find which session is holding which temporary table

I've also blogged about it (again, for #temp tables):

http://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table

I doubt the work table is related to snapshot isolation / version store, but just in case:

Find transactions that are filling up the version store

Also, don't rely on DBCC OPENTRAN; - I've observed many scenarios where I know I have an active transaction but it doesn't show up there. And note that database context is important; the database where the transaction is active is not necessarily tempdb. What do you see here? Anything?

SELECT * FROM sys.dm_tran_active_transactions
  WHERE name = N'worktable';

Once you've shrunk tempdb

Of course, this isn't a permanent solution. You're going to shrink tempdb, and then it's going to grow again. This can become very boring and tedious to play this game every time it happens. And if it is going to grow again, what are you going to do with that free space in the meantime? Lease it out and then evict people when tempdb needs it again? You need to either:

Fix the process that is making tempdb grow abnormally large in the first place.
Allocate enough space for tempdb so that it doesn't need to grow, and stop shrinking it (especially if only temporarily; this is just wasted work!).

A couple of other suggestions:

Don't use SHRINKDATABASE (which should be called auto-fragment) or the UI. Write specific, targeted SHRINKFILE commands to affect individual files.
Consider using multiple files for tempdb (which you could spread out to different storage if/when possible), and consider trace flags 1117 (as long as this behavior will not affect your user databases too) and 1118.
Some tips on minimizing tempdb utilization here:
- How to identify which query is filling up the tempdb transaction log?

Best Answer

Related Solutions

Sql-server – Shrink the database only up to its initial size which is set after creating database

Sql-server – TempDB will not shrink. No Open Transactions

Once you've shrunk tempdb

Related Question