June 13, 2002 at 5:31 pm
Hello everyone, I have this question, please help me ASAP as the big boss is watching:
I marked a database read-only through Enterprise Manager. A few minutes later I wanted to make it read-only again and I get the following error:
ERROR 5070: Database state cannot be changed while other users are using the database 'dbname'. ALTER DATABASE failed. sp_dboption command failed.
How can I check to see who is connected to the database. How can I get this database out the read-only stage?
Please help quickly :o(
June 13, 2002 at 5:32 pm
BTW, SQL Profiler does not show anyone who is connected to that database...
June 13, 2002 at 5:43 pm
Run sp_who2 from Query Analyzer and that should show you all processes, to include system processes.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 13, 2002 at 6:40 pm
Thanks a lot!! I checked who was on at the time and killed his session and was able to revert the database from read-only.
I have one more urgent question: We are trying to create a database with data on one disk and index on another. I am going through Enterprise Manager and right clicked on "databases" and choose "New Database...". How do I add a data file for user data and an index file for indexes? I see tabs for Data Files and Transaction Log and do not see an index tab.
Another question (as if I have not asked enough, but I am new to SQL Server and the big guy has thrown a big task at me) is, does BULK INSERT generate any logs? And if yes, is there a way to turn off the log?
Sorry, they want this done ASAP, a consultant i$ waiting and all eyes are on me and I don't have the time to research these questions....
All this to say thanks for your help.
June 13, 2002 at 7:46 pm
What you need to do is create another database file, except where it has "PRIMARY" change it to another name. This creates a different filegroup. Then, when you create the index, you can specify the filegroup. This will ensure data and indexes are separate.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
June 13, 2002 at 8:00 pm
quote:
Another question (as if I have not asked enough, but I am new to SQL Server and the big guy has thrown a big task at me) is, does BULK INSERT generate any logs? And if yes, is there a way to turn off the log?
In regards to this the answer is depends on your version of SQL.
From SQL7 BOL
quote:
Look at SELECT INTO/BULKCOPYImportant Before a user can do nonlogged bulk copies, the system administrator or database owner must first use SQL Server Enterprise Manager (or the sp_dboption system stored procedure) to set the select into/bulkcopy option for that database to true. If the option is not set and a user tries to bulk copy data into a table that does not have indexes, SQL Server generates a warning message and logs the bulk copies. By default, select into/bulkcopy is false in newly created databases. To change the default for all new databases, set this option in the model database.
From SQL2000 BOL
quote:
Look at Recovery ModelsSimple Permits high-performance bulk copy operations.
Reclaims log space to keep space requirements small.
Changes since the most recent database or differential backup must be redone. Can recover to the end of any backup. Then changes must be redone.
Full No work is lost due to a lost or damaged data file.
Can recover to an arbitrary point in time (for example, prior to application or user error).
Normally none.
If the log is damaged, changes since the most recent log backup must be redone.
Can recover to any point in time.
Bulk-Logged Permits high-performance bulk copy operations.
Minimal log space is used by bulk operations.
If the log is damaged, or bulk operations occurred since the most recent log backup, changes since that last backup must be redone.
Otherwise, no work is lost.
Can recover to the end of any backup. Then changes must be redone.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 13, 2002 at 8:17 pm
Thanks guys, with your help I was able to get the work done. Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply