Files you want to delete are located in bdump,udump and cdump directory. Path to those dir's can be viewed by typing sho parameter dump in SQLPlus. Dumps indicate that something is not working as expected in SQL Server and forcing SQL to either terminate the process or terminate itself. Each dump had three files generated. Out of them text and log were readable, but mdmp needs a special tool (windows debugger) to read it. Here is what I saw in the files which were readable. SQL CREATE DIRECTORY DUMPFILES1 AS '/usr/apps/dumpfiles1'; Then, a user on a UNIX-based client system using csh can assign the value DUMPFILES1 to the environment variable DATAPUMPDIR. The DIRECTORY parameter can then be omitted from the command line. The dump file employees.dmp, and the log file export.log, are written to '/usr/apps. Hi: When I run disk cleanup in Vista, it comes back with lots of different temporary files that I can delete (it 'ticks' them by default).
Introduction
While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design. Automated backup and restore strategies ensure the recovery plan is successful. However, strategies relying on frequent backups, although part of a sound strategy, can start causing issues when available storage space becomes limited. Old database backups are in many cases unnecessary, and it is often prudent to delete those backups to increase available storage. This can be done manually or automatically. There are several options to delete old backup files automatically in SQL Server:
- Delete old database backup files automatically in SQL Server using a SQL Server Agent Job
- Delete old database backup files automatically in SQL Server using a SQL Server Maintenance plan
Delete old database backup files automatically in SQL Server using SQL Server Agent:
For this option, the SQL Server Agent service must be installed and running. It requires basic knowledge and understanding of Transact-SQL (T-SQL) scripting, but the process is then entirely automated, so there is no further input from the user needed. It is necessary to create a stored procedure first upon which the SQL Server Agent job we make would call to execution. The advantage of this is the ability to use the same stored procedure across different jobs with different input parameters. To create a SQL Server Agent scheduled to delete old backup files, we must do the following steps:
- SQL Server Agent service must be running. In the Object Explorer and check the icon beside SQL Server Agent. Should the message “Agent XPs disabled” is shown beside it, go to Control Panel/System and Security/Administrative Tools and run Services (the exact location may vary due to different operating systems). Locate the SQL Server Agent service for the corresponding SQL Server instance, select it and click on Start in the top left of the window, or simply right click on it and select Start. If the service is already running, you can skip this step:
- Create a user stored procedure which will use the input from the SQL Server Agent scheduled job to delete old backup files. Right click on the database upon which we want to act and select New Query:In the new query window enter the following T-SQL:As it can be seen in this query, an extended system stored procedure xp_delete_file is being used. It reads the file header to check what type of file it is and will only delete certain types based on the input parameters we choose. After running the query, we should end up with a stored procedure:
- After creating the stored procedure, we need to create a scheduled job with SQL Server Agent which will use the stored procedure with our parameters to delete the old backup files.To do that, right click on SQL Server Agent and select New then Job…
- On the General tab, enter a descriptive name and optionally a description for the job:
- On Steps tab, go to New…:
- In the New Job Step window, under General tab, enter a descriptive job name, under Database select the database which we want the job to work on and under Command: insert the following line:usp_DeleteOldBackupFiles ‘D:MSSQL_DBBackups’, ‘bak’, 720To explain the line above:usp_DeleteOldBackupFiles – calls the stored procedure we created earlier‘D:MSSQL_DBBackups’ – the first parameter tells the stored procedure where to look
‘bak’ – the second parameter tells what extension or file type to lookNote: for the extension, do not use dot before the extension as the xp_delete_file already takes that into account. ‘.bak’ is incorrect use as opposed to ‘bak’, which is correct use.720 – the third parameter which tells the stored procedure the number of hours a backup file must be older than to get deleted. - Under Advanced tab, choose what will be done after this step is done successfully. Seeing that this is the only step, we can select ‘Quit the job reporting success’. Here we can also set how many times the step will retry as well as time interval between tries in minutes.
- Next tab is Schedules. Under this tab we can set up when will this job work.Go to New…:
- In the new window, enter a descriptive name for the schedule. Also, for Schedule Type check that it is set to Recurring for proper scheduled operation. After that, we can set up the schedule using the options below:The rest of the tabs is optional in this use case, though, we could use Notifications tab to set up email notifications when the job is completed.
- After completing the previous step, our Job is created. It will run according to schedule, deleting old database backup files as set in the job itself. SQL Server Management Studio by default doesn’t show any real-time notifications when the job is performed. If we want to check the job history, we can right click on the job and select View History:
- In the new window we can see the history for the selected job which executed successfully:
Delete old database backup files automatically in SQL Server using SQL Server Maintenance plan:
SQL Server Maintenance plans are another way of deleting old backup files by using the cleanup task.
Sql Server Delete File
- When connected to the server, expand it and the Management folder after. Then right click on Maintenance Plans and click on Maintenance Plan Wizard
- In the Maintenance Plan Wizard, click Next
- Enter a descriptive name and, optionally, a description, and in the bottom right click on Change…
- When New Job Schedule window pops up, check if the Schedule type is set to Recurring. After that, we can set up the schedule using the options below. After that, click OK.
- Check if everything is correct before clicking on Next:
- In the next window, check Maintenance Cleanup Task and click on Next:
- In the following window, because we have only one task, there is no ordering, so we proceed by clicking on Next:
- In the following window:
- Under Delete files of the following type: we select Backup files by clicking on the radio button.
- Under File location: we select Search folder and delete files based on an extension. Under that, we specify where to search for the folders and what type of an extension to look for. Also, we can check the Include first-level subfolders option if the backups are stored in separate subfolders.Note: The file extension we input must not contain dot (‘.’) – ‘.bak’ is incorrect, ‘bak’ is correct
- Under File age: we check the option Delete files based on the age of the file at task run time and specify the age of the files below
After checking that everything is correct, we proceed by clicking Next: - In the next window, we may select that a report is written and/or mailed to the email address we input every time the Maintenance plan runs.
- In the next window we press Finish to complete the creation of our Maintenance Plan. After that we can check under Management → Maintenance Plans for our newly created plan:
Downloads
Please download the script(s) associated with this article on our GitHub repository
Related posts:
![Sql dump file reader Sql dump file reader](/uploads/1/1/8/5/118526406/275613056.jpg)
By: Sergey Gigoyan | Updated: 2015-11-18 | Comments (8) | Related: More >Database Administration
Problem
SQL Server allows to use more than one transaction log file, but the question arises whether it is necessaryand what is the benefit of having multiple transaction log files. There is a misconception among some developers that having multipletransaction log files can increase performance because SQL Server can use them in parallel. SQL Server usesonly one transaction log file at the moment and there is no parallelism in this case. Sometimes more than one log file can beneeded for the purpose of troubleshooting. So, normally there is no necessity to have more than one log file.Let’s consider a case, when our database has more than one log file and we should retain only one.This tip aims to describe how to remove unnecessary log files correctly and retain only one.
Solution
Before starting to illustrate how to remove unnecessary log files, let's briefly describe how SQL Server workswith log files: when database has more than one log file, SQL Server keeps writing to the first one until it's full,then switches to the second and so on. After the last log file becomes full SQL Server returns back to the first one and the cycle continues.However, as we mentioned, sometimes more than one log file may be required. For example when the disk, where the log file is locatedbecomes full and we need to create the second log file in another location, but after troubleshooting the problem,we should delete the second log file, because there is no use to have more than one log file.Now, let's assume that we have a database with two log files, and our task is to remove the second one.The following script creates the TestDB database with two log files and TestTable table. To run it by yourself, you will need to replace“D:SQL Data” with an existing folder path.
Now let's understand the transaction log physical structure: Internally transaction log file consist ofVirtual Log Files (VLF), which are the unit of management in the transaction log file. It means that when database engine grows or shrinksthe log file, it does that with complete VLFs (for example it can't shrink half ofthe VLF). The size of virtual log files as well astheir number in the physical log file isn't fixed and is managed dynamically bythe database engine. To monitorthe transaction log file internallywe use 'DBCC LOGINFO' command, which provides information about virtual log files. In the script below we used this command for our database:
And the result is the following:
FileID is log file IDs for our database. Status indicates is VLF reusable ornot (possible values: 0 - yes, 2 -no). As we can see there is only one VLF withStatus=2. Now when we will insert data into the TestTable and monitor how log files are growing:
With this example we can see that both log files grew and now there are VLFs with 'Status=2' in the second log file (FileID=3) also:
Now we need to remove TestDB_log2.ldf file. Note, that we can remove only the secondary log files. Removingthe primary log file is not allowed by SQL Server.Each database has only one primary log file and the first log file which is created inthe database creation script is considered the primary.If we try to remove the second log file:
We will receive the following message:
Sql Server Dump File
We can remove the transaction log file only when it's empty, therefore wefirst need to empty it.To do that, we should back up the transaction log. Since our 'TestDB' database isnewly created and there are no full backups, we needto issue a full database backup for the TestDB database, after which we canissue a transaction log backup:
The transaction log backup truncates the log file (there are some exceptions, which are out of scope of thistip). Log truncation deletes inactive virtual log files fromthe start of the logical log and releases space in the log file. However, truncation does not reduce the size of a physical log file. It only frees space in it,which can be reused. Let’s run 'DBCC LOGINFO' again:
As we can see there are no virtual log files in the 'TestDB_log2.ldf' file with Status=2 and now our log file is empty and ready for removal:
The removal is successful.
However when we check log the information again, we will see that the logical log file still exists:
Win Dump File Delete
If we do another log backup, the file will be deleted:
Next Steps
- Keep this tip in mind to determine transaction log usage and how to remove anunneeded log file.
- Check out these resources:
Last Updated: 2015-11-18
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.
View all my tips
View all my tips