November 6, 2015 at 10:48 am
I can run this command to make changes for 1 DB USE [master]
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO
Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.
November 6, 2015 at 2:49 pm
You can run the following with the results to text and it will generate the statements for you.
SELECT 'ALTER DATABASE ['+d.name+'] MODIFY FILE (NAME=N'''+mf.name+''', FILEGROWTH = 10000KB)'+CHAR(13)+'GO'
FROM sys.master_files mf
JOIN sys.databases d
ON mf.database_id=d.database_id
WHERE mf.type=1
AND d.database_id>4
November 6, 2015 at 4:45 pm
Chitown (11/6/2015)
Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server?
How do you imagine running a script without connecting to the server? Telepathically over the air? 🙂
In the Registered Servers window (View -> Registered Servers) you can create a New Server Group, then add all the 200 servers into the group. Then select that group and click New Query. It will open a new window and connect automatically to all the servers. So any query you run in this window will be executed on all the servers.
November 9, 2015 at 10:01 am
JeremyE (11/6/2015)
You can run the following with the results to text and it will generate the statements for you.
SELECT 'ALTER DATABASE ['+d.name+'] MODIFY FILE (NAME=N'''+mf.name+''', FILEGROWTH = 10000KB)'+CHAR(13)+'GO'FROM sys.master_files mf
JOIN sys.databases d
ON mf.database_id=d.database_id
WHERE mf.type=1
AND d.database_id>4
November 9, 2015 at 10:27 am
the script provided just generates the commands, soy uo can review them easily.
you'd want to run the results of the commands, or stick them into a variable as a stack of commands via FOR XML and EXECUTE(@sqlcommand)
Lowell
November 9, 2015 at 10:27 am
Jeremy's script wont alter any database,
you need to select the out put of the query and run it in another query window.
You can develop dynamic query on top of this and if you know the list of servers, you can try generating sqlcmd command for each server from the list.
November 9, 2015 at 10:35 am
So I ran the above script from the central Management servers and inserted all the data into a temp table.
CREATE TABLE #TEST
(SQLQUERY VARCHAR (1000))
INSERT INTO #TEST SELECT 'ALTER DATABASE ['+d.name+'] MODIFY FILE (NAME=N'''+mf.name+''', FILEGROWTH = 102400KB)'+CHAR(13)+'GO'
FROM sys.master_files mf
JOIN sys.databases d
ON mf.database_id=d.database_id
WHERE mf.type IN (1) AND d.database_id>4
Now I have the server name column and sqlquery column.
Now, I was thinking maybe using the cursor to execute the alter statement.
November 9, 2015 at 2:53 pm
Update:
Still stuck.
declare @sqlcommand nvarchar (500)
declare CRS cursor
for
select command from #test
open CRS
FETCH NEXT FROM CRS
WHILE @@FETCH_STATUS = 0
begin
set @sqlcommand = N'command from #test'
exec SP_EXECUTESQL @sqlcommand
END
deallocate CRS
November 9, 2015 at 4:16 pm
You almost got it right:
declare @sqlcommand nvarchar (500)
declare CRS cursor
for
select command from #test
open CRS
FETCH NEXT FROM CRS INTO @sqlcommand
WHILE @@FETCH_STATUS = 0
begin
-- PRINT @sqlcommand
exec SP_EXECUTESQL @sqlcommand
FETCH NEXT FROM CRS INTO @sqlcommand
END
deallocate CRS
November 10, 2015 at 8:37 am
ALTER DATABASE [SSISDB] MODIFY FILE (NAME=N'log', FILEGROWTH = 66000KB)
GO;
ALTER DATABASE [PRODUCT_DB_SSIS_SOURCE] MODIFY FILE (NAME=N'PRODUCT_DB_SSIS_SOURCE_log', FILEGROWTH = 66000KB)
GO;
So when I run this declare @sqlcommand nvarchar (500)
declare CRS cursor
for
select command from #test
open CRS
FETCH NEXT FROM CRS INTO @sqlcommand
WHILE @@FETCH_STATUS = 0
begin
-- PRINT @sqlcommand
exec SP_EXECUTESQL @sqlcommand
FETCH NEXT FROM CRS INTO @sqlcommand
END
deallocate CRS I get this error when query #1 gets executed. "Incorrect syntax near 'GO'."
November 10, 2015 at 9:10 am
Finally :-):-)
drop table #test
create table #test (command varchar (500))
insert into #test SELECT 'USE [MASTER] ' + CHAR(13) + 'ALTER DATABASE [' + d.name + N'] '
+ 'MODIFY FILE (NAME = N''' + mf.name + N''', FILEGROWTH = 66000KB)'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM sys.master_files mf
JOIN sys.databases d ON mf.database_id = d.database_id
WHERE d.database_id > 4
AND d.state_desc <> 'offline'
AND mf.type_desc = 'LOG';
select * from #test
declare @sqlcommand nvarchar (500)
declare CRS cursor
for
select command from #test
open CRS
FETCH NEXT FROM CRS INTO @sqlcommand
WHILE @@FETCH_STATUS = 0
begin
-- PRINT @sqlcommand
exec SP_EXECUTESQL @sqlcommand
FETCH NEXT FROM CRS INTO @sqlcommand
END
deallocate CRS
November 10, 2015 at 10:05 am
Remove GO. It's a batch separator for SSMS, not a command.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply