Script: view information of currently running SQL Server Jobs

Below script returns five (5) columns result set as output. You can further enhance this query to accommodate your requirement.

1. Job_Name: This is job’s registered name on the server’s instance.
2. Last_Executed_Step_ID: This is the ID of the last executed step in the job. E.g. if a job has 4 steps, and currently it is running 3^rd step, so last executed step will be 2 and it will be populated with 2 against Job’s 2^nd step row, while all other steps rows will have 0.
3. Step_ID: This query returns all steps included in the job along with their id, while 2^nd column, returns id same as in this column.
4. Step_Name: This reflects job step name that was given during job creation.
5. Start_Execution_Date: This column returns execution date and time of this job. So you can get an idea when this started execution.

SELECT 
LTRIM(RTRIM(CONVERT(VARCHAR(100),j.name))) job_name

,ISNULL(a.last_executed_step_id,'') last_executed_step_id

,s.step_id

,RTRIM(CONVERT(VARCHAR(100),s.step_name))step_name

,a.start_execution_Date

FROM msdb.dbo.sysjobs j WITH (NOLOCK)

INNER JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON j.job_id = s.job_id

LEFT OUTER JOIN msdb.dbo.sysjobactivity a WITH (NOLOCK) ON j.job_id=a.job_id

AND (s.step_id=a.last_executed_step_id OR a.last_executed_Step_id IS NULL)

AND run_requested_date>=CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME)

AND stop_execution_Date IS NULL

WHERE j.enabled=1

AND j.category_id IN (0,1,2,3,4,5,98,99)

AND j.job_id in 

(SELECT job_id FROM msdb.dbo.sysjobactivity WITH (NOLOCK)

WHERE start_execution_date>= CAST(CONVERT(VARCHAR,GETDATE(),101) AS DATETIME) 

AND stop_execution_date IS NULL )

ORDER BY 1,3

Database & Disk space analysis

BEGIN

CREATE TABLE #LOGSPACE
(DBNAME VARCHAR(100)
,LOGSIZE DECIMAL(30,10)
,LOGSPACE DECIMAL(30,10)
,STATUS INT
)

CREATE TABLE #DRIVESPACE
(DRIVE VARCHAR(2)
,DRIVESPACE BIGINT
)

INSERT INTO #LOGSPACE
EXEC ('DBCC SQLPERF(LOGSPACE)')

INSERT INTO #DRIVESPACE
EXEC ('MASTER.SYS.XP_FIXEDDRIVES')

SELECT F.TYPE_DESC DBFILES
,LEFT(F.PHYSICAL_NAME,1) DRIVE
,F.STATE_DESC DBSTATE
,((F.[SIZE]*8)/1024.00)/1024.00 DBCURRENTSIZE
,ISNULL(((A.RESERVED*8)/1024.00)/1024.00 - ((D.LOGSIZE/1024.00) * (D.LOGSPACE/100.00)),(L.LOGSIZE/1024.00) * (L.LOGSPACE/100.00)) DBRESERVEDSIZE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN 'PAGES' WHEN F.IS_PERCENT_GROWTH=1 THEN 'PERCENTAGE' END DBGROWTHTYPE
,F.GROWTH DBGROWTHVALUE
,CASE WHEN F.IS_PERCENT_GROWTH=0 THEN ((F.GROWTH*8)/1024.00)/1024.00 WHEN F.IS_PERCENT_GROWTH=1 THEN (((F.[SIZE]*8)/1024.00)*(F.GROWTH/100.00))/1024.00 END DBGROWTHSIZE
,CASE WHEN F.MAX_SIZE=0 THEN 'FIXED SIZE' WHEN F.MAX_SIZE=-1 THEN 'UNLIMITED' ELSE CONVERT(VARCHAR,((CONVERT(BIGINT,F.MAX_SIZE)*8)/1024.00)/1024.00) END MAX_SIZE
,DS.DRIVESPACE/1024.00 FREESPACEONDRIVE
INTO #SPACEANALYSISREPORT
FROM SYS.DATABASE_FILES F
INNER JOIN #DRIVESPACE DS ON DS.DRIVE = LEFT(F.PHYSICAL_NAME,1)
LEFT OUTER JOIN #LOGSPACE L ON L.DBNAME = DB_NAME() AND F.TYPE_DESC='LOG'
LEFT OUTER JOIN #LOGSPACE D ON D.DBNAME = DB_NAME() AND F.TYPE_DESC='ROWS'
LEFT OUTER JOIN
(SELECT DB_ID() DATABASE_ID, 'ROWS' TYPE_DESC,SUM(TOTAL_PAGES) RESERVED, SUM(USED_PAGES) USED FROM SYS.ALLOCATION_UNITS) A ON A.TYPE_DESC=F.TYPE_DESC

SELECT * FROM #SPACEANALYSISREPORT

DROP TABLE #LOGSPACE
DROP TABLE #DRIVESPACE
DROP TABLE #SPACEANALYSISREPORT

END

Current Identity value, Identity usage and other identity information

SELECT
QUOTENAME(SCHEMA_NAME(T.SCHEMA_ID)) + '.' + QUOTENAME(T.NAME) AS TABLENAME,
C.NAME AS COLUMNNAME,
CASE C.SYSTEM_TYPE_ID WHEN 127 THEN 'BIGINT' WHEN 56 THEN 'INT'
WHEN 52 THEN 'SMALLINT' WHEN 48 THEN 'TINYINT' END AS 'DATATYPE', IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS CURRENTIDENTITYVALUE,
IDENT_INCR(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYINCREMENT,
IDENT_SEED(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME) AS IDENTITYSEED,
CASE C.SYSTEM_TYPE_ID
WHEN 127 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 9223372036854775807
WHEN 56 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 2147483647
WHEN 52 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 32767
WHEN 48 THEN (CONVERT(DECIMAL,IDENT_CURRENT(SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME)) * 100.00) / 255 END AS 'PERCENTAGEUSED'
FROM SYS.COLUMNS AS C
INNER JOIN SYS.TABLES AS T ON T.[OBJECT_ID] = C.[OBJECT_ID] AND C.IS_IDENTITY = 1
ORDER BY PERCENTAGEUSED DESC

how to view association among File Groups, System Logical and Physical Files and Tables

You can use this code to view file group, logical filename and physical filename associated to a table. It can be beneficial if you have large number of database files and want to see objects (tables) in a file or to view the file associated to a table.

To see whole list of tables in each data file
A table can have only one value at a time in index table (0 or 1), 1 for clustered index and 0 for heap, so you can use both values, if a table has clustered index it will not have heap associated value in index table and vice versa.

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.INDEX_ID IN (0,1)

To see whole list of tables in a specific data file
You can get file id or file name or logical file name from below query or you can use FILE_ID() function if you know logical file name.

SELECT FILEID, FILENAME, NAME FROM SYS.SYSFILES

SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SF.FILEID = FILE_ID('Logical File Name')

To see filename for a table
SELECT OBJECT_NAME(SI.OBJECT_ID) AS OBJECTNAME
,SI.DATA_SPACE_ID AS FILE_GROUP_ID
,SFG.NAME AS FILE_GROUP_NAME
,SF.NAME AS LOGICAL_FILE_NAME
,SF.FILENAME AS PHYSICAL_FILE_NAME
FROM SYS.INDEXES SI
INNER JOIN SYS.FILEGROUPS SFG ON SFG.DATA_SPACE_ID=SI.DATA_SPACE_ID
INNER JOIN SYS.SYSFILES SF ON SF.GROUPID = SFG.DATA_SPACE_ID
WHERE SI.OBJECT_ID = OBJECT_ID('Table Name')

Moving database files to another new location

You can move database data and log files to any other location by following the below steps, these come under Planned Relocation.

1. Check if there is any user connected to DB by executing the below query.
   
   SELECT SPID,LOGINAME,HOSTNAME,PROGRAM_NAME FROM SYS.SYSPROCESSES WHERE DBID=DB_ID('database_name')
   
   If there is any replication agent [Log Reader Agent] is running, stop that agent from Replication Monitor, Kill all other user connection if they are not important as:
   
   KILL @SPID – get spid from above query
   
   
2. Run ALTER DATABASE database_name SET OFFLINE.
3. Move the file to the new location.
4. Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name' )
5. Run ALTER DATABASE database_name SET ONLINE.
6. Run replication agent [Log Reader Agent], if stopped.
   

Example:

ALTER DATABASE DB1 SET OFFLINE

Copy log file from the current location to a new location by using Copy and Paste. E.g. you move File1.ldf from c drive to d drive.

ALTER DATABASE database_name MODIFY FILE ( NAME = 'DB1_Log', FILENAME = 'D:\File1.ldf' )

ALTER DATABASE DB1 SET ONLINE

Table's rows count without using COUNT() function

You can get table's total number of rows as:

SELECT SUM(ROWS) AS Total_Rows
FROM SYS.SYSINDEXES
WHERE ID=OBJECT_ID('Table1')
AND INDID IN (0,1)

OBJECT_ID('Table1'):
This function will return the object id for table "Table1" or any other specified table.

INDID:
It can be 0 for heap and 1 for clustered index, and it will have only one value at a time 0 or 1, greater than 1 values are for non-clustered indexes.