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
Solved

SQL 2008 disk space is full

Posted on 2010-09-21
8
2,259 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
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:ewangoya
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 32

Expert Comment

by:ewangoya
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
Help Required 3 108
Check ALL SP in database make sure there are no errors 17 43
Index and Stats Management-Specific tables 8 22
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Are you looking to recover an email message or a contact you just deleted mistakenly? Or you are searching for a contact that you erased from your MS Outlook ‘Contacts’ folder and now realized that it was important.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

861 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