InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:05:38 GMT20http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxI had the same problem. After I backed up the database, everything seemed to reset and I could truncate the log.Fri, 15 Feb 2013 08:17:12 GMTabrighthttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxGreat topic for me:-)Just been scratching my head at same issue...Msg 8985, Level 16, State 1, Line 1Could not locate file *_log' for database 'I*' in sys.database_files. The file either does not exist, or was dropped...using...SQL 2005 SP3 simple recovery mode.By specifying file_id the DBCC SHRINKFILE (2, 100) worked perfect.Thanks for helping.Mon, 07 Jan 2013 05:07:39 GMTDaniel Taylor-446457http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote]Glad to help! Maybe worth a small blurb on my blog... Hmm...[/quote]I would say go for it, I looked around for a long time and didn't find much info out there. Still wish I could work out why it doesn't like the log names but hey at least it works :)Wed, 11 Jan 2012 08:19:55 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote][b]jpomfret7 (1/11/2012)[/b][hr][quote]USE testDBCC SHRINKFILE(2,20)Since we already know that the fileid is 2, go ahead and try this. [/quote]Hooray!! That worked!Did some additional testing there must be something weird going on with the file name because using the file id instead of logical name for DBCC SHRINKFILE(2, TRUNCATEONLY) works also.Still a weird issue but thanks everyone for there help on this![/quote]Glad to help! Maybe worth a small blurb on my blog... Hmm...Wed, 11 Jan 2012 08:14:07 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote]I would surmise 3MB was its default size.[/quote]Sure was :)Wed, 11 Jan 2012 07:46:36 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxI would surmise 3MB was its default size.Wed, 11 Jan 2012 07:43:32 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxThe test log shrank to 3mb.My aim is to shrink the log files as small as possible so I can manually allocate the initial size. We are seeing high numbers of VLF's for each log due to inappropriate growth settings. So at the end of the day the log files will be a decent size for the database and not be internally fragmented.Here's a pretty good article that explains it better than I do :)[url]http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/[/url]Thanks again!Wed, 11 Jan 2012 06:49:28 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxInteresting, thanks for posting that back.Just curious, if you did still use truncateonly (naughty :-) ), what did the file shrink to?Wed, 11 Jan 2012 06:42:19 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote]USE testDBCC SHRINKFILE(2,20)Since we already know that the fileid is 2, go ahead and try this. [/quote]Hooray!! That worked!Did some additional testing there must be something weird going on with the file name because using the file id instead of logical name for DBCC SHRINKFILE(2, TRUNCATEONLY) works also.Still a weird issue but thanks everyone for there help on this!Wed, 11 Jan 2012 06:32:14 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote][b]jpomfret7 (1/10/2012)[/b][hr][quote]To be safe, you really should run against test.sys.database_files[/quote]The file name and info matches in sys.database_files, sys.master_files, sys.sysaltfiles and dbo.sysfiles...[quote]what is the recovery model of your database?[/quote]We are in simple recovery and I ran a checkpoint before attempting to shrink the logs[quote]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 [/quote]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 :)[/quote]I just always did this when needed:[code="sql"]USE testDBCC SHRINKFILE(2,20)[/code]Since we already know that the fileid is 2, go ahead and try this.Wed, 11 Jan 2012 06:03:45 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote]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?[/quote]the parameter is ignored, so the shrink command defaults to trying to shrink to the default file size (size the file was created). You should specify a target size.Wed, 11 Jan 2012 03:20:18 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote]To be safe, you really should run against test.sys.database_files[/quote]The file name and info matches in sys.database_files, sys.master_files, sys.sysaltfiles and dbo.sysfiles...[quote]what is the recovery model of your database?[/quote]We are in simple recovery and I ran a checkpoint before attempting to shrink the logs[quote]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 [/quote]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 :)Tue, 10 Jan 2012 21:32:42 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxwhen 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!Tue, 10 Jan 2012 16:03:05 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxI would not expect it to give the error you see, but anyway the truncateonly option has no effect when applied to a log shrinkTue, 10 Jan 2012 15:29:15 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote][b]ScottPletcher (1/10/2012)[/b][hr]Given that this is SQL 2008 (or SQL 2005 at least), you mean:SELECT *FROM test.sys.database_filesright? :-)[/quote]Yes of course :-)Tue, 10 Jan 2012 15:01:11 GMTazdznhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxwhat is the recovery model of your database?Tue, 10 Jan 2012 14:56:27 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxYes I can physically see the file thereTue, 10 Jan 2012 14:48:28 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote]Running SELECT * FROM test.dbo.sysfiles I got the following [/quote]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.Tue, 10 Jan 2012 14:48:09 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspx[quote][b]jpomfret7 (1/10/2012)[/b][hr]Running SELECT * FROM test.dbo.sysfiles I got the following [code="other"][/codefileid groupid size maxsize growth status perf name filename1 1 6705152 -1 0 2 0 test_data J:\SQLdata\test_data.mdf2 0 1280415 -1 64000 66 0 test_log H:\SQLLog\test_log.ldf3 2 823280 -1 64000 2 0 test_index G:\SQLIndex\test_index.mdf4 1 1363672 -1 64000 2 0 test_data1 P:\SQLData\test_data1.ndf[/code][/quote]And does that drive letter and file path still exist? I have to ask... :)Tue, 10 Jan 2012 14:37:32 GMTSQLKnowItAllhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxRunning SELECT * FROM test.dbo.sysfiles I got the following [code="other"][/codefileid groupid size maxsize growth status perf name filename1 1 6705152 -1 0 2 0 test_data J:\SQLdata\test_data.mdf2 0 1280415 -1 64000 66 0 test_log H:\SQLLog\test_log.ldf3 2 823280 -1 64000 2 0 test_index G:\SQLIndex\test_index.mdf4 1 1363672 -1 64000 2 0 test_data1 P:\SQLData\test_data1.ndf[/code]Tue, 10 Jan 2012 14:35:20 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxGiven that this is SQL 2008 (or SQL 2005 at least), you mean:SELECT *FROM test.sys.database_filesright? :-)Tue, 10 Jan 2012 14:22:18 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxCan you give us the result you have when executing :SELECT * FROM test.dbo.sysfilesTue, 10 Jan 2012 14:19:26 GMTazdznhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxOk.See what is in the sys.master_files "table" for that db:SELECT *FROM sys.master_filesWHERE database_id = DB_ID(N'test')Tue, 10 Jan 2012 14:19:25 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxName in the first column in 'test_log'Tue, 10 Jan 2012 13:57:54 GMTjpomfret7http://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxVerify 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_helpfileThe first column will show the logical file name, which, of course, must match what you put in the SHRINKFILE command.Tue, 10 Jan 2012 13:54:06 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1233467-391-1.aspxHello 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:[code="sql"]use testDBCC SHRINKFILE(test_log, TRUNCATEONLY)--oruseDBCC SHRINKFILE(test_log,2, TRUNCATEONLY)[/code] I get the following message:[code="plain"]Msg 8985, Level 16, State 1, Line 1Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped. [/code]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.ThanksJessTue, 10 Jan 2012 12:31:01 GMTjpomfret7