Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Unable to shrink transaction log - could not locate files Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:18 AM
Points: 147, Visits: 732
Hello folks,

OK so I have done as much digging as I can on the net and haven't found a solution to this problem so I thought it was time for a post.

I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:

use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)

I get the following message:

Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.


I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.

I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.

I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.

Any ideas/suggestions would be much appreciated.

Thanks

Jess
Post #1233467
Posted Tuesday, January 10, 2012 1:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:14 PM
Points: 2,098, Visits: 3,155
Verify the logical file names are what you expect them to be.

USE <your_db_name>

EXEC sp_helpfile

--Or: EXEC <your_db_name>.dbo.sp_helpfile

The first column will show the logical file name, which, of course, must match what you put in the SHRINKFILE command.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1233515
Posted Tuesday, January 10, 2012 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:18 AM
Points: 147, Visits: 732
Name in the first column in 'test_log'
Post #1233516
Posted Tuesday, January 10, 2012 2:19 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:14 PM
Points: 2,098, Visits: 3,155
Ok.

See what is in the sys.master_files "table" for that db:

SELECT *
FROM sys.master_files
WHERE
database_id = DB_ID(N'test')


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1233531
Posted Tuesday, January 10, 2012 2:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,520, Visits: 254
Can you give us the result you have when executing :
SELECT * FROM test.dbo.sysfiles



Post #1233532
Posted Tuesday, January 10, 2012 2:22 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:14 PM
Points: 2,098, Visits: 3,155
Given that this is SQL 2008 (or SQL 2005 at least), you mean:

SELECT *
FROM test.sys.database_files

right?


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1233533
Posted Tuesday, January 10, 2012 2:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:18 AM
Points: 147, Visits: 732
Running SELECT * FROM test.dbo.sysfiles I got the following

[/code
fileid groupid size maxsize growth status perf name filename
1 1 6705152 -1 0 2 0 test_data J:\SQLdata\test_data.mdf
2 0 1280415 -1 64000 66 0 test_log H:\SQLLog\test_log.ldf
3 2 823280 -1 64000 2 0 test_index G:\SQLIndex\test_index.mdf
4 1 1363672 -1 64000 2 0 test_data1 P:\SQLData\test_data1.ndf

Post #1233546
Posted Tuesday, January 10, 2012 2:37 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:39 AM
Points: 2,694, Visits: 3,387
jpomfret7 (1/10/2012)
Running SELECT * FROM test.dbo.sysfiles I got the following

[/code
fileid groupid size maxsize growth status perf name filename
1 1 6705152 -1 0 2 0 test_data J:\SQLdata\test_data.mdf
2 0 1280415 -1 64000 66 0 test_log H:\SQLLog\test_log.ldf
3 2 823280 -1 64000 2 0 test_index G:\SQLIndex\test_index.mdf
4 1 1363672 -1 64000 2 0 test_data1 P:\SQLData\test_data1.ndf



And does that drive letter and file path still exist? I have to ask... :)


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1233550
Posted Tuesday, January 10, 2012 2:48 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 3:14 PM
Points: 2,098, Visits: 3,155
Running SELECT * FROM test.dbo.sysfiles I got the following


To be safe, you really should run against test.sys.database_files, since that is what SQL is actually looking at ... notice the error message that you got originally.


SQL DBA,SQL Server MVP('07, '08, '09)

"We came in spastic, Like tameless horses /
We left in plastic, As numbered corpses / ...
Remember Charlie, Remember Baker /
They left their childhood On every acre /
And who was wrong? And who was right? /
It didn't matter in the thick of the fight." : the inimitable Mr. Billy Joel, about the Vietnam War
Post #1233567
Posted Tuesday, January 10, 2012 2:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:18 AM
Points: 147, Visits: 732
Yes I can physically see the file there
Post #1233568
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse