All Initially we have created all databases with initial size of 1 gb and now we are rethinking to take the size back to 250 mb is that possible to take the initial size back ,if so what is best and easy procedure or script to do it . if any one knows help out.
If you have not resized the Model database, you can simply shrink the user databases. Otherwise, you need to shrink Model database first. If you want to specify the size for data files only, go through the option of Shrink file.
i understand, it can be done database by database but i',m thingking to do it by script or procedure because i have databases like more than 100, so it takes too much long time and pain, if you have other ideas doing by script please provide.
[quote user="reethu"] because i have databases like more than 100, [/quote] Can you try something like this? sp_MsForEachDB 'USE ?; IF DB_ID() >4 DBCC shrinkfile (1, 250)' Note: You may have to add some more conditions to exclude any database if you want to.
Great it works perfectly to me But two small clarification. when i apply this command got two quastions :: 1. what if data is more than 205 mb 2.what if size is already lessthan that specified . i think it happens when above conditions meet . DBCC Cannot shrink to behind data in it. 2 ans:: it wont affect the less size it leaves as it is. are they correct if not give me the correction. Thanks again.
DBCC ShrinkFile will remove the free space only. This is what I get from books online: If target_size is specified, DBCC SHRINKFILE tries to shrink the file to the specified size. Used pages in the part of the file to be freed are relocated to available free space in the part of the file retained. For example, if there is a 10-MB data file, a DBCC SHRINKFILE operations with a target_size of 8 causes all used pages in the last 2 MB of the file to be reallocated into any unallocated pages in the first 8 MB of the file. DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file. For example, if 7 MB of a 10-MB data file is used, a DBCC SHRINKFILE statement with a target_size of 6 shrinks the file to only 7 MB, not 6 MB. Hope this helps
HOw are you creating the databases, using a script? If so then simply change the value on that script , otherwise follow as Preethi referred.