Category: T-SQL

Today I like to write about a problem that excists in a lot of environments. The file sizes and file growth of the databases are default configured with 1MB growth and 10% growth. I see a lot of environments and most of them do not have adjusted values for these settings. The default setting for database files is:

  • Data: initial size 2MB; Autogrowth: by 1MB, unrestricted growth
  • Log: initial size 1MB; Autogrowth: by 10 percent, unrestricted growth

Good reading about the default values is this article [Choosing Default Sizes for Your Data and Log Files]. So after reading that excellent blog article by Paul S. Randal you should be convinced that the default settings are not the best for your environment. But what should they be?

Of course the well known answer is: “It depends…”

In an ideal situation you know what the expected growth in data for your database is and you pre-size your database files accordingly. However I rarely see an environment where the DBA and/or the Developers have any clue about the growth in data for the next few months, years of even a longer time frame.

After changing file sizes too long by hand I started thinking about creating a script for automating this based on the file sizes the databases have when you run the script.

I started with defining 4 possible sizes for database file growth settings:

  • Databases that grow per 100MB
  • Databases that grow per 250MB
  • Databases that grow per 1.000MB
  • Databases that grow per 2.500MB

The next thing is to define what file sizes are the limits for these file growth settings:

  • Databases from 1MB to 249MB
  • Databases from 250MB to 2.499MB
  • Databases from 2.500MB to 4.999MB
  • Databases from 5.000MB to ∞

Next I started writing a script to programmatically create the T-SQL statement to change the settings for the databases.

First I check what the result will be for each database with an overview for every new file size and file growth setting:

SELECT [SMF].[database_id] AS [Database ID]

, [SMF].[name] AS [File name]

, [SMF].[size] * 8 / 1024 AS [Current size]

, ROUND ((([SMF].[size]) * 8 / 1024 / 100) , 2 , 0) * 100 + 100 AS [New size in growth per 100]

, ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 250 + 250 AS [New size in growth per 250]

, ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000 AS [New size in growth per 1000]

, ROUND ((([SMF].[size]) * 8 / 1024 / 2500) , 2 , 0) * 2500 + 2500 AS [New size in growth per 2500]

FROM [master].[sys].[master_files] [SMF]

The result from the script above will be something like this (results will vary per environment):

 

 

 

 

 

Next I created the script that creates a result based on the current file sizes:

SELECT [SMF].[database_id] AS [Database ID]

, [SMF].[file_id] AS [File ID]

, [SMF].[name] AS [File name]

, [SMF].[size] * 8 / 1024 AS [Current size]

, [SMF].[growth] * 8 / 1024 AS [Current growth]

, [SMF].[is_percent_growth] AS [Is current percentage growth]

, CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 100) , 2 , 0) * 100 + 100)

WHEN [SMF].[size] * 8 / 1024 < 2500 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 250 + 250)

WHEN [SMF].[size] * 8 / 1024 < 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000)

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 2500) , 2 , 0) * 2500 + 2500)

END AS [New file size]

, CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN '100'

WHEN [SMF].[size] * 8 / 1024 < 2499 THEN '250'

WHEN [SMF].[size] * 8 / 1024 < 4999 THEN '1000'

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN '2500'

ELSE '1024'

END AS [New file growth]

FROM [master].[sys].[master_files] [SMF]

In the results from that script you will see what the current sizes and growth settings are and the proposed new sizes and growth settings (results will vary per environment):


 

 

 

 

 

So with this logic in place I could create the final script that creates the T-SQL code for changing the database file sizes for each database in the SQL Server instance based on the current file sizes and defenitions made earlier. In scripts like these I never make them that the T-SQL code will automatically run. The scripts create T-SQL code that you need to copy paste into a new query window so that you can check if the script accomplishes what you want. Before running the script below make sure your results are displayed in Text (CTRL + T)

In this script I use the SQLCMD mode option :CONNECT + Servername to be sure the changes will be done on the correct SQL Server instance.

SELECT ':CONNECT ' + @@SERVERNAME + '

ALTER DATABASE [' + CONVERT(nvarchar(1000), DB_NAME(SMF.database_id)) + '] MODIFY FILE ( NAME = ''' + RTRIM(SMF.name) + ''', SIZE = ' +

CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 250) , 2 , 0) * 100 + 100)

WHEN [SMF].[size] * 8 / 1024 < 2500 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size])* 8 / 1024 / 250) , 2 , 0) * 250 + 250)

WHEN [SMF].[size] * 8 / 1024 < 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 / 1024 / 1000) , 2 , 0) * 1000 + 1000)

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN CONVERT(nvarchar(100), ROUND ((([SMF].[size]) * 8 /1024 / 2500) , 2 , 0) * 2500 + 2500)

END + ', FILEGROWTH = ' + (

CASE

WHEN [SMF].[size] * 8 / 1024 < 100 THEN '100'

WHEN [SMF].[size] * 8 / 1024 < 2499 THEN '250'

WHEN [SMF].[size] * 8 / 1024 < 4999 THEN '1000'

WHEN [SMF].[size] * 8 / 1024 >= 5000 THEN '2500'

ELSE '1024'

END + 'MB )' )

FROM [master].[sys].[master_files] [SMF]

The results from this script are the T-SQL commands for changing the file sizes and file growth settings (results will vary per environment):


 

 

 

 

 

 

 

 

 

When you used CTRL + T to get the results to text the result will look like above. Select only the commands and copy them into a new Query windows:


 

 

 

 

 

 

 

Default you will see the :CONNECT + Server lines not in grey, to get your script to run correctly you first need to enable SQLCMD mode via the menu option [Query] and [SQLCMD Mode]

Now you can run the query created by the script to change your database file sizes and file growth settings.

Remember that every time you run the query the sizes will change. This script is mainly intended for first time running after installing a new server with databases or when you need to change the default settings for the first time.

Of course you could run this script every now and then. My advise in this case is to change only databases that needs to change and leave the already changed databases as they are.

There is impact when running the results from the script because databases are grown to new sizes and that takes disk I/O. So in heavy used environments I advise to run the results from the script planned in a time windows where there is lower usage or better in a maintenance window.

Today I got the question if it is possible to create a login trigger that only is triggered on SQL Server login’s (except SA of course)​ that are used from SQL Server Management Studio to connect to a SQL Server instance.

So the first thing you do in cases like this is to check if someone else has solved this already for you. I found the following post​ that had only part of the solution.​

​So I changed the code so that it does only block login attempts from SQL Server Management Studio with a SQL Server account. Domain accounts and domain groups keep working as requested and also SA keeps working.

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
IF  EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'TR_Block_Logon_Via_Management_Studio_SQL_Login')
DROP TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
GO

/****** Object:  DdlTrigger [TR_Block_Logon_Via_Management_Studio_SQL_Login]    Script Date: 08/11/2015 16:47:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login]
ON ALL SERVER
FOR LOGON
AS

BEGIN

DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE login_name <> 'sa'
AND nt_user_name = ''

IF @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [TR_Block_Logon_Via_Management_Studio_SQL_Login] ON ALL SERVER
GO

There is however a way to bypass this solution so it is not completely fool proof. It is meant as a first blocking.