Appreciate your help 
Author Message
 Appreciate your help

Hi,
I am using MS SQL 6.5 and still fighting with these problems.
I appreciate some hints from experts in this forum.

1. I still got error message # 1105: syslog full in tempdb. I have
created a special device for system log, so the system log is no longer
shared with user data. For a couple of weeks it worked fine, I thought
that I have solved this problem. But yesterday this problem happened
again, so that the application can not use the database anymore.

2. Even worse, I can not access the database at all, always says that
tempdb is for single user and currently is being used !!!  Please, how
can I get away from this situation?

3. I plan to shrink the size of one database. because I saw that out of
112 MB of space given, 110 MB is available. System log is using
sseparate device. But surprisingly enough, I can only shrink the
database to 110 MB.

Regards,
Frank



Thu, 18 Oct 2001 03:00:00 GMT
 Appreciate your help

Quote:

>Hi,
>I am using MS SQL 6.5 and still fighting with these problems.
>I appreciate some hints from experts in this forum.

>1. I still got error message # 1105: syslog full in tempdb. I have
>created a special device for system log, so the system log is no longer
>shared with user data. For a couple of weeks it worked fine, I thought
>that I have solved this problem. But yesterday this problem happened
>again, so that the application can not use the database anymore.

>2. Even worse, I can not access the database at all, always says that
>tempdb is for single user and currently is being used !!!  Please, how
>can I get away from this situation?

>3. I plan to shrink the size of one database. because I saw that out of
>112 MB of space given, 110 MB is available. System log is using
>sseparate device. But surprisingly enough, I can only shrink the
>database to 110 MB.

>Regards,
>Frank

1. Normally, separating data and log is exactly what you want to do
but in the case of tempdb, no.  Tempdb is exactly that: temporary
storage.  You make the most efficient use of the available space by
keeping data and log together.  Furthermore, tempdb is recreated each
time you start SQL Server so that's another reason for not making
separate allocations.

There are two ways to correct this.  One, configure tempdb in RAM for
2MB, stop and restart SQL Server, take tempdb OUT of RAM and again
stop and start SQL Server.  This time, tempdb will be recreated on the
first default device (in alphabetical order) with at least 2MB of
available space.  Expand tempd data portion only to the desired size.
Second is quick but when you modify system tables directly you can
seriously impair or destroy the operation of your server.  With that
caveat in place, change the segmap column in master.dbo.sysusages to 7
for all rows with a dbid of 2 and the stop and restart SQL Server.
WARNING:  If you change the wrong entries you can make your user
databases unuseable!!!

2.  Close all connections to SQL Server.  Open an ISQL/w or ISQL
window and execute:  sp_dboption tempdb, single, false.  If this
fails, you can again modify systems tables but be very careful!
Change the status column of master.dbo.sysdatabases to 12 where
dbid=2.  A status of 12 permits select into/bulk copy and truncate log
on checkpoint.  Tempdb has it's log truncated at checkpoint as a
matter of course but it also does not hurt to set it, either.

3.  Shrinking a database may not work as you might expect.  The
database allocation will be scanned from the END of the space
backward.  When the first object is located, that is the limit you can
shrink the database.  An illustration may help.  You have a 112 MB db
with 100MB of free space.  Page 51200 is the highest page allocated
(100MB).  Assuming that the data and log are together, you would be
able to shrink the database to about 100MB.  If you have a 100MB
database with 75MB data and 25MB log and it's essentially empty, you
should be able to shrink the database to about 76MB.  All of the log
except for 1MB can be shrunk but a database requires at least some log
space so you can't shrink it below the log allocation.

If you want to go below this limitation, create a new database of the
size you want and use transfer manger to move all objects into the new
database from the old one.  Then rename both databases.



Fri, 19 Oct 2001 03:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Anders, Cindy and Remus I appreciate your help - Where I am at this point

2. Appreciate any help!!

3. Newbie who would appreciate some help.

4. Need info on databases by 2pm 7/5/94, appreciate any help

5. Anders, Cindy and Remus I appreciate your help - Where I am at this point

6. MAPI Error....Help,help,help. deeply appreciated for help in any form.

7. !!! Help !!! Rookie Question, Appreciate Any Help...

8. Ado Help Please Help any help is appreciated

9. query help appreciated. max()/joins/subquery

10. IMMEDIATE help appreciated

11. ****ANY HELP APPRECIATED**********


 
Powered by phpBB® Forum Software