Error 9002: The Transaction Log for MSDB database is full.

Question

Error 9002: The Transaction Log for MSDB database is full.

Viewing 15 posts - 16 through 30 (of 42 total)

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

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

Start SQL (from the command line) with traceflag 3608 and with -m

p.s. Rebuilding the system DBs was a little extreme. Adding a second log file temporarily would have fixed this.

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

forsqlserver SSCoach Points: 18902 More actions · Answer 2

Gila Thanks,

It has been started but when I am going to login it is now allowing and giving error 18456 and same for sqlcmd ...how to add second log file...Please help

Thanks

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

It's a bit late to add a log file now, that's what would have fixed the log full without rebuilding the system databases.

Since master has been rebuilt, all logins are gone and SQL's back in windows mode. Log in with a local admin account (and please use SQLCMD)

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

forsqlserver SSCoach Points: 18902 More actions · Answer 4

for sqlcmd every user is giving 18456 on cmd

Can u show the way to enter into the sqlcmd

Thanks

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

GilaMonster (11/7/2011)
Log in with a local admin account

And by that I mean a member of the local administrators group. If SQL is in single user mode, any local admin is a sysadmin.

For details of how to run SQLCMD and the switches, see Books Online or run SQLCMD /?

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

forsqlserver SSCoach Points: 18902 More actions · Answer 6

This is 2008 R2 standard edition and if any account has not permitted from the logins of sql server to access sql serve then it cannot access and login in the SQLSevrer.

In 2005 it was not the case....

I hope so...Please correct if I am wrong...

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 7

The server is only starting using -t 3608 not with -m -c

Why??

Thanks

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

forsqlserver (11/7/2011)
The server is only starting using -t 3608 not with -m -c
Why??

Can you please attach error screen shot?

FYI:

m

Indicates to start an instance of SQL Server in single-user mode. Only a single user can connect when SQL Server is started in single-user mode. The CHECKPOINT mechanism, which guarantees that completed transactions are regularly written from the disk cache to the database device, is not started. (Typically, this option is used if you experience problems with system databases that require repair.) Enables the sp_configure allow updates option. By default, allow updates is disabled.

c

Indicates that an instance of SQL Server is started independently of the Windows Service Control Manager. This option is used when starting SQL Server from a command prompt, to shorten the amount of time it takes for SQL Server to start.

Note: When you use this option, you cannot stop SQL Server by using SQL Server Service Manager or the net stop command, and if you log off the computer, SQL Server is stopped.)

forsqlserver SSCoach Points: 18902 More actions · Answer 9

Please find the error:

The following error I am getting:

Windows could not start the sql server on local computer For more information review the sytem event log ,If this is a Non microsoft service ,Contact the service vendor and refer to service pack error code 1814.

Thanks

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 10

For more information review the sytem event log

And what does event log say about this error?

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

forsqlserver (11/7/2011)
This is 2008 R2 standard edition and if any account has not permitted from the logins of sql server to access sql serve then it cannot access and login in the SQLSevrer.

As I've said more than once in this thread, when SQL is started in single user mode (with the -m flag), any member of the local administrators group is a sysadmin.

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

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

forsqlserver (11/8/2011)
Please find the error:
Windows could not start the sql server on local computer For more information review the sytem event log ,If this is a Non microsoft service ,Contact the service vendor and refer to service pack error code 1814.

And?

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

forsqlserver SSCoach Points: 18902 More actions · Answer 13

Thanks Gail,

I am getting below error If I am starting sql server service as normal without any trace.

Windows could not start the sql server on local computer For more information review the sytem event log ,If this is a Non microsoft service ,Contact the service vendor and refer to service pack error code 1814.

Thanks

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

Seriously? Do you want this fixed or do you want to mess around for a few more days?

We need exact errors (and an error that just says 'for more info see the event log' is not a useful error message), and for you to follow a logical troubleshooting sequence, not try random stuff. Start SQL, from the command line, with -T3608 and -m, post the messages. Log in with SQLCMD, using a windows account that's a member of the local administrators group.

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

forsqlserver SSCoach Points: 18902 More actions · Answer 15

I have the .bak files of all databses.

I have remove the database engine service and reinstall the sql server database engine service due to which I have login in the server and then I have restore the master db and MSDB.

I have not checked the TFS Application service yet databases are up and working.

Thanks to all,

Thanks