January 11, 2012 at 3:20 am
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?
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.
---------------------------------------------------------------------
January 11, 2012 at 6:03 am
jpomfret7 (1/10/2012)
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 🙂
I just always did this when needed:
USE test
DBCC SHRINKFILE(2,20)
Since we already know that the fileid is 2, go ahead and try this.
Jared
CE - Microsoft
January 11, 2012 at 6:32 am
USE test
DBCC SHRINKFILE(2,20)
Since we already know that the fileid is 2, go ahead and try this.
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!
January 11, 2012 at 6:42 am
Interesting, thanks for posting that back.
Just curious, if you did still use truncateonly (naughty 🙂 ), what did the file shrink to?
---------------------------------------------------------------------
January 11, 2012 at 6:49 am
The 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 🙂
Thanks again!
January 11, 2012 at 7:43 am
I would surmise 3MB was its default size.
---------------------------------------------------------------------
January 11, 2012 at 7:46 am
I would surmise 3MB was its default size.
Sure was 🙂
January 11, 2012 at 8:14 am
jpomfret7 (1/11/2012)
USE test
DBCC SHRINKFILE(2,20)
Since we already know that the fileid is 2, go ahead and try this.
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!
Glad to help! Maybe worth a small blurb on my blog... Hmm...
Jared
CE - Microsoft
January 11, 2012 at 8:19 am
Glad to help! Maybe worth a small blurb on my blog... Hmm...
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 🙂
January 7, 2013 at 5:07 am
Great topic for me:-)
Just been scratching my head at same issue...
Msg 8985, Level 16, State 1, Line 1
Could 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.
February 15, 2013 at 8:17 am
I had the same problem. After I backed up the database, everything seemed to reset and I could truncate the log.
August 20, 2015 at 11:18 am
I was having the same issue. I could not execute the shrink command on my logical log file name when the name was different in sys_database_files and sys_master_files. To remedy this, I detached the database, deleted the log file and reattached without the log file so it would recreate it. This makes the logical log file name the same in both sys_database_files and sys_master_files. Now the shrink command works on the logical log file name.
My problems began when I detached all my databases in SQL Server 2008 and attached them in SQL Server 2014 version.
Hope that helps someone save some time.
August 20, 2015 at 2:41 pm
Doug-107928 (8/20/2015)
I was having the same issue. I could not execute the shrink command on my logical log file name when the name was different in sys_database_files and sys_master_files. To remedy this, I detached the database, deleted the log file and reattached without the log file so it would recreate it. This makes the logical log file name the same in both sys_database_files and sys_master_files. Now the shrink command works on the logical log file name.My problems began when I detached all my databases in SQL Server 2008 and attached them in SQL Server 2014 version.
Hope that helps someone save some time.
:crying: Don't tell people to delete their log file. That is one of the worst things you can do. Again, don't EVER do that.
Jared
CE - Microsoft
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply