Description
You attempt to shrink a transaction log file associated with a SQL Server database, but you receive in error:
"Could not locate file '
Transaction Log Name' for database
'
Database Name' in sys.database_files. The file either does not exist, or was dropped."
Solution
1) Run SP_HelpFile to get the correct name of the transaction log file.
USE
Database Name
EXEC sp_helpfile
(TechNet, 2013)
This will return the following information about the database files, including the associated transation log(s):
Name, FileId, FileName, FileGroup, Size, MaxSize, Growth, Usage
2) Run the Transact-SQL statement again, using the correct
Transaction Log Name. You can find this in the
Name column of the SP_HelpFile query results set.
USE [
Database Name]
GO
ALTER DATABASE
Database Name SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(
Transaction Log Name, 1)
ALTER DATABASE
Database Name SET RECOVERY FULL WITH NO_WAIT
GO
(TechNet, 2013)
3) Verify that there are configured, scheduled, and enabled SQL Server Backup jobs in place for:
a) Full Database Backup
b) Transaction Log Backup
*Important for Full Recovery Model*
Reference
TechNet (2013). Backup Under the Full Recovery Model. Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms190217%28v=sql.105%29.aspx
TechNet (2013). DBCC SHRINKFILE (Transact-SQL). Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms189493.aspx
TechNet (2013). Recovery Models and Transaction Log Management. Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/ms366344%28v=sql.105%29.aspx
TechNet (2013). sp_helpfile. Retrieved August 11, 2013 from http://technet.microsoft.com/en-us/library/aa933456%28v=sql.80%29.aspx
