SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing the initial size of db file


Changing the initial size of db file

Author
Message
TDP
TDP
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 190
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?
Roshan Jospeh
Roshan Jospeh
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 2093
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
TDP
TDP
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 190
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
hema kumar-479337
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 138
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
TDP
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 190
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
Mike John
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3310 Visits: 5983
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
Adi Cohn
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7695 Visits: 6594
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
TDP
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 190
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. Smile
binary lumberjack
binary lumberjack
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 37
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
  tosscrosby
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5490 Visits: 5316
Mike John (1/19/2009)
DBCC SHOINKDATABASE
Mike John


New in SQL 2005???? Tongue

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search