Trying to create a new job from sql server 2005 Management Studio. The nifty wizard under Management provides options to create backups, and clean history, but I cant seem to find out how to create a job to purge old backups.
Looking at the commnand line Sql Server Agent Job shows:
It seems there are at least 3 ways to back up my databases in SQL2000
1) Veritas BackupExec 2) SQL Sever Backup 3) SQL Maintenance Plan
I don't quite see what the difference is between 2 & 3.
I'm currently using Veritas to do a full backup to tape, and a separate full backup to disk via a Maint Plan (a complete backup & trans log backup). It looks as though I've finally got my trans log to stop getting bigger (it started a new one) .... can I delete the huge one I don't need anymore. Does "truncate" mean the log is deleted, or just inactive as a new log gets created. I been reading up on this, but apparently it hasn't sunk in yet.
Please give me any long winded, but clear help you might have
We are running SQL Server 2000 and making full database backups using a maintenance plan. The transaction logs are being backed up via a separate plan.
However, the transaction log backups aren't purging but the database ones are. They are both run under the same id and both write to the same directory. Old text reports also get purged fine. I've got 'Delete older than 1 week' ticked for everything.
Can anyone think of anything else to check ? Thanks
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 !
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!!
(1) Maint plan is setup to backup the databases that users actually use to update company information (2) diffrent databases. (2) maint plan backs up the master database this is setup to run by itself. (3) maint plan is setup to backup the model - msdb databases together.
I setup the email notification option to inform me if my backups were successfull. below is an example that I'm recieving everymorning on every database. Something about the single user mode is causing the problem. can somone explain this to? Maybe give me a direction to follow to correct this problem. Below is an example error message that I'm recieving.
I can backup any database by right clicking selecting all tasks then backup database and I get a complete backup to problem. Why is my Maint. Plan not going through. the rebuilding of indexs is successful.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'My Server name' as 'My_Domain_NameMy_SQL_Admin_Account (trusted) Starting maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:00 PM [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL Server Driver][SQL Server]Database state cannot be changed while other users are using the database 'Leo' [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed. [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed. [1] Database Leo: Check Data and Index Linkage... [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to be in single user mode. ** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database SlcBom: Check Data and Index Linkage...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'LEO-SLCBOM Full Nightly' on 4/13/2004 11:15:01 PM SQLMAINT.EXE Process Exit Code: 1 (Failed)
I have a maint plan for a single database to backup the database, thats it, very simple. It fails every time.
When I do a view history of the maint plan, it is empty as if it has never run. When I do a view history of the job, it shows it has failed after 13 seconds. With this message. "Executed as user: sa. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
Any help would be great as I cannot get any backups to run. I am worried to say the least right now.
I am creating a sql 2005 maintenance plan backup job. How can i specify 'write log files to some particular drive'. I am new to sql server 2005 Please advice
I have just done an in-place upgrade of a sql2000 SP4 instance to SQL 2005. After the upgrade I applied SP2 and cumulative hotfix rollup 4, plus the SQL 2000 DTS designer components for backward compatibility.
all went well with the upgrade.
I then migrated the maintenance plan for the system databases to 2005 SSIS version, and find it includes tempdb! This causes the jobs to fail.
Have I come across anther maintenance plan issue or have I done something wrong? Should I have applied GDR2 before the cumulative roll up?
Dear All, With SQL Server 7 SP2 Maint. Plan, Integrity, Attempt to repair minor problems - there was a feature that sometimes left databases in single user mode.
I have recently updated to SQL Server 2000 with a production database (option set OFF) - does this feature still apply to SQL Server 2000?
I created a single step plan that does integrity checks. It fails with the error below. I created a new connection using our clusters virtual sql name.
Executed as user: ACCTCOMsqlagent. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:10:06 PM Error: 2008-02-15 13:10:49.02 Code: 0xC00291EC Source: {0CF32F3D-A8D1-492A-9C0F-AD4E0FC67D14} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-15 13:10:49.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-02-15 13:11:31.26 Code: 0xC0024104 Source: Check Database Integrity Ta... The package execution fa... The step failed.
The agent and db are running under domain accounts and the job is owned by sa.
I've set up a maintenance plan to back up the database at a certain time of day. But it never seems to work, which I don't understand. I'm the creator of the database as well as the DBA with all rights. I can create a manual backup, and I have full access to the default backup directory on SQL server. I've also tried scheduling automatic backups, but it won't work from there either. I've tried pretty much everything to get this to work. I've checked the Maintenance Plan History for that particular Maintanance Plan, and it shows no evidence of failure--or any history of activity at all--nothing. It just ignores the maintenance plan entirely. What could be the problem here?
Hi, I have a scheduled maintenance plan that backs up my database (SQL 2000). The db is 108 Meg. Usually the backup files are around 89 Meg and the transaction log backups are around 4.5 Megs.
The problem is that every once a while the backup fails with the following error:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL Server Driver][SQL Server]Write on 'C:Program FilesMicrosoft SQL ServerMSSQLBACKUPCRM_db_200312230100.BAK' failed, status = 112. See the SQL Server error log for more details. [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
What usually happens is that the last backup that has failed has a size of 9 or 8 Gigs!
My guess is that a backup file gets corrupt (the size increases to 8 Gigs) and the server runs out of space and won’t be able to do more backups. However I can’t figure out why the backup file size increases to and gets corrupt.
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 an "Issues" table for my technicians. An issue can be on "hold"or "assigned".I want to get a count for each tech with a column showing number ofissues on hold and a column for number of issues assigned. It wouldlook like this --Tech Num_Assigned Num_On_HoldFred 3 10Carol 6 7I can get each column separately, but I want both in the same answertable!Is that too much to ask??? :)
Hi,Don't worry about the vars, they are defined,the following line give me an err of "Incorrect syntax near '.'."Goal: to rename nonstardard column name.EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')','COLUMN';Thanks.
I want it to either display the label (All) or the name of the month. I get an error when All is the parameter selected but not when any month is selected. If I removed the MonthName() function, I don't get an error but I also don't get the month name.
Hello, I see the following in connection strings all the time: providerName="System.Data.SqlClient"/> For example:<connectionStrings><add name="classifiedsConnection" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|classifiedsdb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/></connectionStrings> What is providerName="System.Data.SqlClient" ? What is it used for?Thanks, Jim
I am a non-technician and have had an .asp site with an SQL database on a shared server for several years. I now need to move to a new shared server and want to understand as much as possible and to be able to continue accessing my data directly. I have used Enterprise Manager in the past, withut really even knowing what it was or what it was a part of, to pull lists, etc. I am now using Vista and the new hosting company says I can download Enterprise Manager from Microsoft.com. I tried and found only the ability to download SQL Server 2005 Express. This seemed to imply I would get Enterprise Manager as a result. I attempted to download SQL Server 2005 Express, following all the instructions. When it said I had sucessfully finished, I went to All Programs to find it and found "MS SQL Server 2005" listed. Under the lising I found "Configuration Tools" .
Under Configuration Tools I find three more options: "SQL ServerConfiguration Manager", SQL Error and Usage Reporting" and SQL Server Surface Area Configuration" These are all Greek to me and I am uncertain if I even got the "SQL Server 2005 Express" program downloaded.
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 big database and for each table i have Active field (N/Y) and i wanna delete all record the active N. how i can do that without delete from each tables and looking to details tables before. our database have a complex relations. please any solution.
All, I records that have two columns, date col and time col.
I am trying to write a SQL statement to extract the rows that fall within a range of time. Eg current date/time + 10 hours and current date/time - 10 hours.
I'm assuming that the .ldf file houses all the transaction logs, well mine is getting quite large (like 6 times the size of my .mdf) and I'm wondering if there is a way to purge logs back to a specific date. Can anyone give me some direction? Thanks
Hi all I have table with about 67 million records that are marked for deletion.
I know that I can
DELETE from table WHERE ToBeDeleted='t'
But this may be too big a task for the server considering the amount of data to delete at once. And if it runs out of resources and errors then nothing gets deleted....
Is there a way to segmant or loop so i can delete like 100k records at a time?
A client of mine just asked about Archiving and Purging their data (based on different time constraints). For isntance, certain data would get archived after 3 years over to a different database, others purged all together. My thoughts off the bat are store procedures run on a job schedule.
Greetings, I have a SQL Server 2005 database which is populated with test data. I need to copy this database to a new instance and then purge the copied database of it's contents for the next round of testing. I know there exists a Copy Database Wizard w/in the SQL Server Management Studio; I'm assuming I need to perform this first, then purge the existing data w/in the copy. The Copy operation looks pretty straight forward, but I haven't a clue on how to perform the purge. Can someone help? Regards, Loopsludge
Suppose I am appending to my transaction log dump device every half hour,thus adding 48 log dumps per day. How can I purge my transaction dump device to only keep the last 1 week's worth of these logs. I do not wanted to issue an INIT in the command, because this will wipe it completely. I noticed an EXPIREDATE and RETAINDAYS parameter for the DUMP command. Can I use these to selectively purge the backup device or will these allow the device to be wiped clean also?
Anyone have anything hints on getting the option of DELBKUP to work. I have a maintenance job that backs up the database. Also set to delete backups older than 1 day. This doesn't seem to be working.
