Protected: CTE

This post is password protected. To view it please enter your password below:

Protected: framework

This post is password protected. To view it please enter your password below:

Protected: ssis

This post is password protected. To view it please enter your password below:

sp_BLITZ™ – SQL Server Takeover Script

Really useful script to check your SQL Server environment.

http://www.brentozar.com/blitz/

 

Database stuck in restoring state

Usually due to a restore being done without checking the RESTORE WITH RECOVERY option. But check first sp_who2 and run the following to see if the backup is really running a restore:-

SELECT percent_complete ,
*
FROM sys.dm_exec_requests
WHERE command LIKE ‘%RESTORE DATABASE%’

If no real backup is being done try:-

RESTORE DATABASE [DBName]
WITH RECOVERY

Cannot shrink log file 4 (*****_Log2) because all logical log files are in use.

Shrinking log issue – Unable to shrink the log message:

Cannot shrink log file 4 (*****_Log2) because all logical log files are in use.
Shrinking files issue, the following command fails.

USE *****
DBCC SHRINKFILE (*****_Log2, TRUNCATEONLY)

Unable to shrink log all the ususal suspects failed, message given:-

Cannot shrink log file 4 (*****_Log2) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I even tried flushing dummy transactions through the log. Nothing worked however when we looked at the log_reuse_wait_desc field from select * from sys.databases we saw REPLICATION in this field indicating that replication was still running. We only have snapshot replication set up and it wasn’t/shouldn’t be running. I cleared this message by the following command:-

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

The logs now shrink back without issue.

 

Install SQL Server 2005 on drive other than C:

One of the things that can catch you out is that SQL Server 2005 will always install on the C drive unless you take action to avoid it, and that means you can not just run the setup directly from the install CD. Assuming you have already made this mistake and you have installed on the C drive

Solution -
1. remove the sql server 2005 in add remove programs….DO NOT remove sql native client.

2. Run the below command in command prompt to configure the setup path manually:

D:\TEMPSQL\SERVERS is the setup folder of the sql cd.

start /wait D:\TEMPSQL\SERVERS\setup.exe INSTANCENAME=MSSQLSERVER INSTALLSQLSHAREDDIR=”d:\sqlserver2005\90\Tools” INSTALLSQLDIR=”d:\sqlserver2005″ INSTALLSQLDATADIR=”d:\sqlserver2005\DATA” ADDLOCAL=All

 

Create Database Mail in SQL Server 2005/2008

/*==========================================================================

– Purpose: Used to create Database Mail in SQL Server 2005

– Author: Carolyn Richardson

– Date: 24/09/2007

– ***Important***

Replace servername with actual servername to make it clear re which server is sending the mail, also alter the mail server details.

==========================================================================*/

–Reconfigure the server to allow email

 

use master

go

sp_configure

‘show advanced options’,1

go

reconfigure

with override

go

sp_configure

‘Database Mail XPs’,1

reconfigure

go

/*Add Service Account Permission

As an extra security feature SQL Server 2005 uses a role with specific permissions in the MSDB database, not even system administrators can use the mail feature without being a member of this role. So you need to either add the specific account that runs SQL Server to this role, or add all administers:-

*/

 

EXECUTE

msdb.dbo.sysmail_add_account_sp

@account_name =’ServerNameMailAccount’,

@description

=’Mail account for Database Mail’,

@email_address

=’ServerName@address.com’,

@display_name

=’ServerNameMailAccount’,

@use_default_credentials

= 0,

@mailserver_name =’smtp.domain.net’ –alter mailserver details

/*

If you look at the propertities you may want to altert the anonymous login to SQL Server authentication.

Create Mail Profile

The next component of the configuration requires the creation of a Mail profile.

We are going to create “ServernamMailProfile” using the sysmail_add_profile procedure to create a Database Mail profile.

For help on options use sp_helptext sysmail_add_profile_sp

*/

EXECUTE

msdb.dbo.sysmail_add_profile_sp

@profile_name =’ServerNameMailProfile’,

@description

=’Profile used for database mail’

/*

Add Account Profile

Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account using the Database Mail profile you created in last step

For help on options use sp_helptext sysmail_add_profileaccount_sp

*/

 

EXECUTE

msdb.dbo.sysmail_add_profileaccount_sp

@profile_name =’ServerNameMailProfile’,

@account_name

=’ServerNameMailAccount’,

@sequence_number

= 1

/*Set default Profile

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.

For help on options use sysmail_add_principalprofile_sp

*/

 

EXECUTE

msdb.dbo.sysmail_add_principalprofile_sp

@profile_name =’ServerNameMailProfile’,

@principal_name

=’public’,

@is_default

= 1 ;

/*To send a test email from SQL Server. Execute the statement below.*/

 

declare

@body1 varchar(100)

set

@body1 =’Server :’+@@servername+’ My First Database Email ‘

EXEC

msdb.dbo.sp_send_dbmail @recipients=’email@address.com’, –change to your email address

@subject

=’My Mail Test’,

@body

= @body1,

@body_format

=’HTML’;

To set up SQL Server Agent Mail to use Database Mail

In Object Explorer, expand a server.

Right-click SQL Server Agent, and then click Properties.

Click Alert System.

Select Enable Mail Profile.

In the Mail system list, select Database Mail.

In the Mail profile list, select a mail profile for Database Mail.

Restart SQL Server Agent.