By Daniel Sauer
A straight forward task; yet, here we are. As this was certainly not planned to be a series of events while maintaining a single database in development, the A-Team plans for risks in essentially every task that we are assigned. Overcoming a previously solved issue: Shrink failed for Database ‘LCRAA’. (Microsoft.SqlServer.Smo), further down the ‘TO-DO’ list was to Backup and truncate the database log.
problem: need to backup and truncate a database log and within the operation you get an error describing that there was not enough disk space
fix:
As this is a continuation of a previous task for maintaining a database, this event occurred while the target database was in ‘single-user’ mode.
Furthermore, the backup device showed that the A-Team had approximately 100GB remaining on the target drive for completing the operation. The current state of the database and log is approximately 7GB and 144GB respectfully.
Based on this information and reviewing the storage device, the A-Team changed course on how to truncate the database. Using the following command allows us to overcome the space issue and proceed with checking this particular task off the ‘TO-DO’ list.
alter database lcraa set recovery simple
dbcc shrinkfile(lcraa, 10);
alter database lcraa set recovery full