In my production environment, ALL OF THE SUDDEN, my backups via EM schedule tasks, fail, yet when i examine the error, it says that the DUMP/LOAD was successful. I receive the following error:
'Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestore history. This may indicate a problem with the msdb.. . . . . .
I expanded the MSDB database, and ran sp_purgehistory (no params) and it still FAILS. Is this related to the log? Please HELP asap. Thank you in advance.
I recently changed the SA password on my SQL Server 6.5 installation and discovered that this had caused the Scheduled CmdExec Tasks (defined in SQL Executive) to fail with:
"Process Exit Code 1. Microsoft (R) SQLMaint Utility, Version 6.50.240 Copyright (C) Microsoft Corporation, 1995 - 1996 [Microsoft SQL-DMO] Error 4002: [SQL Server] Login failed"
Changing the password back to the original resolves the problem, but is a less than satisfactory solution.
Any advice on how I can change the SA password and allow CmdExec tasks to continue running would be gratefully appreciated.
I tried installing SQL 2005 Dev Edition on my Win 2000 Professional machine. That failed. But now I cannot send out emails or even open a simple Email task from DTS. This is something that worked perfectly fine before I attempted the SQL 2005 installation. It seems that the SQL 2005 installation somehow messed up the the MAPI profile. The exact error message that I get when trying to execute or open a Email task in DTS is: CAnnot load MAPI Interface layer for DTS. Please make sure that semmap90.dll is installed.
The import from Flat File Source fails: Error 0xc02020a1: Data Flow Task 1: Data conversion failed.
The data conversion for column "ArticleName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)
I have changed the size of the column "ArticleName" (varchar) to max but the error comes up again.
The data i want to import came with multiple flat files. They all could import properly but this one is a problem.
The comapny i work for has a server running the following.
Opteron 246x2
2 Gig memory
320 Gig Sata 2 drives
Windows 2003 Standard Edition
SQL Server 2005 Express Edition. The Free one.
There are approx 10 users that connect to the server.
There are two programs which seem to use sql server. Act 7.0 and service ceo.
When the computer is rebooted its at 0% for sqlserver.exe
Than when all connect it maxes it 50% and its steady there. Seems Service ceo affects it the most.
Told by comapny need to buy full blown sql server to resolve problem. But i dont think this is the problem.
Questions: Is it common for server to be at 50% all the time with sql server running?
And if its not is there a way to reduce the sql cpu usage.
I am new to sql server and have done alot of research and fixes. Ive unsitalled and reinstalled all sql instances and done the tweaks suggested. Any fresh ideas would be great thanks.
I've got a little console app that basically pulls back a recordset from our SQL Server 2005, goes through each row in the dataset and may/may not insert a record into a different table in the database. We use sproc's for every transaction and I close every connection in the application. However, when the application ends, I still show connection pools open in the performance monitor. Same with websites that I know have no traffic or that have been stopped by me in IIS.Last night I showed a total of 6000+ "Current # pooled and nonpooled connections". Should I be worried about what seems to be unending growth in the connection pools? If so, how can I look to manage this better?
Hi, I've been trying to figure out this error message I'm getting. The exact error message is: " Procedure addservicerequest has no parameters and arguments were supplied." and this is applied to a SQL Command, but what confuses me is that I've got parameters already created and added. Is there any advice to help me out with this problem? Here's the code for reference: 1 Dim sqlparadate As New sqlparameter2 Dim sqlparacompany As New SqlParameter3 Dim sqlparalocation As New SqlParameter4 Dim sqlparacontact As New SqlParameter5 Dim sqlparaphone As New SqlParameter6 Dim sqlparadetails As New SqlParameter7 Dim sqlparaso As New SqlParameter8 Dim sqlparadrive As New SqlParameter9 Dim sqlparawork As New SqlParameter10 Dim sqlparaback As New SqlParameter11 Dim sqlparaproblem As New SqlParameter12 13 Dim sqlcon As New SqlConnection14 Dim sqlinsert As New SqlCommand("addservicerequest", sqlcon)15 16 'txttest.Text = caldateofservice.SelectedDate 17 If sqlcon.State = Data.ConnectionState.Closed Then 18 sqlcon.ConnectionString = "***" 19 sqlcon.Open()20 End If 21 22 sqlinsert.CommandType = Data.CommandType.StoredProcedure23 'sqlinsert.CommandText = "addservicerequest"24 'sqlinsert.Connection = sqlcon 25 26 With sqlparadate27 .SqlDbType = Data.SqlDbType.SmallDateTime28 .Direction = Data.ParameterDirection.Input29 .ParameterName = "@dateofservice" 30 .Value = caldateofservice.SelectedDate.ToString31 End With 32 sqlinsert.Parameters.Add(sqlparadate)33 34 With sqlparacompany35 .SqlDbType = Data.SqlDbType.NVarChar36 .Direction = Data.ParameterDirection.Input37 .ParameterName = "@company" 38 .Value = txtcompany.Text39 End With 40 sqlinsert.Parameters.Add(sqlparacompany)41 42 With sqlparalocation43 .SqlDbType = Data.SqlDbType.NVarChar44 .Direction = Data.ParameterDirection.Input45 .ParameterName = "@location" 46 .Value = txtlocation.Text47 End With 48 sqlinsert.Parameters.Add(sqlparalocation)49 50 With sqlparacontact51 .SqlDbType = Data.SqlDbType.NVarChar52 .Direction = Data.ParameterDirection.Input53 .ParameterName = "@contactname" 54 .Value = txtcontactname.Text55 End With 56 sqlinsert.Parameters.Add(sqlparacontact)57 58 With sqlparaphone59 .SqlDbType = Data.SqlDbType.NVarChar60 .Direction = Data.ParameterDirection.Input61 .ParameterName = "@contactphone" 62 .Value = txtcontactphone.Text63 End With 64 sqlinsert.Parameters.Add(sqlparaphone)65 66 With sqlparadetails67 .SqlDbType = Data.SqlDbType.NVarChar68 .Direction = Data.ParameterDirection.Input69 .ParameterName = "@details" 70 If ddldetails.SelectedValue = "Other" Then 71 .Value = txtreason.Text72 Else 73 .Value = ddldetails.SelectedValue74 End If75 End With 76 sqlinsert.Parameters.Add(sqlparadetails)77 78 With sqlparaso79 .SqlDbType = Data.SqlDbType.NVarChar80 .Direction = Data.ParameterDirection.Input81 .ParameterName = "@serviceorder" 82 .Value = txtserviceorder.Text83 End With 84 sqlinsert.Parameters.Add(sqlparaso)85 86 With sqlparadrive87 .SqlDbType = Data.SqlDbType.NVarChar88 .Direction = Data.ParameterDirection.Input89 .ParameterName = "@drivetime" 90 .Value = ddldrivetime.SelectedValue91 End With 92 sqlinsert.Parameters.Add(sqlparadrive)93 94 With sqlparawork95 .SqlDbType = Data.SqlDbType.NVarChar96 .Direction = Data.ParameterDirection.Input97 .ParameterName = "@worktime" 98 .Value = ddlworktime.SelectedValue99 End With 100 sqlinsert.Parameters.Add(sqlparawork)101 102 With sqlparaback103 .SqlDbType = Data.SqlDbType.NVarChar104 .Direction = Data.ParameterDirection.Input105 .ParameterName = "@backhome" 106 .Value = ddlbackhome.SelectedValue107 End With 108 sqlinsert.Parameters.Add(sqlparaback)109 110 With sqlparaproblem111 .SqlDbType = Data.SqlDbType.NVarChar112 .Direction = Data.ParameterDirection.Input113 .ParameterName = "@typeproblem" 114 .Value = ddlproblem.SelectedValue115 End With 116 sqlinsert.Parameters.Add(sqlparaproblem)117 118 sqlinsert.ExecuteNonQuery()119 120 sqlcon.Close() Specifically it occurs on line 118. Thanks
I have a database with a table called RAW, this table receives msgs via XML, sms and various other formats. The data is written into this table at a rate of approximately 50-100 rows per minute.
I have a SP which takes the data written into the raw table and performs various actions looks for account information, writes to a log table, writes to incident table’s gets GPS information and so on.
The records written into the raw table need to be processed at almost instantly with a maximum of under a minute from when they arrive into the table.
At present there is a sql agent job which executes the SP, this consist of a step to execute the SP which on completion moves to the next step which is a loop waitfor delay and then back to step 1.
The trouble is it never actually finishes and runs 24/7 there is no break point for error handling and occasionally the records that arrive in the raw table do not get processed and the job has to be restarted for it to pick them up again.
I am looking for best way to handle this process, I thought about a trigger but the performance impact on using a trigger was too heavy to consider.
when is seemd that everything works some weird behaviours comes out.
I try to summarize the problem without to post the complete code.
Service Broker is set to have a dialog between two databases on the same SQL Server instance.
The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.
The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).
The conversation has a Timeout Dialog to end the dialog after a while.
The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.
n.b.the Receive is within a Transation that I commit at the end.
some other informations that in the meanwhile I found out :
This was my complete WAIT FOR(RECEIVE :
WAITFOR ( RECEIVE top(1) -- just handle one message at a time @message_type=message_type_id, --the type of message received @messagetypename=message_type_name, @message_body=message_body, -- the message contents @dialog = conversation_handle -- the identifier of the dialog this message was received on FROM [TargetQueue] ), timeout 1000;
if (@@ROWCOUNT = 0) BEGIN COMMIT; BREAK; END
IF I delet TIMEOUT 1000, everything works as expected ... Inside if (@@ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout) I'm happy that it works what if this is the solution, it make no sense to me!
- Transactional (5 articles - tables only) Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).
Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums. As an example: - Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s)) - Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))
and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.
The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.
Any idea what should I do with this? Any help would be greatly appreciated. Thanks.
We have an issue with the Version Store growing constantly. According to sys.dm_os_performance_counters, "Version Generation rate (KB/s)" is growing, but "Version Cleanup rate (KB/s)" isn't. We use read-committed snapshot isolation
While dbcc opentran and sys.dm_exec_requests don't show any long running transactions, I wrote a query looking at sys.dm_tran_ active_snapshot_ database_transactions. This shows a number of long running transactions but, according to sys.dm_exec_sessions, they are all sleeping. The transactions that are running come and go very quickly, as I would expect.Could these sleeping transactions be responsible for preventing the version store from cleaning up?
The log for our SQL 2000 database shows constant "starting up database dbname" entires. Is there a option that causes the databases to constantly be starting or is this a new feature of SQL 2000.
Our report server are constantly getting the below error.
What causes this - I know how to fix it, in fact, I've automated it but why does it constantly happen on some servers? I guess I'd like to know what causes it to try and fix it at those points instead of having to fix it here. Proactively.
Reporting Services Error
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
Trying to set up a tranform task between a mysql db using and ADO.NET connection and sql server. My query to pull from the mysql db is something like "select x,y,z from table where last_updated" > @User::LastUpdated. This command is set up as an expression for the Data Flow Task and is the value for the [DataReader Source].[SqlCommand]
I have two questions.
Why does the package attempt a query against the mysql database all the time? And Why is the query attempting to pull the entire table instead of having any regards for my where clause?
I've even added where last_updated > greatest('2006-08-15', '" + @User::LastUpdated to attempt to get it a where clause even when the parameter isn't set yet.
What is the trick? This is not feasible when pulling from multi-million row tables.
Hello, I'm using SQL service Broker 2005 with ASP.NET 2.0 in order to use the sql cache dependency. everything works fine... I have only a doubt regarding a query that is constantly executed on mu db ( i can see it be means of the SQL Profiler)
The query is: exec sp_executesql N'BEGIN CONVERSATION TIMER ("'') TIMEOUT = 120; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-GUID]), TIMEOUT @p2;',N'@p2 int',@p2=60000
The web application is not running from a browser ...
It this ok or I forget to clean/reset something from my web application and/or sql server?
The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.
I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.
Hello I have a very large base msdb, I do not arrive has to purify it with sp_delete_backuphistory, it is much too long, have another solution? thank you
Hi , I am facing a paculiar problem.I got some jobs scheduled in MSDB.They are running fine for some time. Yesterday when i tried to open the properties of a job from EM i am getting an error 'Select Into Bulk copy option not enabled' Can anyone got this before? I appreciate if any one can throw some thoughts on this. thanks in advancce MM
I know that msdb is used to manage the job schedulers in sql server 7.0 ( job agent ). Does this (msdb) like other databases, where I can control its size, shrink it or expand it.
IF am backing up the msdb, and I errased all jobs scheduled in sql agen. Can I restore all the deleted Jobs by restoring the msdb.
Does anyone know how to fix this message? Message: Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestorehistory. I ran DBCC CHECKDB (msdb) and no errors were reported. Thanks
I have a job called JOBA scheduled to run every 10 min. Is there a way in Tsql that I can find out whether the job is currently running or not.Is there any table/field in msdb database that gives me the desired info.
My MSDB is too big [about 14 GB]. When I closely looked all tables, here is what I found -
Top 3 biggest tables are -
sysmaintplan_logdetail - rows- 124923 - data - 13375456 KB DTA_Tuninglog - 181658 rows - data - 163248 KB sysmail_mailitems - records - 14227 data - 126208 KB
I am not a DBA, so if any one can explain in simple terms how I can cleanup this maintenance plan log detail without increasing the size of tempdb? How can I stop this database growing in the future?
are there restrictions on what a DBA can do on a developer's edition?
i'm asking coz i get really bad connection problems when changing service accounts, client aliases, port numbers, connecting to different components like ssis, reporting services, database engine, etc...
in sql2000, changing these things are a breeze, is there some guidelines when making these changes in 2005?
Just want to know whether you guys do the following as a dba:
1. Setup a logging for tracking database as well as table size that containing size, indexing size. You can measure the growth. 2. Record for indexing for each database. I think this is over kill task. 3. Record database setup such as create statistic, update statistic etc.
These tasks can be automate to record every month for example and record this into Administration database for instance.
If I'm in the Data Flow tab in VS 2005, how can I select only certain components to run to test? I tried highlighting the ones that I want to run but it's running all of them in the tab...some of the components I want to take out for testing then maybe put back in later. If I delete the tasks I don't want to run, then I end up having to recreate them
I am trying to restore my msdb database after rebuilding my RAID, and reinstalling sql. I can restore my user databases fine, but my msdb gives me error 3132: The media set for database msdb' has 2 family members but only 1 are provided. All members must be provided.
I really don't want to lose all my jobs, so any help would be appreciated. thanks.
I have a problem with my MDSB database. After my nightly reboot, I received an error in my Lof File stating: Error 9001: Cannot file log device for msdb database. I looked and saw the msdblog.ldf, so I am alittle confused. I ran a dbcc checkdb, dbcc newalloc and dbcc updateusage on msdb, but I did not get any errors. I added an additional log file to try and fix it, but nothing has fixed it. I am perplexed because all my scheduled backups are failing.
I want to move the physical location of the msdb database on a SQL server. However I cannot drop the database with the drop database statement, even setting it to dbo use only and single user mode. I get an error message saying the database is in use. Obviously I do not want to reinstall the server. The reason I want to move it is because it is located in the default dir on the C: drive which is running out of space.
hi, I already have jobs in the sql server agen. I want to copy all these jobs to another sql server, What is the best way to do it. here what I am thinking to do 1. Back up the msdb from the sql server which has the jobs 2. restore msdb into the sql server where I need to create the jobs.
I got an error message, 15089 cannot change the 'single user' option of a database while another user is in the database.
But no one is in except me.? thanks for your input
Problem with Msdb error 1105 can not allocate space in sysbackupdetail. msdb has 1.0MB of database space available and 1.97MB of log space. I intended to expand the database but must first expand the device. the available space for the device is -703. So I can not adjust and expand. Any ideas. My hourly dumps appear to be functioning if you view them ny expanding the device but tasks show them as failing.
when i am trying to restore MSDB database from a disk backup it is giving an error saying "Database in use.System administrator must have exclusive use of the databse to run the restore operation". But i am not using msdb anywhere.when i see the processes under current activities i see an application named sqlagent-alert engine using the database.even after killing this process it reppaears and stops the restore operation. so i stopped sql server agent and did the restore operation.It worked. so is it necessary to stop sql server agent while restoring msdb?may i know the logical reason for it?
