DECLARE
@path VARCHAR(255),
@cmd VARCHAR(300);
-- customize this path, of course, if necessary:
SELECT
@path = 'C:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\',
@cmd = 'dir /b "' + @path + '*.trc"';
DECLARE @files TABLE
(
fn VARCHAR(64)
);
INSERT @files
EXEC master..xp_cmdshell @cmd;
DELETE @files
WHERE fn IS NULL;
SELECT
e.DatabaseName,
e.[FileName],
e.SPID,
e.Duration,
e.StartTime,
e.EndTime,
FileType = CASE e.EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END,
[TraceFile] = f.fn
FROM
@files f
CROSS APPLY
fn_trace_gettable(@path + f.fn, DEFAULT) e
WHERE
e.EventClass IN (92,93)
-- AND f.fn LIKE 'log[_][0-9]%.trc'
ORDER BY
e.StartTime DESC;
Wednesday, June 09, 2010
Excel - if a cell value exist in list on other column list
=IF(ISNA(VLOOKUP(A15,$E$1:$E$600,1,FALSE)),"",A15)
Tuesday, June 08, 2010
Cleanup duplicate record, another interesting method
SET ROWCOUNT 1
AGAIN:
DELETE member_mlc FROM
(SELECT memberid,mlc_status FROM member_mlc GROUP BY memberid,mlc_status HAVING COUNT(*) > 1) a
WHERE member_mlc.memberid = a.memberid and member_mlc.mlc_status = a.mlc_status
IF @@ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
AGAIN:
DELETE member_mlc FROM
(SELECT memberid,mlc_status FROM member_mlc GROUP BY memberid,mlc_status HAVING COUNT(*) > 1) a
WHERE member_mlc.memberid = a.memberid and member_mlc.mlc_status = a.mlc_status
IF @@ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
Thursday, May 27, 2010
Wildcard search characters in SQL
underscore is a wildcard for single charachter
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '_a_a%' order by TABLE_NAME
ces_barclaysplc dbo data_type BASE TABLE
ces_barclaysplc dbo data_type_group BASE TABLE
ces_barclaysplc dbo data_type_group_operator BASE TABLE
ces_barclaysplc dbo datafeed_questionbank BASE TABLE
ces_barclaysplc dbo datafeed_questionbank_BAKUP BASE TABLE
ces_barclaysplc dbo datafeed_testanswer BASE TABLE
ces_barclaysplc dbo datafeed_testbank BASE TABLE
ces_barclaysplc dbo datafeed_testquestion BASE TABLE
to search for underscore use [_]
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '[___]%' order by TABLE_NAME
ces_barclaysplc dbo ___analytics_report_schedule_08_26_2009 BASE TABLE
ces_barclaysplc dbo ___datafeed_talent_20100210 BASE TABLE
ces_barclaysplc dbo ___datafeed_users_20100210 BASE TABLE
ces_barclaysplc dbo ___dataseg_ucf BASE TABLE
ces_barclaysplc dbo ___lo_options BASE TABLE
ces_barclaysplc dbo ___loadrl BASE TABLE
ces_barclaysplc dbo ___pdOnline_20100210 BASE TABLE
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '_a_a%' order by TABLE_NAME
ces_barclaysplc dbo data_type BASE TABLE
ces_barclaysplc dbo data_type_group BASE TABLE
ces_barclaysplc dbo data_type_group_operator BASE TABLE
ces_barclaysplc dbo datafeed_questionbank BASE TABLE
ces_barclaysplc dbo datafeed_questionbank_BAKUP BASE TABLE
ces_barclaysplc dbo datafeed_testanswer BASE TABLE
ces_barclaysplc dbo datafeed_testbank BASE TABLE
ces_barclaysplc dbo datafeed_testquestion BASE TABLE
to search for underscore use [_]
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME like '[___]%' order by TABLE_NAME
ces_barclaysplc dbo ___analytics_report_schedule_08_26_2009 BASE TABLE
ces_barclaysplc dbo ___datafeed_talent_20100210 BASE TABLE
ces_barclaysplc dbo ___datafeed_users_20100210 BASE TABLE
ces_barclaysplc dbo ___dataseg_ucf BASE TABLE
ces_barclaysplc dbo ___lo_options BASE TABLE
ces_barclaysplc dbo ___loadrl BASE TABLE
ces_barclaysplc dbo ___pdOnline_20100210 BASE TABLE
Tuesday, May 25, 2010
Show statistics and query plans
SET SHOWPLAN_ALL on
go
set statistics IO on
set statistics time on
.....QUERY IS HERE
GO
SET SHOWPLAN_ALL off
go
go
set statistics IO on
set statistics time on
.....QUERY IS HERE
GO
SET SHOWPLAN_ALL off
go
Monday, May 24, 2010
Resource Governor in SQL Server 2008 - Sample
http://www.sql-server-performance.com/articles/per/Resource_Governor_in_SQL_Server_2008_p1.aspx
Thursday, May 20, 2010
VBScripts to manage Text Files
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/other/textfiles/
Wednesday, May 19, 2010
Tuesday, May 18, 2010
filegroup get all DB tables and views info
-- Get all DB tables and views filegroup info
SET NOCOUNT ON
-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)
-- Get the file group information for all indexes of all tables and indexedviews
-- on the database. Note that auto-statistics created by SQL server are excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0
SET NOCOUNT OFF
SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC
-- Cleanup
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO
----------------------
--another simple way--
----------------------
-- to identofy which tables and indexes are used in which filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('Third')
select name from sysindexes where groupid=filegroup_id('Third') and indid > 0
SET NOCOUNT ON
-- This table will hold the collected filegroup information
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
CREATE TABLE #tblFileGroupInfo(
TableOrViewName NVARCHAR(128),
[Table/View] VARCHAR(16),
AttributeName NVARCHAR(128),
AttributeType VARCHAR(128),
AttributeIndid INT,
FileGroupName NVARCHAR(128),
FileGroupID INT)
-- Get the file group information for all indexes of all tables and indexedviews
-- on the database. Note that auto-statistics created by SQL server are excluded,
-- since they are hardly of interest.
INSERT INTO #tblFileGroupInfo(
TableOrViewName,
[Table/View],
AttributeName,
AttributeType,
AttributeIndid,
FileGroupName,
FileGroupID)
SELECT a.TABLE_NAME,
CASE WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'
ELSE 'Table'
END,
ISNULL(b.[name], 'NA'),
CASE WHEN b.indid = 0 THEN 'Table Row Data (table has no
clustered index)'
WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'
WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'
ELSE 'Non Clustered Index'
END,
b.indid,
FILEGROUP_NAME(b.groupid),
b.groupid
FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)
INNER JOIN sysindexes b WITH (NOLOCK)
ON OBJECT_ID(a.TABLE_NAME) = b.[id]
WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0
SET NOCOUNT OFF
SELECT *
FROM #tblFileGroupInfo WITH (NOLOCK)
ORDER BY TableOrViewName ASC, AttributeIndid ASC
-- Cleanup
IF OBJECT_ID('tempdb..#tblFileGroupInfo', 'U') IS NOT NULL
DROP TABLE #tblFileGroupInfo
GO
----------------------
--another simple way--
----------------------
-- to identofy which tables and indexes are used in which filegroup
select distinct(object_name(id)) from sysindexes where groupid=filegroup_id('Third')
select name from sysindexes where groupid=filegroup_id('Third') and indid > 0
Filegroup space usage and allocation
create table #Data(
FileID int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)
create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
FileID int null
)
create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)
INSERT #Data (Fileid , [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')
update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid
INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, FileID)
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
FileID
FROM #Data
order BY 1,2
insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')
insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.FileId FileID
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.fileid=f.fileid
and l.db = DB_NAME()
SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5
DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log
FileID int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)
create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
FileID int null
)
create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)
INSERT #Data (Fileid , [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')
update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid
INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, FileID)
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
FileID
FROM #Data
order BY 1,2
insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')
insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, FileID)
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.FileId FileID
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.fileid=f.fileid
and l.db = DB_NAME()
SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.FileID = s.FileID
ORDER BY 1,2,3,4,5
DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log
Subscribe to:
Posts (Atom)