Hi!
I am moving some of my db to another disk drive.
Using this t-sql code to stop them before moving:
alter database "databasename"
set offline
go
Using this code to stop the db`s works well on some db`s, but som db`s wont stop at all.
I have to stop the excecution of the query after a few hours.
My system is SQL server 2005, sp2.
Hi, I am relative newbie to SQLServer. When I try to take a user database offline, the query "hangs," with the query processing circle spinning. The Sharepoint 7 application is running on top of the Enterprise SQL Server 2005 db with several logins sleeping and awaiting commands. No errors are generated until I kill the offline command. Anyone have any ideas? Do I need to kill all the connections?
View 2 Replies View RelatedSay you have a database that had a process run for 2 days in a transaction until if filled the disk. The error causes a rollback (presumable another 2 days). I want to restore from a backup instead of waiting for the rollback to complete. Please to be telling me how to?
Jay
to here knows when
I have to design, load and write scripts for a remotely hosted SQL 7.0 Server.
My desktop computers are both Windows 98. What do I need to develop and publish databases from my desktop to the remote host? I have always had the luxury of having direct access to the SQL Server and cannot find any conclusive guidance at Microsoft's site.
Thanks so much for your help.
hi
i executed sp_dboption,'my_db','offline','true'
and my_db went offline ok but when i cheked in enterprise manager list of db's i found my_db marked as (suspect/offline), i refresh the window and a got the same result but wen i close the enterprise manager and open it again my_db was not in the list of db's.Now i want to bring back my_db to online by executing sp_dboption 'My_db','offline','false' i was not able to do this and the command exist with this error the database is not in the list use sp_helpdb.
Please can anyone help me on this
Best Regards
Mamzy
On occasion, I notice that some of my backup devices have become "offline" and the scheduled tasks associated with them will no longer run.
I get this error:
Can`t open dump device `d:MSSQLBACKUPdvbk_AFISlog.DAT`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)
Does anyone know how to get them back online without having to create a new device? Thanks in advance...
I have a customer who's database was marked Suspect due to a drive failure that left a torn page. Before trying to reset the Suspect flag they took the database offline, and now we cannot get the database back online. Any ideas?
View 1 Replies View RelatedHi all,
We have a serious problem.
The database in SQL Server 6.5 is marked offline
and the error log displayed the following messages :
udopen : file 'F:Filename.DAT' is incorrect size (614400 bytes, should be 537600)
udactivate (primary) : failed to open device 'F:Filename.DAT' for vdn 131
The entries for the device exists in sysusages and sysdevices.
Anyone encountered this before?
Please help if you know how we can get the database online, again.
Many Thanks.
OK, so I took one of my db's offline for maintenance, then realized that I wanted to be logged in as another user, so I logged out.
Now SS won't let me log in, because every time I try to connect I get:
A connection could not be established...
Reason: Cannot open user default database. Login failed..
The db I took offline is the default db for every single privileged account on the server. Why do I feel like I just locked my keys in the car?
Any help would be greatly appreciated! TIA!
Over the weekend I had one of my databases taken offline. To my knowledge no one explicitly took this database offline. This isn't to say that someone did and is refusing to speak up. I'm just trying to find some answers.
I do know that our IT dept. did maintenance to a number of our servers and they swear nothing was done to this one. So my question is, "Is there any SQL process that can take a database offline."
I've checked the log files but there isn't anything that directly points to that database being taken offline.
Running Windows 2000 Advanced Server SP4, SQL 2000 SP3 on a NT INTEL X86 platform.
Any ideas or comments are appreciated!
What exactly is database offline? I tried to google it but didn't get what I was looking for.
One of my environments has few users. They attach databases and run an application to produce the results. Sometimes they forget to detach databases after their use and these databases remain in SQL Server. Sometimes they leave the database to use it in future.
At the moment there are about 900 databases attached. I believe these attached databases are using the servers resources and therefore, decrease performance. Is this correct?
I was thinking to force the users to detach databases that they do not need and attach when they need. Then thought why shouldn't just set the database to offline and take it back online when they need. Does this increase performance? Does this save server's resources?
Thanks,
Canada DBA
hi everyoneCould someone please help me in following:One of my database in SQL 2000 going Offline automatically. When ibring it back Online its Ok for 20/30 minutes and then again appear asOffline. I had similar problem when one of the database keep going to'Single user' automatically.Any idea what happening.Thank you*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI have some reports running on a server based on cubes, available through the network.
Some users need to use these reports while they are offline and not connected to the network or internet (at a customers site for example).
Is there a way to achieve this?
Thanks,
Frans
I plan on using SqlServer 2005 Express as a local datastore for my application when working offline. The idea is that the user would initiate a Synchronize action from the client application, this action would start replication going between a Networked sql 2000/2005 server and their local Express server. (At the beginning I plan on replicating the entire database).
View 6 Replies View RelatedI have installed SQL Server on my laptop (full version), but can only access it while on the network. If I disconnect from the network, open Enterprise Manager, and click on my connection, I get the error "sql server does not exist or access denied". Why can't EM find my own machine? I even logged onto my own computer as a local user, instead of the network domain, and still recieved the error. Can someone tell me where I can edit (maybe my registration properties), so that I can run SQL server, while offline.
Thanks!
Trevor
Hi
How to offline a spcific DataBase via Query analyzer
its very important please help me
thanks
Hi,
Is there any method to get an historical log where appears the database status (Online|offline) ?
Regards.
Hello there,I have a user who can bring offline a database but he cannot bring thedatabase back online.The error which pops up is:Error5011: User does not have permission to alter database dbnameALTER DATABASE statement failed.sp_dboption command failed.Any ideas?Thanks
View 1 Replies View RelatedHi,i try get information about the online or offline status of a databasewithin ms-sql srv 2000.I can look it up within server manager, but I'm looking for a way to getthis information from the command line or a select statement.Is there a command or a sql statement that tells me the status of adatabase ? There's some information in master.dbo.sysdatabases, but itlooks very cryptic.Any help appreciated, thanks,stefan
View 1 Replies View RelatedHello,I am trying to check the integrity of SQL database. The 'problem' withSQL server is that it's fixing the DB automatically after restore so Ineed a way to do it offline, much like using eseutil on Exchangedatabases.Thanks in advance.
View 1 Replies View RelatedHi,
I try to restore a database but it pop error said "the database is in use". So, I try to take the database offline so that I can store the database. But it takes me forever (1 hour till now). It is still showing "1 remaining". Do you have any idea why ? Thank you very much in advance!
I currently have a very long script that I need to run on several servers through an SSIS package. The issue stems from servers that have one or two databases offline. This causes the script to fail. Is there an easy way to get the script to either run on offline databases or skip those that are offline? Thanks
-Kyle
Hi,
I am currently working for a client that is interested in performing some data mining on their customer data. The plan was to build a data warehouse, cubes, RS reports etc. and serve this up over the web.
However, we now have the problem of having to cater for countries with a very low bandwidth where the Reporting Services approach would not deliver the required performance due to the dependency of the solution on the network bandwith.
I am therefore looking into processing offline cubes and copying them over the network as part of the overnight process to a server in the countries.
I noticed that PivotTable Services in 2000 had the CREATE MINING MODEL ...... DDL statement for creating offline mining models.
Is this capability still there with SSAS 2005?
Can anyone post any links to usefull resources on this subject?
Thanks in advance.
Hi
What is the best way to perform daily offline/remote/off-premise backups of our websistes Sql Server 2005 database?
I've looked into using SSIS but for one reason or another am having serious problems with this. Ideally we'd like to be able connect over the internet and/or lan and pull the entire database down in one go. Our db is ~600mb so isn't major.
We used to use Sql Server 2000's DTS and this worked well, but I'm having a real hard time using SSIS to do the same so was wondering what the other options are
Many thanks
Ben
I'm going overseas this summer with my laptop and I have to continue working on a project. Unfortunately that laptop only has Windows Vista Home Premium. I can't afford to get the upgrade but am in the position of having to copy a database from the server to my laptop so I can continue working on it. What I need to do is find a way to copy the database over but in the case where the two computers aren't connected over a network. Is this possible? I tried to simply copy the .mdb, but there were permissions errors (of course). Is there a way to do this? It's pretty important that I get it on the laptop one way or another. Thank you!
View 3 Replies View RelatedI attached a query that runs fine when all of the databases on an instance are online, but when even a single database is offline, it errors out. I need some way to add at the beginning something like "if db = offline, skip". Is there anyway to make this work? Shouldn't there be a list of the DB's on the instance someplace and there state? If so, couldn't I just say run the script against the db's that are online? Finally, this needs to work for both SQL Server 2000 and 2005 instances. Any help is greatly appreciated.
----------------------------Begin Script-----------------------------
declare @dbname varchar(200)
declare @mSql1 varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
Go
----------------------------End Script-----------------------------
I have a non-recoverable filegroup in a partition that I would like to remove from the database. Every command that I know of requires the filegroup to be on-line before I can remove it.
Is it possible to remove an off-line filegroup from a database?
SQL Server 2005 Enterprise Edition.
Hello ,
I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.
When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.
So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.
The script is:
Set NoCount on
DECLARE @dbname VARCHAR(100)
deCLARE @Status varchar(100)
Declare @Message VARCHAR(8000)
DECLARE @date varchar(100)
set @date = convert(varchar(100), getdate(),109)
set @Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
WHILE
@@FETCH_STATUS = 0
BEGIN select @message = @message + @@Servername + '-' + @dbname + ' - ' + @Status + Char(13)+ ‘- ‘ + @date
FETCH NEXT FROM dbname_cursor INTO @dbname, @Status
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor
print @message
EXEC master.dbo.xp_smtp_sendmail
@FROM = N'testsql2000@is.depaul.edu',
@TO = N'dvaddi@depaul.edu',
@server = N'smtp.depaul.edu',
@subject = N'Status of the Database on Testsqlserver!',
@type = N'text/html',
@message = @message
Thanks
1.can we restore the db if it is offline?
2.restore information stores in table 'msdb..restorehistory' if we restore the db when the db is offline?
If I add a nonclustered index to an existing table, does it take the table offline while creating the index?
View 2 Replies View RelatedSome of my receiving service side applications usually work offline, andthey only are supposed to get connected to the network once/few times a day,to send/receive Service Broker messages.Will my sending service side application (permanent connected to thenetwork) keep retrying repeatedly to connect to the receiver?- Is retrying like that time consuming?.- I don't like it keeping trying over and over. Is retrying behaviorconfigurable?thx
View 2 Replies View RelatedFollowing a disk crash whilst building an index, the metadata for a 300 million row table has become corrupt.
The index was being built on a specific file/filegroup which I was able to mark offline and the Db continued to be usable.Â
Unfortunately one of our processes now fails (trying to use the index that was never complete). I can't diable the index, drop the table, truncate the table or delete the data. Partial restores of the Db excluding the offending filegroup are successful, but the problem continues to be there!!!
If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?
Example:
Both salespeople sync the following data down:
OrderId Desc
1 Order 1
2 Test Order
Both salespeople go offline and add orders
Salesperson 1 adds:
OrderId Desc
3 Joes Order
Salesperson 2 adds:
OrderId Desc
3 Kathys Order
Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.