List down folders underlying file info using SQL query

Sometimes we need to list down file/folder’s information from a folder like filename, extension, created date and modified date etc.

The below query can give you the list of all content’s info from given folder path.

SQL Query:

DECLARE @FolderPath VARCHAR(MAX) = ''

IF OBJECT_ID('Tempdb..#FileDetails') IS NOT NULL
DROP TABLE #FileDetails

CREATE TABLE #FileDetails (FileDetails VARCHAR(MAX))

INSERT INTO #FileDetails (FileDetails)
EXEC ('EXEC XP_CMDSHELL ''powershell.exe -c "Get-ChildItem '''''+@FolderPath+''''' | SELECT Name,CreationTime,LastWriteTime,Extension|foreach{$_.Name+''''@''''+$_.CreationTime+''''|''''+$_.LastWriteTime+''''#''''+$_.Extension}"''')

SELECT SUBSTRING(FileDetails,0,CHARINDEX('@',FileDetails)) AS [File Name]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('@',FileDetails)+1,19)) AS [Creation Date Time]
, CONVERT(DATETIME,SUBSTRING(FileDetails,CHARINDEX('|',FileDetails)+1,19)) AS [Modified Date Time]
, ISNULL(NULLIF(SUBSTRING(FileDetails,CHARINDEX('#',FileDetails)+1,LEN(FileDetails)),''),'Directory') AS [Extension]
FROM #FileDetails
WHERE FileDetails IS NOT NULL

Output Content:

  Column Name Description Remarks
File Name Name of content  File name, Folder name etc.
Creation Date Time File/Folder creation date-time
Modified Date Time Last Modified date-time
Extension File extension  like.. .sql, .xlxs etc.

#filedetails

How to recover Tempdb system database step by step

Hello Friends,

We all know Tempdb is very important component of SQL SERVER Instance and there are some incidents where things goes crazy due to Tempdb.

I have some crazy experiences with Tempdb like giving wrong path OR forget to specify extension while shifting Tempdb database or Somehow Tempdb got crashed so the SQL Instance couldn’t able to work and will give  error like “Path is not found for Tempdb database” or “Specified path is not found”.

It’s bit difficult to recover Tempdb back on track in this situation. But using command prompt we can do that.

You can follow the below steps to recover Tempdb database if SQL Instance is not able to bring up because not able to create Tempdb.

Step 01: STOP ALL Services of sql instance.
Step 02: Open cmd and execute below command:
For Default SQL Instance:
       NET START MSSQLSERVER /f /T3608 -- to bypass system db recovery.
For Named Instance:
       NET START MSSQL$/f/T3608 

Step 03: Open SQLCMD using below command in cmd prompt:
SQLCMD -S
GO

Step 04: Execute below code to change tempdb path in SQLCMD.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev' , FILENAME = '.mdf' )
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog' , FILENAME = '.ldf' )
GO

Step 05: Exit from SQLCMD USING EXIT.

Step 06: Stop SQL Service:

NET STOP MSSQLSERVER /f /T3608
OR
NET STOP MSSQL$

Step 07: Start services from config manager.

Then Enjoy!!!

Hope this post help someone….

Multi value parameter passed to stored procedure is not working in SSRS

Hello Friends,

Today, I was facing very  unusual problem in SSRS-2012 and that was very frustrating.

I had created a SSRS report with multi valued parameters and just for testing; I put the SQL query in text format inside its data-set and used the parameter with IN clause (like ColumnName IN (@ParameterName)) and as we know SSRS has the capability to handle multi value parameter; need not to handle manually like we need to do in Stored Procedures.

After some days, when My development got over I decided to change my text query to stored procedure for better performance and created the stored procedure with required parameters and handled multi value passing and filter data accordingly and was trying to run the report but It was not working!!!

I checked the parameter by displaying the parameter’s value but that was showing me #Error….. It was very frustrating that usual stuff was not working. I searched over the net but  didn’t find anything.

Then I decided to apply very famous  method of developers called “Hit & Try”!!!

Then finally came to know that SSRS remembers the nature of the parameter for what it had created. In my case; It had created for IN clause not for passing parameter to a stored procedure ( here I want to highlight that it is not related to .data file; I deleted it and tried that one as well).

Then, I deleted all the parameters from the .rdl file and refresh the data-set. It created all the parameter used in stored procedure. I linked them back with their respective data-sets and run the report and it worked!!!!!

So this time I senses that it is going to pass for a stored procedure and it worked. It is strange but it worked for me.

If anyone facing the same problem then delete all parameters then refresh the databases. I will work and don’t forget to take backup of .rdl file before it to prevent any consequences.

How to know default location of data & log files in SQL Server

Hello Friends,

If I say, How I can create a database dynamically? you most probably tell me “easy man”!!! , just put CREATE DATABASE script in dynamic query string with database name as a parameter and execute it, whenever you want. Absolutely correct!!!;

But If I say I have number of servers and don’t want to go and manually alter the script with it’s appropriate file-path location. Then, above idea will fail; because, we put file-path (log & data files) as a plain text in the query string.

To serve this purpose, SQL Server 2012 onward, very interesting parameters named “InstanceDefaultDataPath” & “InstanceDefaultLogPath” added in SERVERPROPERTY function.

This gives you default location, configured for data and log files respectively.  Try below code ….

SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Data File Path]
, SERVERPROPERTY('InstanceDefaultLogPath') AS [Log File Path]

DBA with PowerShell : Get perfmon counters using PowerShell

dba-with-powershell

DBA with PowerShell” series is to focus on gathering information about SQL Server’s infrastructure. It’s little complex to fetch such detailed information using SQL Server; whereas Windows PowerShell gives us various options and saving the PowerShell output into a database becomes more useful information for analysis perspective. I am here using PowerShell 5.0 but given script will run in lower versions as well. Windows PowerShell is a very powerful, lightweight and free scripting tool based on .Net framework. You can download it from here.

Performance monitoring is a very crucial part of every DBA. In Performance monitoring “CPU utilization”, “memory utilization” and logical/physical disk usage are very important aspects to monitor. Built-in tools like “activity monitor”, “perfmon” and sometimes third party tool are very useful but with cost; either in term of money or machine resources.

We all know that perfmon and other default tools are resources intensive; that’s why these tools are not often used in prod environment, except very specific troubleshooting requirements.

I was keep thing, if we can fetch these info form system directly and save it to database for further analysis; and then I got a chance to attend Mr. Mukesh Shende‘s PowerShell training sessions in my org.. He shared some fundamentals & advance features of PowerShell. That inspires me to write PS scripts to fetch such infrastructure’s information and save the PowerShell output into a database table. I will share all of them in my coming posts.

Perfmon data with PS Script:

The script is to fetch performance counter’s information including “CPU utilization”, “memory usage”, Physical & logical disk read/write info etc. and saves into a SQL Server database table. You need to schedule this script to collect data in required frequency.

Step 01: Creating a table to hold the information

CREATE TABLE [dbo].[SystemInfo] (
[ID] [int] IDENTITY(1,1) PRIMARY KEY,
[DateTime] DATETIME NULL,
[Path] [varchar](100) NULL,
[Value] DECIMAL(18,2) NULL,
[CounterGroup] [varchar](100) NULL,
)

Step 02: Open PowerShell console as administration and past below to create “event log source”.

[System.Diagnostics.EventLog]::CreateEventSource("PSPerformanceCounter", "Application")

Step 03: The PS Script

You need to schedule it in SQL agent job under PowerShell type OR can execute manually for run-time information from PS-ISE.

Configure Agent Job:

1. In SSMS open “SQL Server Agent” and create a new job.

2. Assign a “Domain User” which should have “admin rights” in server and “read-write” permission on the above created table.

3. Create job step as Type as “PowerShell” and paste below code in command area.

4. Replace <SQLInstanceName> with your SQL Instance name.

5. Replace <DatabaseName> with the database, where you have created the above table.

5. Schedule it for every 5 min.


$SQLInstanceName = "<SQLInstanceName>"
$DatabaseName = "<DatabaseName>"
$Couters = 'CPU utilization,\Processor(_total)\% Processor Time','CPU utilization,\Process(*sql*)\% Processor Time','CPU utilization,\System\Processor Queue Length','Memory,\Memory\Page Reads/sec','Memory,\Memory\Page Writes/sec','Memory,\Memory\Available MBytes','Memory,\Memory\Pages/sec','Memory,\Memory\Page Faults/sec','Memory,\Paging File(_total)\% Usage','Disk IO,\LogicalDisk(*)\Disk Reads/sec','Disk IO,\LogicalDisk(*)\Disk Writes/sec','Disk IO,\LogicalDisk(*)\Avg. Disk Queue Length','Disk IO,\LogicalDisk(*)\Avg. Disk Read Queue Length','Disk IO,\LogicalDisk(*)\Avg. Disk Write Queue Length','Disk IO,\LogicalDisk(*)\% Free Space','Disk IO,\LogicalDisk(*)\Free Megabytes','Disk IO,\LogicalDisk(*)\Disk Transfers/sec','Physical IO,\PhysicalDisk(*)\Avg. Disk Queue Length','Physical IO,\PhysicalDisk(*)\Disk Reads/sec','Physical IO,\PhysicalDisk(*)\Disk Writes/sec','Physical IO,\PhysicalDisk(*)\Disk Transfers/sec','Network,\SQLServer:Wait Statistics(*)\Network IO waits','Network,\Network Interface(*)\Bytes Received/sec','Network,\Network Interface(*)\Bytes Sent/sec','SQL Server,\SQLServer:General Statistics\User Connections','SQL Server,\SQLServer:SQL Statistics\Batch Requests/sec','SQL Server,\SQLServer:Access Methods\Page Splits/sec','SQL Server,\SQLServer:Buffer Manager\Lazy Writes/sec','SQL Server,\SQLServer:Databases(*)\Transactions/sec','SQL Server,\SQLServer:Databases(*)\Active Transactions','SQL Server,\SQLServer:SQL Statistics\SQL Compilations/sec','SQL Server,\SQLServer:SQL Statistics\SQL Re-Compilations/sec','SQL Server,\SQLServer:Latches\Average Latch Wait Time (ms)','SQL Server,\SQLServer:Databases(*)\Data File(s) Size (KB)','SQL Server,\SQLServer:Databases(*)\Log File(s) Used Size (KB)','Locks,\SQLServer:General Statistics\Processes Blocked','Locks,\SQLServer:Locks(*)\Lock Requests/sec','Locks,\SQLServer:Locks(*)\Number of Deadlocks/sec','Locks,\SQLServer:Locks(*)\Lock Waits/sec','Locks,\SQLServer:Locks(*)\Average Wait Time (ms)','Locks,\SQLServer:Locks(*)\Lock Timeouts (timeout > 0)/sec'

try
{

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Data Source=$SQLInstanceName;
Initial Catalog=$DatabaseName;
Integrated Security=SSPI;"

$conn.open()

$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.connection = $conn

foreach ($ctval in $Couters)
{
$Couter = $ctval.Substring($ctval.IndexOf(',') + 1,$ctval.Length - $ctval.IndexOf(',') - 1)

$Prc = Get-Counter -Counter $Couter

$Val = $Prc.CounterSamples | SELECT Path,@{Name = "Timestamp"; Expression= {$_.timestamp}} , @{Name = "CookedValue"; Expression= {IF(($Couter -like '\Process(*') -OR ($Couter -like '\System*')) {$_.CookedValue/$env:number_of_processors} else {$_.CookedValue}}}

foreach ($Info in $Val)
{
$cmd.commandtext = "INSERT INTO SystemInfo (DateTime,Path,Value,CounterGroup)
VALUES('{0}','{1}','{2}','{3}')" -f $Info.Timestamp.ToString("yyyy-MM-dd HH:mm:ss"),$Info.Path,$Info.CookedValue,$ctval.Substring(0,$ctval.IndexOf(','))

$cmd.ExecuteNonQuery()
}
}

$conn.close()
}

catch
{

Write-EventLog -LogName Application -EntryType Error -source 'PSPerformanceCounter' -Message $_.Exception.Message -EventId 1234
$conn.close()
}

Step 04: Enjoy 🙂

 

Update 17-Aug-2018: Error message has changed from “Something went wrong” to “$_.Exception.Message”. Correct message will be logged into event log under source name “PSPerformanceCounter”

DBCC CHECKDB failed with error “Check Catalog Msg 3851, State 1”

Hello Friends, Today, I came to very interesting fact of SQL Server, i.e. If we restore master database with different name and try to check integrity using DBCC CHECKDB then it gets failed with er…

Source: DBCC CHECKDB failed with error “Check Catalog Msg 3851, State 1”

DBCC CHECKDB failed with error “Check Catalog Msg 3851, State 1”

Hello Friends,

Today, I came to very interesting fact of SQL Server, i.e. If we restore master database with different name and try to check integrity using DBCC CHECKDB then it gets failed with error “Check Catalog Msg 3851, State 1: An invalid row (class=,depid=,depsubid=) was found in the system table sys.syssingleobjrefs (class=13).
Msg 8992, Level 16, State 1, Line 2” .

Database Integrity checks” is a common and routine task for every DBA, which checks integrity of all objects using DBCC CHECKDB command. Today, I came to one situation where one database were failing integrity checks with “Check Catalog Msg 3851, State 1: An invalid row (class=,depid=,depsubid=) was found in the system table sys.syssingleobjrefs (class=13).Msg 8992, Level 16, State 1, Line 2” Error and it was quit surprising because the erroneous database was having similar name like master database. So I was suspecting; how master database has been corrupted!?; that could be a tense situation. Because we know the importance of system databases.

While internet searching I came to a very nice blog post by Paul S. Randal and came to know that integrity check fails for database which has restored from master database backup file.