Create a database called Testing,
CREATE DATABASE Testing ON PRIMARY ( NAME = 'Testing', FILENAME = 'D:\MSSQL12.MSSQL2014\MSSQL\DATA\Testing.mdf', SIZE = 4 MB, FILEGROWTH = 8 KB ) LOG ON ( NAME = 'Testing_Log', FILENAME = 'D:\MSSQL12.MSSQL2014\MSSQL\DATA\Testing_log.ldf', SIZE = 1 MB );
Here is what it look slike with PowerShell Get-ChildItem cmdlet on the database file folder. LastWriteTime as Date Modified as seen in the explorer. Length as the size (in byte) of the file.
During the database creation, the size of the database files was specified (eg. 4 MB for the MDF data file). However, that doesn't mean the file has been entirely allocated (or being used). The script below shows the physical size of the database file as well as the size of what actually being used,
USE testing; SELECT CURRENT_TIMESTAMP AS [time], f.name [file_name], f.size * 8192 [file_size_B], FILEPROPERTY(f.name, 'SpaceUsed') * 8192 [Used_size_B] FROM sys.database_files f;
Notice that the data file (MDF) used size is around 2.49 MB out of the 4 MB of the data file. Similarly to the log file.
Now, we going to perform a few task and see what affect the modified date of the database file
Create table (Data Definition Language, DDL)
The script below create a table in the database,
USE Testing;
CREATE TABLE dbo.tbl1
(
ID int IDENTITY(1,1),
Col1 nchar(4000) DEFAULT (REPLICATE('G', 4000)),
Col2 char(49) DEFAULT (REPLICATE('A', 49))
);
Notice that the used size of the log file has increased slightly. Let's examine the date modified of the database files.
Still the time where the database was first created for both data and log file.
Insert Data (Data Manipulation Language, DML)
Since the database is created on Simple recovery model (Full recovery model without any backup also behave like Simple recovery model) for this demo, the log file is automatically truncated and reused without growing if the transaction size is smaller than the file size. Because of this, we will only focus on the data file from now on.
INSERT INTO dbo.tbl1 DEFAULT VALUES; GO 2
The data file used size has increased to around 2.56 MB. However, date modified of the data file still shown as time when the database first created.
Insert large data that require file growth
Now, we are going to insert more data that large enough to require the data file growth.
INSERT INTO dbo.tbl1 DEFAULT VALUES; GO 207
The data file has grown to around 4.25 MB (64 KB more from the original size), and the data file modified date is updated.
The database is configured to grow (mean auto grow) with 8 KB. Some of you with eagle eye may notice that during the database creation, 8 KB is specified as file growth rate. However, SQL Server grow by minimum an extent (8 data page (8 KB) = 64 KB).
Modify database file size
The script below change the data file size to 10 MB.
ALTER DATABASE Testing MODIFY FILE ( NAME = 'Testing', SIZE = 10MB );
The modified date of the data file has been updated.
Shrink database file
Below is the script to shrink the data file to 6 MB. Some data was added before the shrink task just to show different used size.
INSERT INTO dbo.tbl1
DEFAULT VALUES;
GO 10
DBCC SHRINKFILE ('Testing', 6);
The data file modified date reflect the time the shrink action was performed.
Database Backup
Below script perform a full backup. Some data is also added prior just for the sake of it.
INSERT INTO dbo.tbl1 DEFAULT VALUES; GO 10 BACKUP DATABASE Testing TO DISK = 'Testing.bak';
Nothing change in data file size and its used size. Data file date modified remain the same as previous time.
Auto Close
By default, this auto_close database setting is set to false. As best practice, this should be false. Turning on this setting make SQL Server close the database after the last disconnected connection to the database. This is not recommended in practice as it takes time to re-obtain resources when it reopen the database.
The script below set the Auto_Close setting to true
ALTER DATABASE Testing SET AUTO_CLOSE ON;
In another windows,
Using
sp_who2, we identify and kill all the connection to Testing database,
KILL 56 KILL 57
Both data and log file date modified are updated due to the auto close.
Taking database offline
Below script take the database offline.
ALTER DATABASE Testing SET OFFLINE WITH ROLLBACK IMMEDIATE;
The date modified for both data and log file are updated upon this offline action.
We do another test to turn the database back online and without any changes, take the database offline.
ALTER DATABASE Testing SET ONLINE; ALTER DATABASE Testing SET OFFLINE WITH ROLLBACK IMMEDIATE;
The date modified also updated upon the last database offline action.
Stop SQL Server service
The testing database is brought online. Stop the SQL Server service from SQL Server Configuration Manager, both database files date modified are updated.
Observation
The database files date modified is updated due to these events,
1) Physical file growth - Alter statement, file auto grow
2) Physical file shrink - Shrink command (eg. DBCC SHRINKFILE, DBCC SHRINKDABASE), auto shrink (not tested in this demo, but same concept applied)
3) Database offline - Alter statement, SQL Server service
For the first two observations, the key word is that only physical file size change will update the date modified. Data records are stored in the data pages within the data file. The database engine continue to allocate new data to unallocated data page in the existing data file until it run out of unallocated page, and request/perform a physical file growth which then trigger the update on date modified property.
In this demo, it is not obvious on the log file because the database is behaving as Simple recovery model which it will automatically reuse the virtual log file (smaller logical log file within the log file) without growing if the transaction is small enough within the size of the log file. If it is on Full recovery model, similar date modified concept discussed apply to log file as well.
Conclusion
The date modified of the database files are not a good indicator to determine the recency of the database. The database could be actively being used and the date modified is dated a while back. It is possible that database file with plenty of unallocated space within could continue to allocate new data without triggering database file date modified property to be updated for a long time.
Here are few suggestions without additional setup (eg. trace or audit) to determine if the database is recently used. Each of these method has its pro and con,
- sp_who2 or sp_whoisactive to see if any current or sleeping connection on the database
- Query the sys.dm_db_index_usage_stats dmv to examine the most recent seek, scan, lookup, and update of database table / index.
- Database backup size from backup history table (msdb.dbo.backupset) to see any size change historically.
GOod
ReplyDelete