1. 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).
2. Apr 12, 2016 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.
3. Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services. You can help protect yourself from scammers by verifying that the contact is a Microsoft Agent or Microsoft Employee and that the phone number is an official Microsoft global customer service number.

Introduction

The UNIX/Linux 'find' command can be used to locate any external Oracle files including database files (dbf), alert log files, and all trace and dump files. The 'which' command can also find files, and we can easily find the location of the SQL.Plus executable.

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:

1. 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:

2. 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:

3. 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…

4. On the General tab, enter a descriptive name and optionally a description for the job:

5. On Steps tab, go to New…:

6. 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’, 720

   To 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 look

   Note: 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.

7. 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.

8. Next tab is Schedules. Under this tab we can set up when will this job work.

   Go to New…:

9. 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.

10. 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:

11. 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.

1. When connected to the server, expand it and the Management folder after. Then right click on Maintenance Plans and click on Maintenance Plan Wizard

2. In the Maintenance Plan Wizard, click Next

3. Enter a descriptive name and, optionally, a description, and in the bottom right click on Change…

4. 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.

5. Check if everything is correct before clicking on Next:

6. In the next window, check Maintenance Cleanup Task and click on Next:

7. In the following window, because we have only one task, there is no ordering, so we proceed by clicking on Next:

8. In the following window:

   1. Under Delete files of the following type: we select Backup files by clicking on the radio button.

   2. 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

   3. 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:

9. 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.

10. 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

There's no doubt that log file will become larger and larger with the growth of SQL Server database file. However, it will influence the running speed of computer and occupy more and more space. This is not what we expect to see. So sometimes, it is necessary to shrink even delete SQL Server log file.

The following introduced ways will be applied to SQL Server 2014/2012 to delete log file with SQL Server Management Studio or Transact – SQL.

Way 1: Delete SQL Server Log File in SQL Server Management Studio

Step 1: Shrink Server log file

1. Login in SQL Server Management Studio. In Object Explorer, expand that instance that is connected to SQL Server.

2. Unfold Databases and then right-click the database that you want to shrink.

3. Turn to Tasks - Shrink, and then click Files.

4. Select the file type and file name.

Generally, log file will be shrunk after you click OK now. But if you want to set it in more detailed, options below can be selected.

Option 1: Select Release unused space check box.

Option 2: Select Reorganize files before releasing unused space check box.

If this is selected, the Shrink file to value must be specified. By default, the option is cleared.

Option 3: Enter the maximum percentage of free space to be left in the database file after the database has been shrunk. The value can be between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.

But if we prepare to delete data, the primary data file cannot be made smaller than the size of the primary file in the model database.

Option 4: Select the Empty file by migrating the data to other files in the same filegroup check box.

5. Click OK.

Step 2: Delete SQL Server log file

1. In Object Explorer, make instance connected to SQL Server Database Engine and then expand that instance.

2. Expand Databases, right-click it from which to delete the file, and then click Properties.

Delete Sql Dump Files Online

3. Select the Files page. In the Database files grid, select the file to delete and then click Remove.

4. Click OK.

Way 2: Delete SQL Server Log File with Transact-SQL

If you are familiar with Transact-SQL, follow this way to work for SQL Server database or log file deletion.

Step 1: Empty SQL log file

Memory Dump Files Delete

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 8);
GO

This example uses DBCC SHRINKFILE to shrink the size of a data file named DataFile1 in the UserDB database to 8 MB.

Step 2: Delete SQL Server log file

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example removes the file test1dat4.

USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

Comparing Way 1 to Way 2, the first way is undoubtedly easier for SQL Server new users. But command can work fast. However, it will be easier to controll failure rate when deleting SQL Server file using SQL Server Management Studio. In a word, they can shrink and then delete database file or log file. The difference is just the implementation.

Related Articles: