?
Solved

SQL 2008 disk space is full

Posted on 2010-09-21
8
Medium Priority
?
2,265 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

Microservice architecture adoption brings many advantages, but can add intricacy. Selecting the right orchestration tool is most important for business specific needs.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

765 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