Solved

SQL 2008 disk space is full

Posted on 2010-09-21
8
2,261 Views
Last Modified: 2012-06-22
Hi,

I've got a SQL 2008 server that is out of disk space.  I'm assuming this is because a backup has never been done before and the transaction logs are out of control.  The server is running in Win 2k8 R2 on  an ESX host.  

I've just create a new disk for the backup, but when I try to add windows backup roll, it is asking me to bounce the server because there are some MS updates that require it to, so I need to wait until end of school.

My question is - will the backup automatically truncate the transaction logs?

Thanks

Matt
0
Comment
Question by:mattleedc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:arweeks
ID: 33730548
Yes, once it's backed up the logs should be deleted freeing up the disk space.
It's worth checking this is the issuse, you should be able to find the location of the logs in enterprise manager.
You should really setup automated backup to happen regularly.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33730725
@arweeks
the logs should be deleted?

@mattleedc,
Just truncate the log to a reasonable size after the backup
Backup will not automatically truncate your log files.
You can set up a job that will truncate the log at some time intervals, every month or so
0
 

Author Comment

by:mattleedc
ID: 33731088
ewangoya,

How do you truncate them automatically?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33731163

Open Studio Manager,
Expand Management,
Right click on Maintenance Plans and create a new maintenance plan
Specify what you want to do in the maintenance plan
0
 

Author Comment

by:mattleedc
ID: 33731405
OK, I have had a look and all the databases have been backing up to a folder on the same drive as the database itself amongst many other databases.  This has been configured by a tech that has now left the school.  There was no expiry date set up the backup, so this folder has just been expanding until it is full.  When I try to add an expiry date and click OK, I get

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server 'TCDB01'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "D:\Data\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\PaperCut_backup_2010_09_05_170001_4779470.bak:" 112(failed to retrieve text for this error. Reason: 15105). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Does this have something to do with there being 0bytes free on the disk?

If so how safe is it to delete old .bak files from the backup folder?

Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33732126
full and log backup do NOT shrink files automatically!
log backup will only release the space "internally".

to actually reduce file sizes, you need to use
DBCC SHRINKFILE command:
http://technet.microsoft.com/en-us/library/ms189493.aspx

for data files, this will only work if there is free space...
for log files, this might not work "the first time", you might need some turns of transaction log backups + transactions. however, with a full disk, you might use another path:
* ensure all connections to the application/database stop
* ensure you know all files part of the database
* detach the database
* delete the .ldf file(s) of the database
* attach the database without the .ldf files, which should recreate new file(s)

after that, ensure you implement regular transaction log backups (regular = hourly ...) with database in full recovery mode, or change the db into simple recovery mode (ONLY if you don't need to restore into a point in time, ever)
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 33744946
Unless you are using SIMPLE recovery model, you do have to manage your Logs (and even then, they can still grow out of control).

If you have current problems, then you can use SSMS to help reclaim some space.

You really should try to take a full backup first.

If you right click on your database, go into tasks, shrink files you can select the LOG file to shrink. Choose the middle option, shrink with reorganise. It will show you how big it is and how much you *should* be able to recover.

You can also try setting your database to SIMPLE recovery model, take a backup, and then do the shrink file and revert to FULL recovery (again in SSMS, right click the database, go into properties and on the "options" page you can set / change recovery models).

Have a read of : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html

It explains things in more detail, along with using maintenance plans to help manage your database / transaction log.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 34377866
It would be recommended to change the address of the backup as it won't effect the functioning of your server.
0

Featured Post

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
VM backup deduplication is a method of reducing the amount of storage space needed to save VM backups. In most organizations, VMs contain many duplicate copies of data, such as VMs deployed from the same template, VMs with the same OS, or VMs that h…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question