January 10, 2012 at 12:31 pm
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
January 10, 2012 at 1:54 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 10, 2012 at 1:57 pm
Name in the first column in 'test_log'
January 10, 2012 at 2:19 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 10, 2012 at 2:19 pm
Can you give us the result you have when executing :
SELECT * FROM test.dbo.sysfiles
January 10, 2012 at 2:22 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 10, 2012 at 2:35 pm
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
January 10, 2012 at 2:37 pm
jpomfret7 (1/10/2012)
Running SELECT * FROM test.dbo.sysfiles I got the following
[/codefileid 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... 🙂
Jared
CE - Microsoft
January 10, 2012 at 2:48 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 10, 2012 at 2:48 pm
Yes I can physically see the file there
January 10, 2012 at 2:56 pm
what is the recovery model of your database?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 10, 2012 at 3:01 pm
ScottPletcher (1/10/2012)
Given that this is SQL 2008 (or SQL 2005 at least), you mean:SELECT *
FROM test.sys.database_files
right? 🙂
Yes of course 🙂
January 10, 2012 at 3:29 pm
I would not expect it to give the error you see, but anyway the truncateonly option has no effect when applied to a log shrink
---------------------------------------------------------------------
January 10, 2012 at 4:03 pm
when the database is in full recovery model and you delete a log file, it will still list in sys.database_files and sys.master_files.
You must take a transaction log backup and the file will be removed from the catalogs mentioned above.
For databases in simple recovery the file will go immediately!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 10, 2012 at 9:32 pm
To be safe, you really should run against test.sys.database_files
The file name and info matches in sys.database_files, sys.master_files, sys.sysaltfiles and dbo.sysfiles...
what is the recovery model of your database?
We are in simple recovery and I ran a checkpoint before attempting to shrink the logs
I would not expect it to give the error you see, but anyway the truncateonly option has no effect when applied to a log shrink
I have used this script on other servers and been able to free up log space, what should be used?
It's a puzzle, thanks for all your help so far 🙂
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply