DBCC Shrinkfile cannot identify log from stored logical name post SP3 update

Question

Dear all,

I am currently having an issue with DBCC Shrinkfile. This started after SQL Server 2008 was patched to SP3.

We use a number of databases which are recreated daily from delievered data extracts and several scripts therefore use shrinkfile on these databases as we have no recoverability requirement and take no user input into the databases.

Since we had patches applied to SQL Server 2008 (not R2) however Shrinkfile is only able to identify the logs from their file_id, not their logical name.

If the database properties are edited to perform a truncate (Change logging from simple to full and back again) then Shrinkfile performs correctly using the logical name for a day or two and then the problem recurs.

Does anyone have any suggestions as to why this might have happened, and what I can do to solve the issue? I can confirm that we have checked the system objects and the logcal name and physical filenames are all correct. There is no error in the code (for starters it hasn't been changed in 18 months).

Thanks for your help. Apols for restarting this thread - the previous one got marked as solved by accident, and I have found no way to reverse this.

Iain

Answer 1

Hi Iain,

Please using the following steps:
1. use "Select @@version" to check which version of SQL　Server you are using.
2. Perform a simple test using DBCC SHRINKFILE, see: http://msdn.microsoft.com/en-us/library/ms189493(v=sql.100).aspx 

If possible, could you please supply t-sql statement and error messsage? Since the issue is most related to Database engine, I suggest you can post the issue to Database engine forum for more efficient help.

Thanks,
Eileen

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

Answer 2

changing the database recovery model from SIMPLE recovery mode to FULL Recovery mode or vice versa ,will reset the LSN(log sequence NUmber ) of the database,

please take a log backup before shriunking the ldf file ,check my blod for best practice of shrinking a log file

http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/

truncate only option is not available from SQL Server 2008.

if you ldf(log ) file is continously increasing , please check  for open transactinons usinG

DBCC OPENTRAN

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 3

hi Eileen:-

@@version is:-

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)   Sep 22 2011 00:28:06   Copyright (c) 1988-2008 Microsoft Corporation 

Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)

The command is :-

DBCC

SHRINKFILE ('PerfDB_log', 100)

the error is

"Could not locate file 'PerfDB_log' for database 'PerfDB' in sys.database_files. The file either does not exist, or was dropped.

which sadly is not true. It does exist, was not dropped and still has the same filename and logical name.

And to Ramesh, thanks for the clarification of the SIMPLE/FULL switch, but can you explan why that makes the shrinkfile work, but only for a short period of time?

Iain

Answer 4

1) is it a regular issue or switching one time resolve your issue?

2) have you tried to shrink using file id instead of file name ?

3) check sys.databases for log_reuse_wait column for reason log spacee not shrinking or in resuse

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 5

I'm having the exact same problem, since applying SP3 a few weeks ago.

DBCC SHRINKFILE will not recognize filenames, but it WILL work with file ID's.

The really strange part is that it appears intermittent. I first saw this error ONCE a couple of weeks ago from a nightly job, calling out the command in an SP. I ended up running a statement using file ID's and all was fine.

After that the jobs ran without error (I didn't change a thing), using filename's until a couple of days ago. Now I'm getting the "Cannot locate file" error again...2 days in a row now.

Again, I can still shrink using file ID's.

Answer 6

There are several issues.

1. Why are you shrinking if you don't even know what are SIMPLE and FULL modes for the database?

2. If there is no DBA and you are the reluctant DBA, read the following article and links within the article:

http://www.sqlusa.com/bestpractices2005/shrinklog/

3. If the database is in the SIMPLE mode, you don't have to shrink, it is automatic.

4. Can you shrink from SSMS Object Explorer?

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 7

Hi Kalman,

I'm afraid there is only one issue. You have identified some additional points of learning.

In answer to your questions:-

I am shrinking as the previous DBA set up periodic shrinks within the ETL Script Engine that we use. Our databases are recreated dynamically every day following external data delivery. It is my understanding therefore that the shrinking is neccessary. I am happy to be advised otherwise.

I understand that shrinking is automatic if in SIMPLE mode, however my understanding from the DBA was that the shrink is neccessary due to the constant database regeneration (the databases are not dropped entirely, but each table is individually dropped and recreated).

It is possible to shrink from SSMS Object Explorer and via script. However script will only work with object ID and not the logical name, which seems very odd.

The issue has now resolved itself somewhat in that the server suffered a major disk crash neccessitating a complete rebuild on a new server. This wasn't the solution that I was hoping for.

It is also interesting to note that noonie65 is having the same issue, which seems to point to it being a side effect of the SP3 update.

Iain