Find text in SQL Server Agent Jobs

USE MSDBGO
SELECT Job.name AS JobName
,Job.enabled AS ActiveStatus
,JobStep.step_name AS JobStepName
,JobStep.command AS JobCommandFROM sysjobs JobINNER JOIN sysjobsteps JobStep ON Job.job_id = JobStep.job_idWHERE JobStep.command LIKE '%YourTextHere%'

List tables on their dependency order based on foreign keys for Data Migration

If you want to perform bulk insert operation on multiple tables.

The tables have foreign key relationships between themselves.

If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.



Requirement



Produce a list of tables within a database ordered according to their dependencies.

Tables with no dependencies (no foreign keys) will be 1st.

Tables with dependencies only in the 1st set of tables will be 2nd.

Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.

and so on...





WITH cte (
 lvl
 ,object_id
 ,name
 ,schema_Name
 )
AS (
 SELECT 1
  ,object_id
  ,sys.tables.name
  ,sys.schemas.name AS schema_Name
 FROM sys.tables
 INNER JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
 WHERE type_desc = 'USER_TABLE'
  AND is_ms_shipped = 0
 
 UNION ALL
 
 SELECT cte.lvl + 1
  ,t.object_id
  ,t.name
  ,S.name AS schema_Name
 FROM cte
 JOIN sys.tables AS t ON EXISTS (
   SELECT NULL
   FROM sys.foreign_keys AS fk
   WHERE fk.parent_object_id = t.object_id
    AND fk.referenced_object_id = cte.object_id
   )
 JOIN sys.schemas AS S ON t.schema_id = S.schema_id
  AND t.object_id <> cte.object_id
  AND cte.lvl < 30
 WHERE t.type_desc = 'USER_TABLE'
  AND t.is_ms_shipped = 0
 )
SELECT schema_Name
 ,name
 ,MAX(lvl) AS dependency_level
FROM cte
WHERE schema_Name LIKE '%ORCA%'
GROUP BY schema_Name
 ,name
ORDER BY dependency_level
 ,schema_Name
 ,name;



Reference

Calculate disk space occupied by each schema in SQL Server

SELECT SCHEMA_NAME(so.schema_id) AS SchemaName
,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMBFROM sys.dm_db_partition_stats psJOIN sys.indexes i ON i.object_id = ps.object_id
AND i.index_id = ps.index_idJOIN sys.objects so ON i.object_id = so.object_id
WHERE so.type = 'U'
GROUP BY so.schema_id
ORDER BY OBJECT_SCHEMA_NAME(so.schema_id)
,SizeInMB DESC

List out all MDF and LDF File locations of SQL Sever Databases

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

Move User Databases

USE master; GO -- Return the logical file name. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2012') AND type_desc = N'LOG'; GO ALTER DATABASE AdventureWorks2012 SET OFFLINE; GO -- Physically move the file to a new location. -- In the following statement, modify the path specified in FILENAME to -- the new location of the file on your server. ALTER DATABASE AdventureWorks2012 MODIFY FILE ( NAME = AdventureWorks2012_Log, FILENAME = 'C:\NewLoc\AdventureWorks2012_Log.ldf'); GO ALTER DATABASE AdventureWorks2012 SET ONLINE; GO --Verify the new location. SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'AdventureWorks2012') AND type_desc = N'LOG';

Space occupied by each Schema in SQL Server database

SELECT  SCHEMA_NAME(so.schema_id) AS SchemaName
               ,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMB
        FROM    sys.dm_db_partition_stats ps
        JOIN    sys.indexes i
          ON    i.object_id                                     =           ps.object_id
         AND    i.index_id                                      =           ps.index_id
JOIN sys.objects so
  ON i.object_id = so.object_id
       WHERE    so.type = 'U'
    GROUP BY so.schema_id
    ORDER BY OBJECT_SCHEMA_NAME(so.schema_id), SizeInMB DESC

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