SQL-Articles

Today while checking a server for SQL Server startup performance issue, I’m in thought of listing the startup procedures and to analyze those procedures for any issues. But when I google I couldn’t find any script to list out the startup procedures, hence I’ve written a script to list the startup procedures. I’m sharing the same with you all. You can get the script from below link.

Just for your knowlege, SQL Server startup procedures will exists only in master database. You can’t enable startup option for a procedure which resides in any other database other than master db.

Applies to:

SQL Server 2000
SQL Server 2005
SQL Server 2008

Click here to download the script

Scripts, T-SQL T-SQL

Introduction:

SQL Backup script is available in all online resources. I couldn’t find a script to take care of retention also, so I’m writing this script as procedure to take care of retention too. This script will take full backup of all the databases.

Applies to:

• SQL Server 2000
• SQL Server 2005
• SQL Server 2008

Pre-requisites:

• Xp_cmdshell procedure should be enabled both in SQL 2005 & SQL 2008

Script:

Dowload the script HERE

Usage:

EXEC master.dbo.usp_backup ‘F:\Bkup\’,1

Output:

$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
$ SERVERNAME: SAGARSYS — DATE: Jun 28 2008 10:00AM $
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

Retention
^^^^^^^^^
Deleting the below backup files as part of retention plan……
output
———————————————————
master_20080627.bak
model_20080627.bak
msdb_20080627.bak
NULL

output
———————————————————-
NULL

Database Backups Started
^^^^^^^^^^^^^^^^^^^^^^^^
************Processing master Backup… **************
Processed 352 pages for database ‘master’, file ‘master’ on file 1.
Processed 3 pages for database ‘master’, file ‘mastlog’ on file 1.
BACKUP DATABASE successfully processed 355 pages in 0.747 seconds (3.887 MB/sec).
Backed up to F:\Bkup\master_20080628.bak
********************************************************

************Processing model Backup… **************
Processed 152 pages for database ‘model’, file ‘modeldev’ on file 1.
Processed 2 pages for database ‘model’, file ‘modellog’ on file 1.
BACKUP DATABASE successfully processed 154 pages in 0.344 seconds (3.655 MB/sec).
Backed up to F:\Bkup\model_20080628.bak
********************************************************

************Processing msdb Backup… **************
Processed 608 pages for database ‘msdb’, file ‘MSDBData’ on file 1.
Processed 2 pages for database ‘msdb’, file ‘MSDBLog’ on file 1.
BACKUP DATABASE successfully processed 610 pages in 0.874 seconds (5.712 MB/sec).
Backed up to F:\Bkup\msdb_20080628.bak
********************************************************

============Backup Completed Successfully============

Discussion:

All your comments are highly appreciated, please post your comments @ FORUMS section

SQL Server 2005, SQL Server 2008, Scripts Backup, T-SQL

Introduction

Services uptime can be checked through WMI scripts and other methods also. As a DBA most of us would like to know the uptime of SQL Server, i.e how much time is SQL Server running till the server is on. You can do almost all the stuffs in T-SQL, hence I’m writing the script in T-SQL to find out SQL Server Uptime. You can also use this script to check SQLServer service and SQLAgent server are running status!

SQLScript:

SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME=‘tempdb’
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT ‘SQL Server “‘ + CONVERT(VARCHAR(20),SERVERPROPERTY(‘SERVERNAME’))+‘” is Online for the past ‘+@hr+‘ hours & ‘+@min+‘ minutes’
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N’SQLAgent - Generic Refresher’)
BEGIN
PRINT ‘SQL Server is running but SQL Server Agent <<NOT>> running’
END
ELSE BEGIN
PRINT ‘SQL Server and SQL Server Agent both are running’
END

Output:

SQL Server “SAGARSYS” is Online for the past 2 hours & 48 minutes
SQL Server and SQL Server Agent both are running

Scripts, T-SQL SQL Server, T-SQL

I’m writing this script since most of them are looking a way to find total diskspace available in a drives through sql server. I hope there is no extended procedure for this. I’ve used WMI script to do this, download the vbs script and save it to a location and use this location in the sql script to get the result.

SQL Script

SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp’)
DROP TABLE ##tmp
CREATE TABLE ##tmp(diskspace VARCHAR(200))
INSERT ##tmp
EXEC master.dbo.xp_cmdshell ‘cscript C:\diskspace.vbs’ – change the path here
SET ROWCOUNT 3
DELETE ##tmp
SET ROWCOUNT 0
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE NAME =‘##tmp2′)
DROP TABLE ##tmp2
CREATE TABLE ##tmp2(Driveletter VARCHAR(2),TotalDiskSpace_in_MB FLOAT, Freespace_in_MB FLOAT)
INSERT ##tmp2
SELECT SUBSTRING(diskspace,1,3) , CONVERT(FLOAT,SUBSTRING(diskspace,4,10)),
CONVERT(FLOAT,SUBSTRING(diskspace,15,10)) FROM ##tmp WHERE diskspace IS NOT NULL
SELECT * FROM ##tmp2

Sample Output

Download diskspace.vbs

Scripts, T-SQL, Uncategorized Diskspace, SQL Server, T-SQL

Hi Friends,

In many companies developers won’t be given administrator privilege in OS on their test or development machines. When they execute a query and consider its consuming high CPU usage, since they won’t have admin privilege they are not able to confirm that the CPU is consumed by sqlservice or not. I’ve written a T-SQL Code to check the CPU usage consumed by Sqlservice.
Check out the below script.

Code:

DECLARE @CPU_BUSY int, @IDLE int
SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE WAITFOR DELAY ‘000:00:01′
SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) *1.00) *100 AS ‘CPU Utilization by sqlsrvr.exe’

Output:

CPU Utilization by sqlsrvr.exe
—————————————
0.850000000000000

Hope this small script will help you to check out sqlservice usage via T-SQL Statement!

Scripts CPU usage by sql service