Alter Growth Rate of All Database Files

To avoid unexpected and frequent growth of database files, it is important for a DBA to properly analyze the production databases file growth and setting them up with the appropriate rate.

Script is to change growth rate of all databases to 500MB.

	/*
	Script By: Amna Asif for http://sqlcache.blogspot.com/
	Purpose: To Convert Growth rate of databases
	*/
	USE MASTER
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	SET ARITHABORT ON
	GO
	DECLARE @DBName NVARCHAR(255)
	DECLARE @FileRrowthRate VARCHAR(50)
	DECLARE @Qry NVARCHAR(500),
	@DBLogicalFileName VARCHAR(200)
	SET @FileRrowthRate = 512000
	DECLARE DatabaseList CURSOR
	FOR SELECT DISTINCT
	db.name
	FROM sys.master_files mfile
	INNER JOIN sys.databases db ON mfile.database_id = db.database_id
	WHERE -- is_percent_growth=1 and /* Growth of the file is a percentage then value= 1 anf iF Absolute growth size in pages then value = 0*/
	db.state_desc = 'ONLINE'
	AND db.is_read_only = 0
	AND ( db.database_id > 4
	OR db.name = 'model'
	)
	ORDER BY name
	OPEN DatabaseList
	FETCH NEXT FROM DatabaseList INTO @DBName
	WHILE @@FETCH_STATUS = 0
	BEGIN
	--------------------------------Data File Growth Rate --------------------------
	--------------------------------------------------------------------------------
	SELECT @Qry = ' SELECT @DBLogicalFileNameOUT = NAME
	FROM ' + @DBName + '.sys.master_files AS mf
	WHERE DB_NAME(database_id)=''' + @DBName + '''
	AND TYPE_DESC=''ROWS'' '
	EXEC sp_executesql @Qry, N'@DBLogicalFileNameOUT nvarchar(200) OUTPUT', @DBLogicalFileNameOUT = @DBLogicalFileName OUTPUT ;
	PRINT @DBName
	EXEC
	( 'ALTER DATABASE [' + @DBName + ']
	MODIFY FILE ( NAME = N''' + @DBLogicalFileName + ''', FILEGROWTH = ' + @FileRrowthRate + 'KB )'
	)
	--------------------------------Log File Growth Rate --------------------------
	-----------------------------------------------------------------------------------
	SELECT @Qry = ' SELECT @DBLogicalFileNameOUT = NAME
	FROM ' + @DBName + '.sys.master_files AS mf
	WHERE DB_NAME(database_id)=''' + @DBName + '''
	AND TYPE_DESC=''Log'' '
	EXEC sp_executesql @Qry, N'@DBLogicalFileNameOUT nvarchar(200) OUTPUT', @DBLogicalFileNameOUT = @DBLogicalFileName OUTPUT ;
	EXEC
	( 'ALTER DATABASE [' + @DBName + ']
	MODIFY FILE ( NAME = N''' + @DBLogicalFileName + ''', FILEGROWTH = ' + @FileRrowthRate + 'KB )'
	)
	FETCH NEXT FROM DatabaseList INTO @DBName
	END
	CLOSE DatabaseList
	DEALLOCATE DatabaseList

view raw ChangeGrowthRate.sql hosted with ❤ by GitHub

Import and Export Data from SQL Server

To use OPENROWSET you have to enable Ad Hoc Distributed Queries

 sp_configure 'show advanced options', 1;
 RECONFIGURE;
 GO
 sp_configure 'Ad Hoc Distributed Queries', 1;
 RECONFIGURE;
 GO

Excel
1. To Import Data from Microsoft Office Excel 2007 Worksheet(.xlsx)
Step 1: Configure ACE Driver

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
  GO
  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
  GO

Step 2:

SELECT * INTO dbo.ExcelWorksheetContacts
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0;HDR=NO;Database=E:\SQLCAT\book1.xlsx;',
       [Contacts$]
               )

2. To Import Data from Microsoft Office Excel 97-2003 Worksheet(.xls)

 SELECT * INTO dbo.ExcelWorksheetContacts
 FROM
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;HDR=YES;Database=E:\SQLCAT\ExcelWorksheet.xls',
            'Select FirstName,LastName,DateofBirth,Address1,City,State,ZipCode From [Contacts$]')   

3. To Import Data from Excel with Restricted Columns and Rows

 SELECT * INTO dbo.ExcelWorksheetContacts
 FROM
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',
               'Excel 8.0;Database=E:\SQLCAT\ExcelWorksheet.xls;HDR=yes',
               'SELECT * FROM [Contacts$A1:C3]'
               )
   

4. To Export Data in Excel (Already Have format) from SQL Table  
     1. Excel Sheet must contain Header in First Row
     2. No of Columns must match  

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                           'Excel 8.0;Database=E:\SQLCAT\ExcelWorksheet.xls;HDR=yes',
                           'SELECT * FROM [Contacts$]') SELECT * FROM  dbo.ExcelWorksheetContacts  
      

5. To Import Data from Excel using BCP command
     1. Table ExcelWorksheetContacts should already exist

 DECLARE @sql VARCHAR(500)
 SELECT @sql= 'bcp "SQLCat.dbo.ExcelWorksheetContacts" in "E:\SQLCAT\ExcelWorksheet.xls" -S -PC\SQLSERVER2008R2 -T -c'
 EXEC xp_cmdshell @sql                
 

6. To Export Data in Excel from SQL Table using BCP 
     1. Excel File should already exist

    EXEC xp_cmdshell 'bcp "Select * from SQLCat.dbo.ExcelWorksheetContacts" queryout "E:\SQLCAT\ExcelWorksheet.xls" -S -PC\SQLSERVER2008R2 -T -c' 
  

SQL Server Error:                          
IF you found following ERROR during Import through TSQL just restart the SQL Server Services
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Text File 
1. To Import from a Text File to SQL Table
    1. Table Text_ConstactsFile should already exist

 DECLARE @sql VARCHAR(500)
 SELECT @sql= 'bcp "SQLCat.dbo.Text_ConstactsFile" in "E:\SQLCAT\Text_ConstactsFile.txt" -S -PC\SQLSERVER2008R2 -T -c'
 EXEC xp_cmdshell @sql

2. To Export in Text File from SQL Table
    1. Text file should already exist

 EXEC xp_cmdshell 'bcp "SELECT * FROM SQLCat.dbo.Text_ConstactsFile" queryout "E:\SQLCAT\Text_ConstactsFile.txt" -S -PC\SQLSERVER2008R2 -T -c'

3. Bulk Insert from Text file to SQL Table
    1. Table Text_ConstactsFile should already exist

 BULK INSERT SQLCat.dbo.Text_ConstactsFile
    FROM 'E:\SQLCAT\Text_ConstactsFile.txt'
    WITH
    (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
    )

CSV File 
1. To Import Data From CSV File to SQL Table

  SELECT * INTO SQLCat..CSV_ContactsSheet
  FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=E:\SQLCAT\;HDR=yes;'
                  ,'SELECT top 2 * FROM CSV_ContactsSheet.CSV'
                  )

and an excellent link on Excel Import\Export
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.html

MSDB Purging

Sometimes a DBA goes through such situations where MSDB grows too large and MSDB’s huge historical data becomes the cause for poor performance of maintenance, storage and scheduled alerts. There DBA is required to clean up the MSDB.

MSDB tables contain SQL Server Agent Jobs, Database Mail , Alerts, Operators configurations and Maintenance Plans, Online Database Backups-Restore and Scheduled tasks History.

Method 1:

SQL Server Maintenance Plan GUI contains a task History Cleanup Task

                  Step 1: Server -> Management -> Maintenance Plans ->Maintenance Plan -> Right Click -> Maintenance Plan Wizard

                  Step 2:  Click on -> Next

                  Step 3:  Choose schedule type -> Change Schedule and Click -> Next

                  Step 4:  Choose Clean Up History -> Next

                  Step 5:  Clean Up History -> Next

                  Step 6:  Choose historical data to be deleted ->  Choose Older than One Month -> Next

                  Step 7:  Click Finish to Complete the Wizard

                  Step 8:  Click Close once actions finished successfully

                                       
                  Step 8:  Server -> SQL Server Agent -> Jobs -> Find newly created Plan

                                                

Method 2:
MSDB cleansing through TSQL

Step 1: Find and analyze MSDB tables data sizes

/*-------------------------------- Find the MSDB Tables with Largest Data ------------------------- */

SELECT  sc.name + '.' + t.NAME AS TableName,
        p.[Rows],
        ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, -- Number of total pages * 8KB size of each page in SQL Server
        ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,
        ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB
FROM    msdb.sys.tables t
        INNER JOIN msdb.sys.schemas sc ON sc.schema_id = t.schema_id
        INNER JOIN msdb.sys.indexes i ON t.OBJECT_ID = i.object_id
        INNER JOIN msdb.sys.partitions p ON i.object_id = p.OBJECT_ID
                                            AND i.index_id = p.index_id
        INNER JOIN msdb.sys.allocation_units a ON p.partition_id = a.container_id
WHERE   t.type_desc = 'USER_TABLE'
        AND i.index_id <= 1  --- Heap\ CLUSTERED
GROUP BY sc.name + '.' + t.NAME,
        i.[object_id],i.index_id, i.name, p.[Rows]
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC

Step 2 : Now figure out table with largest data size belongs to which feature of MSDB and clean up them.In my case, I had found heavy size tables in MSDB that relates to 'Backup and Restore History', 'Database Mail Log' and 'Maintenance Plans Log'.

/*---------------------------------- Clean "Backup and Restore History" MSDB tables ---------------- */
--- Backup and Restore Tables
SELECT * FROM msdb..backupfile
SELECT * FROM msdb..backupfilegroup
SELECT * FROM msdb..backupmediafamily
SELECT * FROM msdb..backupmediaset
SELECT * FROM msdb..backupset
SELECT * FROM msdb..restorefile
SELECT * FROM msdb..restorefilegroup
SELECT * FROM msdb..restorehistory


--- Execute system stored procedure to reduces the size of backup and restore history tables 
--- sp_delete_backuphistory @oldest_date
EXECUTE msdb..sp_delete_backuphistory '1-OCT-2013'  -- Delete entries older than OCT 2013

/*---------------------------------- Clean "Database Mail Log" MSDB tables ------------------------- */
--- Database Mail Log Tables
SELECT * FROM msdb..sysmail_faileditems
SELECT * FROM msdb..sysmail_unsentitems
SELECT * FROM msdb..sysmail_sentitems
SELECT * FROM msdb..sysmail_allitems
SELECT * FROM msdb..sysmail_log


--- Execute system stored procedures to reduces the size of e-mail messages and log tables
--1.--e-mail messages--
--- sysmail_delete_mailitems_sp  @sent_before , @sent_status
EXECUTE msdb..sysmail_delete_mailitems_sp 'October 1, 2013',NULL -- Delete entries older than OCT 2013

--2.--LOG--
--- sysmail_delete_log_sp @logged_before,@event_type
EXECUTE msdb..sysmail_delete_log_sp 'October 1, 2013',NULL -- Delete entries older than OCT 2013

/*---------------------------------- Clean "Maintenance Plan Log" MSDB tables ------------------------- */
--- Maintenance Plan Log Tables
SELECT  * FROM msdb..sysmaintplan_logdetail;
SELECT  * FROM msdb..sysmaintplan_log;

--- Execute system stored procedures to reduces the size maintenance plan tables
--Undocumented SP ---  sp_maintplan_delete_log @plan_id, @subplan_id, @oldest_time
EXECUTE msdb..sp_maintplan_delete_log NULL,NULL,'2013-10-16T00:00:00'  -- Delete entries older than OCT 2013

--Alternatively: Truncate Maintenance Plan Log Tables
  DELETE 
  --SELECT  * FROM
  msdb..sysmaintplan_logdetail;

   ---------------------------
  DELETE 
  --SELECT  * FROM
  msdb..sysmaintplan_log;

Step3: Release occupied but unused space from MSDB tables

/*---------------------------------- Release Occupied Spaces ----------------------------------------- */

DBCC SHRINKDATABASE ('MSDB')

Reference Links
Archive Database Mail: http://technet.microsoft.com/en-us/library/ms189258.aspx

SQL Server Profiler

What is Profiler?

A graphical tool used to capture SQL Server Engine’s events.

Major functions

• Creating Trace
•  Watching Trace
•  Storing trace as Tables or Trace Files
•  Replaying Trace

SQL Profiler Uses

• Monitor the performance of an instance of SQL Server
• Debug Transact-SQL statements and stored procedures
• Identify slow-executing queries
• Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected
• Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system
• Audit and review activity that occurred on an instance of SQL Server to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects

Profiler Terminology

• Event:

          An action within an instance of SQL Server Database Engine         

          Examples:

1. Running any T-SQL or performing any operation in the SSMS that is related to database

2. Running stored procedures, and creating jobs

• Event Class:

           A type of event that can be traced and a column in a trace describes the event type.

           Examples:

                         1. SP:Starting and RPC:Completed.

• Event Category:

          Group of events are called an event category

           Examples:

                 1. Stored Procedure, Locks and errors and warnings

                          2. Store Procedures -> SP: Starting; SP: Completed; SP: StmtStarting; SP: StmtCompleted

• Data Column:

          Data column contains value of an event class.

          Examples:

                        1. StartTime, EndTime, Duration, SPID, DatabaseID, ObjectID

• Filter:

          Create seductiveness in data that are collected in trace.

          Filtration Reasons:

                            1. Profiler is too much expensive for the production server’s performance

                             2. Profiler capture too much data and making it extremely difficult to analyse later

• Template:

           Default configuration pattern for trace.Can be saved, imported and exported between instances

           Examples:

                         1. Standard template -> Captures all SP and T-SQL batches

DBA Checklist

To stay safe, a DBA of the SQL Server is required to be familiar with all the critical aspects. After knowing these key aspects he can create an environment in which nothing would be lost before the news or generating alerts.

Following is the basic SQL Server’s DBA Checklist and I found very detailed Checklist on Simple-Talk. 

• Connectivity:

1.    Check all SQL Server services are running

2.    Polls all servers ‘select @@servername’ every 10 minutes to check server availability

3.    Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts.

• Backups:

1.    Check databases full backup(Get list of not recently backed up databases in last 7 days)

2.    Log backups

3.    Archiving

4.    Offsite storage

• Events:

1.    Check errors in your SQL Server Error Log and operating system event viewer and for errors or warnings.
2.  All database logs

3.    Application logs

4.    System logs

5.    Agent history

6.    Device logs

7.    NIC logs etc. Investigate any job failures

8.    Keep a log of any configuration changes you make to the servers.

9.    CRUD (Create, Read, Update, Delete) statements about database, table, view, UDF, triggers, stored procedures.

• Processes:

1.    Check that all required processes on the server are running

2.    Count of total SQL Agent Jobs and successfully run are equal.

3.    Make sure that SQL Mail, SQL Agent Mail, Database Mail or xp_SMTP_Sendmail is working correctly

4.    Export and then Delete all details of backup and restore processes created before a specific time

5.    Replication (If any )

• Integrity:

1.    Perform all database and server integrity checks; look for objects that break rules

(DBCC commands dbcc checkdb / dbreindex / checkcatalog / inputbuffer, sp_who, sp_kill)

• Indexes:

1.    Check on indexes to see if they are being used

2.    Need re-creation or modification in indexes are required

3.    If any are missing

• Volumetric:

1.    Check resources on the server such as files sizes (running out of space on any of disks)

2.    Disk space, CPU usage and memory (running low on server memory for SQL Server)

3.    Monitor growth

4.    Enough space available for all types of backup files

• Performance:

1.    Check application performance, long running or "hung" processes, especially those tied to a SQL Agent job

2.    Regular monitor Performance Counters

3.    Regular check on Blocking issues

4.     Count of Total requests  processing ( Total Batch Count)

5.    To avoid the AUTOGROWTH of the data files during the busiest time, manually increase their size.

6.    Performance statistics, using the Permanence tool; research and resolve any issues.

7.    For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate volumes on your server to isolate random writes from sequential ones

8.    “Auto create statistics” and “auto update statistics”  options should be on for all Databases in other case must manually update the statistics.

9.    If application supports snapshot isolation level, this will result in a Temp DB contention so it is recommended to divide the tempdb database into multiple files based on the number of CPU cores and to place it on a separate drive.

• Procedures:

1.    Check all Disaster Recovery Plans

• Security:

1.    Look for security policy violations(security plan according to needs)

2.    SQL Agent Job modifications, any schedule modification can have negative impact. Use a report that shows which jobs were modified and when, unfortunately it does not reveal "who",

3.    Count to Total connections to SQL Server instance

Configuration Manager

·         Configuration manager is GUI to manage/configure all

§  Sql Server Services,

§  Server Network Protocols,

§  Client Network Configurations

§  And also to change the Connectivity options.

·         Using SQL Server Configuration Manager you can set Service's (such as SQL Server Integration Services, SQL Server Full Text Search Service, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Database Engine Services, SQL Server Agent Service, SQL Server Browser Services etc)

                              §  State à   Start OR Stop

§  Start Mode à   Manual OR Automatic

·         Using SQL Server Configuration Manger, allows  you to Create or Remove Server Alias.

·         Using SQL Server Configuration Manger you can also force Protocol Encryption and Protocol’s

§  State à Enabled OR Disabled

(Shared Memory, Named Pipes, TCP\IP and VIA)

·         Using SQL Server Configuration Manager Tool you can change SQL Server Service Account or change the Password for any of the SQL Server Services.

·         Using SQL Server Configuration Manager, changes in Account settings also configure associated additional changes such as Windows Registory settings and account can read the SQL Server settings immediately once changed where as other Tools (SMO and WMI) can not change associated settings which may effect the proper start of service.

·         Using SQL Server Configuration Manager Tool  changes come into effect immediately by avoiding downtime

  

Reference Links

SQL Server Configuration Manger: http://technet.microsoft.com/en-us/library/ms174212.aspx

Protocol Encryption: http://technet.microsoft.com/en-us/library/ms191192.aspx

                               http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

Account Permissions: http://msdn.microsoft.com/en-us/library/ms143504.aspx