Update Statistics – SQL Server

To initiate stat update on whole database.

EXEC sp_updatestats;

Update Single Table.

UPDATE STATISTICS tableName ;

This will update stats on the particular table with sample size determined by query optimizer. You can specify WITH FULLSCAN option to scan the entire table, however this will take much longer than above.

Update Single Table WITH FULLSCAN.

UPDATE STATISTICS tableName WITH FULLSCAN;

Disable SQL Server Agent Job – SQL Server

Recently we had an issue with our tape backup system, we needed to quickly disable backup jobs to tape and enable backup jobs to drive. There were multiple jobs and using SSMS gui would have taken too much effort and time. To disable or enable multiple jobs update the sysjobs table in msdb.

Get list of jobs.

SELECT * FROM msdb.dbo.sysjobs

Update sysjobs table to disable job.

UPDATE msdb.dbo.sysjobs
SET enabled = 0
WHERE 1=1
AND job_id = '87D56BD2-EC90-4A6C-A00C-30A8F81133F5'

Get count of multiple tables using T-SQL – SQL Server

I was asked by my coworker get row count of tables with a particular column. So I came up with this little script to get it quickly using T-SQL.

DECLARE @tableName VARCHAR(255)
DECLARE @count smallint
DECLARE @stmt NVARCHAR(4000);

##Create insert statement into temp.

SET @stmt = 'INSERT INTO #tmpTable (tableNames, recCount)
SELECT ''** tableName **'', count(*) from ** tableName ** WHERE 1=1 and
= ''value''';

##Create temp table

CREATE table #tmpTable(
tableNames VARCHAR(255),
recCount smallint
)

##Declare CURSOR for select statement from sys.columns

DECLARE db_cursor CURSOR FOR
select distinct OBJECT_NAME(object_id) from sys.columns
where 1=1
and name = '
'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tableName

##Use while loop to insert record count in temp table.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @stmt = REPLACE(@stmt, '** tableName **', @tableName);

EXEC sp_executesql @stmt;

SET @stmt = 'INSERT INTO #tmpTable (tableNames, recCount)
SELECT ''** tableName **'', count(*) from ** tableName ** WHERE 1=1 and
= ''value''';

FETCH NEXT FROM db_cursor INTO @tableName

END

Close db_cursor
DEALLOCATE db_cursor

##Get all the tables with count

select * from #tmpTable

#Drop temp table.

drop table #tmpTable

Kill negative (orphan) pid SQL Server

Sometimes in sql server you will see negative pid running and blocking other processes. These are distributed transactions and sometimes they don’t complete or hang and block other processes. Negative PID can’t be killed by issuing normal kill pid command, to kill negative PID request_owner_guid is required.


USE Master;
GO

SELECT
DISTINCT(request_owner_guid)
FROM sys.dm_tran_locks
WHERE request_session_id =-2
GO

Once you have the guid, use it to kill the negative PID.

KILL 'guid'

Generate restore command for SQL Server Database backup.

This script will generate restore command for the most recent backup. This script can be used for databases in simple and full recovery model, this script ignores differential backup, which I will include in a later post.


DECLARE @databaseName sysname
DECLARE @newDatabaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT
DECLARE @stats varchar(2)
DECLARE @replace char(1)
DECLARE @repCom varchar(50)
DECLARE @copyOnly bit

-- set database values
SET @databaseName = 'dbName' --- Original Database.
SET @newDatabaseName = 'newDBName' --- Restored AS
SET @stats = '5'
SET @replace = '0'
SET @copyOnly = 0

DECLARE @filename varchar(255), @physicalfile varchar(255), @comMove varchar(500),
@fullMove varchar(4000), @dbName varchar(100), @newDbName varchar(100);
SET @dbName = @databaseName;
SET @newDbName = @newDatabaseName;
SET @fullMove = '';
IF LTRIM(RTRIM(@dbName)) = LTRIM(RTRIM(@newDbName)) OR @replace = 1
BEGIN
SET @repCom = ', REPLACE This will replace database!!'
END
ELSE
SET @repCom = ''

DECLARE update_file_name CURSOR
FOR SELECT name,REPLACE(physical_name, @dbName, @newDbName) FROM sys.master_files
WHERE 1=1
AND database_id = (SELECT database_id FROM sys.databases WHERE 1=1 AND name = @dbName)
ORDER BY CASE WHEN FILE_ID = 2 THEN 1000 ELSE FILE_ID END
OPEN update_file_name
FETCH NEXT FROM update_file_name
INTO @filename, @physicalfile

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @comMove = ', ' + 'MOVE N''' + @filename + ''' TO N''' + @physicalfile + ''''
SET @fullMove = @fullMove + @comMove

FETCH NEXT FROM update_file_name INTO @filename, @physicalfile;

END

CLOSE update_file_name;
DEALLOCATE update_file_name;

SELECT @backup_set_id_start = MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND is_copy_only = @copyOnly

SELECT @backup_set_id_end = MIN(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start
AND is_copy_only = @copyOnly

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id, 'RESTORE DATABASE ' + @newDatabaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY' + @fullMove + ', STATS = ' + @stats + @repCom
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id = @backup_set_id_start
AND is_copy_only = @copyOnly

UNION
SELECT backup_set_id, 'RESTORE LOG ' + @newDatabaseName + ' FROM DISK = '''
+ mf.physical_device_name + ''' WITH NORECOVERY'
FROM msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE b.media_set_id = mf.media_set_id
AND b.database_name = @databaseName
AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
AND b.type = 'L'
AND is_copy_only = @copyOnly
UNION
SELECT 999999999 AS backup_set_id, 'RESTORE DATABASE ' + @newDatabaseName + ' WITH RECOVERY'
ORDER BY backup_set_id

Database Full Backup Script – Sql Server

Following will run full backup of specified database to specified folder. This script will run copy_only backup with compression. It also includes setting for BUFFERCOUNT and MAXTRANSFERSIZE. 

Backup file will be in “databaseName_backup_yyyy_mm_dd_hhminss.bak” format.


DECLARE @backupfile varchar(100),
@db_name varchar(100), @backup_folder varchar(500);
SET @db_name = 'databaseName';
SET @backup_folder = 'backupLocation';
SET @backupfile = N'' + @backup_folder + @db_name + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar(50), getdate(), 20), ' ', '_'), '-', '_'), ':', '') + N'.bak';

DECLARE @backup_cmd nvarchar(4000)
SET @backup_cmd = 'BACKUP DATABASE ' + @db_name + ' TO DISK=''' + @backupfile + ''' WITH COPY_ONLY, FORMAT, INIT,MEDIANAME = N''sql_jobs'',
NAME = N''sql_jobs-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS=5, CHECKSUM, BUFFERCOUNT=100, MAXTRANSFERSIZE=2194304'

EXEC [sp_executesql] @backup_cmd

Get row count of table – SQL Server

There are times when as a DBA or developer you want to get the count of a table. To get the current count you can simply do:
SELECT COUNT(*) FROM [table_name]

Which works fine, when tables are small or medium size, but for large table (10 millions rows or above) this can take a while to return. If the count is for getting a “close enough” number, then sys.partitions can be used for getting this count quickly. sys.partitions keeps track of indexes for each table and rows contain within each index. The row count may be little off, depending on when the query is executed (pending commits etc), but for a “good enough” number count using sys.partitions is a faster option.

SELECT OBJECT_NAME(object_id), rows FROM sys.partitions WHERE index_id = 1

OBJECT_NAME will return the table name and index_id = 1 will return single row for tables with multiple indexes.

Where are the backup files? – SQL Server

In the previous post we explored backupset table in msdb, which holds data about backups, backup times etc. So now that we have good backups, where are the backup files? We can query backupmediafamily table for this information. physical_device_name column holds the full path (if saved on disk else the media name) to backup file.

SELECT * FROM msdb.dbo.backupmediafamily

We can also join backupmediafamily to backupset on media_set_id column to get information about relevant backup files.

SELECT physical_device_name, bs.backup_start_date, bs.backup_finish_date FROM msdb.dbo.backupmediafamily bf
INNER JOIN msdb.dbo.backupset bs ON bf.media_set_id = bs.media_set_id
WHERE 1=1
AND bf.media_set_id = 18518

Get information about backups – SQL Server

backupset table in msdb can be used to get information about database backups. This table holds information about of type of backup being done, recovery_model, backup_size, backup_start_date, backup_finish_date etc.

Full backups are of type ‘D’ (Database Backup), log backups are type ‘L’ (Log Backup) and incremental are of type ‘I’ (Incremental Backup).

To get full backup details sorted by backup finish time:
SELECT * FROM msdb.dbo.backupset
WHERE 1=1
AND type = 'D'
ORDER BY backup_finish_date DESC

Results can be filtered based on database by including database name like this:
SELECT * FROM msdb.dbo.backupset
WHERE 1=1
AND type = 'D'
AND database_name = ''
ORDER BY backup_finish_date DESC