DISCLAIMER: I'm a dev rather than a DBA.
We use Log4Net to capture database interactions - lots of INFO messages, a few WARNINGs and, crucially, ERRORs. Recently our website was misbehaving and in an effort to diagnose it I looked at the Log4NetLog table.(there are currently 17756271 rows in the table.)
I realised that there was a problem with the design of the table - the useful columns [Message] and [Exception] are nvarchar(4000) and nvarchar(2000) respectively. Our code was trying to write the Exception thrown by the application but because the column wasn't wide enough it was truncating the stack trace.
So I asked our data team to run the following code
ALTER TABLE [dbo].[Log4NetLog];
ALTER COLUMN [Message] nvarchar(MAX) NOT NULL;
ALTER TABLE [dbo].[Log4NetLog]
ALTER COLUMN [Exception] nvarchar(MAX) NOT NULL;
Unfortunately, because of the huge amount of data in the table this essentially took the database off-line, and thus killed the website.
I have two questions, if I may:
1. In the event of someone attempting to execute such a query in future, what is the best way to kill this running query? The database was out of action for half an hour - could this have been speeded up?
2. It would be really useful to be able to apply the DMO script above. To this end we plan a) to schedule it during the night (the website is only used in this country and has virtually no traffic at night) and b) we want to remove old records - say, any record older than 1 year. However, my instinct is that if we were to try to run a query like
([Date] < '27 September 2017')
that this would similarly crash the database. Are there any strategies that might be applied to remove huge numbers of superfluous records?