Got a question from a user today that his SQL Server growing and shrinking on its own.Being shallow in SQL Server for the time being. That's triggered my curiosity and starting to do some reading about it.There are features in SQL Server database for this purpose.
Get a general idea of which DB actually growing or shrinking by monitoring it for a bit.
SELECT
DB.name,
MF.physical_name,
MF.type_desc AS FileType,
MF.size * 8 / 1024 AS FileSizeMB,
fileproperty(MF.name, 'SpaceUsed') * 8/ 1024 AS UsedSpaceMB,
mf.name LogicalName
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id;
SELECT * FROM sys.database_files;
Look at the autoshrinking option
select name, is_auto_shrink_on from sys.databases;
From the GUI.
Also likelihood reindexing, large batch jobs and database shrinking. Those do cause transaction logs to grow and shrink.
Also might be DBA performing shrinkfile.
BACKUP LOG VCDB_LOG TO DISK = 'E:\SQLbackup.bak'
GO
DBCC SHRINKFILE (N'VCDB_LOG' , 10)
GO
No comments:
Post a Comment