Multiple DB File Expansions Script Problem
-
All-
I am attempting to increase the size of multiple data files for multiple databases using a single script. After running the script I noted that some of the files had expanded as expected and some did not. Has anyone seen this before? Here's the script (db names changed to protect the innocent):
--begin script--
use master
go
--update data file sizes and disable filegrowth
ALTER DATABASE DB1 MODIFY FILE(NAME =DB1_Data, SIZE=50MB, FILEGROWTH=0MB)
ALTER DATABASE DB2 MODIFY FILE(NAME =DB2_Data,FILEGROWTH=0)
ALTER DATABASE DB3 MODIFY FILE(NAME=DB3_Data,FILEGROWTH=0)
ALTER DATABASE DB4 MODIFY FILE(NAME=DB4_Data,FILEGROWTH=0)
ALTER DATABASE DB5 MODIFY FILE(NAME=DB5_Data, SIZE=250MB, FILEGROWTH=0MB)
ALTER DATABASE [DB 6] MODIFY FILE(NAME=DB6_Data, SIZE=100MB, FILEGROWTH=0MB)
ALTER DATABASE DB7 MODIFY FILE(NAME=DB7_Data, SIZE=100MB, FILEGROWTH=0MB)
ALTER DATABASE DB8 MODIFY FILE(NAME=DB8_dat, SIZE=1024MB, FILEGROWTH=0MB)
ALTER DATABASE DB9 MODIFY FILE(NAME=DB9_Data, SIZE=60MB, FILEGROWTH=0MB)
ALTER DATABASE DB10 MODIFY FILE(NAME=DB10_Data, SIZE=20MB, FILEGROWTH=0MB)
ALTER DATABASE DB11 MODIFY FILE(NAME=DB11_Data, SIZE=20MB, FILEGROWTH=0MB)
ALTER DATABASE DB12 MODIFY FILE(NAME=DB12_dat, SIZE=300MB, FILEGROWTH=0MB)
ALTER DATABASE DB13 MODIFY FILE (NAME=DB13_Data,FILEGROWTH=0)
ALTER DATABASE DB14 MODIFY FILE(NAME=DB14_Data, SIZE=200MB, FILEGROWTH=0MB)
ALTER DATABASE DB15 MODIFY FILE(NAME=DB15_Data, SIZE=200MB, FILEGROWTH=0MB)
ALTER DATABASE DB16 MODIFY FILE(NAME=DB16_Data, SIZE=10MB, FILEGROWTH=0MB)
ALTER DATABASE DB17 MODIFY FILE(NAME=DB17_Data, SIZE=20MB, FILEGROWTH=0MB)
ALTER DATABASE DB18 MODIFY FILE (NAME=DB18_Data,SIZE=200,FILEGROWTH=0)
ALTER DATABASE DB19 MODIFY FILE(NAME=DB19_Data, SIZE=20MB, FILEGROWTH=0MB)
ALTER DATABASE DB20 MODIFY FILE(NAME=DB20_dat, SIZE=500MB, FILEGROWTH=0MB)
ALTER DATABASE DB21 MODIFY FILE(NAME=DB21_Data, SIZE=25MB, FILEGROWTH=0MB)
ALTER DATABASE DB22 MODIFY FILE(NAME=DB22_Data, SIZE=50MB, FILEGROWTH=0MB)
ALTER DATABASE DB23 MODIFY FILE(NAME=DB23_Data, SIZE=30MB, FILEGROWTH=0MB)
ALTER DATABASE DB24 MODIFY FILE(NAME=DB24_Data, SIZE=50MB, FILEGROWTH=0MB)
ALTER DATABASE DB25 MODIFY FILE(NAME=DB25_Data, SIZE=750MB, FILEGROWTH=0MB)
ALTER DATABASE DB26 MODIFY FILE(NAME=DB26_Data, SIZE=30MB, FILEGROWTH=0MB)
ALTER DATABASE DB27 MODIFY FILE(NAME=DB27_dat, SIZE=100MB, FILEGROWTH=0MB)
ALTER DATABASE DB28 MODIFY FILE(NAME=DB28_Data, SIZE=5120MB, FILEGROWTH=0MB)
ALTER DATABASE DB29 MODIFY FILE(NAME=DB29_Data, SIZE=30MB, FILEGROWTH=0MB)
ALTER DATABASE DB30 MODIFY FILE(NAME=DB30_Data, SIZE=30MB, FILEGROWTH=0MB)
ALTER DATABASE DB31 MODIFY FILE(NAME=DB31_Data, SIZE=30MB, FILEGROWTH=0MB)
ALTER DATABASE DB32 MODIFY FILE(NAME=DB32_Data, SIZE=100MB, FILEGROWTH=0MB)
ALTER DATABASE DB33 MODIFY FILE(NAME=DB33_Data, SIZE=500MB, FILEGROWTH=0MB)
ALTER DATABASE DB34 MODIFY FILE(NAME=DB34_Data, SIZE=100MB, FILEGROWTH=0MB)
ALTER DATABASE DB35_Directory MODIFY FILE(NAME=DB35_Directory_dat, SIZE=25MB, FILEGROWTH=0MB)
ALTER DATABASE DB36 MODIFY FILE(NAME=DB36_Data, SIZE=1536MB, FILEGROWTH=0MB)
ALTER DATABASE DB37 MODIFY FILE(NAME=DB37_Data, SIZE=50MB, FILEGROWTH=0MB)
ALTER DATABASE DB38 MODIFY FILE(NAME=DB38_Data, SIZE=100,FILEGROWTH=0)
GO
--end script--
I have parsed the script and double checked the logical file names. Some of them were incorrect during the original execution of the script. I immediately followed with a second script containing ALTER DATABASE commands to expand only those that failed. I don't see any correlation between the inconsistencies and the bad logical file names contained in the original script. Any ideas?
Thanks for any help!
Mike
DBA-Wannabe, Enterprise Systems Engineer by trade
-
You need to make just a single change per ALTER DATABASE, and in your case you are changing both the SIZE and the FILEGROWTH. Break those out into two ALTER DATABASE statements and you should be fine.
Let us know how that works out for you.
-
David,
I've attempted to test your suggestion in my test environment but I haven't been able to reproduce the filespec errors I saw in production. I've attempted this with one, two and three databases being modified in a single batch. Is this a numbers thing with the quantity of DBs being modified?
Here's the script I used for testing:
------begin db create script------
USE master
GO
CREATE DATABASE TestDB1
ON
( NAME = 'TestDB1_dat',
FILENAME = 'D:\mssql\data\TestDB1.mdf',
SIZE = 25,
MAXSIZE = 100,
FILEGROWTH = 25)
LOG ON
( NAME = 'TestDB1_log',
FILENAME = 'D:\mssql\data\TestDB1.ldf',
SIZE = 2,
MAXSIZE = 5,
FILEGROWTH = 1)
GO
CREATE DATABASE TestDB2
ON
( NAME = 'TestDB2_dat',
FILENAME = 'D:\mssql\data\TestDB2.mdf',
SIZE = 25,
MAXSIZE = 100,
FILEGROWTH = 25)
LOG ON
( NAME = 'TestDB2_log',
FILENAME = 'D:\mssql\data\TestDB2.ldf',
SIZE = 2,
MAXSIZE = 5,
FILEGROWTH = 1)
GO
CREATE DATABASE TestDB3
ON
( NAME = 'TestDB3_dat',
FILENAME = 'D:\mssql\data\TestDB3.mdf',
SIZE = 25,
MAXSIZE = 100,
FILEGROWTH = 25)
LOG ON
( NAME = 'TestDB3_log',
FILENAME = 'D:\mssql\data\TestDB3.ldf',
SIZE = 2,
MAXSIZE = 5,
FILEGROWTH = 1)
GO
------end db create script------
------begin db file modify script------
USE MASTER
GO
ALTER DATABASE TestDB1 MODIFY FILE(NAME=TestDB1_DAT, SIZE=50, FILEGROWTH=0)
ALTER DATABASE TestDB2 MODIFY FILE(NAME=TestDB2_DAT, SIZE=50, FILEGROWTH=0)
ALTER DATABASE TestDB3 MODIFY FILE(NAME=TestDB3_DAT, SIZE=50, FILEGROWTH=0)
go
------end db file modify script------
Mike
-
I probably didn't describe that clearly enough. It's not the number of databases being altered per batch that's the problem. It's the number of changes per database that is the problem. You can only make a single change using MODIFY FILE per ALTER DATABASE statement.
In other words, you can change the SIZE or the FILEGROWTH, but not both in a single ALTER DATABASE statement.
So replace
ALTER DATABASE TestDB1 MODIFY FILE(NAME=TestDB1_DAT, SIZE=50, FILEGROWTH=0)
with
ALTER DATABASE TestDB1 MODIFY FILE(NAME=TestDB1_DAT, SIZE=50)
and
ALTER DATABASE TestDB1 MODIFY FILE(NAME=TestDB1_DAT, FILEGROWTH=0)
Clear as mud?
-
I understand the 'single change per statement' theory (call me a lungfish...mud is my thing). The issue I have is that I can't reproduce the problem by using multiple changes in a single statement on my test server. In other words the following works:
ALTER DATABASE TestDB1 MODIFY FILE(NAME=TestDB1_DAT, SIZE=50, FILEGROWTH=0)
To re-phrase, management won't go for the fix unless I can prove the problem exists (state government - political bullshxx). Anyway, how can I prove this is the issue (short of running the script on production) or find a documented instance of the problem online?
Mike
-
You could point them to BOL, which explicitly states this. Check out the MODIFY FILE description section under the ALTER DATABASE help screen, and you'll at least have evidence that MS says not to do it.
As for why it sometimes works and sometimes doesn't, without knowing what it does internally when it's parsing the ALTER DATABASE statement, I really have no clue.
When I face political issues like that, I usually let them nicely understand that I'm abdicating all responsibility if they aren't willing to follow the advice of the vendor. Something like "I'll be happy to go against the SQL Server documentation, but I can't guarantee that doing so won't completely erase all of your data".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply