December 18, 2018 at 6:07 pm
SQL 2008 instance was running on a VM. It ran out of space so IT decided to shutdown and expand the VM drive size. SQL wouldn't restart and the error we see is:
The transaction log for database 'master' is full due to 'CHECKPOINT'
Started SQL with T3605 ... trying alter database, dbcc shrinkfile, dump transaction ... everything in every forum post ... everything comes back with the same error above.
Any ideas?
December 19, 2018 at 2:05 am
kvacola - Tuesday, December 18, 2018 6:07 PMSQL 2008 instance was running on a VM. It ran out of space so IT decided to shutdown and expand the VM drive size. SQL wouldn't restart and the error we see is:The transaction log for database 'master' is full due to 'CHECKPOINT'
Started SQL with T3605 ... trying alter database, dbcc shrinkfile, dump transaction ... everything in every forum post ... everything comes back with the same error above.
Any ideas?
Is the SQL started with 3605? have you stared sql and checked the master LDF size and free space. What is the total free space after a VM drive addition. There is no need to reboot SQL to expand the drive in VM. Check with your VM admin and make a change onthe host.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 19, 2018 at 8:10 am
We tried starting it with 3605 and 3608 ... same behaviour. The master.mdf and mastlog.ldf aren't very big, like maybe 20MB. The IT tech said that the VM was reporting critical free space, so he expanded the drive. Didn't stop stop anything properly, just shutdown the whole system.
When we try to start SQL normally it starts, but then shuts down after 5-10 seconds.
I've never seen this error before, and all the online posts always mention another database and not the master.
December 19, 2018 at 9:24 am
Also I can only start using net start MSSQLSERVER /f /T3608 ... using the normal "sqlservr" command from the Binn directory just results in:
2018-12-19 08:43:55.35 spid4s Error: 9002, Severity: 17, State: 1.
2018-12-19 08:43:55.35 spid4s The transaction log for database 'master' is full due to 'CHECKPOINT'.
2018-12-19 08:43:55.35 spid4s SQL Server shutdown has been initiated
December 19, 2018 at 4:04 pm
Solved! We were able to grab the master.mdf and mastlog.ldf from an image backup a couple of months back. I replaced the files and started up SQL ... it didn't quite start fully, but enough that I could use SSMS. I found the table in another database had grown to nearly to over 260,000,000+ rows ... since it should be 0 normally, I was able to truncate it.
December 20, 2018 at 8:01 am
kvacola - Wednesday, December 19, 2018 4:04 PMSolved! We were able to grab the master.mdf and mastlog.ldf from an image backup a couple of months back. I replaced the files and started up SQL ... it didn't quite start fully, but enough that I could use SSMS. I found the table in another database had grown to nearly to over 260,000,000+ rows ... since it should be 0 normally, I was able to truncate it.
"A couple of months back"??? You got lucky. Do you backup the 3 "M" system databases at all?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2018 at 8:26 am
We don't normally backup the "M" databases, but we're definitely considering it now. The one that has the user data is "supposed" to be backed up daily, but never thought about doing master, model, or tempdb. The user data was always the most critical and we figured we could always just rebuild and restore from the backup. But, the system management was handed off to another group, and "oops" we didn't have a current backup.
December 20, 2018 at 8:45 am
kvacola - Thursday, December 20, 2018 8:26 AMWe don't normally backup the "M" databases, but we're definitely considering it now. The one that has the user data is "supposed" to be backed up daily, but never thought about doing master, model, or tempdb. The user data was always the most critical and we figured we could always just rebuild and restore from the backup. But, the system management was handed off to another group, and "oops" we didn't have a current backup.
No need to backup TempDB.... just the "3-Ms"... Master, MSDB, and Model. You can probably get away with not backing up Model but it's important when making a new database and, IIRC, helping with TempDB during a restart. I don't know about that last one for sure but intend to never find out the hardway. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2021 at 2:05 am
Had same issue on 2017 and could not start sql server with -f or any trace codes.
Replacing a backup of master.mdf and master did work.
note: we backed up master, but had to extract the mdf and ldf files from it on another machine.
https://www.se.com/se/en/faqs/FA231358/ was helpful
RESTORE FILELISTONLY
FROM DISK = 'C:\Program files (x86)\Schneider Electric\Power Monitoring Expert\config\cfg\DBBackups\Network \ION_Network.bak'
-- get the logical names from here and put into the first part of the Move clauses
GO
RESTORE DATABASE ION_Network
FROM DISK = 'C:\Program files (x86)\Schneider Electric\Power Monitoring Expert\config\cfg\DBBackups\Network \ION_Network.bak'
WITH MOVE 'ION_Network_Data' TO 'C:\ Program files (x86)\Schneider Electric\Power Monitoring Expert\Database\Network \ION_Network.mdf',
MOVE 'ION_Network_Log' TO 'C:\ Program files (x86)\Schneider Electric\Power Monitoring Expert\Database\Network \ION_Network.ldf'
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply