Can't shrink WSUS SQL database

Question

I have a Windows SBS2011 installed. It has file sharing, Exchange and WSUS running off it.

WSUS console was, and always been, running slowly - I have no comparison with any other system, so I considered it normal; it was (and is) usable.

One day I noticed that WSUS database grew to 15 GB - surprising, but ok - I figured there must be a ton of things to remove (after all, I have some 15 PCs on the network).

I started with standard WSUS tools, but when selecting all the options at the same time it timed out again and again. I think I solved this by selecting just a single option for each run.

The I used the script from https://gallery.technet.microsoft.com/scriptcenter/6f8cde49-5c52-4abd-9820-f1d270ddea61#content - pasted this in SQL Manager and ran the query. Took a while, finished without errors.

So I went to "shrink file" - checked "Reorganize pages before releasing unused space" - Manager suggests that I can shrink to 11800 MB (not much of a shrink, but still better than before). Unfortunately, each time I try to run it, it ends with "Shrink failed for datafile 'SUSDB'; Lock request time out period exceeded".

I tried stopping IIS (on which WSUS is running), stopping every imaginable service that seemed related to updates, reloading the server and trying the same on fresh boot - to no effect.

I understand that there is some process accessing the database? If so: 1) how can I find what the process is 2) is it possible to take this DB offline and then shrink? I'm not worried about uptime

EDIT:

USE SUSDB
GO
SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

SUSDB   C:\WSUS\SUSDB\\UpdateServicesDbFiles\SUSDB.mdf  14616.19    2722.81 1   PRIMARY
SUSDB_log   C:\WSUS\SUSDB\\UpdateServicesDbFiles\SUSDB_log.ldf  1191.13 1167.38 2   NULL

USE SUSDB
DBCC Loginfo();

2   253952  8192    163903  0   64  0
2   253952  262144  164009  0   64  0
2   270336  516096  163904  0   64  163318000000023900032
2   262144  786432  164008  0   64  163319000000026400016
2   262144  1048576 164010  0   64  163321000000024800015
2   262144  1310720 164011  0   64  163322000000022300063
2   262144  1572864 163901  0   128 163323000000022800053
2   262144  1835008 163902  0   128 163324000000025600002
2   262144  2097152 163905  0   128 163327000000020700125
2   262144  2359296 163906  0   128 163328000000022100068
2   262144  2621440 163907  0   128 163329000000025300005
2   327680  2883584 163908  0   128 163330000000021700075
2   327680  3211264 163909  0   128 163331000000037800011
2   393216  3538944 164006  0   128 163332000000035500055
2   393216  3932160 164007  0   128 163333000000045100118
2   458752  4325376 164012  0   128 163338000000021100087
2   253952  4784128 164013  0   128 163339000000061100055
2   270336  5038080 164014  0   128 163339000000061100055
2   253952  5308416 164015  0   128 163341000000026700009
2   270336  5562368 164016  0   128 163341000000026700009
2   253952  5832704 164017  0   128 163343000000024200058
2   335872  6086656 164018  0   128 163343000000024200058
2   253952  6422528 164019  0   128 163345000000039900001
2   401408  6676480 164020  0   128 163345000000039900001
2   253952  7077888 164021  0   128 163347000000050200049
2   466944  7331840 164022  0   128 163347000000050200049
2   253952  7798784 164023  0   128 163349000000063800034
2   253952  8052736 164024  0   128 163349000000063800034
2   278528  8306688 164025  0   128 163349000000063800034
2   253952  8585216 164026  0   128 163352000000027800019
2   253952  8839168 164027  0   128 163352000000027800019
2   344064  9093120 164028  0   128 163352000000027800019
2   253952  9437184 164029  0   128 163355000000038600059
2   253952  9691136 164030  0   128 163355000000038600059
2   475136  9945088 164031  0   128 163355000000038600059
2   253952  10420224    164032  0   128 163358000000060800124
2   253952  10674176    164033  0   128 163358000000060800124
2   253952  10928128    164034  0   128 163358000000060800124
2   286720  11182080    164035  0   128 163358000000060800124
2   262144  11468800    164036  0   128 163362000000027600053
2   262144  11730944    164037  0   128 163362000000027600053
2   262144  11993088    164038  0   128 163362000000027600053
2   393216  12255232    164039  0   128 163362000000027600053
2   327680  12648448    164040  0   128 163366000000048800045
2   327680  12976128    164041  0   128 163366000000048800045
2   327680  13303808    164042  0   128 163366000000048800045
2   327680  13631488    164043  0   128 163366000000048800045
2   327680  13959168    164044  0   128 163370000000032500113
2   327680  14286848    164045  0   128 163370000000032500113
2   327680  14614528    164046  0   128 163370000000032500113
2   458752  14942208    164047  0   128 163370000000032500113
2   393216  15400960    164048  0   128 163374000000061700045
2   393216  15794176    164049  0   128 163374000000061700045
2   393216  16187392    164050  0   128 163374000000061700045
2   393216  16580608    164051  0   128 163374000000061700045
2   393216  16973824    164052  0   128 163378000000046600088
2   393216  17367040    163898  0   64  163378000000046600088
2   393216  17760256    163899  0   64  163378000000046600088
2   524288  18153472    163900  0   64  163378000000046600088
2   458752  18677760    163910  0   64  163391000000033800120
2   458752  19136512    163911  0   64  163391000000033800120
2   458752  19595264    163912  0   64  163391000000033800120
2   524288  20054016    163913  0   64  163391000000033800120
2   524288  20578304    163914  0   64  163395000000076500005
2   524288  21102592    163915  0   64  163395000000076500005
2   524288  21626880    163916  0   64  163395000000076500005
2   524288  22151168    163917  0   64  163395000000076500005
2   524288  22675456    163918  0   64  163399000000073100071
2   524288  23199744    163919  0   64  163399000000073100071
2   524288  23724032    163920  0   64  163399000000073100071
2   720896  24248320    163921  0   64  163399000000073100071
2   589824  24969216    163922  0   64  163403000000114600013
2   589824  25559040    163923  0   64  163403000000114600013
2   589824  26148864    163924  0   64  163403000000114600013
2   720896  26738688    163925  0   64  163403000000114600013
2   655360  27459584    163926  0   64  163407000000114100021
2   655360  28114944    163927  0   64  163407000000114100021
2   655360  28770304    163928  0   64  163407000000114100021
2   786432  29425664    163929  0   64  163407000000114100021
2   720896  30212096    163930  0   64  163411000000124800060
2   720896  30932992    163931  0   64  163411000000124800060
2   720896  31653888    163932  0   64  163411000000124800060
2   851968  32374784    163933  0   64  163411000000124800060
2   786432  33226752    163934  0   64  163415000000139500024
2   786432  34013184    163935  0   64  163415000000139500024
2   786432  34799616    163936  0   64  163415000000139500024
2   983040  35586048    163937  0   64  163415000000139500024
2   917504  36569088    163938  0   64  163419000000163800050
2   917504  37486592    163939  0   64  163419000000163800050
2   917504  38404096    163940  0   64  163419000000163800050
2   917504  39321600    163941  0   64  163419000000163800050
2   983040  40239104    163942  0   64  163423000000147900112
2   983040  41222144    163943  0   64  163423000000147900112
2   983040  42205184    163944  0   64  163423000000147900112
2   1114112 43188224    163945  0   64  163423000000147900112
2   1114112 44302336    163946  0   64  163427000000185700121
2   1114112 45416448    163947  0   64  163427000000185700121
2   1114112 46530560    163948  0   64  163427000000185700121
2   1114112 47644672    163949  0   64  163427000000185700121
2   1179648 48758784    163950  0   64  163431000000189900040
2   1179648 49938432    163951  0   64  163431000000189900040
2   1179648 51118080    163952  0   64  163431000000189900040
2   1376256 52297728    163953  0   64  163431000000189900040
2   1310720 53673984    163954  0   64  163435000000236300134
2   1310720 54984704    163955  0   64  163435000000236300134
2   1310720 56295424    163956  0   64  163435000000236300134
2   1441792 57606144    163957  0   64  163435000000236300134
2   1441792 59047936    163958  0   64  163439000000253700043
2   1441792 60489728    163959  0   64  163439000000253700043
2   1441792 61931520    163960  0   64  163439000000253700043
2   1572864 63373312    163961  0   64  163439000000253700043
2   1572864 64946176    163962  0   64  163443000000280200026
2   1572864 66519040    163963  0   64  163443000000280200026
2   1572864 68091904    163964  0   64  163443000000280200026
2   1769472 69664768    163965  0   64  163443000000280200026
2   1769472 71434240    163966  0   64  163447000000316400068
2   1769472 73203712    163967  0   64  163447000000316400068
2   1769472 74973184    163968  0   64  163447000000316400068
2   1835008 76742656    163969  0   64  163447000000316400068
2   1966080 78577664    163970  0   64  163451000000327500101
2   1966080 80543744    163971  0   64  163451000000327500101
2   1966080 82509824    163972  0   64  163451000000327500101
2   1966080 84475904    163973  0   64  163451000000327500101
2   2162688 86441984    163974  0   64  163455000000356600034
2   2162688 88604672    163975  0   64  163455000000356600034
2   2162688 90767360    163976  0   64  163455000000356600034
2   2162688 92930048    163977  0   64  163455000000356600034
2   2359296 95092736    163978  0   64  163459000000391100110
2   2359296 97452032    163979  0   64  163459000000391100110
2   2359296 99811328    163980  0   64  163459000000391100110
2   2424832 102170624   163981  0   64  163459000000391100110
2   2621440 104595456   163982  0   64  163463000000444000077
2   2621440 107216896   163983  0   64  163463000000444000077
2   2621440 109838336   163984  0   64  163463000000444000077
2   2621440 112459776   163985  0   64  163463000000444000077
2   2883584 115081216   163986  0   64  163467000000482100082
2   2883584 117964800   163987  0   64  163467000000482100082
2   2883584 120848384   163988  0   64  163467000000482100082
2   2883584 123731968   163989  0   64  163467000000482100082
2   3145728 126615552   163990  0   64  163471000000533900071
2   3145728 129761280   163991  0   64  163471000000533900071
2   3145728 132907008   163992  0   64  163471000000533900071
2   3276800 136052736   163993  0   64  163471000000533900071
2   3473408 139329536   163994  0   64  163475000000611200061
2   3473408 142802944   163995  0   64  163475000000611200061
2   3473408 146276352   163996  0   64  163475000000611200061
2   3538944 149749760   163997  0   64  163475000000611200061
2   3801088 153288704   163998  0   64  163479000000662600057
2   3801088 157089792   163999  0   64  163479000000662600057
2   3801088 160890880   164000  0   64  163479000000662600057
2   3932160 164691968   164001  0   64  163479000000662600057
2   4194304 168624128   164002  0   64  163483000000740500036
2   4194304 172818432   164003  0   64  163483000000740500036
2   4194304 177012736   164004  0   64  163483000000740500036
2   4325376 181207040   164005  0   64  163483000000740500036
2   4587520 185532416   164053  0   64  163534000000047700098
2   4587520 190119936   164054  0   64  163534000000047700098
2   4587520 194707456   164055  0   64  163534000000047700098
2   4784128 199294976   164056  0   64  163534000000047700098
2   5111808 204079104   164057  0   64  163538000000907600022
2   5111808 209190912   164058  0   64  163538000000907600022
2   5111808 214302720   164059  0   64  163538000000907600022
2   5111808 219414528   164060  0   64  163538000000907600022
2   5570560 224526336   164061  0   64  163542000000968500083
2   5570560 230096896   164062  0   64  163542000000968500083
2   5570560 235667456   164063  0   64  163542000000968500083
2   5767168 241238016   164064  0   64  163542000000968500083
2   6160384 247005184   164065  0   64  163546000001100600003
2   6160384 253165568   164066  0   64  163546000001100600003
2   6160384 259325952   164067  0   64  163546000001100600003
2   6225920 265486336   164068  0   64  163546000001100600003
2   6750208 271712256   164069  0   64  163550000001189800011
2   6750208 278462464   164070  0   64  163550000001189800011
2   6750208 285212672   164071  0   64  163550000001189800011
2   6946816 291962880   164072  0   64  163550000001189800011
2   7471104 298909696   164073  0   64  163554000001324900120
2   7471104 306380800   164074  0   64  163554000001324900120
2   7471104 313851904   164075  0   64  163554000001324900120
2   7471104 321323008   164076  0   64  163554000001324900120
2   8192000 328794112   164077  0   64  163558000001427600114
2   8192000 336986112   164078  2   64  163558000001427600114
2   8192000 345178112   163820  0   128 163558000001427600114
2   8323072 353370112   163821  0   128 163558000001427600114
2   9043968 361693184   163822  0   128 163562000001594300110
2   9043968 370737152   163823  0   128 163562000001594300110
2   9043968 379781120   163824  0   128 163562000001594300110
2   9043968 388825088   163825  0   128 163562000001594300110
2   9895936 397869056   163826  0   128 163566000001734700117
2   9895936 407764992   163827  0   128 163566000001734700117
2   9895936 417660928   163828  0   128 163566000001734700117
2   10092544    427556864   163829  0   128 163566000001734700117
2   10944512    437649408   163830  0   128 163570000001940600095
2   10944512    448593920   163831  0   128 163570000001940600095
2   10944512    459538432   163832  0   128 163570000001940600095
2   10944512    470482944   163833  0   128 163570000001940600095
2   11993088    481427456   163834  0   128 163574000002108700063
2   11993088    493420544   163835  0   128 163574000002108700063
2   11993088    505413632   163836  0   128 163574000002108700063
2   12189696    517406720   163837  0   128 163574000002108700063
2   13238272    529596416   163838  0   128 163578000002353700029
2   13238272    542834688   163839  0   128 163578000002353700029
2   13238272    556072960   163840  0   128 163578000002353700029
2   13238272    569311232   163841  0   128 163578000002353700029
2   14548992    582549504   163842  0   128 163582000002554200112
2   14548992    597098496   163843  0   128 163582000002554200112
2   14548992    611647488   163844  0   128 163582000002554200112
2   14614528    626196480   163845  0   128 163582000002554200112
2   15990784    640811008   163846  0   128 163586000002827300029
2   15990784    656801792   163847  0   128 163586000002827300029
2   15990784    672792576   163848  0   128 163586000002827300029
2   16121856    688783360   163849  0   128 163586000002827300029
2   8781824 704905216   163850  0   128 163590000003116800124
2   8781824 713687040   163851  0   128 163590000003116800124
2   8781824 722468864   163852  0   128 163590000003116800124
2   8781824 731250688   163853  0   128 163590000003116800124
2   8781824 740032512   163854  0   128 163590000003116800124
2   8781824 748814336   163855  0   128 163590000003116800124
2   8781824 757596160   163856  0   128 163590000003116800124
2   9043968 766377984   163857  0   128 163590000003116800124
2   9699328 775421952   163858  0   128 163598000001738400048
2   9699328 785121280   163859  0   128 163598000001738400048
2   9699328 794820608   163860  0   128 163598000001738400048
2   9699328 804519936   163861  0   128 163598000001738400048
2   9699328 814219264   163862  0   128 163598000001738400048
2   9699328 823918592   163863  0   128 163598000001738400048
2   9699328 833617920   163864  0   128 163598000001738400048
2   9699328 843317248   163865  0   128 163598000001738400048
2   10616832    853016576   163866  0   128 163606000001868600003
2   10616832    863633408   163867  0   128 163606000001868600003
2   10616832    874250240   163868  0   128 163606000001868600003
2   10616832    884867072   163869  0   128 163606000001868600003
2   10616832    895483904   163870  0   128 163606000001868600003
2   10616832    906100736   163871  0   128 163606000001868600003
2   10616832    916717568   163872  0   128 163606000001868600003
2   11010048    927334400   163873  0   128 163606000001868600003
2   11730944    938344448   163874  0   128 163614000002118600123
2   11730944    950075392   163875  0   128 163614000002118600123
2   11730944    961806336   163876  0   128 163614000002118600123
2   11730944    973537280   163877  0   128 163614000002118600123
2   11730944    985268224   163878  0   128 163614000002118600123
2   11730944    996999168   163879  0   128 163614000002118600123
2   11730944    1008730112  163880  0   128 163614000002118600123
2   11730944    1020461056  163881  0   128 163614000002118600123
2   12845056    1032192000  163882  0   128 163622000002264300009
2   12845056    1045037056  163883  0   128 163622000002264300009
2   12845056    1057882112  163884  0   128 163622000002264300009
2   12845056    1070727168  163885  0   128 163622000002264300009
2   12845056    1083572224  163886  0   128 163622000002264300009
2   12845056    1096417280  163887  0   128 163622000002264300009
2   12845056    1109262336  163888  0   128 163622000002264300009
2   13303808    1122107392  163889  0   128 163622000002264300009
2   14155776    1135411200  163890  0   128 163630000002570700024
2   14155776    1149566976  163891  0   128 163630000002570700024
2   14155776    1163722752  163892  0   128 163630000002570700024
2   14155776    1177878528  163893  0   128 163630000002570700024
2   14155776    1192034304  163894  0   128 163630000002570700024
2   14155776    1206190080  163895  0   128 163630000002570700024
2   14155776    1220345856  163896  0   128 163630000002570700024
2   14483456    1234501632  163897  0   128 163630000002570700024

LAZYWRITER_SLEEP    502876  502108515   64210   343
SLEEP_BPOOL_FLUSH   1123769 13416429    46  1762
WRITELOG    433820  10724319    14757   10405
BACKUPIO    1697    7586640 5023    156
PAGEIOLATCH_SH  733164  5234379 5616    1934
PAGEIOLATCH_EX  20626   123911  1045    78
BROKER_TASK_STOP    7   60294   9999    0
SQLTRACE_BUFFER_FLUSH   14  51917   3993    0
PAGELATCH_SH    98  50840   6349    0
IO_COMPLETION   958 38313   530 31
BACKUPTHREAD    190 30763   3244    156
LCK_M_S 12  22651   21652   15
PAGELATCH_EX    70192   22651   2028    156
SERVER_IDLE_CHECK   2   21996   19999   0
PAGEIOLATCH_UP  205 17877   546 15
LATCH_EX    40  12183   1466    140
LCK_M_U 5   10062   9921    15
LCK_M_X 3   9999    9999    0
CHKPT   1   8798    8798    0
SLEEP_SYSTEMTASK    1   8798    8798    0

Answer 1

Edit based on reply:

Thanks for sharing your results. Your main write slowdowns are more likely due to your transaction logs from the brief analysis provided, and not your database size. Don't shrink. that is not going to help out at all and make things more fragmented. Read here for more background info on how shrink works which causes this. Your performance issues are caused by other factors per your results.

You have a highly fragmented transaction log as suspected from the number of VLFs returned. The analysis that your transaction logs are a major source of performance woes is backed up by the fact that you see 'WRITE_LOG' as the 3rd highest wait stat in your server, this is out of a few hundred metrics This shows several issues we should resolve first. Finally we see that your disk space isn't astronomic, and it has free space. Here is a quick background on why that is bad.

Next we see backupio and pageiolatch_sh and pagelatch_ex as well as io_completion. This shows that your system is suffering from poor disk performance currently. You probably have high disk read/sec and disk write/sec metrics in perfmon on your transaction log and data file. Here is a great guide showing you how to use Perfmon to troubleshoot sql performance issues.

Also, as you see backups are also high on the list which could be throwing off the statistics. If you wanted to be sure that in fact it is not backups polluting your metrics, you could copy the waitstat metrics somewhere, reset them, and collect the requested metrics from perfmon during regular and backup hours.

Transaction log pruning and monitoring disk metrics are going to give you the most performance gains for writes immediately, as well as make db restore faster. Below is how to fix the tlog VLF issue. Basically you want to make sure you checkpoint and your last VLF isn't active by doing a DBCC LOGINFO(); and seeing a status of 0. If you're in full recovery mode then make sure you are taking tlog backups, and wait for the backup. Then shrink the transaction log and grow it based on these guidelines. http://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Besides setting up perfmon and looking at these statistics, You will want to see if your transaction log is set to autogrow at the default of 1mb. You will want to change that to a different number to avoid VLF fragmentation. Typically if we don't know, a best practice is to strive around 50 VLFs per Kim Tripp. Here are some of the best practices to TLog management from her.

End Edit.

Per your comments it seems like you're looking at the total DB space which includes transaction logs. Please run the following commands and post the results so we can see what is taking up that space. SQL Express won't let your data files go over 10GB anyways so I suspect there's some log issues which is causing your performance issues. Typically 'database size' is never the actual issue, tertiary effects of it are which can be resolved.

Let's see how much space you are using in your DB, including log files, and the total database/log space allocated. This is a script by community member Glenn Berry:

-- Individual File Sizes and space available for current database
-- (Query 36) (File Sizes and Space)
USE <DBNAME>
GO
SELECT f.name AS [File Name] , f.physical_name AS [Physical Name], 
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) 
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

Let's see how many VLFs you have.

USE <DBNAME>
DBCC Loginfo();

Please give us the rows returned by the 2nd query.

Finally, if you're having performance issues, have you been able to run PerfMon and collect metrics? If not, go to start - run - Perfmon.msc. Add:

Disk Sec/read
Disk Sec/write
Total CPU %
Total RAM usage

Those would be the initial metrics to look at. You could also list your total wait stats to see which ones you're waiting on the most to do a initial performance diagnostics by running:

select top 20 * 
from sys.dm_os_wait_stats
order by wait_time_ms desc

Let us know! Good luck.

Answer 2

1. Connect to the database in SQL Server Management Studio. Execute the following command in a new query editor window:

EXEC sp_who2

This should provide you with a list of processes on SQL Server. Find the lines which have your database name on them. These should provide you with the process that is attached to them.

2. When you take your database offline, it is not possible to do a shrink.

But in general, it is not a good idea to shrink databases. You should provide sufficient storage for the database to grow. This will prevent the "jojo"-effect shrinking has: your database grows, you shrink it, the database grows, you shrink again and so on. Even worse, a database shrink causes massive index fragmentation. Paul Randall wrote a nice article about it, if you want more information.