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
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.
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
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) ---------------------------------------------------------------------------------------------------------------
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 ??
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
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?
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,
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
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.
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
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.
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.
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
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?
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 .... ....
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)
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. . . .
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?
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..
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?
