I'm running SQLMaint.exe for DBCC and backups overnight, because I like the -BkUpOnlyIfClean switch, but I'm having a problem. I need
Due to space constraints, I need to overwrite the backup every night. The -DelBkUps switch is nice, but it accepts a parameter of weeks, not days. Is there some syntax I can use with SQLMaint.exe to get what I want, or am I going to have to do an NT 'at' commnad to get rid of the file just before th enext dump, or what? Any suggestions would be intensely appreciated.
I am a big fan of the backup naming convention used by maintenance plans and the SQLMAINT utility. My problem is that I want to use the same naming convention "db_name_diff_YYYYMMddhhmmss" because of my situation: I'm in California, my databases are in Pennsylvania, I don't have domain access to the servers, I use a scheduling tool (required) that only uses T-SQL or cmd line utilities, and I want the backups named as above. SQLMAINT provides for full and tlog backups, which I have set up, but I want to do diff backups the same way, with the same naming convention. Any ideas?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
I have a SQL data source and a GridView control in a user control. I used to have them on a specific page that is not the default. When I clicked on a link on the default page to get to the page containing them, they worked as desired.Now I've moved them to a user control so I can include them on multiple pages, including the default page. Included in the code for the SQL data source is the Selecting event that defines what the SQL query should be. There is a default query defined in the source code of the ascx control, but that's not the query that I want to run when the control is displayed.However, when my default page is displayed, it seems to ignore the Selecting event and displays the query that is in the code for the default page. When I click to another page, that one defines the user control properly.I tried fixing it in the Page_Load event for the default page, but now I'm getting an error that CommandText is not initialized. How do I initialize the query for the SQL data source for the default page?
Can anyone here tell me about initializing high-values?I want to create a generic stored procedure that will perform thefollowingSelect * from ? where ? between parm1 and parm2no problem, but when no parms are passed I want to perform a select *so.......In the declare section I set parm1 = nullI want to initialize parm2 to HIGH-VALUES, but I cannot find theoption. Last I used it was SQL SERVER 7.0
SQL Server 2005 Books Online provides an article entitled, "Initializing a Transactional Subscription without a Snapshot". Is it possible in SQL Server 2000 to initialize transactional replication without a snapshot?
So far, I have been unable to find a similar procedure mentioned in the SQL 2000 Books Online. I was able to follow the 2005 procedure using SQL 2000 until I got to the step that says to enable the "Allow initialization from backup files" option on the "Subscription Options" tab of the "Publication Properties" dialog. But that option does not appear in the SQL 2000 version of the specified dialog box.
We have customers that are using web synchronization. Could you, please tell us when service pack for the bugs, discussed in thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 will be reliazed?
What replication participants should apply the service pack in a configuration where Subscriber (MS SQL 2005 Express) gets synchronized with Publisher/Diustributor (MS SQL 2005 Standard)?
I'm trying to force an anonymous subscription to re-intialize on it's next sync attempt. I can do this from the subscriber no problems, and from the publisher using 'Re-initalize All Subscriptions', but I can't seem to re-intialize only a single subscription from the publisher.
To do this I'm trying to execute sp_reinitmergesubscription using the subscriber details found in the sysmergesubscriptions table. This executes ok and when the subscriber starts to sync it does try to re-initialize starting with generating a new snapshot, but after processing for a while it throws the following error messages:
Error messages: The merge process could not allocate memory for an operation; your system may be running low on virtual memory. Restart the Merge Agent. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147198720) Get help: http://help/MSSQL_REPL-2147198720 An error occurred while reading the .bcp data file for the 'CDP_TableDates' article. If the .bcp file is corrupt, you must regenerate the snapshot before initializing the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199428) Get help: http://help/MSSQL_REPL-2147199428 The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
There doesn't appear to be any shortage of memory on the server. There is no problem regenerating the snapshot from the publisher, and there's no problem with syncing through the web using delta syncs or re-initializing from the subscriber, so I can't see any obvious cause from those error messages.
I'm using SQL Server 2005 SP2 on the publisher, and SQL ServerCE 3.1 on the subscriber.
SQL Server 2008 standard as the publisher SQL Server 2012 express as the subscriber
and I tend to use web synchronization; there is no domain trust.
After following the instructions at MSDN for configuring web synchronization, I have an error that I can't get past - after creating the initial snapshot on the publisher, I try to run replmerg.exe at the subscriber and I always get this error:
"The subscription to publication 'TestReplication' has expired or does not exist."
If I refresh the publisher's "Local Publications" and look within the "TestReplication", it does show that the subscriber is a known subscriber. Likewise, if I refresh subscriber's "Local Subscription", it has an entry for TestReplication publication.
I already verified that the user used by Replisapi.dll has the read permission to the snapshot folder, is a member of PAL, is db_owner of the publishing database and distribution database. I am using self-signed certificate for this test and I have already installed the certificate at the subscriber machine so that HTTPS is trusted. I can run diagnosis from subscriber so I know subscriber can reach and logs are being left at the publisher's IIS.
I have seen several posts on the subject, and have tried various configs. At this point, I am simply trying to connect to SQL Express, that's all.
I have an app. in MS Access that uses SQL Express as a back-end database (this is the simple desktop version of our software). I don't remember having so many issues with just connecting to a small db engine on a single computer..! Just to get it off my chest, there was a post about this subject where someone listed more than 15 items to check... If anyone at Microsoft is reading... connections should not require a 15-item check-list.. People running sql express are not creating a sophisticated database system, probably running it because, they want a free, easy solution, and do not have an IT person, nor are they intersted in reading any 1 of the 15 items... Just trying to connect to the instance of the database that runs on the same computer.... that should not require more than possibly 3 different settings.
My objective at this point, is simply to connect! Nothing else.
My environment settings are:
OS: Win XP Professional MS Access(2003) and SQL Express both installed on the same computer All connection attempts are on the same computer SQL Express is installed in mixed mode SQL Browser running, Network DDE running (if needed) All Network protocols are enabled (tried disabling and enabling each 1 at a time as well) All Client protocols are enabled I tried setting network library to "dbmssocn", and left blank for default (uses namedpipes) I tried using host name, localhost, and IP address for servername (with the SQLEXPRESS of course) Also tried leaving it blank I tried with Integrated security, also tried with the user "sa" and "test" user (both enabled and capable of logging in and accessing the database) I tested the connection from SQL Express Manager, successfully connected in all modes (secure, and with SQL users) to make sure there are no issues with the users or security. Tried turning the SQL browser off (this should not be needed in local mode) Tried using an alias Tried using attach database as a filename And, the firewall is turned off.
Always getting the error "....connection failed because of an error in initializing provider" (whether using namedpipes, or TCP, or shared mem)
Both SQL Express and Access are installed on the computer, that should include the providers...
Please let me know if there is anything left for checking and/or trying... Appreciate your help...
I am creating my first SSIS package, simple, I read a file, a computation and then write it to an excel.
I am getting the message: "Test connection failed because of an error in initializing provider." The database is NOT remote. My computer is slow though. after about 10 or 15 seconds after clicking the button to test my connection, I get the message and much more. The message asks me if the instance name is correct. I don't know what this would be. This message is on the first task, reading the file (finding the file).
The last sentence of the long message is: "Named pipes provider: Could not open a connection to SQL Server (2).
My question is: Why am I getting this message and what do I need to do to resolve this problem?
Is anyone using the -DelBkUps option with their maintenance plan on SQL 7.0?? if so is it actually deleting the files? Mine is running but not deleting any of the old backup files.
I would like to run the following SQLMAINT command on my database every 14days:
At 2:00 AM, this command needs to kick off: SQLMAINT.EXE -D cabinet -CkAl -CkCat -UpdSts -RebldIdx 100 -Rpt D:MSSQLLOGcab_maint.rpt
Question: How can I "kill" all users, then place the database in "dbo" mode, run "SQLMAINT", and then release database access back to the public - all in an automated process.
Was wanting to get some feedback on using sqlmaint to produce database backups in a production environment. Especially on databases > 10GB. My preference is not to use it but I'm arguing with another DBA and wanted some outside feedback.
We had to failover our primary db server for maintenance to our secondary replica. The primary was rebooted during maintenance. We failed back after the maintenance and one of the databases is not synchronizing.
I checked sys.dm_hadr_database_replica_states, and it is showing that it is INITIALIZING.
It has been in this state for more than 45 mins now. The last_sent_time, last_received_time, last_hardened_time and last-redone_time are all stuck with a time stamp 45 mins ago.
They haven't changed. How do i resume this database and bring it back in sync?
I tried suspending and resuming the data movement, but hasn't worked.
I'm having a problem sending the query set as an email text attachment. Test transmissions from Database mail working fine.
Send simple messages with the sp_send_dbmail sproc works fine as well.
It is only when I try and send a query result that things blow up. The query itself is working fine also, so I'm now down to think there is some esoteric problem with the sproc itself.
Surface config features have database mail on, and SQL Mail off.
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 476 Query execution failed: Error initializing COM Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
Investigating the sproc itself shows...
Line 476 in the sproc is the beginning of a 'trap' --Raise an error it the query execution fails -- This will only be the case when @append_query_error is set to 0 (false) IF( (@RetErrorMsg IS NOT NULL) AND (@exclude_query_output=0) ) BEGIN RAISERROR(14661, -1, -1, @RetErrorMsg) END
RETURN (@rc) this is the last section of code in the sproc
I run the SQLMAINT.EXE utility nightly to do DBCC checkdb, checkalloc, and checkcat checks on all databases. On one server this check seems to lock up the server displaying error 17832, "unable to read login packets". Once this occurs the only way we can find to get out is to reboot the NT Server. We have observed this behaviour once every two or three weeks. Anyone seen anything similar?
SQL2000 SP3a I would like to generate an HTML output file as a report which should be possible. After creating the maintenance plan using the wizard I used the created syntax and established a second job which now uses the changed syntax - however the job doesnt run. In the log file it just states the basic sqlmaint switches. Current job syntax - SQL uses build in system account.
I have a maintenance plan on a client's site, there's only one databasein the plan and it's set to backup db and log, this job failsconsistently. The one step in the job contains this line:EXECUTE master.dbo.xp_sqlmaint N'-PlanID392B2334-6800-4655-A6C7-5414D9072505 -WriteHistory -VrfyBackup-BkUpMedia DISK -BkUpDB "C:Program FilesMicrosoft SQLServerMSSQLBACKUPDailyBackups" -DelBkUps 4WEEKS -BkExt "BAK"'Which fails if run in QA with the message:Server: Msg 22029, Level 16, State 1, Line 0sqlmaint.exe failed.I've searched for this error and cannot find anything to help.There's no "-S" in the path, it writes absolutely no history on theplan. I set the plan to write history in the log directory on theserver, it wrote nothing to that directory.The database doesn't get backed up but is able to be backed up manuallyto the path specified in the plan. I also have a separate job with aSQL backup statement in it that works OK so SQLAgent is working.
I am using SQL 2000,everyday we are running database backup plan but it shows a error like "Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." Our database recovery model is also in Full mode.Burt i don't know why its happening with us,before that database maintenance plan was working fine but for last 2 months its not working.
I have a situation where I am running sqlmaint on 10 - 12 databases on the same server. All of them run successfully; However, one returns a failure because of no message. MSDB is not full. There is no messages in the errorlog. And the actual SQLMAINT executed just fine.
I installed a new SQL Server and implementd a maintenance plan for user databases. In doing so, all the jobs seem to work except the Integrity checks. These jobs fail with the following error: sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Has anyone seen this? Or has an idea how to fix it?
