SQL desperation thread !

Liquid_Entity · 3 Jan 2019 at 12:04

I have a situation on an SQL database. For some reason the databaselog LDF file was set to unrestricted growth. It has now become over 290gb in physical size and the disk drive is 464gb and only has 127gb left.

I can't run a standard DBCC shrink command and no matter what i find on line it doesn't fix it. I can't run a backup of the databasedata MDF file either as it all too big.

Does anyone have any idea how I can get this reduced without causing major issue?

Liquid_Entity · 3 Jan 2019 at 12:38

Msg 3032, Level 16, State 2, Line 1
One or more of the options (no_log) are not supported for this statement. Review the documentation for supported options.

Liquid_Entity · 3 Jan 2019 at 12:53

It's a standalone windows 7 desktop with SQL running so can't allocate more space to it. I have thrown everything I can find online at this and no matter what I do it won't reduce the LDF file, in fact it's getting bigger every time.

Shrink > Files and tried every option in there

Liquid_Entity · 3 Jan 2019 at 13:41

Thanks all, still doesn't want to playball.

Your commands above give me this so I think I need to find that truncate_only is now in this version of SQL - Server 2008 R2

Msg 155, Level 15, State 1, Line 1
'truncate_only' is not a recognized BACKUP option.

Liquid_Entity · 3 Jan 2019 at 13:44

So I just found this script on a google search, seems to do the same thing, executing now...

USE FrontOff;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE FrontOff
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1); -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE FrontOff
SET RECOVERY FULL;
GO

Liquid_Entity · 3 Jan 2019 at 13:46

Still no joy

Cannot shrink log file 2 (frontofflog) because the logical log file located at the end of the file is in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Liquid_Entity · 4 Jan 2019 at 08:59

I work for a well known retailer and this is typical of every store server. It is a standalone Dell PC in a 500gb 2 x HDD in RAID 1 config (awfully unreliable) and is using SQL for the EPOS systems. On the whole every server is fine but this one got set with an 'unrestricted growth' on the log file. It grew so much that we are now in this predicament.

I've managed to claw back 50gb of disk space by turning off growth completely and emptying unused space on the log file however it's still too big to run any kind of backup to then enable the shrink. I think gonna have to fit an external drive to back it up.

Liquid_Entity · 4 Jan 2019 at 15:45

El Pew said:
Why in the name of everything that is holy are you running business critical databases on a 9 year old desktop OS? Let me guess, this is SQL Express as well?

Have you worked in a retail environment before? We're quite modern compared to some

edit: this is a single database for a single store, we have over 1300 stores that are all of the same setup. 1 dell server in raid1 running SQL 2008 R2 (probably the free version of course) for the database

Liquid_Entity · 4 Jan 2019 at 16:24

You're preaching to the choir mate. Sadly nobody outside of IT would understand and getting budget to update is nigh on impossible.

With regards to audit we are rigorously audited and customer data is not held in this database. Only monetary and stock figures.