Solved

SQL 2008 disk space is full

Posted on 2010-09-21
8
2,257 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CloudBerry to Amazon for VM 3 71
Union 2 queries to a cte (temp table perhaps) 9 36
convert null in sql server 12 34
Present Absent from working date rage 11 21
How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
A Bare Metal Image backup allows for the restore of an entire system to a similar or dissimilar hardware. They are highly useful for migrations and disaster recovery. Bare Metal Image backups support Full and Incremental backups. Differential backup…
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
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…

773 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