Tag Archives: file

How to monitor file stats using a batch script

The use case for this batch script is to monitor SQL Server files using Windows task manager scheduled to run the file every 5 minutes. The batch scripts gathers the stats of  every file in a referenced directory. The script can obviously be adapted however to something that suits your specific needs.

Note: Running this script against the C drive on a laptop took 15 minutes and produced a text file that was around 50 Mb. The script is best utilized in scenarios such as monitoring file sizes on drives used by applications that do not generate thousands of files, e.g. backup directories etc.

There is two scripts presented below. The first is the simpler of the two scripts, it just records the file stats of a single directory.

Directions for use:

Take either script below and save it as a batch file called FileStatsLogger.

Two variables need to be updated to use the script:

“LogPath=C:\Log\” Change C:\Log\ to another directory if you want the output file written somewhere else.

“ScanDir=C:\FolderToScan\” Change C:\FolderToScan\ to the directory you want to gather file stats from.

The file that will be created, FileStats, will record the following information:

  • A timestamp of when the batch script was run
  • A reference to each parent directory
  • Every file creation date and time
  • File size in Kbs
  • Every file owner
  • The name of the file
  • How many files are in the directory and their collective size

The output file will look like this when opened.

FileStats

Each time the script is run it will update the text file FileStats.

@ECHO OFF

REM SET TIMESTAMP VARIABLE
SET Timestamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%

REM SET FILE LOGGING VARIABLES
SET "LogPath=C:\Log\"
SET "LogFileName=FileStats.txt"
SET "Log=%LogPath%%LogFileName%"
SET "ScanDir=C:\FolderToScan\"

REM CREATE FILE IF DOES NOT EXIST
IF NOT EXIST "%Log%" ECHO File Created Timestamp: %Timestamp% > "%Log%"

REM TIMESTAMP ENTRY
ECHO/ >> "%Log%"
ECHO Entry Timestamp: %Timestamp% >> "%Log%"
ECHO/ >> "%Log%"

REM SCAN DIRECTORY
DIR /a %ScanDir% >> %Log%

To scan multiple directories create a text file called DirectoryList and populate the the file with the directories you want to scan. Each directory on a new line, for example:

L:\
D:\
T:\

It is assumed the directory list text file will be kept in the same directory as the batch file FileStatsLogger but you should add the full file path to were DirectoryList.txt is referenced in the script to avoid any problems.

Note: L, D & T are the naming convention for the Log, Data and tempdb drive names for SQL Server. These are just drive letters examples but you can be more specific like C:\Users\UserName\Desktop\ just be sure to include the backslash at the end of the reference.

This script will create a new log file for each day as the files can be large. The filename will following the format FileStats_YYYYMMDD.

As before you will have to:

“LogPath=C:\FileStats\” Change C:\Log\ to another directory if you want the output file written somewhere else.

“ScanDir=C:\FileStats\DirectoryList.txt” Change to the directory you will store DirectoryList.txt.

Though it is recommended you keep everything in a folder like this (potentially writing the FileStats log to a directory off of the host server):

FileStatsDirectory

@ECHO OFF

REM SET TIMESTAMP VARIABLES
for /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G
SET "dd=%datetime:~6,2%"
SET "mth=%datetime:~4,2%"
SET "yyyy=%datetime:~0,4%"
SET "hh=%time:~0,2%"
SET "mm=%time:~3,2%"
SET "ss=%time:~6,2%"

SET "FileNameDate=%yyyy%%mth%%dd%"
SET "Timestamp=%yyyy%%mth%%dd%%hh%%mm%%ss%"

REM SET FILE LOGGING VARIABLES
SET "LogPath=c:\FileStats\"
SET "LogFileName=FileStats_%FileNameDate%.txt"
SET "Log=%LogPath%%LogFileName%"


REM CREATE FILE IF DOES NOT EXIST
IF NOT EXIST "%Log%" ECHO File Created Timestamp: %Timestamp% > "%Log%"

REM TIMESTAMP ENTRY
ECHO/ >> "%Log%"
ECHO Entry Timestamp: %Timestamp% >> "%Log%"
ECHO/ >> "%Log%"

REM READ DIRECTORY LIST AND SCAN EACH DIRECTORY REFERENCED 
FOR /f "tokens=*" %%x IN (c:\FileStats\DirectoryList.txt) DO DIR /a /s /q "%%x" >> %Log%

Finally you can create a Windows task scheduler task to run the script at an interval that suits your needs.

The description of the task could be something along the lines of:

This task runs the batch script “FileStatsLogger” every 5 minutes which
logs the stats for each file of each directory referenced in the file
Directory List. The stats are recorded in the FileStats txt file.
All resources related to this tasks are contained in the FileStats directory.

How to perform incremental database file shrinking on SQL Server

First off you don’t ever shrink a database file unless you known it won’t need the space back again. (There’s some caveats to that statement but it mostly holds true) If that’s the space the file has consumed generally that’s the space the file needs. Shrinking just means the file will grow again and SQL Server will take a performance hit while it does that. You’re better off just adding the additional disk space.

If you need to perform a shrink and you’re worried about how long it will take and the performance impact it will have you can perform shrinks in small chunks. This is good practice particularly in a production transactional (OLTP) system as small increments  can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and allows the shrink process to interrupted without losing all progress.

The script below can be used to shrink a database file in small increments until it reaches a target free space size based on the current free space percentage.

Simply provide as variables the database name, whether the file to be shrunk is the rows or log file, how much space to be left in terms of a percentage and how many shrinks you wish to perform. If you only want to truncate the file set the flag to 1 and you can ignore providing values for the number of shrinks or the percentage of space to keep.

(TruncateOnly means releasing all free space at the end of the file to the operating system without performing any page movement inside the file)

The script will determine given the number of shrinks and the target size what the shrink increment should be. It will then loop and execute the DBCC SHRINKFILE command to shrink the database file by the calculated increment until it reaches the target free space.

Progress updates are written to the global temp table ##DbStats so you can query this table during the shrink to see how far along the process is. The query to perform this check is commented out at the bottom of the script.

SET NOCOUNT ON;

DECLARE @DbName SYSNAME;
DECLARE @DbFileName SYSNAME;
DECLARE @RowsOrLog VARCHAR(4);
DECLARE @SpaceToLeavePercentage VARCHAR(2);
DECLARE @Sql VARCHAR(MAX);
DECLARE @ShrinkSql VARCHAR(MAX);
DECLARE @NumberOfShrinks INT;
DECLARE @FreeMbRounded INT;
DECLARE @TargetFreeMbRounded INT;
DECLARE @ShrinkIncrementInMb INT;
DECLARE @ShrinkToInMb INT;
DECLARE @TruncateOnly BIT;

/*SET USER INPUT VARIABLES*/
SET @DbName = '_DatabaseName_';
SET @RowsOrLog = 'Rows';
SET @SpaceToLeavePercentage = '10';
SET @NumberOfShrinks = 1;

--SET @TruncateOnly = 1;
IF OBJECT_ID('tempdb..##DbStats') IS NOT NULL
BEGIN
	DROP TABLE ##DbStats
END;

IF OBJECT_ID('tempdb..#Check') IS NOT NULL
BEGIN
	DROP TABLE #Check
END;

CREATE TABLE ##DbStats (
	Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,LogicalName SYSNAME
	,FreeMbRounded INT
	,TargetFreeMbRounded INT
	,DiffMb INT
	,ShrinkIncrementInMb INT
	,NumberOfShrinksLeft INT
	);

CREATE TABLE #Check (
	Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,LogicalName SYSNAME
	,FreeMbRounded INT
	,TargetFreeMbRounded INT
	,DiffMb INT
	,ShrinkIncrementInMb INT
	,NumberOfShrinksLeft INT
	);

IF @SpaceToLeavePercentage IS NULL
BEGIN
	SET @SpaceToLeavePercentage = '10'
END

SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
SELECT LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,FLOOR(FreeMbRounded) - FLOOR(TargetFreeMbRounded) AS DiffMb
FROM (
SELECT NAME AS LogicalName
	,CAST(ROUND(CONVERT(DECIMAL(12, 1), (size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0), 0) AS INT) AS FreeMbRounded
	,CAST(ROUND(CONVERT(DECIMAL(12, 1),((((size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0) / 100.0) * ' + @SpaceToLeavePercentage + ')), 0) AS INT) AS TargetFreeMbRounded
FROM ' + QUOTENAME(@DbName) + '.sys.database_files 
WHERE type_desc = ''' + @RowsOrLog + '''
) AS a
';

INSERT INTO ##DbStats (
	LogicalName
	,FreeMbRounded
	,TargetFreeMbRounded
	,DiffMb
	)
EXEC (@Sql);

SET @TargetFreeMbRounded = (
		SELECT TargetFreeMbRounded
		FROM ##DbStats
		)
SET @DbFileName = (
		SELECT LogicalName
		FROM ##DbStats
		);

IF @TruncateOnly = 1
BEGIN
	SET @ShrinkSql = '
USE ' + QUOTENAME(@DbName) + ';

DBCC SHRINKFILE (
		N' + '''' + @DbFileName + '''' + '
		, 0
		, TRUNCATEONLY
		) WITH NO_INFOMSGS;
';

	EXEC (@ShrinkSql);
END
ELSE
BEGIN
	SET @ShrinkIncrementInMb = (
			(
				SELECT (FLOOR(MAX(FreeMbRounded)) - @TargetFreeMbRounded)
				FROM ##DbStats
				) / @NumberOfShrinks
			);

	UPDATE ##DbStats
	SET ShrinkIncrementInMb = @ShrinkIncrementInMb
		,NumberOfShrinksLeft = @NumberOfShrinks
	WHERE NumberOfShrinksLeft IS NULL;

	SELECT TOP 1 *
	FROM ##DbStats
	ORDER BY Id_DbStats DESC;

	IF @ShrinkIncrementInMb > 0
	BEGIN
		WHILE @NumberOfShrinks > 0
		BEGIN
			SET @FreeMbRounded = (
					SELECT FreeMbRounded
					FROM ##DbStats
					WHERE Id_DbStats = (
							SELECT MAX(Id_DbStats)
							FROM ##DbStats
							)
					);

			IF @TargetFreeMbRounded > (@FreeMbRounded - @ShrinkIncrementInMb)
			BEGIN
				SET @ShrinkToInMb = @TargetFreeMbRounded;
			END
			ELSE
			BEGIN
				SET @ShrinkToInMb = (@FreeMbRounded - @ShrinkIncrementInMb);
			END

			SET @ShrinkSql = '
USE ' + QUOTENAME(@DbName) + ';

DBCC SHRINKFILE (
		N' + '''' + @DbFileName + '''' + '
		,' + CONVERT(VARCHAR(12), @ShrinkToInMb) + '
		) WITH NO_INFOMSGS;
';

			EXEC (@ShrinkSql);

			SET @NumberOfShrinks = @NumberOfShrinks - 1;
			SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
SELECT LogicalName
,FreeMbRounded
,' + CONVERT(VARCHAR(255), @TargetFreeMbRounded) + '
,FLOOR(FreeMbRounded) - ' + CONVERT(VARCHAR(255), @TargetFreeMbRounded) + ' AS DiffMb
,' + CONVERT(VARCHAR(255), @ShrinkIncrementInMb) + '
,' + CONVERT(VARCHAR(255), @NumberOfShrinks) + '
FROM (
SELECT NAME AS LogicalName
	,CAST(ROUND(CONVERT(DECIMAL(12, 1), (size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0), 0) AS INT) AS FreeMbRounded
	,CAST(ROUND(CONVERT(DECIMAL(12, 1),((((size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0) / 100.0) * ' + @SpaceToLeavePercentage + ')), 0) AS INT) AS TargetFreeMbRounded
FROM ' + QUOTENAME(@DbName) + '.sys.database_files 
WHERE type_desc = ''' + @RowsOrLog + '''
) AS a
';

			INSERT INTO ##DbStats (
				LogicalName
				,FreeMbRounded
				,TargetFreeMbRounded
				,DiffMb
				,ShrinkIncrementInMb
				,NumberOfShrinksLeft
				)
			EXEC (@Sql);

			SELECT TOP 1 *
			FROM ##DbStats
			ORDER BY Id_DbStats DESC;
		END
	END
	ELSE
	BEGIN
		SELECT *
		FROM ##DbStats;
	END

	DROP TABLE ##DbStats
END;
		--DROP TABLE ##DbStats
		/*CHECK TO RUN IN ANOTHER SSMS WINDOW*/
		/*
SELECT TOP 1 Id_DbStats
	,LogicalName
	,FreeMbRounded
	,TargetFreeMbRounded
	,DiffMb
	,ShrinkIncrementInMb
	,NumberOfShrinksLeft
FROM ##DbStats WITH (NOLOCK)
ORDER BY Id_DbStats DESC;
/*
/*CHECK PROGRESS*/
/*
SELECT percent_complete AS PercentageComplete
	,start_time AS StartTime
	,STATUS AS CurrentStatus
	,command AS Command
	,estimated_completion_time AS EstimatedCompletionTime
	,cpu_time AS CpuTime
	,total_elapsed_time AS TotalElapsedTime
FROM sys.dm_exec_requests
WHERE Command = 'DbccFilesCompact'
*/
*/
/*
DROP TABLE ##DbStats
*/
*/

 

If you found this post helpful please like, comment and share.

How to create a datetime stamp within a batch script in the format YYYY-MM-DD

Below is a batch script that populates the variable Timestamp with a datetime value in the format YYYY-MM-DD_HH:MM:SS.

This technique can then be used when creating files etc.

To try out the example below copy the text and paste it into a text editor like notepad. Save the file as timestamp with the extension .bat and then double click on the filestamp.bat file.

This code example will display the current datetime in windows command shell. It will also create a blank file called Test along with the current datetime in the format yyyymmddhhmmss in the same directory the batch script is run from.

@ECHO off
CLS
ECHO Date format = %date%

REM Breaking down the format 
FOR /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G
ECHO dd = %datetime:~6,2%
ECHO mth = %datetime:~4,2% 
ECHO yyyy = %datetime:~0,4%
ECHO/
ECHO Time format = %time%
ECHO hh = %time:~0,2%
ECHO mm = %time:~3,2%
ECHO ss = %time:~6,2%
ECHO/

REM Variable format 1
SET Timestamp=%date:~6,8%-%date:~3,2%-%date:~0,2%_%time:~0,2%:%time:~3,2%:%time:~6,2%
ECHO New Format 1: %Timestamp%
ECHO/
REM Variable Format 2
SET Timestamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%
ECHO New Format 2: %Timestamp%
ECHO/
REM Building a timestamp from variables
SET "dd=%datetime:~6,2%"
SET "mth=%datetime:~4,2%"
SET "yyyy=%datetime:~0,4%"
SET "Date=%yyyy%%mth%%dd%"
ECHO Built Date from variables: %Date%
ECHO/

REM Write Timestamp into file name
REM BREAK>Test%Timestamp%.txt 
PAUSE

 

How to write text to File in SQL Server Management Studio

The following is a tutorial on creating a stored procedure that will allow you to create a file in any directory and insert text into this newly created file.

In order for this to work you will need to authorise the running of system stored procedures with Object Linking and Embedding functionality (See OLE).

Authorisation is needed as the stored procedure we will create rely on the system SPs sp_OACreate and sp_OAMethod.

sp_OACreate: Creates an instance of an OLE object.

sp_OAMethod: Calls a method of an OLE object.

NOTE: By default, SQL Server blocks access to OLE Automation stored procedures by turning the components off as part of the security configuration for the server.

Run the script below to grant authorisation.

--AUTHORIZE SYSTEM STORED PROCEDURES
sp_configure 'show advanced options'
	,1;
GO

RECONFIGURE;
GO

sp_configure 'Ole Automation Procedures'
	,1;
GO

RECONFIGURE;
GO

--AUTHORIZED

 
Next we will create the stored procedure WriteToFile. Substitute the DatabaseName with the database you will be using.

--CREATE STORED PROCEDURE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[WriteToFile]', 'P') IS NOT NULL
	DROP PROCEDURE [WriteToFile];
GO

CREATE PROCEDURE [dbo].[WriteToFile] @File VARCHAR(255)
	,@Text VARCHAR(MAX)
	WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @OLE INT
	DECLARE @FileID INT

	EXECUTE sp_OACreate 'Scripting.FileSystemObject'
		,@OLE OUT

	EXECUTE sp_OAMethod @OLE
		,'OpenTextFile'
		,@FileID OUT
		,@File
		,8
		,1

	EXECUTE sp_OAMethod @FileID
		,'WriteLine'
		,NULL
		,@Text

	EXECUTE sp_OADestroy @FileID

	EXECUTE sp_OADestroy @OLE
END;

 
Below is an example using the WriteToFile stored procedure.

--WRITE TO FILE EXAMPLE
/*
CHANGE:
DATABASE NAME: DatabaseName 
 */
USE "DatabaseName";
GO

DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'
SET @Txt = 'Hello World'

EXEC [DatabaseName].[dbo].[WriteToFile] @Path
	,@Txt;

 

Using the same stored procedure here’s an example writing a table to the file using concatenation and a loop. This process works by taking the table row by row and writing the concatenated value to the file. There are easier ways to achieve this however, i.e. utilising the export functionality built into SSMS and saving this as a package to be run as a job.

--STORED PROCEDURE CREATED
--WRITE TABLE TO FILE EXAMPLE
DECLARE @Path VARCHAR(255)
DECLARE @Txt VARCHAR(MAX)
DECLARE @loopId AS INT
DECLARE @maxId AS INT
DECLARE @TempCustomer TABLE (
	ID_column INT IDENTITY PRIMARY KEY
	,FirstName NVARCHAR(100)
	);

--NOTE: THE LOG FILE WILL BE CREATED IF IT DOES NOT ALREADY EXIST
SET @PATH = 'C:\temp\WriteToFileExampleLog.txt'

INSERT INTO @tempCustomer (FirstName)
VALUES ('Paul')
	,('Jim')
	,('John')

SET @loopId = 1
SET @maxId = (
		SELECT MAX(ID_column)
		FROM @TempCustomer
		)

WHILE @loopId <= @maxId
BEGIN
	SELECT @Txt = CONVERT(VARCHAR(10), ID_column) + ', ' + FirstName
	FROM @TempCustomer
	WHERE ID_column = @loopId

	PRINT @Txt

	EXEC [TEST_DB].[dbo].[WriteToFile] @Path
		,@Txt;

	SET @loopId = @loopId + 1
END

 

How to save game scores in Corona SDK by writing to a file.

The scope of this tutorial will be to record and store the highest score achieved by a player in a game. This is done by:

  • Creating a file with a score of zero where the file does not exists.
  • When a player dies:
    • The previous score, i.e. value within the file, is assigned to a variable.
    • The player’s new score is assigned to a variable.
  • The two variables are compared.
  • If the previous score is higher nothing is changed.
  • If the previous score is lower the new score will be written to the file in its place.

Note:

No UI is provided as part of the tutorial as corona sdk often changes the manner in which objects are displayed, meaning the UI could break with future corona sdk versions. We will be working from the simulator output window alone.

For security reasons, you are not allowed to write files in the system.ResourceDirectory (the directory where the application is stored). You must specify either system.DocumentsDirectory, system.TemporaryDirectory, or system.CachesDirectory in the system.pathForFile() function when opening the file for writing. Read move about this here.

Below is a table describing when and where each directory should be used.

systemDirectoriesTo use this tutorial create a folder containing a main.lua file.
Paste the code below into the file and save.
Open the file with Corona SDK and the score.txt file will be created and populated with a score of zero.

Play around with the Player Score variable:
newScore = 99

Enter a higher score and it will overwrite what currently exists in the file.

Enter a lower score and nothing will be changed.

-- main.lua

local path = system.pathForFile( "score.txt", system.DocumentsDirectory )

deleteFile = function()
 local result, reason = os.remove(path) 
	if result then
		print( "File removed" )
	else
		print( "File does not exist", reason )  --> score.txt: No such file or directory
	end
end

--[[ 
Uncomment below to remove file
--]]

--deleteFile()

-- Player Score

newScore = 99

-- io.open opens a file at path. returns nil if no file found
-- fh short for file handle
-- "r" is the read instruction
local fh, reason = io.open( path, "r" )

if fh then
    -- Read all contents of file into a variable oldScore
	-- This will be the previous score
	-- To read file content as number use "*number"
	-- To read file content as text use "*a"
	-- "\n" new line
    local oldScore = fh:read( "*number" )
    print( "Old contents of " .. path .. "\n" .. oldScore )
	
	if oldScore < newScore then
		-- re-opening the file in "w+" mode will erase all previous data stored in the file
		-- in the comments below is a table listing all the file mode types
		fh = io.open( path, "w+" )
		-- Set the score to the player's new score.
		fh:write( newScore )
		print( "New contents of " .. path .. "\n" .. newScore )
	end	
else
	-- Error logic
    print( "Reason open failed: " .. reason )  -- display failure message in terminal

    -- create file because it doesn't exist yet
    fh = io.open( path, "w" )

    if fh then
        print( "Created file" )
    else
        print( "Create file failed!" )
    end
	
	-- Set the score to zero.
    fh:write( 0 )

end

io.close( fh )

--[[
The various file modes are listed in the following table:

"r"	Read-only mode and is the default mode where an existing file is opened.
"w"	Write enabled mode that overwites existing file or creates a new file.
"a"	Append mode that opens an existing file or a creates a new file for appending.
"r+" Read and write mode for an existing file.
"w+" All existing data is removed if file exists or new file is created with read write permissions.
"a+" Append mode with read mode enabled that opens an existing file or creates a new file.

]]--

See the lua online book’s I/O library section for more information on working with files.