Category: Databases

A quick method to find all the data and log file paths for all databases on a database server instance.

SELECT name, physical_name AS current_file_location FROM sys.master_files

Example Output:

name	current_file_location
master	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmaster.mdf
mastlog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmastlog.ldf
tempdev	E:MSSQLData	empDB.MDF
templog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA	emplog.LDF
tempdev2	E:MSSQLDATA	empdev2.ndf
modeldev	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmodel.mdf
modellog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmodellog.ldf
MSDBData	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMSDBData.mdf
MSDBLog	C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAMSDBLog.ldf
ReportServer	E:MSSQLDataReportServer.mdf
ReportServer_log	E:MSSQLDataReportServer_log.LDF
ReportServerTempDB	E:MSSQLDataReportServerTempDB.mdf
ReportServerTempDB_log	E:MSSQLDataReportServerTempDB_log.LDF

This is my preferred method.

onstat -g ses <sesid>

Where <sesid> is the session id of the connected user querying the database.
To get the session id

onstat -g ses

This will produce output similar to below.

IBM Informix Dynamic Server Version 9.40.FC7     -- On-Line -- Up 4 days 09:44:52 -- 1445712 Kbytes
session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
31457    informix -        0        -        0        12288      11368      off
31450    informix -        0        -        0        12288      11368      off

That will print out all the sessions;  from there, use the session id column (column 1) to get the session id and plug it into the first command to show last executed statement.

onstat -g ses 31457

This produced the following output for me

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain
30943 -              v004               CR  Not Wait   0    0    9.03 Off
Last parsed SQL statement :
  select distinct(tmptype) from form_cat

This will show jobs that will run. I lost the link I found this at. Eventually, I need to add a few columns to report scheduling information, etc. This is a nice start though.

USE msdb
;WITH CTE AS (SELECT schedule_id, job_id, RIGHT('0'+CAST(next_run_time AS VARCHAR(6)),6) AS next_run_time, next_run_date
FROM sysjobschedules)
SELECT A.name Job_Name,
'Will be running today at '+
SUBSTRING(CONVERT(VARCHAR(10), CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time),1,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2) 'Scheduled At'
FROM sysjobs A ,CTE B
WHERE A.job_id = B.job_id
AND SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 5,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date) , 7,2) +'/'+
SUBSTRING(CONVERT(VARCHAR(10),next_run_date),1,4) = CONVERT(VARCHAR(10),GETDATE(),101)
AND (SUBSTRING( CONVERT(VARCHAR(10),
CASE WHEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) > 12
THEN SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) -12
ELSE SUBSTRING (CONVERT(VARCHAR(10),next_run_time) , 1 ,2) END),1,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),3,2)
+':'+SUBSTRING (CONVERT(VARCHAR(10), next_run_time ),5,2)) >
SUBSTRING (CONVERT( VARCHAR(30) , GETDATE(),9),13,7)

tsql so I can remember in the future.

declare @db_id int
declare @object_id int
set @db_id = DB_ID('EMR')
set @object_id = 0
select  db_name(@db_id) as [database], object_id, object_name(object_id, @db_id) as object_name,
index_id,
avg_fragmentation_in_percent,
page_count
from sys.dm_db_index_physical_stats(@db_id, null, null, null, 'LIMITED')
where page_count > 1000
and
avg_fragmentation_in_percent > 35
order by avg_fragmentation_in_percent desc

database                                                                                                                         object_id   object_name                                                                                                                      index_id    avg_fragmentation_in_percent page_count
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------- --------------------
EMR                                                                                                                              1091587027  ARCH_HXDIAGNOSIS                                                                                                                 1           35.2092352092352             1386

Here’s a quick T-SQL to get you the current version of your SQL server.

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY ('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition
GO

You can also use:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')