SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to shrink transaction log - could not locate files


Unable to shrink transaction log - could not locate files

Author
Message
jpomfret7
jpomfret7
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 806
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
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20128 Visits: 7418
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
jpomfret7
jpomfret7
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 806
Name in the first column in 'test_log'
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20128 Visits: 7418
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
azdzn
azdzn
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2092 Visits: 289
Can you give us the result you have when executing :
SELECT * FROM test.dbo.sysfiles



ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20128 Visits: 7418
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
jpomfret7
jpomfret7
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 806
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


Jared Karney
Jared Karney
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14002 Visits: 3697
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... Smile

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
ScottPletcher
ScottPletcher
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20128 Visits: 7418
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
jpomfret7
jpomfret7
SSC-Addicted
SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)SSC-Addicted (424 reputation)

Group: General Forum Members
Points: 424 Visits: 806
Yes I can physically see the file there
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search