I’m starting this topic since last week I had faced a backup issue with compressed drive. In one of our server we have used compressed folder to take backup of our SQL databases. This is working fine and the db is used for OLTP application and it had grown about 73GB. Now we have started facing backup failures only for that database alone all other databases (less than 10GB) are running fine in the maintenance plan. I’ve checked the machine performance and SQL Server is the only process using 50% of CPU and 3GB of memory, other than that the server is not under stress and in fact that box is dedicated to SQL Server.
Tag Archives: Administration - Page 4
Linked Server Problem – Windows 2003 SP1. (Setting MSDTC Security Configuration)
Posted by Vidhya Sagar
on April 26, 2009
2 comments
In one of our server running Windows 2003 & SQL Server 2000 our system administrators has applied the service pack 1 and they have completed successfully. The next day night all the SQL jobs in another server (Windows 2000 & SQL Server 2000) which is configured to link the Win2k3 server has been failed with the error message below.
Executed as user: mydomain\sqlaccount. The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction. [SQLSTATE 42000] (Error 7391) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. [SQLSTATE 01000] (Error 7300). The step failed.
Startup procedure to check SQL Server status when restarted
Posted by Vidhya Sagar
on April 23, 2009
Comments Off
I’ve written a startup procedure to check SQL Server status when the SQL Server gets restarted. Most of the time as a DBA when a Server gets rebooted or SQL Services is restarted we need to make sure everything is fine in SQL server to make sure SQL server is running healthy. Some companies will have budget to buy a monitoring tool, so that all these things will be taken care by that tool. This small procedure will help the DBA’s to know the status of SQL Server in HTML format through mail when it’s restarted.
Applies to:
- SQL Server 2005
- SQL Server 2008
Pre-Requisites
- Xp_cmdshell procedure should be in enabled state
- A database mail profile should be configured as default profile (else you need to provide this profile information inside the procedure)
- Script
- This script will monitor the below parameters in SQL Server.
- SQL Server Uptime
- SQL Server Service Status
- SQL Server Agent Status
- Database(s) status
- Authentication mode
/*
Procedure Name : serverstartup
Written By : Vidhya Sagar
www.sql-articles.com
*/
USE master
GO
CREATE PROC serverstartup
AS
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##sqlservice')
DROP TABLE ##sqlservice
CREATE TABLE ##sqlservice (details VARCHAR(100))
IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##agentservice')
DROP TABLE ##agentservice
CREATE TABLE ##agentservice (details VARCHAR(100))
WAITFOR DELAY '00:02'
DECLARE @sname VARCHAR(100), @starttime VARCHAR(30)
DECLARE @authmode VARCHAR(25), @subject VARCHAR(250)
DECLARE @insname VARCHAR(50),@agentname VARCHAR(50)
DECLARE @sqlstatus VARCHAR(100), @agentstatus VARCHAR(100)
DECLARE @dbstatus VARCHAR(100), @dbdetail VARCHAR(2000)
DECLARE @sctsql VARCHAR(200), @HTML VARCHAR(8000)
SELECT @sname = @@SERVERNAME
SELECT @authmode=case SERVERPROPERTY('IsIntegratedSecurityOnly') when 1 then 'Windows' else 'Mixed' end
SELECT @starttime=CONVERT(VARCHAR(30),create_date,109) from sys.databases where database_id=2
IF (serverproperty('InstanceName')) IS NOT NULL
BEGIN
SET @insname='mssql$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))
SET @agentname='sqlagent$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))
END
ELSE
BEGIN
SET @insname='mssqlserver'
SET @agentname='sqlserveragent'
END
SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@insname+' | FIND "STATE"'''
INSERT ##sqlservice
EXEC (@sctsql)
SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@agentname+' | FIND "STATE"'''
INSERT ##agentservice
EXEC (@sctsql)
IF EXISTS(SELECT 1 FROM ##sqlservice WHERE details LIKE '%RUNNING%')
SET @sqlstatus = 'Running'
ELSE
SET @sqlstatus = '<font color="red">Not Running</font>'
IF EXISTS(SELECT 1 FROM ##agentservice WHERE details LIKE '%RUNNING%')
SET @agentstatus = 'Running'
ELSE
SET @agentstatus = '<font color="red">Not Running</font>'
IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc<>'ONLINE')
BEGIN
SET @dbstatus= '<font color="red">Some of the database(s) are offline</font>'
SELECT @dbdetail = '<table border="1"><tr><th>Database Name</th><th>Database Status</th></tr><tr>'
SELECT @dbdetail = @dbdetail + '<td align="Center">' + name + '</td><td align="Center">'+state_desc+'</td></tr></table>' FROM sys.databases WHERE state_desc<>'ONLINE'
END
ELSE
BEGIN
SET @dbdetail = ''
SET @dbstatus='All databases are online'
END
SET @subject=@sname+' : SQL Server is restarted. Please Check'
SET @HTML='<h3>'+@sname+'</h3><br>'+'<table border="1"><tr><th>SQL Server Startup time</th><th>SQL Server Service</th><th>SQL Agent Service</th> <th>Database(s) Status</th><th>Authentication Mode</th></tr><tr><td align="Center">'+
@starttime+'</td><td align="Center">'+@sqlstatus+'</td><td align="Center">'+@agentstatus+'</td><td align="Center">'+@dbstatus+'</td><td align="Center">'+@authmode+'</td></tr></table><br><br>'+@dbdetail
EXEC msdb.dbo.sp_send_dbmail
@recipients ='kvs1983@indiamvps.net',
@subject=@subject,
@body =@HTML,
@body_format ='HTML'
GO
---Enabling startup parameter for that procedure
EXEC sp_procoption 'serverstartup','startup',True
GO
In the script above there are two parameters that need to be taken care by the implementer.
- WAITFOR – I’ve used this command since this is the time for the database to get into steady state i.e. roll back and roll forward time. If the database is in huge size then the roll back or roll forward will take some time, you need to adjust this value based on the time its taking. By default I’ve set this value to 2 min.
- Mail Recipients – You need to provide the email address to whom the alert mail need to be delivered. In the script above I’ve used my hotmail ID. Please change this to your mail ID. If you don’t have a default mail profile, then use @profile_name parameter in the mail procedure to use the correct mail profile.
Testing
Let’s test the script developed. Since the script is marked for startup when ever SQL Server is restarted this procedure will run and provide the health check status for SQL Server to the mail recipients given in the script.
- When Everything is normal and running fine.
- In this case I’ve stopped SQL Agent service, lets check the alert mail. You can find SQL Agent status as not running.
- Now lets make a database to emergency mode. I’ve created a new database named EMERGENCY_TEST and made it to emergency mode. From the mail below you can check that you can get the db details too.
I’m sharing this procedure since some of the DBA’s might use this procedure to simplify their process.
Your comments are suggestions are always welcomed to improve me better. You can either post your queries @ Forums sections else mail me.
Resource Database – 5th System database
Posted by Vidhya Sagar
on April 17, 2009
Comments Off
In this article we are going to take a look at the 5th system databases available right from SQL Server 2005. As everyone know till SQL Server 2000 there are four system database is available and they are master, model, msdb and tempdb databases. From SQL Server 2005 Microsoft has introduced a new system database to the database engine called Resource database (actual name for this database is mssqlsystemresource ) which is hidden to the users. Read more »
Return Code Values Documentation for sp_send_dbmail procedure
Posted by Vidhya Sagar
on March 31, 2009
Comments Off
I saw a question on MSDN forums today about return code values documentation for sp_send_dbmail procedure. Microsoft has not documented these return values to the user because when an error exists in the code the user will get a clear description about the error which is easy to understand hence they have provided only description for 0 (failure) and 1 (success). I’ll provide the list of return values and their description here, I’m sharing this since some of the developers will read this value and they can make use of it to get the reason for failure.