Log File Size

use ReportBuilderTraining

---
--- explore controlling transaction log size
---

---
--- first, truncate the transaction log
---

-- Truncate the log by changing the database recovery model to SIMPLE.  
ALTER DATABASE ReportBuilderTraining  
SET RECOVERY SIMPLE;  
GO  

-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (ReportBuilderTraining_Log, 1);  
GO  

-- Reset the database recovery model.  
ALTER DATABASE ReportBuilderTraining  
SET RECOVERY FULL;  
GO  

SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files ;  
DBCC SQLPERF(LOGSPACE);  
GO 

---
--- Now, build up the transaction log
---

if OBJECT_ID('dbo.Numbers') is not null
	drop table dbo.Numbers
go

create table dbo.Numbers (n int not null)
go

insert into dbo.Numbers (n)
select top 1000000 row_number() over (order by a.number) n
	from master.dbo.spt_values a
	cross join master.dbo.spt_values b

ALTER TABLE dbo.Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (n)
go 


---
--- check size now
---

SELECT file_id, name, type_desc, physical_name, size, max_size FROM ReportBuilderTraining.sys.database_files ;  
DBCC SQLPERF(LOGSPACE);  
GO 


---
--- do a full backup
---

use master

declare @ts varchar(20) = convert(varchar, getdate(), 112) + '_' + right('00' + cast(datepart(hh, getdate()) as varchar), 2) + cast(datepart(mi, getdate()) as varchar)
declare @fn varchar(255) = 'c:\backups\ReportBuilderTraining_' + @ts + '.trn'

backup database ReportBuilderTraining 
	TO DISK=@fn

go



---
--- check size now, and shrink it
---

use ReportBuilderTraining

SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files ;  
DBCC SQLPERF(LOGSPACE);  
GO 

DBCC SHRINKFILE (ReportBuilderTraining_log, 1);  
GO  

SELECT file_id, name, type_desc, physical_name, size, max_size FROM sys.database_files ;  
DBCC SQLPERF(LOGSPACE);  
GO