Archive for the ‘Database Administration’ Category

How to view the database auto-growth settings and correct them?

Auto-growth is the process which the SQL Server engine expands the size of the database data or log files when they are out of space. The growth size will depend on the settings you have specified for the file growth options for your databases. There are three settings that you can set for the file growth by:

  • Specific size
  • Percentage of the current size
  • Not grow at all

In addition, you have two options when configuring the maximum size for the database data or log files:

  • Unrestricted growth: the files will keep on growing until you are out of disk space
  • Restricted growth: you specify the maximum file size in MB for the files

You should always set the initial size for the database data and log files appropriately to prevent the auto-growth events. When an auto-growth event occurs, the negative impacts can be:

  • Locking and blocking: when auto-growth occurs, a database level lock is required. All the processes will need to wait until auto-growth completes.
  • Fragmentations: when auto-growth occurs, SQL Server will need to find additional disk space somewhere in the disk drive(s) and usually the disk space will not physically next to the existing database file space.

When creating a new database, you should always establish the auto-growth settings in addition to setting the initial sizes correctly. By default, new databases are created based on the settings of the Model system database. The Model database has the default settings of auto-growth by 1 MB for data files and 10% for log files. Both have unrestricted growth. The files will grow until the disk is full. Imagine you have a transaction intensive OLTP database and it’s over 500GB for the data file and 200GB for the log file and the auto-growth setting is using the default settings. Whenever an auto-growth event is needed, the data file will grow by 1MB and the log file will growth by 2,000MB or more (10% of the existing log file size). It’s definitely not pretty as the auto-growth events for the data file will occur constantly as it grows by 1MB.

Now, if the SQL Server instance has been in production for over 2 years and there are over 20 databases, how you can quickly identify the auto-growth settings for each of the databases without going through each one by right-clicking the databases and selecting and viewing the properties of the databases and then browsing to the Files section and finally clicking the ellipses button to view the auto-growth setting? The following script can help you:

-- auto growth settings for data and log files
select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth 
    WHEN 1 THEN 'Yes'
    ELSE 'No'
  END AS [is_percent_growth]
, CASE mf.is_percent_growth
	WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
	WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
  END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
    WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
    WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128)) 
  END AS [next_auto_growth_size_MB] 
, CASE mf.max_size
	WHEN 0 THEN 'No growth is allowed'
	WHEN -1 THEN 'File will grow until the disk is full'
	ELSE CONVERT(VARCHAR, mf.max_size)
  END AS [max_size]
, physical_name  
from sys.master_files mf

You can identify how often an auto-growth event occurs by:

  • Querying the default trace for the “Data File Auto Grow” and “Log File Auto Grow” events
  • If the default trace is not running, then run a trace to capture the “Data File Auto Grow” and “Log File Auto Grow” events

Auto-Grow Events

It is highly recommended that you pre-size the data and log files to eliminate or reduce the auto-growth events. You can modify the initial size, max size, and the file growth settings of the database data and log files by using the following code:

ALTER DATABASE DatabaseName
MODIFY FILE
(
NAME = FileName
, SIZE = InitialSize
, MAXSIZE = RestrictedMaxSize
, FILEGROWTH = SizeToGrowBy
)

For example, to modify the initial size, max size, and the file growth settings for the data and log files for the TestDB:

-- modify the data file settings
ALTER DATABASE TestDB
MODIFY FILE
(
NAME = TestDB
, SIZE = 60MB
, MAXSIZE = 100MB
, FILEGROWTH = 5MB
) 
-- modify the log file settings
ALTER DATABASE TestDB
MODIFY FILE
(
NAME = TestDB_log
, SIZE = 60MB
, MAXSIZE = 100MB
, FILEGROWTH = 12%
)

How to get notified on new database(s) creation(s)?

You wish to document the databases in your SQL Server environment for future references in addition to compile a run-book for troubleshooting, disk space planning, and capacity planning purposes. But, due to the number of databases and servers in your SQL Server environment, it is not an easy task. The application support team can install new applications without your knowledge or your team’s approval and new databases will be created when the new applications are installed. There was and still is no standard procedure on documentations. While you can’t time-travel back to undo this to help the team and yourself understand the importance of documentations, it is never too late to start doing the right things. You can start by documenting the new databases whenever they are created and then document the databases created in the past when you get a chance. In addition, it is critical to backup the newly created database(s) to have a baseline full backup or else the transaction log backups will fail for the newly created database(s) that are in full recovery model and no full backups have been ran (if your transaction log backup job doesn’t have the mechanism to check of missing full backups and then run the full backups first before running the transaction log backups).  Sure, you can try to ask the application team to give you advanced notices before installing the new applications that will have new databases to be created. Most likely due to their heavy workload, they will be too busy or wouldn’t be able to remember to notify you or the DBA team. Wouldn’t it be nice if you can automate the process of notifying the team on new database(s) creations?

Here are the overview steps and concepts:

  1. Develop the T-SQL code to find new databases in the past hour.
  2. Schedule a SQL Server Agent job to run the T-SQL code on an hourly basis and notify the designated recipients if there are new database(s).

Here are the implementation steps:

  1. Develop the T-SQL code to find new databases in the past hour.
  2. -- Alert on the new database(s) created in the past hour
    -- Replace with your recipients in line #24
    
    -- declare the variable for interval hours to detect the new database(s)
    DECLARE @IntervalInHours TINYINT -- check new database(s) in the past hour(s)
    SET @IntervalInHours = 1 -- replace with the number of hours you wish to check for new database(s)
    
    -- check for new database(s)
    IF EXISTS(	
    	SELECT 1 
    	FROM master.sys.databases db 
    	WHERE DATEDIFF(HOUR, db.create_date, GETDATE()) <= @intervalInHours 
    		AND db.name <> 'tempdb') 
    BEGIN -- new database(s) exist(s)
    
    -- declare the required variables 
    DECLARE @Recipients VARCHAR(123) -- recipients for this alert
    , @IsDBMailEnabled BIT
    , @MailProfile VARCHAR(123)
    , @MailSubject VARCHAR(123)
    , @Subject VARCHAR(123)
    , @TableHTML VARCHAR(MAX)
    
    SET @Recipients = 'email@company.com' -- specify your recipients here, separate with ;
    
    -- check if database mail is enabled
    SELECT @IsDBMailEnabled = CONVERT(INT, ISNULL(value, value_in_use))
    FROM sys.configurations
    WHERE name = 'Database Mail XPs'
    
    IF @IsDBMailEnabled = 0
    	RAISERROR ('Warning: Database mail is disabled. Database mail is required to send out reports', 12, 1)
    
    ELSE
    	BEGIN
    		SELECT @TableHTML = 
    		'<html><table border = "1">' +
    		'<tr>
    			<th>Database Name</th>
    			<th>Create Date</th>
    			<th>DB Owner</th>
    			<th>Compatibility Level</th>
    			<th>Recovery Model</th>
    		</tr>'
    		
    		SELECT @TableHTML = @TableHTML +
    			'<tr>' +
    			'<td>' + name + '</td>' +
    			'<td>' + CONVERT(VARCHAR, create_date, 13) + '</td>' +
    			'<td>' + suser_sname(owner_sid) + '</td>' +
    			'<td>' + CONVERT(VARCHAR(3),compatibility_level) + '</td>' +
    			'<td>' + recovery_model_desc + '</td></tr>'
    		FROM master.sys.databases db 
    		WHERE (DATEDIFF(HOUR, db.create_date, GETDATE()) <= @IntervalInHours)
    				AND (db.name <> 'tempdb') 
    		SELECT @TableHTML = @TableHTML + '</table></html>'
    		
    		-- Send email notification
    		SELECT @MailSubject = CONVERT(VARCHAR(50),@@servername) + ' New database(s) alert!'
    		
    		-- Find the database mail profile name
    		create table #dbmail_profile
    		(
    		profileid int null,
    		profilename varchar(125) null,
    		accountid int null,
    		accountname varchar(125) null,
    		sequencenumber int null
    		)
    		insert #dbmail_profile
    		EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
    
    		select @MailProfile = profilename
    		from #dbmail_profile
    		where sequencenumber = 1
    
    		drop table #dbmail_profile
    
    		EXEC msdb.dbo.sp_send_dbmail
    			@profile_name = @MailProfile,
    			@recipients = @Recipients,
    			@subject = @MailSubject,
    			@body_format = 'HTML',
    			@body = @TableHTML;
    	END
    END
    
  3. Schedule a SQL Server Agent job to run the T-SQL code on an hourly basis and notify the designated recipients if there are new database(s).

REFERENCES:

SUSER_SNAME()
http://msdn.microsoft.com/en-us/library/ms174427(v=sql.105).aspx

What are the possible recovery models for the SQL Server databases?

Have you ever had a conversation with your colleagues where they keep on saying “recovery mode” instead of “recovery model” of a database? You might have even second guessed yourself if you were the one who is mistaken on the correct terminology. Make no mistake about it. You are certainly correct. But, there’s no need to argue with them or correct them. You can just keep on consistently refer to it correctly. Others will follow when they know you are correct. Next time, you should be more confident in yourself. It is “recovery model” not “recovery mode.” If you feel otherwise, please do share with us in the comment section. So, what exactly does recovery model mean and what are the possible recovery models that are available in SQL Server and when do you select one over the others? Here’s a brief explanation just to refresh your memory.

Recovery models control the transaction log maintenance. You choose one over the others based on your business rules and the recoverability that the databases must have in case of disaster. There are 3 different types of recovery models:

  • Simple
  • Bulk-logged
  • Full

Simple recovery model is usually selected for the databases that are in development or testing servers where the recoverability of the data is not critical to the business. In case of disaster, you can only recover up to the most recent full or differential backup as you can’t take transaction log backups in the simple recovery model.

Bulk-logged recovery model should be used only when you need to perform large bulk operations such as bulk importing over large amounts of data. It should only be used on a temporary basis and switch back to full when the bulk operations are completed. You can select it to reduce the transaction log space during a bulk transaction as they won’t be logged. In case of disaster, you cannot recover the database to the point of failure. On a side note: “Large amounts of data” is a fully loaded statement and can cause endless debates on what is considered as large. Many of the documentations have stay away from being specific just to avoid debates. But, I will say >= 5 GB should be considered as large. All of us are certainly entitled to our own opinions. Either way you spin it, others can pick it apart and get into debate with you if they wish to. Let them talk and hear them out. As per Stephen Covey, “SEEK FIRST TO UNDERSTAND, THEN TO BE UNDERSTOOD.” Let’s get into habit number 5.

Full recovery model should be the recovery model for all production databases until otherwise specified with good reasons. If full, differential, and transaction log backups are accounted for, it is the only recovery model that can recover to any point in time.

How can you find the recovery model for each of the databases in the SQL Server instance? Here’s the script:

-- Querying the recovery model for each of the databases
SELECT name as DatabaseName
, recovery_model_desc
FROM SYS.DATABASES

REFERENCES:

Recovery Model Overview
http://msdn.microsoft.com/en-us/library/ms189275(v=sql.105).aspx

Backup Under the Bulk-Logged Recovery Model
http://msdn.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

Operations That Can Be Minimally Logged
http://msdn.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

How to relocate the data and log files for the System databases to different hard disk drives?

Last week you were assigned to administer more than 20 instances of SQL Server after the company has acquired a new company for expansion. After your initial check of the instances, you have found that an important instance that is installed on a physical server has all the system databases located in the C:\ drive. Most likely, it was installed by the previous “Accidental SQL Server DBA” choosing all the default options during the installation process. For unknown reasons, he/she did not get a chance to relocate the system databases data and log files to a separate hard disk drive. All the System databases (master, msdb, model, and tempdb) will by default be located in the C:\ drive unless otherwise specified during installation. (We will discuss the resource, distribution, reportserver, and the reportservertempdb databases in a separate blog.)

Despite your effort in helping the System Administration team and management to understand not to install application software on the same hosts that have SQL Server installed, no avail, as budget, politics, and bureaucracies usually win at the end of the day. Two applications were installed yesterday. The application software installed or to be installed usually also have C:\ drive as the default installation location. After your in-depth research and implementation of negotiation skills and diplomacy, you were able to have the System Administration team and management agree to add extra hard disk drives on the physical server so that you can relocate the System databases and user databases off the C:\ drive. Now is your chance to correct the “Common Man” setup by the predecessors. Let’s do it right this time around.

Preparation steps:

1. You must know that the file relocation process for the master and tempdb databases are different from the msdb and model databases. You may think this is funny as it did not happen to you, I have seen very experienced SQL Server DBAs in emergency troubleshooting bridge calls copied the tempdb data and log files over to different hard disk drives even though they are the same ones who know very well that the tempdb database gets recreated every time SQL Server service restarts. The system databases data and log files relocation process will require a SQL Server service restart, therefore, no need to copy the tempdb data and log files over to separate hard disk drives. To relocate the data and log files for the master database, you will need to browse to the SQL Server Configuration Manager and edit the Startup Parameters values to point to the location for the data and log files.

2. Submit a change request, schedule a maintenance downtime window, and notify all parties for this planned task. Plan this according to your business rules. The time for the approval and the maintenance downtime window will vary.

Once ready, here are the implementation steps:

1. Backup all user and system databases prior to starting this maintenance task. There’s no need to backup the tempdb database, you can’t even if you tried. Please refer to http://www.handsonsqlserver.com/how-to-run-full-backups-for-ad-hoc-emergency-roll-outs/ on how to backup all databases.

2. Capture the current locations of the system databases and output to store in an Excel spreadsheet.

-- Identify the current physical paths of the system databases
select DB_NAME(mf.database_id) as DatabaseName
, type_desc as FileTypeDesc
, name as FileName
, physical_name
from sys.master_files mf
where DB_NAME(mf.database_id) IN ('master', 'msdb', 'model', 'tempdb')

3. Create the folders in the hard disk drives to store the data, log, error log, agent error log files.

4. With the exception of the master database, for each file to be moved, run the TSQL statement to modify the location of the database data and log files.

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' );

For example:

-- modifying the system databases data and log files locations

USE master
GO
-- msdb
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBData,
FILENAME ='C:\SECOND\MSDBData.mdf'
)
ALTER DATABASE MSDB
MODIFY FILE (NAME = MSDBLog,
FILENAME ='C:\SECOND\MSDBLog.ldf'
)
-- model
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev,
FILENAME ='C:\SECOND\modeldev.mdf'
)
ALTER DATABASE MODEL
MODIFY FILE (NAME = modellog,
FILENAME ='C:\SECOND\modellog.ldf'
)
-- tempdb, no need to copy the tempdb data and log files to the new locations
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = tempdev ,
FILENAME ='C:\SECOND\tempdb.mdf'
)
ALTER DATABASE TEMPDB
MODIFY FILE (NAME = templog,
FILENAME ='C:\SECOND\templog.ldf'

5. To relocate the master database data and log file: Browse to SQL Server Configuration Manager, then click on the SQL Server Services node, right-click on the corresponding SQL Server instance, and then click Properties. Click on the Advanced tab then edit the Startup Parameters values to point to the location for the data, log, and error log files. The click the OK button. Note: Following –d is the data file location, -l is the log file location, and –e is the error log. For example: -dD:\Master\Data\master.mdf;-eE:\ErrorLog\ERRORLOG;-lL:\Master\Log\mastlog.ldf.

6. Stop the SQL Server service.

7. Move the master.mdf, mastlog.ldf, tempdb.mdf, templog.ldf, model.mdf, modellog.ldf, msdbdata.mdf, and msdblog.ldf files to the new locations.

8. Start the SQL Server service.

9. Verify the file locations changes.

-- Identify the current physical paths of the system databases
select DB_NAME(mf.database_id) as DatabaseName
, type_desc as FileTypeDesc
, name as FileName
, physical_name
from sys.master_files mf
where DB_NAME(mf.database_id) IN ('master', 'msdb', 'model', 'tempdb')

10. To change the SQL Server Agent log path: In SQL Server Management Studio, expand SQL Server Agent, right-click Error Logs and click Configure, in the Configure SQL Server Agent Error Logs dialog box, specify the new location for the SQLAGENT.OUT file. For example: D:\SQLServerAgentErrorLog\SQLAGENT.OUT.

11. To change the database default location: In SQL Server Management Studio, right-click the SQL Server server and click Properties. In the Server Properties dialog box, select Database Settings. In Database Default locations, key in or browse to the new location for the data and log files. The click the OK button.

12. Restart the SQL Server service to complete.

REFERENCE:

Moving System Databases
http://msdn.microsoft.com/en-us/library/ms345408(v=sql.105).aspx

How to relocate the data and log files for user databases to different hard disk drives?

Your company has acquired a new company in South Florida. You have been requested to fly down to Miami to help with the transition and do a routine check on the SQL Servers in the office. On one of the SQL Servers that resides on a physical server, you have found that even though the server has multiple hard disk drives, the data and log files of the user databases are located in the D:\ drive. You have raised this concern and submitted a change request for a scheduled maintenance window over the weekend to relocate the data and log files to separate hard disk drives that are available in this physical server. The change request has been approved. You will have to work on and finish the data and log files relocation task this Saturday before hitting the beaches. The following steps can help you finish this change request quickly and correctly:

  1. Identify the database(s) you will need to relocate the data and log files and their current physical paths. Save the output to an Excel file.
  2. Take a backup of the database(s) to have the data and log files relocated in case a restore is needed.
  3. Create the corresponding folders for the data and log files in the available hard disk drives for the database(s) you plan to relocate the data and log files and make sure they have sufficient disk space to store the files.
  4. Run the TSQL statement(s) to set the database(s) offline.
  5. Move the data and log files to the new folders you had created.
  6. Run the TSQL statement(s) to modify the file locations for the database(s).
  7. Run the TSQL statement(s) to set the database(s) online.
  8. Verify the files locations changes.

Let’s do it. In this example, we will relocate the data, log, and filestream files for the AdventureWorks2008R2 database:

  1. Identify the database(s) you will need to relocate the data and log files and their current physical paths. Save the output to an Excel file.
  2. -- Identify the current physical paths of the database(s) data, log, and filestream files
    select DB_NAME(mf.database_id) as DatabaseName
    , type_desc as FileTypeDesc
    , name as FileName
    , physical_name
    from sys.master_files mf
    where DB_NAME(mf.database_id) = 'AdventureWorks2008R2' -- use IN for multiple databases
    
  3. Take a backup of the database(s) to have the data and log files relocated in case a restore is needed.
  4. -- take a backup of the database(s)
    BACKUP DATABASE AdventureWorks2008R2
    TO DISK = 'C:\SQLBackups\aw20008r2.bak'
    WITH COMPRESSION
    
  5. Create the corresponding folders for the data and log files in the available hard disk drives for the database(s) you plan to relocate the data and log files and make sure they have sufficient disk space to store the files.
  6. Run the TSQL statement(s) to set the database(s) offline.
  7. -- set the database(s) offline
    ALTER DATABASE AdventureWorks2008R2
    SET OFFLINE
    
  8. Move the data and log files to the new folders you had created.

  9. Run the TSQL statement(s) to modify the file locations for the database(s).
  10. -- modify the file locations for the database(s)
    ALTER DATABASE AdventureWorks2008R2
    MODIFY FILE ( NAME = AdventureWorks2008R2_Data
    , FILENAME = 'C:\AW\Data\AdventureWorks2008R2_Data.mdf'
    )
    ALTER DATABASE AdventureWorks2008R2
    MODIFY FILE ( NAME = AdventureWorks2008R2_Log
    , FILENAME = 'C:\AW\Log\AdventureWorks2008R2_Log.ldf'
    )
    ALTER DATABASE AdventureWorks2008R2
    MODIFY FILE ( NAME = FileStreamDocuments2008R2
    , FILENAME = 'C:\AW\FileStream\Documents2008R2'
    )
    
  11. Run the TSQL statement(s) to set the database(s) online.
  12. -- set the database online
    ALTER DATABASE AdventureWorks2008R2
    SET ONLINE
    
  13. Verify the files locations changes.
  14. -- verify the new physical paths
    select DB_NAME(mf.database_id) as DatabaseName
    , type_desc as FileTypeDesc
    , name as FileName
    , physical_name
    from sys.master_files mf
    where DB_NAME(mf.database_id) = 'AdventureWorks2008R2' -- use IN for multiple databases
    

Now, you can go enjoy your beers under the palm trees.

REFERENCES:

Moving User Databases
http://msdn.microsoft.com/en-us/library/ms345483(v=sql.105).aspx

How to query the databases logical data and log file names, physical path, and space utilization information?

You wish to query all the databases in the current SQL Server instance to find their database names, logical files names, physical path, total space reserved in MB, space used in MB, space free in MB, and % of space free. All these valuable information are stored in the sys.database_files system table. The space allocation and utilization information are stored as data pages. Note that 1 page = 8KB. 128 pages = 1 MB. To convert the data pages over to MB for the space used in MB, space free in MB, you must first use the FILEPROPERTY() function and pass in the logical file name and SpaceUsed as parameters, then divided by 128. Instead of a while loop, we can use the undocumented stored proc sp_MSForeachDB to loop through all databases in the instance. The following script is an example of how it can be done:

-- Querying the data and log file names, physical path, and space info

-- create a temp table to store the databases' files specs info for all databases
IF OBJECT_ID('TEMPDB..#DatabaseFilesSpecs') IS NOT NULL
DROP TABLE #DatabaseFilesSpecs

CREATE TABLE #DatabaseFilesSpecs
(
	ID INT identity (1,1)
	,Database_Name VARCHAR(123)
	,Logical_File_Name VARCHAR(123)
	,Physical_Name NVARCHAR(MAX)
	,TotalSpaceReservedMB DECIMAL(10,2)
	,SpaceUsedMB DECIMAL(10,2)
	,SpaceFreeMB DECIMAL(10,2)
	,PercentSpaceFree DECIMAL (10,2)
)

-- load the databases' files specs into the temp table
INSERT INTO #DatabaseFilesSpecs
EXECUTE sp_MSforeachdb' USE ?
SELECT db_name() as Database_Name
,df.name as Logical_File_Name
,df.physical_name
,ROUND(CAST((df.size) AS FLOAT)/128,2) AS TotalSpaceReservedMB
,ROUND(CAST((FILEPROPERTY(df.name,''SpaceUsed'')) AS FLOAT)/128,2) AS SpaceUsedMB
,ROUND((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128),2) AS SpaceFreeMB
,ROUND((((CAST((df.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(df.name,''SpaceUsed''))AS FLOAT)/128)) / (CAST((df.size) AS FLOAT)/128) * 100),2) AS PercentSpaceFree
FROM sys.database_files df
'
-- Insert the following between line #21 and #22 to omit the list of system databases
-- IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

-- query the #DatabaseFilesSpecs temp table
SELECT
	ID
	,Database_Name
	,Logical_File_Name
	,Physical_Name
	,TotalSpaceReservedMB
	,SpaceUsedMB
	,SpaceFreeMB
	,PercentSpaceFree
FROM #DatabaseFilesSpecs

REFERENCES:

FILEPROPERTY()
http://msdn.microsoft.com/en-us/library/ms188401(v=sql.105).aspx

sys.database_files
http://msdn.microsoft.com/en-us/library/ms174397(v=sql.105).aspx