03.25.08

Mass update sql job owners

Posted in DBA, Microsoft Sql server, TSQL at 12:20 pm by

After a domain migration you might find yourself in a position where you have a whole bunch of jobs with dead domain accounts. Then you could use sql server management studio to update the job ownership one by one. Or you could make a script which updates it for you. The next script gives you a the basics to script this. Just change the OLD_ACCOUNT by the account you want to replace and the NEW_ACCOUNT by the account you want to replace it with.
SELECT ‘EXEC MSDB.dbo.sp_update_job ‘ + char(13) +
‘@job_name = ‘ + char(39) + j.[Name] + char(39) + ‘,’ + char(13) +
‘@owner_login_name = ‘ + char(39) + ‘NEW_ACCOUNT’ + char(39) + char(13) + char(13)
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] = ‘OLD_ACCOUNT’
ORDER BY j.[name]

The output of this script should copy paste and run in the sql server management console.

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.

03.17.07

Dedicated Administrator Connection

Posted in DBA, Microsoft Sql server, TSQL at 1:44 pm by

Microsoft SQL Server 2005 provides a dedicated administrator connection (DAC). The DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server-even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and

SQL Server Management Studio, only one person can login on it.
By default it only accepts local connections. To change it to accept remote

connections use:

sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Using a DAC
Open Management studio, and open a new Query connection to admin:instance using a

sys admin account. You cannot use the Object Explorer with DAC. If you just open an

try to connect to the server you end up with this error:

Dedicated administrator connections are not supported. (ObjectExplorer)

You need to open the management console then cancel the connect to server window

and select a new querry and go admin:servername\instance

Here you can execute querries like

select Session_id, login_time, cpu_time, memory_usage, reads, writes, login_name

from sys.dm_exec_sessions WITH (NOLOCK)

03.15.07

Add & remove columns & constraints to tables

Posted in DBA, Microsoft Sql server, TSQL at 9:13 pm by

–Sample Making a column not null and make it default to a value
–When the table has been in use and the existing values shouldn’t change.
update test1 set code = 0 WHERE code is null;
alter table test1
alter column code tinyint not null;
ALTER TABLE test1 ADD CONSTRAINT DF_test1_code DEFAULT ((0)) FOR code;

–Sample Adding a column with a default value
ALTER TABLE test1
ADD code tinyint not null
CONSTRAINT DF_test1_code DEFAULT ((0));

–Removing a constraint
ALTER TABLE test1
DROP CONSTRAINT DF_test1_code;

–Removing a column
ALTER TABLE test1
DROP column code;

03.14.07

The art of scripting replications.

Posted in DBA, Microsoft Sql server, TSQL at 9:00 pm by

Open the Microsoft sql server management studio.
In the object explorer pane you connect to the base server.
You open the replication node then local publications and Rightclick on the desired replication.
Select generate script from the pop up menu.
Make sure “Create or enable the components.” is selected.
Script to file…
Then open this file replace the name of original server in the whole file with the name of the serevr you want to deploy to.

In my case it only was written once in the :exec sp_addpublication

Then look for @job_password = null if the @job_login isn’t null either you have to provide the password for that account.

Also look for
exec sp_grant_publication_access @publication = N’My publication’, @login = N’oldserverSQLServer2005SQLAgentUser$oldserver$MSSQLSERVER’

Change the oldserver to newserver name. (better check for the correct account first!

First enable distribution on your new server.
Then run the file you crafted.

03.13.07

Creating a distributor with the sql server management studio and losing connection with the server

Posted in DBA, Microsoft Sql server, TSQL at 11:35 pm by

Scenario: Using the sql server management studio you are setting up a server to act as distributor. Then while you have clicked finished the connection is lost between your workstation and the server. Now the server thinks he’s a distributor but then again he’s not sure! You try to redo the previous action but that won’t work. What you need to do is tell the server to give up being a distributor.


use master

exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1

GO

Now just restart the distribution creation wizard.

03.12.07

Generating insert statements from data(manual way)

Posted in DBA, Microsoft Sql server, TSQL at 9:12 pm by

Sample :

DECLARE @replunit as char(2);
DECLARE @kantoor_nr as varchar(4);
DECLARE @kas_nr as varchar(4);
DECLARE @bediende_nr as varchar(4);

SET @replunit = ‘BE’;
SET @kantoor_nr = ’141′;
SET @kas_nr = ’1′;
SET @bediende_nr = ’996′;

SELECT
INSERT INTO KasBeginSaldo
(
KBS_ReplUnit,
KBS_KantoorNr,
KBS_KasNr,
KBS_ItemCode,
KBS_SubCode,
KBS_TijdStip,
KBS_BediendeNr,
KBS_Aantal,
KBS_Waarde,
KBS_VerschilWaarde,
KBS_BediendeNrTeller,
KBS_TijdstipTelling
) VALUES
( @replunit,
@kantoor_nr,
@kas_nr, ‘ +
”” + CAST(KBS_ItemCode AS varchar(8000)) + ”’, ‘ +
”” + CAST(KBS_SubCode AS varchar(8000)) + ”’, ‘ +
”” + CAST(getdate() AS varchar(8000)) + ”’, ‘ +
‘@bediende_nr, ‘ +
CAST(KBS_Aantal AS varchar(8000)) + ‘, ‘ +
CAST(KBS_Waarde AS varchar(8000)) + ‘, ‘ +
CAST(KBS_VerschilWaarde AS varchar(8000)) + ‘, ‘ +
CAST(KBS_BediendeNrTeller AS varchar(8000)) + ‘, ‘ +
”” + CAST(KBS_TijdstipTelling AS varchar(8000)) + ”” +
‘);’ as statement
FROM
KasBeginSaldo
WHERE
KBS_KantoorNr = @kantoor_nr AND
KBS_KasNr = @kas_nr AND
KBS_ReplUnit = @replunit;

« Previous entries Next Page » Next Page »