Changing the initial size of db file

Question

Post reply

Changing the initial size of db file

TDP

Ten Centuries

Points: 1144
More actions
January 19, 2009 at 5:23 am

#128054

I need to shrink database and log files to less than their inital size, thus change the initial size of the dabase file.
How do I do this?

Viewing 15 posts - 1 through 14 (of 14 total)

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

1. Backup your log file

2. DBCC SHRINKDATABASE(Databasename)

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TDP Ten Centuries Points: 1144 More actions · Answer 2

I need to shrink the database files to less than the current initial size of the database. Shrinkfile, shrinkdatabase and logbackup only shrinks to the initial size of the database files.

Thus, I do not want to shrink, I want to change the initial size of the files.

hema kumar-479337 SSC Veteran Points: 279 More actions · Answer 3

Its seems to be an open issue in both sql server 2000 and SQL Server 2005. We cannot shrink the database less than the initial size.

TDP Ten Centuries Points: 1144 More actions · Answer 4

You would think that if you cannot shrink the file below the initial size they'd at least give us the possibility to be able to decrease the initial size then.... :hehe:

Mike John SSCertifiable Points: 7216 More actions · Answer 5

DBCC SHOINKDATABASE will not allow a shrink below the original size, but DBCC SHRINKFILE will allow you to shrink to any desired size. (provided this size is larger than the currently used space!)

Mike John

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

TDP (1/19/2009)
You would think that if you cannot shrink the file below the initial size they'd at least give us the possibility to be able to decrease the initial size then.... :hehe:

Actually shrink file does let you shrink the files so they’ll be smaller then the initial size. Check the script bellow that shows it:

--Create the database. Notice the size of the files

CREATE DATABASE MyDB

ON

( NAME = MyDB_dat,

FILENAME = 'c:\MyDB.mdf',

SIZE = 10MB,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = MyDB_log,

FILENAME = 'c:\MyDB.ldf',

SIZE = 3MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )

go

use MyDB

go

--Get the file's size

select name, size from MyDB.sys.database_files

--Srinking both log and data files

dbcc shrinkfile('MyDB_Dat',1)

go

dbcc shrinkfile('MyDB_log',1)

go

--Compare the new size with the size before the use of dbcc shrinkfile

select name, size from MyDB.sys.database_files

go

--Cleanup the mess:-)

use master

go

drop database MyDB

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TDP Ten Centuries Points: 1144 More actions · Answer 7

Thanks guys. Think there might have been open transactions or replication that were not allowing me to shrink the file to less than the initial size. 🙂

binary lumberjack SSC Eights! Points: 890 More actions · Answer 8

You can't shrink below the initial size. Your only option is to back up the database, drop and recreate it with a lower initial size, and then restore. You should only set the initial size when you know you will always need at least that much space.

tosscrosby SSCoach Points: 18045 More actions · Answer 9

Mike John (1/19/2009)
DBCC SHOINKDATABASE
Mike John

New in SQL 2005???? 😛

-- You can't be late until you show up.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

binary lumberjack (1/19/2009)
You can't shrink below the initial size. Your only option is to back up the database, drop and recreate it with a lower initial size, and then restore. You should only set the initial size when you know you will always need at least that much space.

Again - if you use shrinkdatabase, that might be true, but shrinkfile DOES in fact allow you to make the initial size smaller.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jason-ellen SSC Journeyman Points: 85 More actions · Answer 11

It looks like shrinkfile worked as I can see all the expected size from sys.database_files and sqlperf(logspace). But the actual size of the logfile still keeps the same physical size as the initial size.

nlindebringhs SSC Rookie Points: 42 More actions · Answer 12

Make sure the log has been backed up first or set the recovery model to simple.

This will allow the log file to be shrunk to a size smaller than the original file size specified.

hmbmeister SSC-Addicted Points: 417 More actions · Answer 13

shrinkfile will let you go below the initial size. I shrunk the file and tried to lower the initial size, stopped and restarted the sql service and it went right back to the original initial size. Does that make any sense?

WHug Ten Centuries Points: 1248 More actions · Answer 14

From BOL:

You can reduce the default size of an empty file by using DBCC SHRINKFILE target_size. For example, if you create a 5-MB file and then shrink the file to 3 MB while the file is still empty, the default file size is set to 3 MB. This applies only to empty files that have never contained data.