Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Unable to shrink transaction log - could not locate files Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 2:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,356, Visits: 13,686
what is the recovery model of your database?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1233575
Posted Tuesday, January 10, 2012 3:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:52 PM
Points: 1,520, Visits: 254
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



Post #1233578
Posted Tuesday, January 10, 2012 3:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,992, Visits: 12,939
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

---------------------------------------------------------------------

Post #1233594
Posted Tuesday, January 10, 2012 4:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 6,356, Visits: 13,686
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"
Post #1233621
Posted Tuesday, January 10, 2012 9:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:36 AM
Points: 147, Visits: 731
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 :)
Post #1233709
Posted Wednesday, January 11, 2012 3:20 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,992, Visits: 12,939
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.


---------------------------------------------------------------------

Post #1233840
Posted Wednesday, January 11, 2012 6:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:39 AM
Points: 2,694, Visits: 3,387
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.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1233913
Posted Wednesday, January 11, 2012 6:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:36 AM
Points: 147, Visits: 731
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!
Post #1233930
Posted Wednesday, January 11, 2012 6:42 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 8, 2014 8:17 AM
Points: 5,992, Visits: 12,939
Interesting, thanks for posting that back.

Just curious, if you did still use truncateonly (naughty ), what did the file shrink to?


---------------------------------------------------------------------

Post #1233935
Posted Wednesday, January 11, 2012 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 8:36 AM
Points: 147, Visits: 731
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 :)
http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

Thanks again!
Post #1233939
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse