We have maintanance plan defined that runs every night for optimization, integrety check and full backup. Somehow, for last two days I have been receiving the following error about two out of four databases.
[1] Database customere7: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 15089: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot change the 'single user' option of a database while another user is in the database.
[2] Database producte7: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 15089: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot change the 'single user' option of a database while another user is in the database.
Strange thing is that we don't have this database in single user mode. They are the the main production databases and optimization and backup jobs run OK.
Please help me..I am not sure whether there is a major problem with database or what I should check. Does running integrety check after optimization job affect anything?
Daily activities seem normal with these databases.
i have a few maintanance plans on one database that i wanna copy acrross the whole plans to another database of same set up... is there a way to do this?
I have a Mantenance Plan running on my Production Server. Each job has one step. -Optimization (each week) -Integrity Checks (each week) -T-Log Backups (each hour) -Full Back up (once a day)
The Job is saying the last three are failing but niether the SQL Server Logs or MS Application Log are reporting a success or faluire. "write to windows application log on faluire" is set for all of them. The backups are occuring, as the files are in thier destination.
I have moved few databases in 2000 to a different server which run's on sql server 2005 and its not a instance ogf 2000. I backed up databases in 2000 moved those files to other server and then restored them. I had solved the problem of orphand users by deleting and adding again.
Now when i try to setup maintenance plan i have the following error after walking through the wizard
TITLE: Maintenance Plan Wizard Progress ------------------------------ Adding tasks to the maintenance plan failed. ------------------------------ ADDITIONAL INFORMATION: Object reference not set to an instance of an object. (Microsoft.SqlServer.Management.MaintenancePlanWizard) ------------------------------ BUTTONS: OK ------------------------------
what do i need to fix this????Any help is appreciated...
And what else do i need to make sure everything in 2005 are working fine apart from orphand user.
Hi friends, My company is planning to do exercise for disaster(complete server screwed up). They don't want to add the users or jobs. They are asking me to restore Master,Msdb and User Databases. I Planned like this if there is any mistake please reply me ( It is urgent) 1.Installing NT, IE and MS sql server 7.0. with all appropriate service packs. 2.Creating User databases which were present on the previous screwed up server with same name. 3.Restoring Master Database and Rebooting the server. 4.Restoring Msdb Database and Rebooting the server. 5.Restoring the User Database along with the transaction logs. 6.Running 'DBCC checkdb' command on all the databases. Please reply me soon. Thanks dindu.
Need some comments/ideas on the following scenario:
Current Situation: -I have a PROD DB and a REPORTING DB( Separte Physical Servers). -PROD DB replicates (Transactional) to REPORTING DB. ( Using only Replication Wizard to set up, quite easy) -DB is for running shopfloor(manufacturing) applications, thus needs to be minimum downtime. -Retention period for PROD DB data is 3 months ,Reporting DB is 3 years.
Future Problem that I will encounter: -Purging of data in PROD DB will be replicated into Reporting. -Schema change in PROD DB need to delete existing Publication before able to change schema. -After schema change, need to recreate publication and rerun snapshot.
Questions: -How to prevent the purging to be replicated to Reporting? Switching off Replication when Purging takes place will not help. The only solution I am thinking of is to alter the store procedure for replication during purging. Is there any other "CLEANER" hassle free way?
- How to cater shema changes better? Currently the data is not alot, but down the road, it might go to Terabytes, by then running a snapshot will cost us alot. Is there any way not to redo a snapshot for this scenario?
We have a debate in our team about embedded SQL vs. Stored Procs.
The argument is why use SP's if you can embed the SQL in the code and SQL2K will cache it on the fly?
I can't find any definitive information on pros and cons between the two methods.
If there are no major performance issues, or gotchas, I guess it comes down to developer preference.
SP Pros: - Great SQL support in VS.NET (dev, debug, integration) - Seperation of database specific code from middle tier. - Less lines of code in middle tier - VS.NET support for .xsd dataset definitions. - Logic closer to data for more demanding processes.
Embedded SQL Pros: - Less artifacts for version control - Better encapsulation of logic
I am working on tuning the procedure cache hit ratio for my server. We haveadded 4 Gb of memory to the server, which has helped. In addition, I have runthe DBCC FREEPROCACHE, which helped for a couple of days to get the hit ratioup to about 84% (from 68%).When I use the performance monitor on the server and look at SQL Server CacheManager:Buffer Hit Ratio, I see that the Prepared SQL Plan is around 97%, butthe Procedure Plan hit ratio is down around 55%. I've done some research ondifferent tuning techniques, but can't seem to find 1. a clear definition ofthe difference between the prepared sql plan and the procedure plan and 2.other than adding memory and running dbcc freeprocache, how can I get theprocedure plan cache raised? I do know that there are some procedures thatneed to be modified to be called fully qualified (e.g. exec dbo.sp_###instead of exec sp_###), but I don't think that those will increase theprocedure plan by 30% or more.Any insight you can give would be greatly appreciated.Thanks,Michael--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++ A connection could not be estabished to server--Timeout expired Please verfy SQL Server is running and check your SQL Server registration properties and try again. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
The benefit of the actual execution plan is that you can see the actual number of rows passing through each step - compared to the estimated number of rows.But what about the "cost percentages" ?I believe I've read somewhere that these percentages is still just an estimate and is not based on the real execution.Does anyone know this and preferable have a link to something that documents it?Thanks
hi, I have settup up sql mail and did the following: 1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook 2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services 3. I used the profile that I created in outlook to test the sql mail but got an error: Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account I do have a valid domain account I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed. I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment. The error is: 19015: The encrypton is required but no available certificat has been found.
Hello, I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query. Ex. how can the following be implemented using the same query but in sql server? Access ------ SELECT MAT_Charts.YYYYMM FROM MAT_Charts WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key])) GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested: [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
I am getting error in sysindexes when i run dbcc checkdb on a production db. the error is Server: Msg 8928, Level 16, State 1, Line 1 please help me to remove this all the options of dbcc checkdb as well as table are not helping me
I have deleted a database from SQL Enterprise Manager. Anyone know a way to clear that database from my maintenance plan? I do not wish to just uncheck the deleted database or create a new database plan. Thanks!
I have around 10 maintenance Plans on my server and all the plans are running fine except for one which takes for ever....Yesterday I scheduled it to run at 1 AM and it was still running when I came in this morning...Usually this job used to take only 2 minutes and now it takes forever....
I tried to delete the maintenance Plan and created a new one , but no luck..
But If I try to back up the database by right clicking the database -- All Tasks --- Backup database , it takes less then 2 minutes..But If I try to run the job created by the Maintenance Plan to back up the database it takes forever and all I can see the status of the Job as ' Executing Job Step 1"..
I created a test database and restored the database in Issue onto the test database and created a maintenance Plan for the test database and it runs fine and takes less than 2 minutes.. and I don't see any security issues on this...
B'cause the job neither fails nor executes ..So there are no error messages to look in to ...
Please let me know if any one has encountered the same problem and how to resolve it..
In maint. plan when reorg is selected for a user db, it will also work on sys tables in user database. Is it a good thing to do ? How do I avoid sys tables in a user database not to be part of maint optimization plan ? Some script would be helpful !
When looking at the Est. Execution Plan of a query in QA, what should I be looking for? What numbers are acceptible? Does anybody have a link to any information on this subject?
I have been given a SQL Server 2000 database to look after which has been set up with a Database maintenance plan. The plan is set to backup the complete database and the transaction log. The backups are written to the local disk correctly but the plan is also set to remove any backup files (both database .BAK and transaction log .TRN) that are over one week old. Complete database .BAK files are written daily and the .TRN are written every hour daily. The .BAK files are removed ok automatically but the .TRN files are not - they are just slowly filling the disk. There does not seen to be anything different between the way the main database and the transaction log is set up in the maintenance plan.
I have a strange thing in one of our Maintenance plans.
On the first tab where you check which databases you're including in the plan I have (say my database name is CAT) a 'CAT' and 'cat' database listed and the one chosen is 'cat'. However my database in all other views shows up in all caps. (even when I do an sp_helpdb)
The backups look like they're working, etc. but it just seems weird. If I go to create a new plan it only gives me the one option 'CAT' which is really what's there. I'm new and I'm thinking the database at one time was 'cat' and this is when the maintenance plan was created. Then it was renamed to 'CAT' and there's the two db's showing in the old mainenance plan.
What would you do? Create a new plan with "CAT" and just get rid of the old one with the weird 'cat' and 'CAT'?
I've created a database maintenance plan to backup a database, but it just isn't happening, am i missing something. The maintenance plan appears to be created successfully.
Hi there, Could anyone please help me, I have been given the responsibility of planning and implementing the admnistration of 25 existing SQL Servers (6.5 and 7.0) countrywide and I am unsure as to where I should start. I am relatively new to SQL Server and if someone could just point me in the right direction or possibly recommend any good publications which I could refer to, I would be extremely grateful.
I'm new to SQL server but familiar enough with databases to know this doesn't seem right. Here's the situation: I have a table with real estate property information. There are about 650,000 rows in it. I have a nonclustered non-unique index on the city where the property is located. There are about 40 unique values in this index.
I do a simple query like: SELECT city,address from propinfo where city= 'CARLSBAD'. The query will return about 4,000 rows. The problem is that the execution plan that it chooses is to do a full table scan. I.E. Even though there is an index on City, it chooses to look through 650,000 rows rather than do an index seek. Something sounds inefficient here. BTW, this happens in both SQL 7 and SQL 2000. Can anyone explain why this happens? I've got to think that SQL Server is more efficient here.
I am noticing a discrepency in query plans when a process is run in Analyzer as either a proc or as straight sql.
I have a query that uses a view of 5 tables that have a check constraint on the year. When I run my query in query analyzer and state year = 1999 along with over parameters then the query plan only looks at the one table.
When I take that query and make a stored proc and run the process passing the year = 1999 along with other parameters the plan states that it is looking at all of the tables in the partitioned view.
I need to set up a Maintenance Plan on a 6.5 DB. I know the version is old but the customer cannot upgrade at this time. When I try and run the wizard to set it up I get a warning about running a plan on a DB that is larger that 400MB. Whats the deal? Thanks for the help!!
