1

Query:

DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
 SET RECOVERY FULL;
GO
BACKUP DATABASE Tal 
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
 WITH FORMAT;
GO 
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TAL
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE ('MyTal_Log', 1,TRUNCATEONLY);
GO
-- Reset the database recovery model.
ALTER DATABASE TAL
SET RECOVERY FULL;
GO 

OUTPUT:

(7 rows affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. Processed 440 pages for database 'Tal', file 'Tal' on file 1. Processed 2 pages for database 'Tal', file 'Tal_log' on file 1. BACKUP DATABASE successfully processed 442 pages in 0.434 seconds (7.939 MB/sec). Msg 8985, Level 16, State 1, Line 16 Could not locate file 'MyTal_Log' for database 'master' in sys.database_files. The file either does not exist, or was dropped.

1 Answer 1

1

I would start by saying that shrinking, be it that of data or log file, is really bad avoid it as much as possible. There is hardly anything you can benefit, specially with what you are doing.

As for your question look at the below specific error message

Msg 8985, Level 16, State 1, Line 16 Could not locate file 'MyTal_Log' for database 'master' in sys.database_files

The query is looking for logical file named MyTal_log in master, is this the logical filename of log file of database tal ?, I believe it is not. To confirm run below

sp_helpdb Tal

As shown in pic, select the logical name and use that name in the query, this is Tal database created on my system.

enter image description here

DBCC SQLPERF (Logspace)
USE master;
GO
ALTER DATABASE Tal
 SET RECOVERY FULL;
GO
BACKUP DATABASE Tal 
TO DISK = 'D:\SQLServerBackups\MyTalLog.bak'
 WITH FORMAT;
GO 
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TAL
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
USE tal--**database name**
GO
DBCC SHRINKFILE ('MyTal_Log', 1,TRUNCATEONLY);--add correct logical name
GO
-- Reset the database recovery model.
ALTER DATABASE TAL
SET RECOVERY FULL;
GO 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.