Search This Blog

Loading...

Thursday, March 27, 2014

Shrink the data or log files using DBCC SHRINKFILE

DBCC SHRINKFILE shrinks the size of database/log file of the current database either to the specific size or as small as it can.

Below query gives you the name of the database/log file, Total size(MB), Used Space(MB), %Used, Physical location of file and the Shrink SQL statement with the specific size which you can execute on the database to shrink the respective file:

SELECT file_id ,name ,size_mb=size/128.0 ,UsedSpace_MB=fileproperty(name, 'spaceused')/128.0 ,UsedSpace_Pct = CAST(fileproperty(name, 'spaceused')*1./size as decimal(5,2)) * 100 ,physical_name ,shrink_stmt='dbcc shrinkfile(' + CAST(file_id as varchar) + ',' + CAST((fileproperty(name, 'spaceused')/128 + 1) as varchar) + ')' FROM sys.database_files

Tuesday, September 11, 2012

SSIS - FastLoad options with Triggers

Lets say I have a table (DestTable) which has a trigger on INSERT event.
Now if I am populating the data into this table (DestTable) using SSIS FastLoad option then "will the trigger execute?".

Any guess!!!!!!!!!!!!!!!!!!

Using FastLoad option to populate the records into the table will not execute trigger.
If you want to execute the trigger using FastLoad option then right-click the destination component and click "Show Advance Editor....", go to "Component Properties" and then mention FIRE_TRIGGERS in FastLoadOptions.
Same we use while inserting the data using BULK INSERT in T-SQL.

Wednesday, November 16, 2011

Alternative of CTE

Few days back I was facing a performance issue due to CTE. Then I replaced the CTE with set of sql statements and got the performance boost of 96%.
Here is the sample code which I used :

CREATE TABLE #t (assetid INT, assetname varchar(25), parentassetid INT)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(1,'aa',null)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(100,'a',1)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(101,'b',100)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(102,'c',100)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(103,'d',101)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(105,'e',102)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(106,'f',108)
INSERT INTO #t (assetid,assetname,parentassetid) VALUES(107,'e',102)


CREATE TABLE #item (id INT identity(1,1), assetid INT, assetname varchar(25), lvl INT)
DECLARE @counter INT
SET @Counter = 0
INSERT INTO #item (assetid,assetname,lvl) SELECT assetid,assetname, @counter FROM #t WHERE parentassetid = 1

WHILE @@ROWCOUNT > 0
BEGIN

SET @counter = @counter + 1

INSERT INTO #item (assetid,assetname,lvl) SELECT a.assetid,a.assetname,@counter FROM #t a INNER JOIN #item b
on a.parentassetid = b.assetid and exists (SELECT 1 FROM #item c WHERE b.assetid = c.assetid and c.lvl = (@counter-1))

END
SELECT * FROM #item

I hope this will help you!!!!

Friday, June 17, 2011

Alternate way of RowCount using sys.partitions

Usually we use COUNT(1) to get the number of rows in a table which is time consuming in case of large tables.

Here is the alternate method to get the Row Count using system view in efficient manner:

SELECT OBJECT_NAME(OBJECT_ID), Rows FROM sys.partitions
WHERE INDEX_ID IN (0,1) -- 0 = Heap;1 = Clustered
AND OBJECT_ID = OBJECT_ID('TableName')

To know more about sys.partitions

Wednesday, September 1, 2010

Find the open transaction details in Sql Server

The below query can be used to get the details of OPEN TRANSACTION:

SELECT DB_NAME(dbid) AS DBNAME, (SELECT text FROM sys.dm_exec_sql_text(sql_handle)) AS SQLSTATEMENT FROM master..sysprocesses WHERE open_tran > 0

Monday, August 30, 2010

How to get the size of the database

Today i am posting the methods to find the size of database through the sql statement.
Method 1: Using sp_helpdb
The below statement gives the information about all the databases:
EXEC sp_helpdb

whereas the below gives the information about a specified database (in this case about master database):
EXEC sp_helpdb N'master'

Method 2: Using sys.master_files
It is a system view which contains a row per file of a database as stored in the master database:
SELECT DB_NAME(database_id), SUM(size)*8/1024 as size_in_MB FROM sys.master_files GROUP BY database_id

Wednesday, July 21, 2010

How to find the unused indexes in Sql Server

SELECT OBJECT_NAME(si.OBJECT_ID) as TableName, si.NAME as IndexName, si.INDEX_ID as IndexID
FROM
sys.indexes si INNER JOIN sys.objects so ON si.object_id = so.object_id
WHERE OBJECTPROPERTY(si.object_id,'IsUserTable') = 1 AND index_id NOT IN
(
SELECT index_id FROM sys.dm_db_index_usage_stats sdmv
WHERE sdmv.object_id = si.object_id AND sdmv.index_id = si.index_id
)