Hello,
I am trying to check the integrity of SQL database. The 'problem' with
SQL server is that it's fixing the DB automatically after restore so I
need a way to do it offline, much like using eseutil on Exchange
databases.
Thanks in advance.
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 Relatedhi
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
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.
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,
Is there any method to get an historical log where appears the database status (Online|offline) ?
Regards.
Hi,
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
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 RelatedOne annoying problem has occurred....I want to put database in offline mode....but it is giving me some error...
"ALTER DATABASE failed because a lock could not be placed on database [database]. Try again later. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 5061)" ...
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?
Hi all,
I have an application running on internet, it uses SQL server datatase.
I have an question want to as: can i backup database and update database online or offline?
Thanks.
TungNT
I'm using 2014 SE.I know a backup of a database doesn't take the source DB offline, but then I need to move this DB to another server (for intensive reporting work). At present we restore the DB, but that means putting the DB in single user mode, kicking everyone off, and completing the restore.
I see from 2014 EE notes that "online restore" is possible. EE is of course, mightily expensive.Or perhaps it's possible to configure things to speed up the restore process somehow, so there is less downtime?, the resource impact in creating the backup is quite high, perhaps there's a way to (apart from playing with backup compression) reduce the impact on the source server here?
I have a maintenance plan step to perform transaction log backups. There are a few databases that are offline. The backup seems to fail because it tries to backup the offline databases. The backup step is by itself as one of three subplans, although it also fails when it is the only step in a plan with only one subplan.
I have another server that is only on SP1 and it does not have problems handling the offline databases.
Is this an issue with SP2(9.0.3054)?
I am trying to place a database offline.
When I right in Microsoft SQL Server Management Studio on the database > Take Offline
Set offline failed for Database 'OperationsManagerAC'.
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1205)
So I am trying with some queries to pass the database from Single user to multi user but it fails
USE [master]
GO
ALTER DATABASE OperationsManagerAC SET MULTI_USER GO
Msg 1205, Level 13, State 68, Line 1</p><p>Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I tried also
alter DATABASE OperationsManagerAC SET OFFLINE WITH ROLLBACK IMMEDIATE
IT FAILS AS WELL... with the same error... how to run the SQL Statements without using
How to proceed next?
If you take a database offline to prevent users from accessing it, and then restart the server, will the database be back online when the server starts up?
If so, is there an option to prevent this from happening so that a database taken offline will remain offline until it is manually put online again by an administrator?
Dear All,
One of my users created a MSDE database and did not realise that the limit is 2GB. I can not access the database. Is there anyway I can recover it?
They do not have a backup.
Could I create a new database in Standard Edition and then attach the Data and log files from the MSDE database?
Would that work?
I €˜d like to discus with you the following REAL enough disaster scenario:
1. The TEST database is in the FULL backup mode.
2. WE have a full TEST DB backup and all tr. log backups.
3. DBA moved TEST database OFFLINE for maintenance operations.
4. MDF file for TEST DB was removed by mistake.
5. TRANSACTION LOG (LDF) file is OK.
6. DBA want to recover database to the point of failure.
According with MS SQL Server 2000 documentation it is possible.
We need to backup the transaction log , BUT I CANNOT DO THIS.
PLEASE, HELP.
In the same time, I can make LDF backup and recover database to the point of failure, if database is online and I stop/start SQL Server to remove MDF file.
Does anyone know how to do this? I am in the middle of writing a server side program, and this is one thing that I cannot do without.
Also, if anyone can answer that, how do I do the same with an Exchange 5.5 Email account?
Thanks ahead of time!
Cash Coleman
I have about 50 databases that are only accessed once a month and on a predictable schedule. Would it free up resources on the server if they were kept offline and brought online only when needed ?
View 8 Replies View RelatedHi All,
There is this SQL Server agent job that was calling a SSIS package (uses windows authentication) which was executing fine till the default database for the user was brought ofline, and now the job fails citing authentication failure for the user as the reason.
I have tried pointing master as the Default database for the user and now able to connect to SSMS using the users authentication, but the SQL Agent job wont succeed.
Any pointers ?
yes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedHi !
How do I check when my database was last used ? I have two old databases that I think is not in use, but I want to check to make sure before I delete them or take them "offline".
TIA MAttias
Hello,
I am performing a number of queries on a database and inputting the results of those queries into a target database.
These queries are performed inside a number of data flows.
I would like to do two things:
1) Check that the target database can be seen before entering the dataflows. If not, wait X minutes and then retry.
2) If, for some reason, SSIS loses connectivity to the target database during the process, I would like to, is possible, roll back any current transactions and start over.
Unfortunately my knowledge of SSIS is very limited, so if anyone could lend a hand I would appreciate it.
Best regards,
Simon
Hello, Everyone:
There are some databases in a SQL Server. How to check,
1. User database numbers
2. Backup plan
3. Configuration
4. User database size
5. Nightly process
Thanks a lot
ZYT
I was wondering if it was possible to check what server database you are currently connected to, using T-SQL, when executing commands in Query Analyzer. Even though you choose the server and database when connection, sometimes by habit you may connect to the wrong server & DB, and execute an .SQL file. Is it possible to put a line of T-SQL at the beginning that performs this pseudo-code, as a safety feature:-- While in Query Analyzer with a certain .SQL file open:If current server <> 'TheCorrectServer' and current DB <> 'TheCorrectDB' then cancel this .SQL file execution.
View 3 Replies View RelatedHi everyone,
I am looking for a way to check to see if a database is a snapshot or not. You can for example check different properties of a database by running the following:
SELECT DATABASEPROPERTYEX('databasename','Recovery')
SELECT DATABASEPROPERTYEX('databasename','IsInStandBy')
SELECT DATABASEPROPERTYEX('databasename','Status')
So I am trying to see if there is a way to check for a snapshot
Thanks,
Reghardt
Hi,
I'm wondering is there a command that I can use to check if a certain value exists in the database? Something that returns true or false perhaps?
SELECT * FROM Customers WHERE ID ='1'
Ok so that's simple, but how can I return a true or false if '1' is a value that exists in the db?
thanks.
I want to query my databases to find out if Auto Shrink is set. I know the status column in sysdatabases holds this value as a bit setting but I don't know how can I check this value using a query.
Canada DBA
im trying to set up maintenance plan for the check database integrity...
In sql 2000 you get a nice little log in SQL Logs
DBCC CHECKDB (WSS_Search_db3) executed by xxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
But in SQL 2005
Im not getting a nice log of this but getting it against some system database and not the database i selected
Date1/22/2008 5:19:43 PM
LogSQL Server (Current - 1/22/2008 5:19:00 PM)
Sourcespid84
Message
DBCC CHECKDB (mssqlsystemresource) WITH no_infomsgs executed by XXX found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 1 seconds.
Anyone got this to work?
Trying to get the same message i got for SQL 2000 or at least so i can confirm it ran.