For SQL Server 2005+ You can use the following:
SELECT FILE_ID, type, type_desc,
CAST( CAST( (CASE WHEN size < 128 THEN 128 ELSE size END) * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' AS SIZE,
CASE max_size WHEN -1 THEN 'UNLIMITED' ELSE CAST( CAST( max_size * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB' END AS MAXSIZE,
CASE is_percent_growth
WHEN 0 THEN CAST( CAST( growth * 8. / 1024. AS INT ) AS VARCHAR(20) ) + 'MB'
WHEN 1 THEN CAST( growth AS VARCHAR(30) ) + '%'
END AS FILEGROWTH
FROM sys.master_files
WHERE database_id = DB_ID('model')
GO
This will retrieve File definitions for Model Database.
Later on You can use it either in CREATE DATABASE or ALTER DATABASE.
SSMS actually uses SMO to retrieve these parameters and then creates a Script which looks like:
CREATE DATABASE [aaa] ON PRIMARY
( NAME = N'aaa', FILENAME = N'D:\Data\aaa.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'aaa_log', FILENAME = N'D:\Logs\aaa_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
If Your Application is creating the Database and You can't modify the application -
then You have to use ALTER DATABASE after creation is done:
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa', MAXSIZE = 102400KB )
GO
ALTER DATABASE [aaa] MODIFY FILE ( NAME = N'aaa_log', FILEGROWTH = 10240KB )
GO
This requires at least ALTER DATABASE Permission
(implied by ALTER ANY DATABASE Server Permission)
Obviously, You can generate that script using the first code sample (FROM sys.master_files).
I wouldn't use sysaltfiles for SQL Server 2008R2 because it is Deprecated.
Good luck,
Roi
You can check for differences between value_in_use and value in sys.configurations for non dynamic changes.
Select * from sys.configurations
where value <> value_in_use and is_dynamic=0
to check for a changed logginmode
declare @tab table (name varchar(max),value varchar(max))
insert into @tab EXEC master.sys.xp_loginconfig 'login mode'
insert into @tab
SELECT 'current',CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows NT Authentication'
WHEN 0 THEN 'Mixed'
END
Select
CAST(
Case when (Select value from @tab where name='login mode')<>(Select value from @tab where name='current')
then 1 else 0 end as bit) as loginconfig_changed
Best Answer
If you want to change the Authentication Mode, you can do this by running the following SQL
You may also have to ensure that the
sa
login is enabled.You will however still have to restart the SQL Server service.