SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to shrink transaction log - could not locate files


Unable to shrink transaction log - could not locate files

Author
Message
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51457 Visits: 17657
what is the recovery model of your database?

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
azdzn
azdzn
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2028 Visits: 289
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 :-)



george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23576 Visits: 13698
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

---------------------------------------------------------------------
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51457 Visits: 17657
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" ;-)
jpomfret7
jpomfret7
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 806
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 Smile
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23576 Visits: 13698
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.

---------------------------------------------------------------------
Jared Karney
Jared Karney
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12888 Visits: 3697
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 Smile


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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
jpomfret7
jpomfret7
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 806
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!
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23576 Visits: 13698
Interesting, thanks for posting that back.

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

---------------------------------------------------------------------
jpomfret7
jpomfret7
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 806
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 Smile
http://www.simple-talk.com/sql/database-administration/monitoring-sql-server-virtual-log-file-fragmentation/

Thanks again!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search