SQL desperation thread !

Liquid_Entity

Liquid_Entity

Liquid_Entity

Soldato
Joined
11 Apr 2006
Posts
7,186
Location
Earth
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?
 
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.
 
Dug that out from the old braincells and I havent touched a SQL server for nearly 10 years, so not too surprised the syntax is off.

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017
Note

The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see View or Change the Recovery Model of a Database (SQL Server).
Click to expand...
 
If this is a production database, maybe get some more disk space allocated to that partition while you resolve the backup [or lack-of] issues which are stopping the logs from being cleared down.
 
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 said:
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?
Click to expand...

You'll have to set the DB to Simple Recovery first - you'll lose the ability to use/restore from Transaction backups, but you'll lose that hefty LDF file. Then you might want to ensure that your maintenance plans are correct - if you are using Full Recovery and need to be able to restore from transactions, you'll need something to backup to TRN numerous times throughout the day. Not sure what your DB is for though - but to this date, I have only ever seen Ingres DBs have a need for 'transaction' type backups/restores; all the of the SQL DBs I have used (in ~13 years) have never needed the ability to restore from points throughout the day. Then again, I haven't worked on sure critical SQL DBs.

Best of luck :)
 
It probably isnt going to let you shrink it until it knows its been backed up.

If this is running on a desktop, not a server in a datacentre, you could offline the service and clone onto a bigger disk? Or is this one of those applications thats needed 24/7, runs a database but sits on a desktop under the receptionist/security guards desk? :)
 
I think this is the command we used to use:

BACKUP LOG DatabaseName with truncate_only
DBCC SHRINKFILE (LogFileName, 1)

Replace DatabaseName with the DB's name, and the LogFileName with whatever the LDF is called in the DB properties, think it's under Files....
 
I haven't touched SQL for about 3 years, but used it a great deal in a previous job, and the above ought to wipe out the LDF pretty much instantly - it won't matter if it has been backed up or not.

Just be careful though, as this will prevent you from using the transaction restores - assuming you have a need for Full Recovery.
 
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.
 
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
 
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.
 
It's not got enough disk, theres no room to add a new logfile, so I don't see much option apart from offline the database while the maintenance is done.
We're not talking about stopping the SQL Server services, just a offline of the db using the management tools, leaving the master database up and running.
 
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.
 
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?
Click to expand...
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
 
You must log in or register to reply here.
Back
Top Bottom