Restored a DB from SQL 2000, now SQL can't shrink/find log

Question

Post reply

Restored a DB from SQL 2000, now SQL can't shrink/find log

CraigIW

SSCrazy

Points: 2301
More actions
April 20, 2012 at 8:19 am

#392073

Hi,
I recently restored a database from an old SQL 2000 backup onto a SQL 2008 Standard Edition server. I set the recovery model to Simple and walked away.
I now find that there is 30Gb of data and a 60Gb of log file in that database. I would like to shrink or remove the log file to recover disk space, but SQL won't let me.
If I try and shrink the log, I get the error:
Could not locate file 'Mydb_log' for database 'Mydb' in sys.database_files. The file either does not exist or was dropped. (Microsoft SQL Server, Error: 8985).
The file is definitely there, and is 60Gb, so I really want to resize and/or get rid of it.
How can I do this?
Thanks,
Craig

Viewing 15 posts - 1 through 15 (of 16 total)

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

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

Query sys.database_files and post the name, filename, id and type of all the files in there.

Do Not delete the log file. It can leave the database unusable.

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

CraigIW SSCrazy Points: 2301 More actions · Answer 2

Results:

namephysical_namefile_guidfile_idtype

NewArchiveDataF:\Data\ArchiveData.mdfE4906318-99EC-4E9A-9BE2-EFEE790AACD710

NewArchiveData_logF:\Data\ArchiveData_log.ldfF0E37EDF-F3BB-468E-B00C-7DA012C5372C21

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

DBCC ShrinkFile ('NewArchiveData_log', 1000)

That'll try to shrink the log to 1GB.

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

CraigIW SSCrazy Points: 2301 More actions · Answer 4

That basically gives the same error as I get in the UI :/

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

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

Are you running it in the correct database?

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

CraigIW SSCrazy Points: 2301 More actions · Answer 6

Ha, yes. I just treble-checked and added a USE to be sure. I wish it was that simple 🙂

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

DBCC ShrinkFile (2, 1000)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Just to be sure, in Windows Explorer, does the file exist where sys.database_files says it does (F:\Data\ArchiveData_log.ldf)?

CraigIW SSCrazy Points: 2301 More actions · Answer 9

CraigIW

SSCrazy

Points: 2301

April 20, 2012 at 9:21 am

#1476739

Yes, the file is there at that path.

CraigIW SSCrazy Points: 2301 More actions · Answer 10

GilaMonster (4/20/2012)
DBCC ShrinkFile (2, 1000)

It's now actually doing something using this command! 🙂

I'm not sure how long this will take though.

CraigIW SSCrazy Points: 2301 More actions · Answer 11

CraigIW (4/20/2012)
GilaMonster (4/20/2012)
DBCC ShrinkFile (2, 1000)
It's now actually doing something using this command! 🙂
I'm not sure how long this will take though.

It took about three minutes 🙂

Thanks Gail, this worked. As the db is still on Simple recovery, I'm hoping I should be able to leave it now in this somewhat inconsistent state.

smardi5 SSC Enthusiast Points: 118 More actions · Answer 12

can you try changing it to full recovery, take transaction log backup and try shrinking the database, though this is not relates to your error

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

smardi5 (4/23/2012)
can you try changing it to full recovery, take transaction log backup

Why would you want to do that?

In simple recovery model the log space is marked reusable any time a checkpoint runs. In full recovery it requires a transaction log backup. So by switching to full recovery you increase the requirements to make the log reusable, not decrease them.

p.s. the transaction log backup will fail because there's been no full backup taken since the DB was switched to

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

smardi5 SSC Enthusiast Points: 118 More actions · Answer 14

I wanted him to check if he can take txn log backup, take it delete it, shirnk the db and revert back to simple model. as you rightly pointed out he will not be able to backup txn log since there is no full backup happened after restore.