Solved

SQL 2008 disk space is full

Posted on 2010-09-21
8
2,262 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Looking for good Imaging Software 6 87
Subquery in the where statement always shows up null in the result 5 51
Many to one in one row 2 46
denied execute as 13 55
This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 walk an individual through the process of installing of Data Protection Manager on a server running Windows Server 2012 R2, including the prerequisites. Microsoft .Net 3.5 is required. To install this feature, go to Server Manager…

738 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