Tag: T-SQL Script

SCRIPT GALLERY, SQL

How to list file names in a folder in SQL Server

Here is a very small script to list the file names in a folder using SQL Server.

The below snippet has been useful for us to automate the restore process from a defined path(for large number of backup files). 


/**********************************************************************/
	DECLARE @COMMANDSHELL TABLE ( LINE VARCHAR(512)) 
	DECLARE  @CMD VARCHAR(512) ,@BACKUPFILEPATH NVARCHAR(256)
	SET @BACKUPFILEPATH = 'D:\DATABASES\BACKUPS\' 
	SET @CMD = 'DIR /B ' + @BACKUPFILEPATH +  ' /TC' 
				
	INSERT INTO @COMMANDSHELL EXEC MASTER..XP_CMDSHELL   @CMD 

	--Data Clean up 
	DELETE FROM   @COMMANDSHELL WHERE  LINE IS NULL

	SELECT* FROM @COMMANDSHELL A
/**********************************************************************/

Prerequisites: You may need to enable the xp_cmdshell using sp_configure as below:


--To Enable the advanced Options
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE WITH OVERRIDE

--To Enable XP_CmdShell 
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE WITH OVERRIDE

--To Disable the advanced Options(by default)
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS',0
RECONFIGURE WITH OVERRIDE
DBA Utilities, SQL

Auto File Growth – Different ways to track the growth events

Auto-Growth events are occurring when the data/log file is trying to expand size due to out of space.The amount that grows, depends on the file growth option provided for the database. Recently, in one of the project, it has been observed that the Auto growth event occurred 4 times for one of data file and a delay in response time.

Whats the problem with Auto Growth?

Auto Growth can not be stopped completely, however, a careful configuration of Growth option can control the number of Auto Growth events.The problem with Auto Growth is that during the growth event, the database would be halted until the growth event has completed. For a higher size, it can take more time there by a huge delay in processing for the request and in turn several performance issues.In addition, when the space is allocating, it may not be a contiguous space resulting a physical fragmentation of the database file on the disk. If the number of Auto Growth is high, then there will be more physical fragmentation.

How do you find the Auto Growth events?

This blog post is neither intended nor explained the cause of the growth or its resolution.Lets us look at it later. Here, We would see some of common methods to identify the Auto Growth events in SQL Server.

–Using default trace

Default trace is a very powerful way of capturing issues in SQL Server. Some of common EventClasses and its description is given below.

/*

EventClass Description
********************************************************
18 -Audit Server Starts And Stops
20 -Audit Login Failed
22 -Error log
46 -Object:Created
47 -Object:Deleted
55 -Hash Warning
69 -Sort Warnings
79 -Missing Column Statistics
80 -Missing Join Predicate
81 -Server Memory Change
92 -Data File Auto-Grow
93 -Log File Auto-Grow
116 -Audit DBCC Event
164 -Object:Altered
175 -Audit Server Alter Trace Event
*/

Now, lets look at the way to capture Auto Growth Event from trace as below.

DECLARE @filename NVARCHAR(1000);

-- Get the name of the current default trace
SELECT @filename = CAST(value AS NVARCHAR(1000))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1 AND property = 2;

--To find the filename without rollover number
SET @filename = REVERSE(@filename);
SET @filename = REVERSE(SUBSTRING(@filename,CHARINDEX('_',@filename)+1,LEN(@filename))) 
					+ REVERSE(SUBSTRING(@filename,1,CHARINDEX('.',@filename)));

-- Get the details
SELECT 
  Trace.StartTime, Trace.EndTime,TraceEvent.name AS EventName,Trace.DatabaseName  
,Trace.Filename,Trace.EventClass,(Trace.IntegerData*8)/1024.0 AS GrowthMB 
,(Trace.duration/1000)AS DurationInMS
FROM ::fn_trace_gettable(@filename, DEFAULT) AS Trace 
INNER JOIN sys.trace_events AS TraceEvent ON Trace.EventClass = TraceEvent.trace_event_id  
WHERE (Trace.EventClass = 92  
    OR Trace.EventClass = 93) 
ORDER BY Trace.StartTime

–Using SQL Error log

SQL Server will provide the Auto Growth events in the Error log as below snapshot. The errorlog information contains the file name and database with the time taken to process the Auto Growth. Note, these are the events 5144 & 5145 logged only when autogrowth takes longer time or times out.

AutoGrowth_ErrorLog

–Using Live monitoring query

Using the script in the post, you would be able to see the wait types like LATCH_EX/PREEMPTIVE_OS_WRITEFILEGATHER occurring in the server along with the resource description as FGCB_ADD_REMOVE(A prominent type of Latch – File Group Control Block Add/Remove), which is an indication for Auto Growth Event.

These are my favorite methods to identify the Auto Growth events. If you have any other ways, I would like to request you to share the information here.

SQL, XML

Find the number of columns with exact match of a search word using XQUERY in SQL Server

Problem Statement:
Today, we are going to see a method to find the number of columns with exact match of a search word in a table.

This can be achieved in a very simple way using XQUERY. I just would like to share the code sample that I tried to get the information.

DDL and Sample Data:


create table test_ExactMatchsearch
(
id int identity(1,1),val1 varchar(200), Val2 varchar(200)
)

insert into test_ExactMatchsearch values('SQL','Server')
insert into test_ExactMatchsearch values('BizTalk','Server')
insert into test_ExactMatchsearch values('PLSQL','Oracle')
insert into test_ExactMatchsearch values('PL','SQL')
insert into test_ExactMatchsearch values('SQLServer','SQL')
insert into test_ExactMatchsearch values('SQL','SQL')

In the above table I would like to search “SQL” and find the presence of the exact search word.

Actual Code:


declare @SearchWord varchar(10)
set @SearchWord = 'SQL'

--How to find the number of columns available the exact word.
;with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as ns)
select *,
       (
          select *
 from test_ExactMatchsearch as T2
 where T1.id = T2.id
          for xml path('row'), elements xsinil, type 
       ).value('count(//*[text()=sql:variable("@SearchWord")])','varchar') as ExactMatchColumnCount
from test_ExactMatchsearch as T1

Result:
ExactMatchScrrenshot
Clean up the Table object:


Drop table test_ExactMatchsearch
SQL, T-SQL

Update Table with Random numbers without duplicates in SQL Server

Problem Statement:

Recently, there was a question in one of SQL Server forum asking on updating all table rows with some Random numbers without duplicates. The person who had asked the question could achieve generating the random numbers in a way that there are some duplicates. However, his intention was updating the existing rows with unique random numbers.

Here is the script shared by Questioner


create table RandomNumberUpdate(Col1 bigint)
Go
Insert into RandomNumberUpdate Values(1)
Go 1000

Select * From RandomNumberUpdate

UPDATE RandomNumberUpdate
SET Col1 = CONVERT(int, RAND(CHECKSUM(NEWID())) * 10000)

Once after the update query executes, we can observe there are many duplicates in the table.You may try it yourself and see the duplicates. Use the below query to check the duplicates. 


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

Solution:

Here is a different approach to get UNIQUE values without duplicates.


UPDATE RandomNumberUpdate
SET Col1 = Abs(Checksum(NewId()))

Query the table to confirm for any duplicates as below and see that there are no duplicates.


;With cte as
(
Select *,ROW_NUMBER()over(partition by Col1 Order by Col1 asc) Rn From RandomNumberUpdate
)Select* From cte Where rn>1

--Clean the Code

Drop table RandomNumberUpdate
DBA Utilities, SQL

Find the database size (log/row/total) in SQL Server using T-SQL

Today, one of my colleague asked me how to find the database size in SQL Server using T-SQL.

The below script would help you to get the information:


SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

EDITed on 22nd Aug 2016
The below script can be used to identify the same for all databases:


CREATE TABLE TEMP_FILESIZE(DBNAME sysname, log_size varchar(100),row_size varchar(100), total_size varchar(100))
Insert into TEMP_FILESIZE
EXEC sp_msforeachdb '
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 

		SELECT 
			  database_name = ''?''
			, log_size_mb = CAST(SUM(CASE WHEN type_desc = ''LOG'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
			, row_size_mb = CAST(SUM(CASE WHEN type_desc = ''ROWS'' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
			, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
		FROM ?.sys.master_files WITH(NOWAIT)
		WHERE database_id = DB_ID(''?'') -- for current db 
		GROUP BY database_id
end 
' 
Select * From TEMP_FILESIZE Order by log_size desc
DROP Table TEMP_FILESIZE

Added on 20th Sep 2022
The below script can be used to identify the free space for a database:


SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys. database_files
WHERE type IN (0,1);