Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Changing the initial size of db file Expand / Collapse
Author
Message
Posted Monday, January 19, 2009 5:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
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?
Post #639219
Posted Monday, January 19, 2009 5:39 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 617, Visits: 1,276
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
Custom cleanup script for backups
Post #639227
Posted Monday, January 19, 2009 5:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
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.
Post #639231
Posted Monday, January 19, 2009 5:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 11, 2013 2:29 PM
Points: 27, Visits: 126
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.
Post #639232
Posted Monday, January 19, 2009 5:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
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....
Post #639239
Posted Monday, January 19, 2009 6:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 2,667, Visits: 5,844
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



Post #639272
Posted Monday, January 19, 2009 6:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 2,122, Visits: 5,476
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....


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/
Post #639288
Posted Monday, January 19, 2009 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 4, 2014 1:52 AM
Points: 26, Visits: 183
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. :)
Post #639296
Posted Monday, January 19, 2009 8:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 19, 2011 7:23 AM
Points: 16, 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.
Post #639352
Posted Monday, January 19, 2009 9:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
Mike John (1/19/2009)
DBCC SHOINKDATABASE
Mike John


New in SQL 2005???? :P


-- You can't be late until you show up.
Post #639383
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse