MickeySoft does it again…How to shrink a log file of SQL Server 2008 when the disk is full..

Hi Guys..

First a spoiler…This is in noway related to a database or what-so-ever..But since MS thinks it looks like a database, they sell it as a database..But to further spare you guys the rant of me and  SQL-Server , I’ll just give this little nugget of knowledge..

The other day the database located on a SQL-Cluster stopped, and I soon found out the dedicated disk where the log files are located of all the databases was full.

Mission: shrink/delete the log file..

The recommended way of doing things according to MS:

1) Allocate more space to the log file location

–> Right. Add more space to a physical disk in a physical server..*FAIL*

2) Backup the database

–> Always a good idea. Where it not for the fact the backup will not run, because the log file  location is full. *FAIL*

3) Shrink the log file.

–> Now we are getting somewhere..Since this is MS, we have to use the GUI (although there seems to be some kind of CLI). Using this I followed the following steps:

* The database is on recovery model: full (which causes the problem in the first place!!)
* I changed this to simple (in the database properties)
* Then did a shrink (tasks-shrink- files -…)

This worked. The log files was a lot smaller then, so I changed the recovery mode back to FULL again. The users could use the database again, and the management went on with what they were doing before. And I’m searching for a way to prevent this from happening in the future.

It’s a bit concise information, but it may help you out of a tight spot if you’re an Oracle (wo)man who also needs to manage wannabe databases.

Good luck!

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.