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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 10, 2012 at 2:35 pm
Running SELECT * FROM test.dbo.sysfiles I got the following
[/code
fileidgroupidsizemaxsizegrowthstatusperfnamefilename
116705152-1020test_dataJ:\SQLdata\test_data.mdf
201280415-164000660test_logH:\SQLLog\test_log.ldf
32823280-16400020test_indexG:\SQLIndex\test_index.mdf
411363672-16400020test_data1P:\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
[/codefileidgroupidsizemaxsizegrowthstatusperfnamefilename
116705152-1020test_dataJ:\SQLdata\test_data.mdf
201280415-164000660test_logH:\SQLLog\test_log.ldf
32823280-16400020test_indexG:\SQLIndex\test_index.mdf
411363672-16400020test_data1P:\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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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