Go to Main page..

SUSDB and size of the log file (Microsoft SQL Server





SUSDB and log file size [new]
Good day!
On the WSUS server, the log file has grown: 3 GB - this file and 19 GB transaction file!
Standard means can not reduce its size.
How can you reduce it and why has it grown so much?
Re: SUSDB and log file size [new]
saszay

Grew up because you do not serve, to reduce only standard means and it is possible.
And what do you mean by standard means?
Re: SUSDB and log file size [new]
saszay
Standard means can not reduce its size.

Standard is what?

saszay
How can you reduce it and why has it grown so much?

Learn first
- recovery model
- learn log_reuse_wait from sys.databases
Re: SUSDB and log file size [new]
Standard tools:
 log SUSDB with no_log backup log SUSDB with no_log
 shrinkdatabase ( SUSDB , 10 , notruncate ) dbcc shrinkdatabase ( SUSDB , 10 , notruncate )
 shrinkdatabase ( SUSDB , 10 , truncateonly ) dbcc shrinkdatabase ( SUSDB , 10 , truncateonly )
 shrinkdatabase ( SUSDB ) dbcc shrinkdatabase ( SUSDB )
 database SUSDB SET RECOVERY SIMPLE alter database SUSDB SET RECOVERY SIMPLE


log_reuse_wait = 6 for this base
Re: SUSDB and log file size [new]
saszay
Standard tools:
 log SUSDB with no_log backup log SUSDB with no_log
 shrinkdatabase ( SUSDB , 10 , notruncate ) dbcc shrinkdatabase ( SUSDB , 10 , notruncate )
 shrinkdatabase ( SUSDB , 10 , truncateonly ) dbcc shrinkdatabase ( SUSDB , 10 , truncateonly )
 shrinkdatabase ( SUSDB ) dbcc shrinkdatabase ( SUSDB )
 database SUSDB SET RECOVERY SIMPLE alter database SUSDB SET RECOVERY SIMPLE


log_reuse_wait = 6 for this base


6 means that your replication is configured, and judging by the growth of the log does not work.
Treat replication, reduce the log.
Re: SUSDB and log file size [new]
I do not have replication on this base (there is on the next).
How can it be treated?
Re: SUSDB and log file size [new]
saszay
I do not have replication on this base (there is on the next).
How can it be treated?


 Sp_removedbreplication
Re: SUSDB and log file size [new]
 Sp_removedbreplication

Did not help :(
Re: SUSDB and log file size [new]
saszay
 Sp_removedbreplication

Did not help :(


I hope the base, indicated the right?
Re: SUSDB and log file size [new]
Yes. It was executed without errors, but the file did not decrease.
Re: SUSDB and log file size [new]
saszay
Yes. It was executed without errors, but the file did not decrease.

And this command should not reduce the file.

What will log_reuse_wait from sys.databases now show?
Re: SUSDB and log file size [new]
Now "3" shows
Re: SUSDB and log file size [new]
saszay
Now "3" shows

Open a help and read what it means, what's stopping you?
Re: SUSDB and log file size [new]
saszay
Now "3" shows

which was required to be added - ACTIVE_BACKUP_OR_RESTORE
try shred.
Re: SUSDB and log file size [new]
btw
BACKUP LOG WITH NO_LOG
No longer available. The transaction log is automatically truncated when the database is used. If you need to remove the backup model from the database, switch to the simple recovery model.
Re: SUSDB and log file size [new]
Thanks, helped !!!
Ps. but I still did not understand why the base was in such a state ...
Re: SUSDB and log file size [new]
saszay
Thanks, helped !!!
Ps. but I still did not understand why the base was in such a state ...


Someone, when it made a replication on this base. Then either crookedly deleted, or some other failure. Visually, you did not see the replica, but it was. How about a gopher ...
Re: SUSDB and log file size [new]
slightly different situation, although maybe one to one

I tried to compress the log file, no changes, although it says that 99% of the log is free, the worst thing is that the base takes only 8 MB, and the magazine is 130 GB.

looked for this base
[log_reuse_wait] - 0
[log_reuse_wait_desc] - NOTHING

Attached file Size - 106Kb
Re: SUSDB and log file size [new]
And what is the initial size of the log file?
Re: SUSDB and log file size [new]
Ozerov,

It seems to understand what you are hinting at, indeed, the initial size is great, because This is not the original database, but a copy of the worker with which real problems occur. Using a copy, I wanted to prepare a way to solve the problem. Probably you should light the real base, it has different parameters.

I attach the screen copy.

Attached file Size - 119Kb
Re: SUSDB and log file size [new]
Ozerov,

I tried to reduce the initial size of the log file to 1 GB, it does not allow, or rather, the parameter accepts, but reduced the file only to 16 GB, then checked the free space, writes that it is 99%.
Re: SUSDB and log file size [new]
goalinternet
Ozerov,

I tried to reduce the initial size of the log file to 1 GB, it does not allow, or rather, the parameter accepts, but reduced the file only to 16 GB, then checked the free space, writes that it is 99%.

well then did shrink?
Re: SUSDB and log file size [new]
Ozerov,

shrink base or file?
DBCC SHRINKFILE?

did in SQL Server Management Studio using gui file compression did not help, although this is probably not the case, now I’ll try DBCC SHRINKFILE and unsubscribe
Re: SUSDB and log file size [new]
so look at the sizes of the VLFs.
sort of like 8GB should be
(with your not sickly initial size),
You now go and sit in the second.
let's result
 loginfo dbcc loginfo
Re: SUSDB and log file size [new]
nezhadnye_my
so look at the sizes of the VLFs.
sort of like 8GB should be
(with your not sickly initial size),
You now go and sit in the second.
let's result
 loginfo dbcc loginfo


dbcc loginfo
Fileid FileSize Startoffset FSeqNo Status Parity CreateLSN
2 8332312576 8192 54 2 64 0
2 8332312576 8332320768 0 0 0 0
Re: SUSDB and log file size [new]
Ozerov,

tried all three options
DBCC SHRINKFILE (DispatcherService_log, EMPTYFILE);
DBCC SHRINKFILE (DispatcherService_log, 1024);
DBCC SHRINKFILE (DispatcherService_log, 1024, TRUNCATEONLY);

no shrink log file
Re: SUSDB and log file size [new]
goalinternet
Ozerov,

tried all three options
DBCC SHRINKFILE (DispatcherService_log, EMPTYFILE);
DBCC SHRINKFILE (DispatcherService_log, 1024);
DBCC SHRINKFILE (DispatcherService_log, 1024, TRUNCATEONLY);

no shrink log file

And in the initial size now what is it worth?
Re: SUSDB and log file size [new]
Ozerov,

in the initial costs 15893 MB and does not allow to reduce, rather it allows but does not reduce as a result
Re: SUSDB and log file size [new]
Well, people, as he had expected, 2 VLF and both 8 gig,
less than 2 can not be.
and by 8 they ordered it themselves with their initial size.
everything, nowhere to reduce.
Re: SUSDB and log file size [new]
nezhadnye_my,

but there were 130 gigs, and jumped to 16, why not continue if the free space is more than 99 percent?
Re: SUSDB and log file size [new]
I will offer muck, but I see no other way out.
if you need a smaller log,
need to get the second log file (already the right size, how you want to have it)
transfer the base to full,
small transactions to score the first log and start to score the second.
once transferred to the second log, transfer to simpl,
make checkpoint
Boot to the first log file.
Re: SUSDB and log file size [new]
goalinternet
nezhadnye_my,

but there were 130 gigs, and jumped to 16, why not continue if the free space is more than 99 percent?


I'll tell you right now.
detailed in a valuable blog

when ordered the initial size of the magazine 130 gig,
it was divided into 16 parts (because the ordered size is larger than gig),
those. roughly speaking, for 8 gig, you yourself ordered to make VLFs.
the magazine cannot contain less than 2 VLF,
because it is cyclical, which is something to cycle, if less than 2.
while you only scored the first VLF,
so the rest can be shred.
but not 7 VLFs can be thrown out, but only 6,
so 2 saved.
those. your 16 gigs left.
Re: SUSDB and log file size [new]
nezhadnye_my,

Thanks for the information, the only 8 gigs that I did not order, it was at the time of copying the database, the server itself chose the optimal size, since the original magazine had 130 gigabytes, then it selected quite optimally.

further, we managed to solve the problem, though the whole magazine slammed:
1) detached the base
2) deleted by hand a magazine that is less than 16 gigabytes did not want to decrease
3) I started to attach the database, he said that the magazine was not found and added a virtual line in the grid, I didn’t bang with her, after attaching the new magazine they made 1 MB by default, then you can increase

Thank you all for participating!
Between messages, an interval of more than 1 year.
Re: SUSDB and log file size [new]
goalinternet,

It helped! Thanks for the good and easy way, I have the same problem, the log did not decrease in any!

And also, I couldn’t change the zoom settings, for the model database, I don’t understand why!
Re: SUSDB and log file size [new]
demon_sl
And also, I couldn’t change the zoom settings, for the model database, I don’t understand why!
And for what purpose do you do it?
Re: SUSDB and log file size [new]
Never encountered huge vfls.
And the creation of a second ldfa with a reasonable increment does not help in this case? Is it possible to shrinkfile c emptyfile for the transaction log and will all the blocks from the cleaned file be transferred to a new, newer, smaller vfly?



Old articles you may read here
Go to Main page