10.12.07

Deleting huge amounts of records

Posted in DBA, Microsoft Sql server, TSQL at 11:41 am by

When you have to delete a whole bunch of records from a table which has to stay in use. Then you could do it with a cursor. but you can also do it with the following approach. Use top to limit the amount of records deleted and put the deletion in a loop.

Sample code: 

DECLARE @p as int;

DELETE [dbo].[Klanthistoriek]
FROM (SELECT TOP 50000 klh_id
FROM
[GKA].[dbo].[Klanthistoriek]
WHERE
KLH_CreatieDatum between ’2007-09-18 10:47:40.820′ and ’2007-09-20 15:29:32.663′ ) as temptable
Where [dbo].[Klanthistoriek].klh_id = temptable.klh_id;

SET @p= @@rowcount;
WHILE @p <> 0
BEGIN
DELETE [dbo].[Klanthistoriek]
FROM (SELECT TOP 50000 klh_id
FROM
[GKA].[dbo].[Klanthistoriek]
WHERE
KLH_CreatieDatum between ’2007-09-18 10:47:40.820′ and ’2007-09-20 15:29:32.663′ ) as temptable
Where [dbo].[Klanthistoriek].klh_id = temptable.klh_id;

SET @p = @@rowcount;
PRINT @p;
END

Job from removed maintenance plan

Posted in DBA, Microsoft Sql server, TSQL at 11:36 am by

You removed a maintenance plan and it didn’t delete the job. You got the following error:

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.
The statement has been terminated. (.Net SqlClient Data Provider)

You can remove the job by executing the following code:

– Set @job_name to the job name you want to delete

USE [msdb]
DECLARE @job_name varchar(100)
SET @job_name = ‘job name’

–Delete the logs for the plan

DELETE
sysmaintplan_log
FROM
sysmaintplan_subplans AS subplans
INNER JOIN sysjobs_view AS syjobs ON
subplans.job_id = syjobs.job_id
INNER JOIN sysmaintplan_log ON
subplans.subplan_id = sysmaintplan_log.subplan_id
WHERE
syjobs.name = @job_name;

–Delete the subplan

DELETE
sysmaintplan_subplans
FROM
sysmaintplan_subplans AS subplans
INNER JOIN sysjobs_view AS syjobs ON
subplans.job_id = syjobs.job_id
WHERE
syjobs.name = @job_name;

–delete the job

DELETE FROM
msdb.dbo.sysjobs_view
WHERE
name = @job_name;

Transaction log grows with Bulk Logged?

Posted in DBA, Microsoft Sql server, TSQL at 10:55 am by

When using the Bulk-Logged recovery model your server logs the bulk commands minimally, our so you think. For sql server to log minimally other criteria should be met!

First off the table should not be replicated. And when you do your bulk logged command you also need to lock the table.

SAMPLE

sp_tableoption target_table, 'table lock on bulk load', 1

INSERT INTO target_table
(a,
b
)
SELECT
a,
b
FROM
source_table

sp_tableoption target_table, 'table lock on bulk load', 0

10.11.07

Huge transaction log with sql server

Posted in DBA, Microsoft Sql server, TSQL at 5:24 pm by

Your transaction log is huge and has free space.
So you want to shrink it and it shrinks very little. You can solve this by running following script.

USE your_db
GO
DBCC SHRINKFILE(your_log, 200)
BACKUP LOG your_db WITH TRUNCATE_ONLY
DBCC SHRINKFILE(your_log, 200)

WATCH OUT
The TRUNCATE_ONLY which we use and the NO_LOG options of the BACKUP LOG statement will be removed in a future version of SQL Server. These options remove the inactive part of the log without making a backup copy of it, and truncate the log by discarding all but the active log. This breaks the log chain. Until the next full or differential database backup, the
database is not protected from media failure. Therefore, we strongly recommend that you avoid using either of these options in new development work and that you plan to modify applications that currently use it.
Your transaction log is huge and has no free space.
First we need to find out why it’s in use.

SELECT
name,
log_reuse_wait,
log_reuse_wait_desc
FROM
sys.databases;

Read Factors That Can Delay Log Truncation, this page explains the result.