Sql-server – dump files maxing C drive with corrupt mdf

sql server

I have some databases with corrupt mdf and my C dive on the SQL server is getting maxed out with error dump files,

I am not asking how to troubleshoot why these files are being created, I already know what the problem is, why they are being created and what I need to do to correct the underlying issue.

what I would like to know is how to prevent or limit the creation and retention of these dump files so they don't max out my C drive while I am doing repairs

thanks

Best Answer

You should limit your error log size. According to support, you can accomplish this by executing on the target instance the following script:

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;
GO

Another thing you might want to check is where you have your TempDB stored. If it's on C drive, than perhaps you should move it to another drive.

If you'te getting memory minidumps due to crashes of the database engine, than you should follow Dan Guzman's suggestion and create a script to move or delete them.

Related Solutions

Sql-server – Dealing with large log files

If the database is truly static in nature, putting it into read-only mode would prevent log growth. The queries that are causing the growth would fail however, so this might not actually be what you are looking for.

Put the database into simple recovery mode. That way the transaction logs will be routinely truncated. When in full mode, your logs will continue to grow until they are backed up.

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE WITH ROLLBACK IMMEDIATE
GO

Sql-server – What Are the Log File Names

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }

Best Answer

Related Solutions

Sql-server – Dealing with large log files

Sql-server – What Are the Log File Names

Related Question