New article on “Temp table VS Table variable” is posted in main website. Check it out from the link below
Most of the SQL Developers/DBA would have come across a situation where they need to store the temporary result sets. This is where Temp tables and Table variables come into effect and helps in storing the data sets in a temporary location
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
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
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
Driveletter |
TotalDiskSpace_in_MB |
Freespace_in_MB |
|
C: |
12644.8789 |
1153.70312 |
|
D: |
45504.3945 |
29603.9921 |
|
E: |
10001.371 |
450.316406 |
Lets see the difference between DELETE and TRUNCATE command as below
DELETE |
TRUNCATE |
|
DML Statement |
DDL Statement |
|
Data Rollback is possible, all the row by row data deletion will be recorded in transaction log |
Rollback is possible for entire table and not at row level, since data page reallocation will be logged in transaction log Eg.) BEGIN TRAN TRUNCATE Table Tablename ROLLBACK |
|
No Commit is performed neither before nor after. (Because it is a DML Statement) |
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement) |
|
Row level locking will happen while performing deletion |
Table level locking will be happen while performing truncation |
|
A delete does not move the High Water Mark of the table back to zero, it retains its original position. |
A truncate moves the High Water Mark of the table back to zero |
|
WHERE statement can be included |
WHERE statement cannot be included |
|
Activates DML trigger |
Doesn’t activate DDL trigger |
|
Able to execute the command on the table that contains foreign key |
Not able to execute the command on the table that contains foreign key |
|
Consume more resources since need to process row by row and all these are logged operation |
Consume less resource since it will process the full table and its non-logged operation |
Lets discuss the above points using an example