Monday, December 4, 2017

How to Change the SQL Server Agent Log File Path

SQL Server Agent Error Log: Log File Viewer displays log information from many different components. When Log File Viewer is open, use the Select logs pane to select the logs you want to display. Each log displays columns appropriate to that kind of log. The logs that are available depend on how Log File Viewer is opened.

To modify location and name of SQLServer Agent log file. We use following


The first stored procedure is used to retrieve the SQL Server Agent properties and the second stored procedure is used to set/change the properties for the SQL Agent service.

I strongly suggest testing any undocumented stored procedures in a lab environment first, before changing your production servers.

Steps to move the SQL Agent log file
Step 1

First check the existing location of the SQL Server Agent log file. Run the below undocumented stored procedure to get the current location. This stored procedure will let us know the SQL Agent properties of a particular server. This stored procedure can be found in the msdb database.

EXEC msdb..sp_get_sqlagent_properties
We can see below the different settings that are returned when we run this command.  We are interested in the errorlog_file column for the change we need to make.  Here we can see the current location is the C drive.

Find the current location of sql server agent log file
Step 2

Now we will change the location of the file from the C drive to the G drive.

First we need to create the new destination folders where we want to put the SQLAGENT.OUT file. So, I created the folder "Microsoft SQL Server\MSSQL.1\MSSQL\LOG" on the G drive. If you do not create these folders you will have issues when the SQL Server Agent service restarts.

After the new folder has been created, run the below stored procedure to change the location.

EXEC msdb.dbo.sp_set_sqlagent_properties
@errorlog_file=N'G:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'

Change the agent logfile location to different path
Step 3

Now we will verify whether the SQL Server Agent log file path has changed or not. We will be run the same command as in step 1 to get the SQL Server agent properties. We can see in the below screenshot that the path for the SQL Server Agent log file has been changed to the G drive.

Verify the change of agent log file location
Note, this change will not go into effect until you restart your the SQL Agent service. You can verify this by checking the new location to see if the file SQLAGENT.OUT exists or not.

Step 4

Now restart your SQL Server Agent service to bring the changes into effect. If you have any issues or the SQL Server Agent service does not start (you might get an error like below) then you should check the path you have set in step 2. The path has to be valid to successfully start this service.


Thursday, October 5, 2017

What Is Filling Up The Transaction Log

SELECT db.[name] AS [Database Name] ,
 db.recovery_model_desc AS [Recovery Model] ,
 db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
 ls.cntr_value AS [Log Size (KB)] ,
 lu.cntr_value AS [Log Used (KB)] ,
 CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
 AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
 db.[compatibility_level] AS [DB Compatibility Level] ,
 db.page_verify_option_desc AS [Page Verify Option]
FROM sys.databases AS db
 INNER JOIN sys.dm_os_performance_counters AS lu
 ON = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls
 ON = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;


Wednesday, June 7, 2017

T SQL query to find when was the database backed up lastly on SQL Server

Select database_name As [Database Name]
, [name] As [Backup Name]
, Case When Type = 'D' Then 'Full Backup'
When Type = 'I' Then 'Differential Backup'
When Type = 'L' Then 'Log Backup'
Else 'File or filegroup or partial'
End As [Backup Type]
, Recovery_Model
, [backup_start_date] As [Time of the SQL Backup Job]
FROM [msdb].[dbo].[backupset]

Thursday, May 11, 2017

List of all user databases in SQL Server

FROM   master.sys.databases
WHERE  Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0

Monday, February 27, 2017

Find what script is being run by specific SPID number

SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
FROM sys.dm_exec_sql_text(@sqltext)

SQL Script to find all Date/time columns in database

select table_name
   , column_name
   , data_type
from sysobjects so
inner join syscolumns sc on ( =
inner join systypes st on (st.type = sc.type)
where so.type = 'U'

Friday, February 17, 2017

Tuesday, February 7, 2017

Generate Script of All indexes in a SQL Server database

            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' + + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
       ) + ',' +
       -- default value
       'SORT_IN_TEMPDB = OFF ' + ',' +
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' + + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id =
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                          SELECT ' , ' + + CASE
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key))
                                                                            = 1 THEN
                                                                            ' DESC '
                                                                            ' ASC '
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                          ORDER BY
                                                 FOR XML PATH('')
                                  ) KeyColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                       ) tmp3
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                          SELECT ' , ' +
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column =
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 FOR XML PATH('')
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND = 'IX_Address_PostalCode' --comment for all indexes 

Wednesday, January 25, 2017

Difference between Detaching database and bringing database Offline

SQL Server Offline and Detach Database

Detach Database/Attach Database: The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.
Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.

you can attach a copied or detached SQL Server database. When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:
  • For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.
    If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.
  • If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.
Database Offline: Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.

1) If you are trying to make the database temporary unavailable for a period of time, you could take the database OFFLINE, and make it available by bringing it ONLINE whenever it is or you are ready.

2a) If you want to move the database or log files to different physical location or changing the database and/or log physical file name, AND keep them within the same SQL Server instance.
- Before you take the database offline, you need to know the logical name of the file. The name field is the logical name for the physical file name that you are about to change the path. Record the logical name for later use.

SELECT name, type, type_desc, physical_name, state, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabase')

- Take the database OFFLINE.

-Move the database and/or log files to different location.
- Update the database and/or log files path registered on its SQL Server instance. Execute the T-SQL command for each database and log files,

MODIFY FILE (NAME = YourFileLogicalName,
FILENAME = 'Your Database or Log new physical path')

Bring the database ONLINE.

If you just want to change the logical name, you dont have to take the database OFFLINE. You could change it on SSMS or T-SQL.

Right click on the database > Properties > Select Page > Change it under Logical Name column > OK.

MODIFY FILE (NAME = YourFileLogicalName,
NEWNAME = YourFileNewLogicalName)

2b) If you are relocating a file during part of the scheduled disk maintenance process, you may need to change the file path registered on its SQL Server instance before taking the database OFFLINE. This assure the server has the right path for the database and log files when the instance restart, after being shut down for maintenance.
- When the database and/or log file path is successfully updated on the instance, a message will show; 'The file "YourDatabaseFile" has been modified in the system catalog. The new path will be used the next time the database is started.'

1) If you want to move the database and log files to a different SQL Server instance or another server.
- Rollback active transaction and gain exclusive access. Detach the database. (optional: update statistics) 


EXEC sp_detach_db 'YourDatabase', 'true'

- Move database and log files to different location.
- Attach the database on different instance on same/another server. 

USE master;

ON (FILENAME = 'the physical path of your database file')
LOG ON (FILENAME = 'the physical path of your log file')

- If the database has previously set to single user, you may want to set it to multiple user access option.


2) Even though you can choose to detach and attach the database back on the same SQL Server instance for scenario like changing physical path or file name, it is recommended to use the OFFLINE and ONLINE methods due to the restriction and limitation on DETACH. When you detach a database, you remove the database from the instance. It is required to remove the database from any participation of replication, mirroring and snapshot.


Tuesday, January 24, 2017

Query to List all the partitioned tables in SQL Server database

select object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [object], as [index], as [partition_scheme]
    from sys.indexes i
    join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]