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 Wednesday, April 14, 2004 8:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 5, 2005 9:54 AM
Points: 27, Visits: 1

Hi

I am getting following error when I try to shrink the transaction log:

DBCC SHRINKFILE (wslogdb50_Log, 2000)

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'wslogdb50_Log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any ideas?

Regards

Chandu

 




Post #111163
Posted Wednesday, April 14, 2004 8:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:52 AM
Points: 2,006, Visits: 6,078

Check that you are using the database that you wish to shrink the file of.

What's the output from SELECT NAME FROM SYSFILES ?





Shamless self promotion - read my blog http://sirsql.net
Post #111172
Posted Wednesday, April 14, 2004 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 5, 2005 9:54 AM
Points: 27, Visits: 1

Hi

Thanks for the prompt response.

I get following output for SELECT NAME FROM SYSFILES:

wslogdb50

wslogdb50_Log.LDF

I am getting "Incorrect Syntax error" when  i include .ldf in the DBCC shrinkfile command.

Regards

Chandu




Post #111178
Posted Wednesday, April 14, 2004 9:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:52 AM
Points: 2,006, Visits: 6,078
Try shrinking it using the fileid (SELECT NAME, FILEID FROM SYSFILES), use the same syntax, just replace the filename with the fileid.



Shamless self promotion - read my blog http://sirsql.net
Post #111179
Posted Wednesday, April 14, 2004 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 5, 2005 9:54 AM
Points: 27, Visits: 1

Hi

The statement seems to have completed without any error. But, I am getting following message:

Cannot shrink log file 2 (wslogdb50_Log.ldf) because all logical log files are in use.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Current Size 1257512 Used pages 1257512

The log file increased to 10GB. I need to reduce it ASAP as we are running out of space. Any other alternative? Starting and stopping sql server?

Regards

Chandu




Post #111183
Posted Wednesday, April 14, 2004 9:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:52 AM
Points: 2,006, Visits: 6,078

Run the following, replace DBNAME with your database name and FILEID with the file id.

BACKUP LOG DBNAME WITH NO_LOG
GO
USE DBNAME
GO
DBCC SHRINKFILE (FILEID, 128, TRUNCATEONLY)
GO





Shamless self promotion - read my blog http://sirsql.net
Post #111196
Posted Wednesday, April 14, 2004 9:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 5, 2005 9:54 AM
Points: 27, Visits: 1

Thanks once again for your help.

What is 128?

I have already run following statement:

backup log wslogdb50 with truncate_only

Regards

Chandu




Post #111198
Posted Wednesday, April 14, 2004 10:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 9:52 AM
Points: 2,006, Visits: 6,078
128 is the number of pages to shrink the file to. I usually use that if I want to shrink the file but still leave some space in it.



Shamless self promotion - read my blog http://sirsql.net
Post #111204
Posted Wednesday, April 14, 2004 10:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 5, 2005 9:54 AM
Points: 27, Visits: 1

Excellent !! It worked.

Thanks very much for your help.

Regards

Chandu

 




Post #111210
Posted Monday, July 30, 2007 10:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2007 10:05 AM
Points: 1, Visits: 1

HI Nicholas,

I am facing the same problem.

I am unable to understand the what your talking about.

Try shrinking it using the fileid (SELECT NAME, FILEID FROM SYSFILES), use the same syntax, just replace the filename with the fileid.

Please let me know how can i reduce the file size of the database log.

Thanks

Bapi.

Post #386360
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse