No i am not doing this way i just only to show available free space i have tried as you described 5 times i take backup but not released. Reason for decrease fill factor reduce page splits and increase insert update performance.

You did a checkpoint and then a log backup and then did a shrink file? Can you paste your code? Did you get an error?

I dont get any errors steps completes succesfully but virtual log files status doesnt turn 2 to 0 thatswhy shrink logfile not happening and 60Gb logfile doesnt reduce size.

USE master GO ALTER DATABASE XYZ MODIFY FILE ( NAME = N'XYZ_LOG', SIZE = 4096MB, FILEGROWTH = 256MB ) GO after executes this shows below Msg 5039, Level 16, State 1, Line 19 MODIFY FILE failed. Specified size is less than or equal to current size.

after steps dbcc loginfo shows below i could paste few lines cause of char limit 0 2 458752 466944 1833848 2 64 0 0 2 458752 925696 1833846 2 128 0 0 2 712704 1384448 1833853 2 128 0 0 2 2097152 2097152 1833854 2 128 1833701000076936500001 0 2 2097152 4194304 1833855 2 128 1833701000076936500001 0 2 2097152 6291456 1833856 2 128 1833701000076936500001 0 2 2097152 8388608 1833857 2 128 1833701000076936500001 0 2 2097152 10485760 1833858 2 128 1833706000000077700721 0 2 2097152 12582912 1833859 2 128 1833706000000077700721 0 2 2097152 14680064 1833860 2 128 1833706000000077700721

The modify file size fails because the shrink failed. Are you sure you are doing a check point, a backup, a checkpoint, a backup. It's important to that you issue the checkpoint and then perform the backup a few times.

Yes unfortunately i am sure i have checkpoint. I wish i forget checkpoint :)

now i tried any other method, detach db create new one with same name alter datafile from old database and error has been changed :)

USE master GO Checkpoint GO BACKUP DATABASE XYZ TO DISK = 'L:\BACKUP\XYZ\XYZ.bak' with init GO USE XYZ GO DBCC SHRINKFILE (N'XYZ_LOG' , 1) GO USE master GO ALTER DATABASE XYZ MODIFY FILE ( NAME = N'XYZ_LOG', SIZE = 4096MB, FILEGROWTH = 256MB ) GO

Processed 1360856 pages for database 'XYZ', file 'XYZ' on file 1. Processed 7854824 pages for database 'XYZ', file 'XYZ_LOG' on file 1. Processed 99286 pages for database 'XYZ', file 'XYZ_LOG2' on file 1. BACKUP DATABASE successfully processed 9314965 pages in 159.645 seconds (455.843 MB/sec). Cannot shrink log file 2 (XYZ_LOG) because the logical log file located at the end of the file is in use. (1 row affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. MODIFY FILE failed. Specified size is less than or equal to current size.

Lol yes it would be nice if it was just the checkpoint. So it looks like you're doing a full backup - you must be in simple recovery model. Can you switch back to full recovery model and take another full backup, do a checkpoint, and take a log backup? Then see if it will shrink.

I hope i will try now

Also I'm noticing log2 in your comments. Do you have two log files?

yes correct i have 2 log files

but its temporarily i created for testing

it wont stay i will drop when i fix problem

BACKUP DATABASE [XYZ] TO  DISK = N'L:\BACKUP\XYZ\XYZ.bak' WITH NOFORMAT, NOINIT,  NAME = N'XYZ-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--
checkpoint
--
BACKUP LOG [XYZ] TO  DISK = N'L:\BACKUP\XYZ\XYZ.trn' WITH NOFORMAT, NOINIT,  NAME = N'XYZ-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--
USE
XYZ
GO
DBCC SHRINKFILE (N'XYZ_LOG' , 1)
GO
--
USE
master
GO
ALTER DATABASE XYZ MODIFY FILE ( NAME = N'XYZ_LOG', SIZE = 4096MB, FILEGROWTH = 256MB )
GO

i have started i am waiting

unfortunately still same

:(

   60 percent processed.
    70 percent processed.
    80 percent processed.
    90 percent processed.
    100 percent processed.
    Processed 7953125 pages for database 'XYZ', file 'XYZ_LOG' on file 1.
    Processed 115866 pages for database 'XYZ', file 'XYZ_LOG2' on file 1.
    BACKUP LOG successfully processed 8068991 pages in 168.457 seconds (374.214 MB/sec).
    Cannot shrink log file 2 (XYZ_LOG) because of minimum log space required.

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

Will it let you shrink it to any size smaller than 60 GB? what about 58 GB as a test? Can you try puttin git in single user mode in case an active transaction is causing this? Probably not since you say you've detached and reattached it but worth a shot at this point.

i check with dbcc opentran but show no active

lets i will try single user

alter database XYZ set multi_user with rollback immediate

BACKUP DATABASE [XYZ] TO  DISK = N'L:\BACKUP\XYZ\XYZ.bak' WITH NOFORMAT, NOINIT,  NAME = N'XYZ-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--
checkpoint
--
BACKUP LOG [XYZ] TO  DISK = N'L:\BACKUP\XYZ\XYZ.trn' WITH NOFORMAT, NOINIT,  NAME = N'XYZ-Log Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
--
checkpoint
--
USE
XYZ
GO
DBCC SHRINKFILE (N'XYZ_LOG' , 58000) ;
checkpoint;
--ALTER DATABASE [XYZ]  REMOVE FILE [XYZ_LOG2];

but i think now problem is "Cannot shrink log file 2 (XYZ_LOG) because of minimum log space required."

normally i am an oracle dba

somehow we begun give consultant this sql server

from this day i can not smile :)

I can't do Oracle so I feel your pain. Can you remove the second log file? I don't think it's going to help. You can't swap out the first one for the second so I would just drop the second log file.

ok i will i am waiting backup tx backup and shrink still running

after i will drop

nope

still same

10 percent processed.
Processed 1361768 pages for database 'XYZ', file 'XYZ' on file 1.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 8608465 pages for database 'XYZ', file 'XYZ_LOG' on file 1.
Processed 279598 pages for database 'XYZ', file 'XYZ_LOG2' on file 1.
BACKUP DATABASE successfully processed 10249830 pages in 223.470 seconds (358.333 MB/sec).

So that is for DBCC SHRINKFILE (N'XYZ_LOG' , 58000) ?

Don't run ALTER DATABASE XYZ MODIFY FILE ( NAME = N'XYZ_LOG', SIZE = 4096MB, FILEGROWTH = 256MB ) any more.

It will not work until DBCC SHRINKFILE (N'XYZ_LOG' , 1) works

Or at leats DBCC SHRINKFILE (N'XYZ_LOG' , 4095)

not worked

also drop is not works to

somehow this error rootcause i think

Cannot shrink log file 3 (WEBETP_LOG2) because of minimum log space required.

log file 3?

nope 3 means log2

Okay. So now we only have log1 though right?

we want one log file

Let's see if it will shrink at all

my phyiscal files

DBCC SHRINKFILE (N'XYZ_LOG' , 58000) - I think this is a small value - we want something just under 60 GB

Remove F:\LOG\WEBETP_LOG2.ldf

You need to use remove file

on log2

i am trying but gives same error like shrink file

Cannot shrink log file because of minimum log space required.

BACKUP LOG [WEBETP] TO  DISK = N'L:\BACKUP\WEBETP\Webetp.trn' WITH NOFORMAT, NOINIT,  NAME = N'WEBETP-Log Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
use webetp
go
DBCC SHRINKFILE(WEBETP_LOG2,EMPTYFILE) ;
ALTER DATABASE [WEBETP]  REMOVE FILE [WEBETP_LOG2];
DBCC SHRINKFILE (N'WEBETP_LOG2' , 8)

100 percent processed.
Processed 3 pages for database 'WEBETP', file 'WEBETP_LOG' on file 4.
Processed 0 pages for database 'WEBETP', file 'WEBETP_LOG2' on file 4.
BACKUP LOG successfully processed 3 pages in 0.246 seconds (0.065 MB/sec).
Cannot shrink log file 3 (WEBETP_LOG2) because of minimum log space required.

(1 row affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 2, Line 13
The file 'WEBETP_LOG2' cannot be removed because it is not empty.

I see. Also - DBCC SHRINKFILE (N'XYZ_LOG' , 58000) - this is fine. I started thinking that may have been KB instead of MB but it is not.

How big is the second log file?

2.5GB

first logfile now 69GB

:(

Do you have replication or CDC running? Is it in an availability group?

no cdc or replication

SQL only uses one log file so it doesn't make sense to me.

but db has transaction log shipping

but i have destroy

now no any log shipping replication cdc always on

nothing its just standalone

in oracle we have datapump logical backup for export import

maybe i can take logical backup

and create new db

import to new db

can works ?

if you want i can share with you anydesk we can check together maybe you can see from different viewpoint

Okay. Sure. I have just 30 minutes before I have a consulting call though.

I would stop log shipping. It can't be working because you were just in simple recovery.

my anydesk id 748 534 716

i dropped log shipping

Do you have a link you can send me since I don't have it installed? I'm trying to figure out how to use it.

anydesk.com/tr

if you want i can give teamviewer id

I think I figured out anydesk. Just a moment

ok

Nope. Not working for me. I haven't used Team viewer lately either but send me that and I'll try.

googlemeet?

Okay I'm waiting in the google meeting

again thank you so much share time for me

Sure. Also - just thinking - make sure you're in the user database when you do the checkpoint - not master - can you try again just to be sure?

yes user lets i will try again

i tried same :(

:(