Sql server system command location


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 .....

more ▼

asked Jun 24, 2011 at 01:45 AM in Default

avatar image

85 7 8 13

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?

more ▼

answered Jun 24, 2011 at 01:51 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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...

Jun 24, 2011 at 02:17 AM GMC

Do you have a backup copy of master?

Jun 24, 2011 at 02:19 AM Kev Riley ♦♦

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?

Jun 24, 2011 at 02:29 AM Kev Riley ♦♦

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

Jun 24, 2011 at 02:52 AM Kev Riley ♦♦

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.

Jun 24, 2011 at 03:16 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 26, 2012 at 07:39 AM

avatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 24, 2011 at 01:45 AM

Seen: 2211 times

Last Updated: Apr 26, 2012 at 07:39 AM

Copyright 2018 Redgate Software. Privacy Policy