Best daily MS SQL DBA checklist or DBA daily activity

DBA Morning Checklist

Backups

- Verify that the Network Backups are good by checking the backup emails. If a backup did not complete, contact _____ in the networking group, and send an email to the DBA group.

- Check the SQL Server backups. If a backup failed, research the cause of the failure and ensure that it is scheduled to run tonight.

- Check the database backup run duration of all production servers.

Verify that the average time is within the normal range. Any significant increases in backup duration times need to be emailed to the networking group, requesting an explanation. The reason for this is that networking starts placing databases backups to tape at certain times, and if they put it to tape before the DBAs are done backing up, the tape copy will be bad.

- Verify that all databases were backed up. If any new databases were not backed up, create a backup maintenance plan for them and check the current schedule to determine a backup time.

 

Disk Space

- Verify the free space on each drive of the servers. If there is significant variance in free space from the day before, research the cause of the free space fluctuation and resolve if necessary. Often times, log files will grow because of monthly jobs.

 

Job Failures

- Check for failed jobs, by connecting to each SQL Server, selecting "job activity" and filtering on failed jobs. If a job failed, resolve the issue by contacting the owner of the job if necessary.

System Checks

- Check SQL logs on each server. In the event of a critical error, notify the DBA group and come to an agreement on how to resolve the problem.

- Check Application log on each server. In the event of a critical or unusual error, notify the DBA group and the networking group to determine what needs to be done to fix the error.

 

Performance

- Check Performance statistics for All Servers using the monitoring tool and research and resolve any issues.

- Check Performance Monitor on ALL production servers and verify that all counters are within the normal range.

 

Connectivity

- Log into the Customer application and verify that it can connect to the database and pull up data. Verify that it is performing at an acceptable speed. In the event of a failure, email the Customer Support Group, DBA group, and the DBA manager, before proceeding to resolve the issue.

- Log into the Billing application and verify that it can connect to the database and pull up data. Verify that it is performing at an acceptable speed. In the event of a failure, email the Billing Support Group, DBA group, and the DBA manager, before proceeding to resolve the issue.

 

Replication

- Check replication on each server by checking each publication to make sure the distributor is running for each subscription.

- When replication is stopped, or changes to replication are made, send an email to the DBA group. For example, if the DBA stops the distributor, let the other DBAs know when it is stopped and then when it is restarted again.

- Check for any emails for the SQL Jobs that monitor row counts on major tables on the publisher and subscriber. If a wide variance occurs, send an email message to the DBAs and any appropriate IS personnel.

Function to return a table from a delimitted (csv) string

Create function fn_CSVString
(@CSVStr varchar(8000) ,@Delim varchar(10))
returns @CSV table (s varchar(1000))
as
begin

declare @i int , @j int
select @i = 1

while @i <= len(@CSVStr)
begin
select @j = charindex(@Delim, @CSVStr, @i)
if @j = 0
begin
select @j = len(@CSVStr) + 1
end
insert @CSV
select substring(@CSVStr, @i, @j - @i)

select @i = @j + len(@Delim)
end
Select * from @CSV
return
end

How to check if file exists in dos or not?

-- xp_cmdshell

declare @Path varchar(128) , @FileName varchar(128)
select @Path = 'C:\Temp\' , @FileName = 'file.txt'
declare @cmd varchar(1000)

create table #File(s varchar(1000))
select @cmd = 'dir /B ' + @Path + @FileName

insert #File exec
master.dbo.xp_cmdshell @cmd

if exists (select * from #File where s = @FileName)
print 'exists'
else
print 'not exists'
drop table #File


-- using xp_fileexists
declare @Path varchar(128) , @FileName varchar(128)
select @Path = 'C:\Temp\' , @FileName = 'file.txt'

declare @i int
declare @File varchar(1000)

select @File = @Path + @FileName

exec master.dbo.xp_fileexist @File, @i out

if @i = 1
print 'exists'
else
print 'not exists'

Log file not getting shrink \ truncate

The Log will not get truncate or shrink log records remain active for a long time.
You will get details of same from log_reuse_wait and log_reuse_wait_desc from sys.databases

Factors like:
very long-running transaction
paused database mirroring session, can cause the transaction log to fill.

Log truncation cannot happen during any backup or restore operation

For more details check this:
http://msdn.microsoft.com/en-us/library/ms345414.aspx

Stopping the transaction log file (.ldf) from growing

If the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from growing.

Enterprise managerRight click on the database, properties, Options, set model to simple, OK.

t-sql

sp_dboption [dbname], 'trunc. log on chkpt.', 'true'

Shrinking the log file via detach/attach

Always take a full backup before a detach.

Detach the database, delete/rename the log file, attach the database - this will create a minimum size log file.

Note that the log file must be deleted/renamed otherwise it will be re-used even though it is not mentioned in the attach.

Enterprise managerRight click on the database, All tasks, Detach database, OK.
Delete/rename the disk log file.
Right click on databases, All tasks, Attach database, Select the .mdf file, OK, Yes (to the create new log message).

t-sql

sp_detach_db [dbname]
Delete/rename the disk log file.
sp_attach_single_file_db [dbname], [filename]
where [filename] is the name of the physical data file (.mdf).

How to change the SQL Server name or instance

Here are commands that need to run to change SQL Server name or instance:

sp_dropserver 'old instance name'

sp_addserver 'new instance name', local

Then restart the SQL Server service.

After restarting run the following command

select @@servername

Attach multiple databases

Here is script to attach multiple databases:
Declare @Loc varchar(1000)


Declare @SName varchar(255),@DName varchar(255),@sql VARCHAR(2000),@SQLInst varchar(255),@DB varchar(255),@Log char(1),@Data char(1)
--\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
--SET THE LOCATION OF DATABASE BACKUPS
Set @Loc='D:\iXSQl\Data\'

Declare @DBTable table (DBName varchar(255))
Set @sql='Dir /B '+@Loc

Insert into @DBtable
exec master.dbo.xp_cmdshell @sql

delete from @DBTable where RIGHT(DBName,4)<>'.mdf' or DBName is null

Create table #DBName (p sql_variant ,v sql_variant)

Create table #DBinfo (s sql_variant,f sql_variant,p sql_variant,v sql_variant)

Declare Cur Cursor
For Select DBName from @DBTable

Open Cur
Fetch Next from Cur
into @DName

While @@FETCH_STATUS =0
Begin
Print @DNAme
truncate table #DBName
truncate table #DBinfo
Set @SQL ='dbcc checkprimaryfile(''D:\iXSQl\Data\'+@DName+''',2)'

Insert into #DBName
execute (@sql)

Set @SQL ='dbcc checkprimaryfile(''D:\iXSQl\Data\'+@DName+''',3)'

Insert into #DBinfo
execute (@sql)

Select @DB=rtrim(cast([v] as varchar(255))) from #DBName Where p = 'Database name'
Set @sql = 'CREATE DATABASE '+@DB+' ON '
Select @SQL = @sql+'(FILENAME = N'''+case when rtrim(cast([v] as varchar(255))) like '%Data%'
then stuff(rtrim(cast([v] as varchar(255))),1,1,'D')
when rtrim(cast([v] as varchar(255))) like '%Log%'
then stuff(rtrim(cast([v] as varchar(255))),1,1,'F')end+''' ),' from #DBinfo

Set @sql=left (@sql,len(@sql)-1)

set @sql=@sql+' FOR ATTACH'
Print @sql
exec (@sql)
Fetch Next from Cur
into @DName

End

Close Cur
Deallocate Cur

Drop table #DBName
Drop table #DBinfo

SQL Server 2008 Table Partitioning

When a database table grows in size to the hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes. Just the sheer size of the table causes such operations to take much longer. Even the data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical. The Microsoft® SQL Server® 2008/2005 database software provides table partitioning to make such operations more manageable.


Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table. In addition, the SQL Server optimizer can direct properly filtered queries to appropriate partitions rather than the entire table.

Table Partitioning Overview

• Horizontal Partitioning
• Vertical Partitioning

SQL Server table Partitioning:
SQL Server's table partitioning differs from the above two approaches by partitioning a single table: Multiple physical tables are no longer involved. When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes.

 SQL Server's table partitioning is designed to make loading, aging, and other maintenance operations on large tables easier, as well as improve performance of properly filtered queries using partition elimination.

Benefits of Partitioned Tables

• SQL Server automatically manages the placement of data in the proper partitions.
• A partitioned table and its indexes appear as a normal database table with indexes, even though the table might have numerous partitions.
• The table can be managed at the partition and filegroup level for ease of maintenance.
• Partitioned tables support easier and faster data loading, aging, and archiving, as illustrated in the example on last slide.
• Application queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism.
• In cases where partitioned data will not be modified, you can mark some or most of a partitioned table's filegroups as read-only, making management of the filegroups easier.
• In SQL Server 2008, you can compress individual partitions as well as control lock escalation at a partition level.

Challenges of table Partitioning

• There is a maximum of 1,000 partitions for a table.
• You must manage filegroups and file placement if you place partitions on individual filegroups.
• The metadata-only operations (SWITCH, MERGE, and SPLIT) can be blocked by other DML actions on the table at the time, until a schema-modification lock can be obtained.
• Managing date or time-based data can be complex.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.
• Automating changes to partitioned tables, as in a sliding window scenario, can be difficult, but Microsoft provides some tools to assist in automating the process.

 

How to delete duplicate records in table?

Here is script to delete duplicate records in table:

DECLARE @Count int

DECLARE @FirstName nvarchar(50)
DECLARE @LastName nvarchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT FirstName, LastName, Count(*) - 1 FROM Users
GROUP BY FirstName, LastName
HAVING Count(*) > 1

OPEN dublicate_cursor
FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count

WHILE @@FETCH_STATUS = 0
BEGIN
DELETE TOP(@Count) FROM Users WHERE FirstName = @FirstName AND LastName = @LastName

FETCH NEXT FROM dublicate_cursor INTO @FirstName, @LastName, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Please let know if you face any issue in this.

Rebuilding msdb database or msdb corrupt

If your msdb got corrupted or goes in suspect mode, then you left with two options:

1. Restore from last known good backup.
2. Recreate it.

Most of you know how to restore it, in this post I will tell you how to recreate it.
Steps to recreate msdb:

1. Detach the damaged msdb: You can't just detach msdb because you're not allowed to detach system databases. However, you can if you start the server with trace flag 3608. You can do this by shutting down the server, open cmd (command prompt) navigating to the directory 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' (this is the location where you installed your SQL Server, it can be different on your server) and doing the following: sqlservr.exe -c -T3608
   In another command window run sqlcmd -AthenSp_detach_db 'msdb'; GO
2. Remove the data and log files for msdb
3. Run the instmsdb.sql script in the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
4. Now stop the cmd shell by ctrl + c then shutdown the server and start it again in normal way.
5. And now create the control_client job.

Simple .

Please send me response if you face any issue.