Unable to shrink transaction log - could not locate files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Unable to shrink transaction log - could not...

26 posts, Page 1 of 3

1 2 3 »»»

Unable to shrink transaction log - could not locate files

Rate Topic

Display Mode

Topic Options

Author

Message

jpomfret7

Posted Tuesday, January 10, 2012 12:31 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:32 AM
Points: 111, Visits: 546

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

ScottPletcher

Posted Tuesday, January 10, 2012 1:54 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:38 PM
Points: 1,318, Visits: 1,763

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)
One man with courage makes a majority. Andrew Jackson

Post #1233515

jpomfret7

Posted Tuesday, January 10, 2012 1:57 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:32 AM
Points: 111, Visits: 546

Name in the first column in 'test_log'

Post #1233516

ScottPletcher

Posted Tuesday, January 10, 2012 2:19 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:38 PM
Points: 1,318, Visits: 1,763

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)
One man with courage makes a majority. Andrew Jackson

Post #1233531

azdzn

Posted Tuesday, January 10, 2012 2:19 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:46 PM
Points: 1,493, Visits: 239

Can you give us the result you have when executing :
SELECT * FROM test.dbo.sysfiles

Post #1233532

ScottPletcher

Posted Tuesday, January 10, 2012 2:22 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:38 PM
Points: 1,318, Visits: 1,763

Given that this is SQL 2008 (or SQL 2005 at least), you mean:

SELECT *
FROM test.sys.database_files

right? Smile

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson

Post #1233533

jpomfret7

Posted Tuesday, January 10, 2012 2:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:32 AM
Points: 111, Visits: 546

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

SQLKnowItAll

Posted Tuesday, January 10, 2012 2:37 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:40 PM
Points: 2,610, Visits: 3,116

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

ScottPletcher

Posted Tuesday, January 10, 2012 2:48 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:38 PM
Points: 1,318, Visits: 1,763

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)
One man with courage makes a majority. Andrew Jackson

Post #1233567

jpomfret7

Posted Tuesday, January 10, 2012 2:48 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:32 AM
Points: 111, Visits: 546

Yes I can physically see the file there

Post #1233568

« Prev Topic | Next Topic »

26 posts, Page 1 of 3

1 2 3 »»»

Permissions