How to delete transaction logs without accessing the Database?
-
Hi,
I am running into a problem where I am unable to connect to the database (no physical space available on the hard disk, when I try to expand the databases; system stops responding.) and I want to delete/truncate the transaction logs. Can any of you guide me how to delete the transaction logs without accessing the database.
Please let me know if you want any other information.
Thanks in advance.
-
You can't. Add datafiles on other drives.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
How could I do this..?
When I run the following command:
=======
USE master
GO
ALTER DATABASE CoreDB
MODIFY FILE (NAME = CoreDB_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\CoreDB_log.ldf')
=======
I am not able to do this as again I am accessing the database.
Any other suggestions are welcome
Thanks.
-
sanjeev_krs2004 (12/15/2008)
HCan any of you guide me how to delete the transaction logs without accessing the database.Never ever delete transaction logs. It's the fastest way to get a corrupt and unusable database.
Are there other files you can move off the drive to make space?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Unfortunately no,
there is not even a single file that I can move to any other place.
-
Might try using the DAC to add a new log file on a separate disk (assuming you have one) until you can figure out what is making it grow so much.
Change , SIZE, and FILEGROWTH to fit your needs, and the syntax should be something like:
ALTER DATABASE [ ] ADD LOG FILE
( NAME = N'NEW_LOG', FILENAME = N' \NEW_LOG.ldf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
The Redneck DBA
-
Checkout this application, it has a great disk cleaner, maybe it can give you just enough spare place to connect :
http://www.auslogics.com/en/software/boost-speed/download
Another extreme solution I have found is to go to the Windows folder and move (or delete) all the windows updates unsinstall folders (hidden shown in blue).
That can save you a couple 100 mbs in a hurry. You can always move them back if you need to uninstall something.
Also if you can't do anything but dump the log, at least take a full db backup and make sure it's valid (restore on a test server). That way, the worst case scenario is that you lose 10 minutes of data... make sure you get approval on this before going forward.
-
Another option is delete all the files in this folder :
C:\WINDOWS\Prefetch
Also
C:\WINDOWS\Downloaded Program Files
-
Another thing to check is the size of the TempDB. If SQL 2005, then you can connect to the server with the DAC and all you need to do is to stop the SQL Server Service and this will reset the size of the TempDB database and may give you some breathing room...however this will only be temporary depending on your systems usage of TempDB.
Another quickie is to move (or delete) some of the older SQL log and trace files.
Otherwise, if your disk is full, and you can't find any other room by removing temp files, uninstalling unnecessary applications, features, sample databases (i.e. pubs and northwind...and yes I have seen them on a production server) then your only option is to get more disk.
Good Luck!
SJ
-
Why not do the following I just tested it.
From Management Studio..
Take SQL Server Offline by stopping the service.
Go to the folder that that has both the database and mdf and ldf files. Move these files into a location where there is disk space.
Go back to Management Studio restart the service.
On the database node in Management Studio right click and refresh the list (the database node will disappear).
Move the mdf and ldf files to a more suitable location (where disk space is avalable).
Now re-attach the those two files.
Walla!
-
That's the recommended approach, however he can't even get that far!
-
Ninja's_RGR'us (12/16/2008)
That's the recommended approach, however he can't even get that far!If Grasshopper cannot take SQL-Server offline through management studio then why not take SQL-Server offline via the Services Console in Control Panel/Administrative Tools/Services
A revised approach
Take SQL Server Offline by stopping the service.
Go to the folder that that has both the database and mdf and ldf files. Move these files into a location where there is disk space.
From Management Studio..
On the database node in Management Studio right click and refresh the list (the database node will disappear).
Move the mdf and ldf files to a more suitable location (where disk space is avalable).
Now re-attach the those two files.
Walla!
-
That's brilliant... since the db are already offline anyways, it's not a huge step to shut down the server entirely.
Obivoulsy make sure you get a higher power to approve that move along with all possible consequence for other applications.
-
Thanks a ton you guys....!!!
The only solution I could find is that add more SPACE. I am very thankful to you all who responded in time and have taught me few more things.
Take care.
-
I'm just curious, where did you find the required space? I'D like to know, just in case I run into the same problem.
Accepting all invitesViewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply