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 ««12

DBCC shrinkfile error - Msg 8985 Expand / Collapse
Author
Message
Posted Monday, July 30, 2007 10:35 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:27 AM
Points: 136, Visits: 398

Chandu and Bapi, now that you fellas have the transaction log shrunk, you may want to start asking yourselves some more questions...

  • Why was the log file so big in the first place?
  • Do I have the correct recovery model? (Full, bulk-logged, simple)
  • Are transaction log backups being taken?... how often?...

Books online should be a great resource for you on disaster recovery and transaction log management if you don't have much experience in these areas.




Regards,
Rubes
Post #386367
Posted Monday, July 30, 2007 10:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 9, 2015 9:54 AM
Points: 2,006, Visits: 6,082
Bapi run

SELECT NAME, FILEID FROM SYSFILES

to get the fileID of the log file, then use that in the DBCC SHRINKFILE script




Shamless self promotion - read my blog http://sirsql.net
Post #386368
Posted Monday, October 22, 2012 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 22, 2012 1:11 PM
Points: 15, Visits: 34
Hi Chandu,

How to check/know the file ID. is it the same as SPID? i do have the same error..please help.

thanks

anthony
Post #1375482
Posted Monday, October 22, 2012 9:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 41,498, Visits: 38,092
Please note: 8 year old thread. Chandu has not logged in in 7 years.

Please don't post the same problem in multiple places.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1375502
Posted Friday, May 24, 2013 5:05 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:49 AM
Points: 62, Visits: 326
1)SELECT name, size

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');


2)Try Make database recovery mode to Simple.
3) Right Click Datbase =>Tasks =>Shrinks =>Files => Log => select shrink action => Realease unused space.
select shrink action => select in MB
4) SELECT name, size

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');
check size...
Post #1456388
Posted Friday, May 24, 2013 5:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:46 AM
Points: 41,498, Visits: 38,092
Please note: 9 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1456398
Posted Sunday, April 5, 2015 5:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:13 PM
Points: 1,925, Visits: 827
Nicholas Cain (4/14/2004)
Try shrinking it using the fileid (SELECT NAME, FILEID FROM SYSFILES), use the same syntax, just replace the filename with the fileid.

Just want 2 thank u cause that fixed my issue with 1 of the db`s although the error msg was in sys.database_files & I found some Indexes corruption, even after REPAIR_REBUILD, could not fix the shrink_log file issue till I googled it and found your solution, so thanx again.
Post #1674495
Posted Sunday, April 5, 2015 5:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:13 PM
Points: 1,925, Visits: 827
GilaMonster (5/24/2013)
Please note: 9 year old thread.

Now it`s even 11 years old thread, but amazingly still very useful to fix the current issues, seems that MS will never ran out of bugs
Post #1674496
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse