Unable to shrink database log file

Question

Unable to shrink database log file

Jonathan R

Old Hand

Points: 340
More actions
October 27, 2010 at 10:41 pm

#147398

Hi,
We recently built up a new SQL 2005 server and then attached the pthprod databse to it, pthprod was from a 2000 SQl install. we had an issue where the log file was getting huge so I set the state to Simple and after some research set it back to Full, the database has not done a full backup since this has been done.
I did some research and came up with the following:
SELECT * from sysfiles (to get the right file name)
119600000-11020DataE:\Databases\MSSQL\Data\Pthprod\Pthprod_data.mdf
207301024-12010486420LogE:\Databases\MSSQL\Data\Pthprod\Pthprod_log.ldf
Ran the following:
use PTHPROD
DBCC SHRINKFILE(Pthprod_log, 2)
BACKUP LOG PTHPROD WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Pthprod_log, 2)
Get this error:
Msg 8985, Level 16, State 1, Line 2
Could not locate file 'Pthprod_log' for database 'PTHPROD' in sys.database_files. The file either does not exist, or was dropped.
Msg 8985, Level 16, State 1, Line 4
Could not locate file 'Pthprod_log' for database 'PTHPROD' in sys.database_files. The file either does not exist, or was dropped.
For the life of me I cannot see why this would not work. can anyone advise?
Thanks in advance,
Jonathan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

jonathan.rottanburg (10/27/2010)
Hi,
We recently built up a new SQL 2005 server and then attached the pthprod databse to it, pthprod was from a 2000 SQl install. we had an issue where the log file was getting huge so I set the state to Simple and after some research set it back to Full, the database has not done a full backup since this has been done.

Since u moved from simple to full recovery model, and didnt take a full db backup, the db is still behaving in pseudo-simple recovery mode.

can u take a full backup then a tran log backup? and then try to shrink?

Pradeep Singh

Jonathan R Old Hand Points: 340 More actions · Answer 2

Hi Pradeep,

The databse sucessfully backed up last night so all good there, however when I try run:

DBCC SHRINKFILE(Pthprod_log, 2)

I get the following:

Msg 8985, Level 16, State 1, Line 1

Could not locate file 'Pthprod_log' for database 'PTHPROD' in sys.database_files. The file either does not exist, or was dropped.

Now I know the log file certainly does exist and it has not been dropped so I am at a loss why I am getting this error message. Do you have any further insight?

Thanks,

Jonathan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Pthprod_log is the physical name of the file (E:\Databases\MSSQL\Data\Pthprod\Pthprod_log.ldf). You need either the logical name or the fileid.

Run this in the DB to get the logical name

SELECT df.name FROM sys.database_files AS df WHERE df.type_desc = 'log'

As an aside, you're shrinking the file to 2MB. That's awfully small for a log file. Don't shrink to nothing, shrink to a sensible size for the database activity

Also, now that it's back in full, you must be taking regular log backups. Are they configured?

Please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan R Old Hand Points: 340 More actions · Answer 4

Hi Gail,

Thanks for that, it appears that the name is actually log.

I ran the following: DBCC SHRINKFILE(log, 4000)

and got the following:

DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages

627301024 3185 7301024 3184

I looked at the actual log size on disk and it is still being reflected as 58,000,000 kb

Does this mean that the shrink has not worked correctly?

Thanks,

Jonathan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

jonathan.rottanburg (10/28/2010)
I ran the following:
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
627301024318573010243184

??

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan R Old Hand Points: 340 More actions · Answer 6

Hi Gail,

Something went a bit funkey with the post and it sumitted itself before I had finished posting, here is what it should have said...

Hi Gail,

Thanks for that, it appears that the name is actually log.

I ran the following: DBCC SHRINKFILE(log, 4000)

and got the following:

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

6 2 7301024 3185 7301024 3184

I looked at the actual log size on disk and it is still being reflected as 58,000,000 kb

Does this mean that the shrink has not worked correctly?

Thanks,

Jonathan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Yup. Wait a bit and try again. It happens sometimes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan R Old Hand Points: 340 More actions · Answer 8

Hi Gail,

Yup. I waited and then it worked! Thanks for your help.

Jonathan