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.
Solution


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

SP_GET_SQLAGENT_PROPERTIES
SP_SET_SQLAGENT_PROPERTIES

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.

USE MASTER
GO
EXEC msdb..sp_get_sqlagent_properties
GO
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.

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

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.




Reference

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 db.name = lu.instance_name
 INNER JOIN sys.dm_os_performance_counters AS ls
 ON db.name = ls.instance_name
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
 AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;


Reference: http://www.sqlservercentral.com/scripts/DMV/71997/

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

SELECT *
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


DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 53
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

SQL Script to find all Date/time columns in database


select
    so.name table_name
   ,sc.name column_name
   ,st.name data_type
from sysobjects so
inner join syscolumns sc on (so.id = sc.id)
inner join systypes st on (st.type = sc.type)
where so.type = 'U'
and st.name IN ('DATETIME', 'DATE', 'TIME')

Friday, February 17, 2017