Transact SQL :: Cleanup Suspect Databases

Sep 26, 2015

After cloning our production SQL 2012 server to make DEV environment, there is a bunch of databases in "Suspect" mode. This is because the DB's and Logs have got out of sync.I can clean up each DB with this script:

ALTER DATABASE DB_NAME SET EMERGENCY
GO
DBCC CHECKDB (DB_NAME)
GO
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE DB_NAME SET MULTI_USER
GO

but this takes a long time replacing DB_NAME and executing for each DB.I tried putting it all into a script with variables, but I have done something wrong and it does not work. Also is there a way to just apply it to the suspect DB's?

USE master
Go
DECLARE @dbname sysname, @cmd varchar(1000), @cmd1 varchar(1000), @cmd2 varchar(1000), @cmd3 varchar(1000), @cmd4 varchar(1000)
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
OPEN db_recovery_cursor

[code]....

View 7 Replies


ADVERTISEMENT

Suspect Databases

Sep 30, 1999

Hello,

Today we found a suspect database and after consulting this website went and ran sp_resetstatus on one database with success. After which we stopped and started the server and ran dbcc newalloc, dbcc textall, dbcc checkdb on that database. It successfully changed from its suspect status and random queries showed that the data was still intact.

Exactly the same process was run on another database and it failed to move from the suspect status after restarting the server. We then went through books online, which advised to update sysdatabases setting the database's status to 0. This also failed.

HELP PLEASE.

Does anyone know what else to try? This was a development database with no backups. This would save a weeks work. Thanks for you time.

Cheers
Damian

View 1 Replies View Related

Log Shipping Suspect Databases

Nov 26, 2007



I have 2 sql2000 servers that i have log shipping setup on

On weekends the optimization plan runs creating some large log files that take several hours to restore

In the process most of the databases at the secondary server are marked suspect

Usually I restore the last backup manually and log shipping catches up, but this takes most of the week to do

Why are the databases mark as suspect.

And is there an easier way of fixing the suspect databases, I not sure if i should just run sp_resetstatus



View 8 Replies View Related

Failed To Obtain TransactionDispenserInterface And Invalid LSN, Databases Suspect

Jan 20, 2004

All of a sudden I have 4 of my databases, including pubs that are all marked suspect and seem to be un-recoverable. I have followed the Resetting the suspect status directions as well as trying to attach only the .mdf files but still run into problems reading the .ldf file. I have not tried to do the procedure of creating another database with the same name and structure then swapping the files to trick SQl but will do so if I need to. Does anyone know why this happens all of a sudden to multile databases, of which Pubs I have never used for anything??

In my log files during startup I get this error

Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b

Then this error for every database that is suspect, (the actual LSN numbers are different for each one).

The LSN (4:517:1) passed to log scan in database 'pubs' is invalid..
Is there anything else I can do to prevent this from happening or recover the DB's

Thanks

View 2 Replies View Related

Transactional Replication And Deadlock + Suspect Agent Errors For Over 80 Databases

Oct 25, 2005

Hi,
I have more than 80 databases on my publisher (SQL Server 2000 SP4), I tried to enable Transaction Replication on all of those databases instantly through some T-SQL programming and DTS Packages. Every thing works fine until the snapshot agents starts to take sanpshot from the publisher databases. As soon as their snapshot agents start for those 80 databases, they start giving the deadlock error. All 80 snapshot agents starts at the same time.

Error Message:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Error Detail:
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
(Source: Server_Distribution (Data source); Error number: 1205)
---------------------------------------------------------------------------------------------------------------

WHY???

View 2 Replies View Related

Transact SQL :: After Repair Suspect Some Row Deleted

Jul 1, 2015

today my database is (suspect)and not able to connect to the database.so i have excite this code

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER

and then it worked but some of new rows are  deleted can i restore this rows ??

View 7 Replies View Related

How To Set Database Status To Suspect.don't Resolve The Suspect Problem,I Want To Suspect Database

Apr 29, 2008



Hi
I want to suspect database
stop server first
I try to rename C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLDatamsdbdata.mdf to msdbdata.sav
and then start the server
use command to check:
SELECT status & 256 FROM master.dbo.sysdatabases WHERE name = database_name
if the result is 256,it means the msdb is suspect,but the result is 0,it same as the normal status
do you know how to set database suspect with this way, or do you know other way to suspect databse.
absolutely,I could re-back my server noraml with your way
Thanks

View 6 Replies View Related

Transact SQL :: Trigger Database In Suspect Mode And Get It Out To Normal Mode?

Jul 27, 2015

how to put sql server database in suspect mode intensely and  get it out from suspect mode to normal mode.

   i am using SQL server 2008 R2

View 5 Replies View Related

Transact SQL :: Log What Users Accessed Which Databases

Nov 9, 2015

I'd like to find out who is accessing various databases and log the time they did so.

Is there a way to do this? I just need the account name and the time logged and then to write to a table so I can query it.

View 2 Replies View Related

Transact SQL :: Getting Error When Trying To Union All Between Two Databases?

Nov 13, 2015

I am having an issue when trying to union all between two databases.

This is the error:

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

I have updated the collation in both DB to Latin1_General_CI_AS and I am still getting the error when running the query.

The queries run separately ok.  why I am still getting the collation error.

View 11 Replies View Related

Show Suspect But Not Suspect

Mar 23, 2007

One of my database (name XYZ) shows suspect status in EM but when i try to dig further by running below query i get only "OK" ( see query)

SELECT [name],status, case status when (status & 32) then 'Loading'
when (status & 32) then 'Loading'
when (status & 64) then 'Pre Recovery'
when (status & 128) then 'Recovering'
when (status & 256) then 'Not recoverd'
when (status & 512) then ' Offline'
when (status & 1024) then ' Single user'
when (status & 1024) then ' Read only'
when (status & 32768) then ' Emergency Mode' else 'OK' end as status
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('distribution', 'tempdb', 'model','Pubs','Northwind')

also i run this

select * from sysdatabases where databaseproperty(name,N'IsSuspect') <> 1

and here also i get all the database including "XYZ"...i guess if "XYZ" is suspect the resultset should not be including "XYZ"

Why if the EM shows suspect status FOR "XYZ" DATABASE it should come up when i check status column in sysdatabases table?








View 2 Replies View Related

Transact SQL :: List All File-groups For All Databases?

May 27, 2009

I would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?

View 21 Replies View Related

Transact SQL :: Combining Two Queries From Different Linked Databases

May 20, 2015

I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.

(SELECT DISTINCT
idname, name, address, address2, awardedtoname, suppno
FROM contract INNER JOIN
house ON contract.idname = house.idname)
JOIN
(SELECT DISTINCT
tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,

[Code] ....

How I could combine the results?

View 9 Replies View Related

Transact SQL :: How To Compare Columns On 2 Databases On 2 Different Servers

Aug 12, 2015

I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.

I'm looking for added columns in dbase 1.

Is there a system T-SQL script that can be used for this?

View 4 Replies View Related

Transact SQL :: Script To Disconnect All Users Except One From Two Databases

Aug 20, 2015

I need a script to kill existing sessions on [db1] and [db2] except sessions from [user1]. I have a script like this now:

USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('db1)
EXEC(@kill);

I just need to extend to run on db2 and don't kill sessions from user1

View 3 Replies View Related

Transact SQL :: Script Not Running For Databases Other Than Master Database

Jun 5, 2015

Script only displays the result for 'Master' database. 

What changes are required for script to display result for all databases on the instance?

SELECT DB_Name() AS DatabaseName, OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
ON ind.object_id = indexstats.object_id 
AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC

View 8 Replies View Related

Transact SQL :: Stored Procedure To Determine Which Databases To Restore?

Sep 27, 2015

I have 5 databases that the user will chose which ones to restore. I was thinking the variable with the 5 database names separated by commas. I was thinking about using the CONTAIN function but two of the databases have the same name except for a few letters at the end.

View 3 Replies View Related

Transact SQL :: How To Get All Databases Names With Its Owners In Server Instance

Jul 23, 2013

I want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set  like this:

DatabaseName    db_Owner

master                  sa
pubs                     valia
pubs                     eli
pubs                    maryam
AdentureWork      eli

this script get all owners of the database that is in use:

Use DatabaseName
GO

SELECT  members.name MemberName, roles.name RoleName,roles.type_desc,members.type_desc

View 10 Replies View Related

Transact SQL :: Can Search All Databases And All Stored Procedures With A Certain Column Alias?

Oct 14, 2015

I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]

View 3 Replies View Related

Transact SQL :: Looping Through Multiple Servers And Databases In Stored Procedure

Aug 27, 2015

I am doing some administrative tasks and need to collect some principals information from multiple instances and user databases.

I have table "dbo.instances" with list of instances. 
I have databases from "sys.databases". 

How can I execute the query to get principals information from "sys.database_principals" on each remote instance and database. I know that can use cursor, but not sure how to do this with multiple servers and databases.

View 3 Replies View Related

SQL DB Cleanup

Oct 12, 2007

We have SQL SERVER 2000 Runnin on our server. We are trying to do a db cleanup, ie all tbls/views that were created earlier and are not needed any more, need to be deleted. However, is there a way, to do a cleanup in a better method, other than going thru the whole bunch of tbls/views manually,and determining which ones are needed or not and delete the ones that are not needed.

View 5 Replies View Related

Replication Cleanup Sp.

Feb 9, 1999

Which sp code part is efficient?
1st code from sp_MSdistribution_cleanup. Used for Replication Cleanup job.
For 1500 rows, runs a day. Is something wrong with this code?
2nd code part is an alternative idea!.
/************************************************** *****************/
delete MSsubscriber_status from MSsubscriber_status ss1 where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
job_id < @max_cleanup_job and
job_id <> (select max(job_id) from
MSsubscriber_status ss2 (index = ucMSsubscriber_status) where
ss2.publisher_id = @publisher_id and
ss2.publisher_db = @publisher_db and
ss2.subscriber_id = @subscriber_id and
ss2.subscriber_db = @subscriber_db)
/************************************************** *******************/
select @maxCleanup_job = max(job_id) from
MSsubscriber_status (index = ucMSsubscriber_status) where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db
delete MSsubscriber_status from MSsubscriber_status ss1 where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
job_id < @max_cleanup_job and
job_id <> @maxCleanup_job

View 3 Replies View Related

Ghost Cleanup....

Mar 28, 2008

Hi:

I need to restore a DB but it was prevented by a background process of "Ghost Cleanup".
server is SQL2000 ENT. sp4.

It could not be killed, neither it was stoped after restart the server. Is there a way to change its running schedule and/or to kill it when I need to restore the db?

thanks
David

View 1 Replies View Related

Cleanup The Server Log?

May 14, 2007

Hi,

Just wanted to know how can I delete those old server/error/agent log?

Thanks!

View 1 Replies View Related

Deletescript For Cleanup

Oct 26, 2007



Hi everyone,

I'm writing a small script to clean up our database. We have a couple of databases which contain many gigabytes of data.

The script fills a few temp tables, with price-id's which can be deleted (based on a few rules). Then it deletes all related data first, before actually deleting the price records themselves.

This works fine, except for performance issues. After 12 hours, I had to cancel the running script as it was taking too many resources.

My question is, how can I increase performance? Should I add 'commit' after deleting data in each table? Would it help to make it a stored procedure?

Script looks kinda like this:

SELECT priceId INTO #tobedeleted FROM prices WHERE ... (few rules)

DELETE FROM price_product WHERE priceId IN ... #tobedeleted
....
....

View 6 Replies View Related

Distribution Cleanup

Jun 17, 2007

I would love to be able to run the distribution cleanup job with a switch that says cleanup all distributed transactions.

Because when I use peer to peer replication the @allow_initialize_from_backup publication property is set to true which is good. But it has the down side that transactions are stored the max retention period in the distribution database. I want to use the deafault 72 hours for my retention period so that the subscritions don't get deactivated but in a system with a high transaction rate there wil be a lot of transactions in 72 hours. This means that the cleanup job will have a tough time to figure out which transactions to delete so the cleanup job will run for a long time not a very big problem but the problem is that the cleanup job will keep the log reader agent from delivering trtansactions to the distribution database and the subscribers won't get their data in time.



Could Microsoft please give me a switch so I can choose when I want to save my transaction and when I want to delete them as soon as they have been delivered to all subscribers?

Is this a feature in SQL Server 2008? Could it be released in SP3 for SQL Server 2005. (The SP 2 cleanup job has a bug so I have to use the SP 1 verison of the cleanup job)

View 2 Replies View Related

How To Cleanup All Database&#65311;

Mar 6, 2006

hello,if I want to cleanup all the data that in a database,how to do?
thanks!

View 5 Replies View Related

History Cleanup Task

Feb 11, 2008

I have set up a history cleanup task to be performed once per week, however it doesn't seem to delete the old files, what am I doing wrong?

View 8 Replies View Related

Maint Cleanup Task

Mar 19, 2007

Ques; can "both" files the db backup (.bak) file "and" the (.txt)report file in a maint plan object be cleaned up at same time?

The object is working ok but trying to setup and match backup text reports to the db, I have way more .bak files than text files.

Created from/after db maint plan (which is working ok) from/in the object Maintenance Cleanup Task and the object selections are

Delete files of the following type:
_backup files
_maintenance plan text reports

File location:
_delte specific file
File name: __
_search folder and delete files based on an extension
Folder:__
File extension:__
File age:
_Delete files based on the age of the file at task run time...
delete files oder than the following. . . .

View 1 Replies View Related

Cleanup Task Deleting Everything - T-SQL

Apr 24, 2007

Hi all,

I created a maintenance plan with a cleanup task to delete bak files older than 4 days, but it keeps deleting everything but the current day files.

This is the generated T-SQL:

EXECUTE master.dbo.xp_delete_file 0,N'F:SQLDBBACKUP',N'BAK',N'2007-04-20T11:59:30',1

Someone can tell what's wrong with it?

I've noticed that wheter if you put the condition on files' age or not it generates exactly the same T-SQL statement, it says that the actual one could be different if you set conditions on the task, but if it is so why they work the saem way?

Thanks!

Cheers,

Giovanni

View 4 Replies View Related

Problem With Ghost Cleanup

Nov 12, 2007

I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner?
Thanks in advance..

View 12 Replies View Related

Maintenance Cleanup Task……

Sep 14, 2007




SQL Server 2005 Standard Edition
Windows Server 2003.

I have created an €˜Backup Database Task€™ to create the database backups and checked the option €œCreate a subdirectory for each database€?.

When I add the €œMaintenance Cleanup Task€? I am not getting the similar option to clean the backup files in the subdirectory.

Any suggestion on how to solve this?

I can always uncheck the €œCreate a subdirectory for each database€? option to create the backup files in one location.

Thank you,
Smith

View 10 Replies View Related

Sysmail_mailitems Table Cleanup

Aug 22, 2007

sysmail_mailitems table in msdb stores data for emails sent using database mail. The DB was growing and I found sysmail_mailitems table is taking almost 85% space. What is the procedure to clean up data in this table. I did dirty way of removing FK's for the table, truncating data and re-establishing FK relationships.


Is there some way or settings somewhere to schedule cleanup on this table?

Thanks,

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved