Minimize the Logspace in The Database and The Backup Of Log
Some times we can get the error as "The log file for database 'DatabaseName' is full. Backup the transaction log for the database to free up some log space".
If the Log size is increasing in The Database we can use the Below syntax to reduce. This log size
USE DatabaseName
BACKUP DatabaseName WITH TRUNCATE_ONLY
---Check
SELECT * FROM SYSFILES
DBCC SHRINKFILE(LogFileName )
---To find out the usage of logspace
DBCC SQLPERF(LOGSPACE)
And also used SP_SPACEUSED '.mdf file'
---------------
USE master
GO
DBCC SQLPERF(LOGSPACE) --To know about the Database LogFile
DUMP TRAN DatabaseName WITH NO_LOG--
GO
USE DatabaseName
SP_HELPFILE
GO
DBCC SHRINKFILE ('LogFileName')
---The below link will discuss about that minimizing log space
http://support.microsoft.com/default.aspx/kb/907511
--Some times even shrink log is not working. In that time even this is not possible.
use ToPS
If the Log size is increasing in The Database we can use the Below syntax to reduce. This log size
USE DatabaseName
BACKUP DatabaseName WITH TRUNCATE_ONLY
---Check
SELECT * FROM SYSFILES
DBCC SHRINKFILE(LogFileName )
---To find out the usage of logspace
DBCC SQLPERF(LOGSPACE)
And also used SP_SPACEUSED '.mdf file'
---------------
USE master
GO
DBCC SQLPERF(LOGSPACE) --To know about the Database LogFile
DUMP TRAN DatabaseName WITH NO_LOG--
GO
USE DatabaseName
SP_HELPFILE
GO
DBCC SHRINKFILE ('LogFileName')
---The below link will discuss about that minimizing log space
http://support.microsoft.com/default.aspx/kb/907511
--Some times even shrink log is not working. In that time even this is not possible.
use ToPS
go
alter database ToPS
set recovery simple
go
checkpoint
go
dbcc shrinkfile('toPS_log',10)--Minimizing this to 10MB
go
alter database ToPS
set recovery full
go
use ToPSreports
go
alter database ToPSreports
set recovery simple
go
checkpoint
go
dbcc shrinkfile('toPSreports_log', 10)--Minimizing this to 10MB
go
alter database ToPSreports set recovery full
Comments