asked
Sql server system command location
hai, I recently altered the size of system database in order save the space. After restarting the services i couldnot start the MSSQLSERVICES. when i checked the errorlog it shows the error > SQL Server Assertion: File: <"logmgr.cpp">, line=7648 Failed Assertion = 'logFileSize <= BlkToByteOffset ((ULONG)-1)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted. 2011-06-24 12:48:38.51 spid10s Error: 3624, Severity: 20, State: 1. 2011-06-24 12:48:38.51 spid10s A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. 2011-06-24 12:48:38.67 spid10s Error: 823, Severity: 24, State: 6. 2011-06-24 12:48:38.67 spid10s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\templog.ldf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. 2011-06-24 12:48:38.78 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized. 2011-06-24 12:48:38.78 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. i understood the problem is with templog.ldf .I tried replacing the LDF file from another system. But once the i try start services the size is going to zero and the services are also not starting. All i need to know is, is there any location where all the issued commands will be stored(Like text or XML file) where i can change the run time value for templog.ldf to assert its size. is there any possibilty to rectify the problem without re-installation... Thanks .....
How did you alter the sizes of the system databases? Which system databases did you alter? TempDB is recreated every time the service starts, so copying a tempdb.ldf from another system should neither help nor hinder. Is there enough disk space on the D: drive to create the tempdb log file?
SQL stores a lot of data about itself in the master database - not external files. It will generate the TempDB based on the values it holds in master. Do you mean backup the master from another system, and try and restore that? Probably give you more issues than a simple reinstall.
If you had a backup of master, you could have restored it - it is in master where the config for tempdb is stored. One option is to reinstall and then reattach the user databases
Yes the same problem indeed... I am familiar that tempdb is created every time the instance starts... Just i have given a try... and I have altered the size of templog.ldf by issuing ALTER DATABASE TempDb MODIFY FILE(NAME='TempDev',SIZE=0) & ALTER DATABASE TempDb MODIFY FILE(NAME='Templog',SIZE=0). My Drive have 8GB of Disk Space...
did you try @Pavel's suggestion of attempting to start the server with th `-f` option ? Not sure if having tempDb set to 0 will prevent this or not?
Hai Kev Riley , Thanks a lot for your time... Now i will go for reinstalling... Is there any master database table where i can see the size allocated for the tempDb....Once i install i will check for them.. I could see limited tables in master...i wonder where the info will be stored...in other way i can view only some tables...
The problem relates to there not being enough drive space where the tempdb file is located. We moved one of the databases on that drive away to another drive, started the SQL service successfully and reattached the moved database. Alternative is to grow the drive where the database is located.
1 Person is following this question.
