Friday, September 14, 2012
Friday, September 07, 2012
Reindex Online tables - maintenance_re_index
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
@MaxFragmentation int = 10 ,
@TrivialPageCount int = 100 ,
@RebuildThreshold int = 40
)
as
BEGIN
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;
--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;
---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
DROP TABLE #work_to_do;
SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SET @HasBlobColumn = 0 -- reinitialize
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- Check for BLOB columns
IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
inner join sys.columns SC ON SO.Object_id = SC.object_id
inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
ELSE -- nonclustered. Only need to check if indexed column is a BLOB
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
IF @frag > @RebuildThreshold
BEGIN
SET @command = @command + N' REBUILD'
IF @HasBlobColumn = 1
SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
ELSE
SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
END
ELSE
SET @command = @command + N' REORGANIZE'
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
--PRINT @command
--print 'print '''+@command +''''
--print 'Go'
EXEC (@command)
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
END
/*
maintenance_re_index
*/
ALTER Procedure maintenance_re_index
(
@MaxFragmentation int = 10 ,
@TrivialPageCount int = 100 ,
@RebuildThreshold int = 40
)
as
BEGIN
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @HasBlobColumn int;
--DECLARE @MaxFragmentation int;
--DECLARE @TrivialPageCount int;
--DECLARE @RebuildThreshold int;
---- Tuning constants
--SET @MaxFragmentation = 10 --Change this value to adjust the threshold for fragmentation
--SET @RebuildThreshold = 30 --Change this value to adjust the break point for defrag/rebuild
--SET @TrivialPageCount = 1000 --Change this value to adjust the size threshold
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
if(OBJECT_ID('tempdb..#work_to_do')>1)
DROP TABLE #work_to_do;
SELECT distinct
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @MaxFragmentation
AND index_id > 0 -- cannot defrag a heap
AND page_count > @TrivialPageCount -- ignore trivial sized indexes
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT distinct WTD.*
FROM #work_to_do WTD
INNER JOIN sys.indexes I ON I.object_id = WTD.objectid
WHERE I.is_disabled = 0 AND I.is_hypothetical = 0;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SET @HasBlobColumn = 0 -- reinitialize
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- Check for BLOB columns
IF @indexid = 1 -- only check here for clustered indexes ANY blob column on the table counts
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
inner join sys.columns SC ON SO.Object_id = SC.object_id
inner join sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
ELSE -- nonclustered. Only need to check if indexed column is a BLOB
SELECT @HasBlobColumn = CASE WHEN max(so.object_ID) IS NULL THEN 0 ELSE 1 END
FROM sys.objects SO
INNER JOIN sys.index_columns SIC ON SO.Object_ID = SIC.object_id
INNER JOIN sys.Indexes SI ON SO.Object_ID = SI.Object_ID AND SIC.index_id = SI.index_id
INNER JOIN sys.columns SC ON SO.Object_id = SC.object_id AND SIC.Column_id = SC.column_id
INNER JOIN sys.types ST ON SC.system_type_id = ST.system_type_id AND
(
ST.name IN ('varchar', 'nvarchar', 'varbinary') AND SC.max_length = -1
OR
ST.name IN ('text', 'ntext', 'image', 'xml')
)
WHERE SO.Object_ID = @objectID
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname;
IF @frag > @RebuildThreshold
BEGIN
SET @command = @command + N' REBUILD'
IF @HasBlobColumn = 1
SET @command = @command + N' WITH( SORT_IN_TEMPDB = ON) '
ELSE
SET @command = @command + N' WITH( ONLINE = ON, SORT_IN_TEMPDB = ON) '
END
ELSE
SET @command = @command + N' REORGANIZE'
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
PRINT N'Executing: ' + @command +' Has Blob = ' + convert(nvarchar(2),@HasBlobColumn);
--PRINT @command
--print 'print '''+@command +''''
--print 'Go'
EXEC (@command)
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
END
Friday, August 31, 2012
parent child relation hierachy script
with cte as
(
select 1 as lvl, SpaceShuttleID , ParentSpaceShuttleID from SpaceShuttle where SpaceShuttleID = 200
union all
select cte.lvl + 1, b.SpaceShuttleID , b.ParentSpaceShuttleID from SpaceShuttle b join cte on cte.SpaceShuttleID = b.ParentSpaceShuttleID
)
select * from cte order by lvl;
Friday, August 17, 2012
modify sysjobs trigger to send alert
USE [msdb]
GO
/****** Object: Trigger [dbo].[trig_sysjobs_insert_update] Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view.
IF (EXISTS (SELECT *
FROM inserted
WHERE inserted.originating_server_id NOT IN
(SELECT v.originating_server_id
FROM sysoriginatingservers_view AS v)))
BEGIN
RAISERROR(14379, -1, -1, 'dbo.sysjobs')
ROLLBACK TRANSACTION
RETURN
END
else
begin
Declare @Body1 nvarchar(max)
Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'palexander@csod.com',
@body = @Body1,
@subject = 'SQL Server Job Modification Alert'
end
END
GO
/****** Object: Trigger [dbo].[trig_sysjobs_insert_update] Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view.
IF (EXISTS (SELECT *
FROM inserted
WHERE inserted.originating_server_id NOT IN
(SELECT v.originating_server_id
FROM sysoriginatingservers_view AS v)))
BEGIN
RAISERROR(14379, -1, -1, 'dbo.sysjobs')
ROLLBACK TRANSACTION
RETURN
END
else
begin
Declare @Body1 nvarchar(max)
Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'palexander@csod.com',
@body = @Body1,
@subject = 'SQL Server Job Modification Alert'
end
END
Thursday, August 16, 2012
SQL job queries
--JOB INFO
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
--JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'
Wednesday, August 15, 2012
lists all the default constraints and the default values for the user tables in the database
SELECT
b.name AS TABLE_NAME,
d.name AS COLUMN_NAME,
a.name AS CONSTRAINT_NAME,
c.text AS DEFAULT_VALUEFROM sys.sysobjects a INNER JOIN
(SELECT name, id
FROM sys.sysobjects
WHERE xtype = 'U') b on (a.parent_obj = b.id)
INNER JOIN sys.syscomments c ON (a.id = c.id)
INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)
WHERE a.xtype = 'D'
ORDER BY b.name, a.namefunction to get time for next job
use msdb
go
/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
@interval int = 10,
@jobname nvarchar(100) = null ,
@schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN
set @jobname = @jobname + '%'
set @schedulename = @schedulename + '%'
declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
declare @nexttime int
--set @nexttime = 10004
--set @nexttime = 206
--set @nexttime = 5506
--set @nexttime = 230847
--set @nexttime = 145712
--set @nexttime = 41942
--set @nexttime = 225847
--set @nexttime = 235847
select top 1 @nexttime = c.active_start_time
--a.schedule_id, b.name, c.active_start_time,c.name, *
from sysjobschedules a
inner join sysjobs b on a.job_id = b.job_id
inner join sysschedules c on a.schedule_id = c.schedule_id
where
(b.name like @jobname or @jobname is null)
and
( c.name like @schedulename or @schedulename is null)
order by c.active_start_time desc
select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10), @nexttime/100),2), @curr_sec = right(convert(nvarchar(10), @nexttime),2)
set @next_sec = @curr_sec
set @next_min = (@curr_min + @interval)%60
set @next_hr = @curr_hr + (@curr_min + @interval)/ 60
set @next_hr = case when @next_hr >= 24 then 0 else @next_hr end
--select @nexttime, @curr_hr , @curr_min , @curr_sec
--select @nexttime, @next_hr , @next_min , @next_sec
select @nexttime = convert(int,
(case when @next_hr < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
(case when @next_min < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
(case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
)
RETURN @nexttime
END
GO
go
/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
@interval int = 10,
@jobname nvarchar(100) = null ,
@schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN
set @jobname = @jobname + '%'
set @schedulename = @schedulename + '%'
declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
declare @nexttime int
--set @nexttime = 10004
--set @nexttime = 206
--set @nexttime = 5506
--set @nexttime = 230847
--set @nexttime = 145712
--set @nexttime = 41942
--set @nexttime = 225847
--set @nexttime = 235847
select top 1 @nexttime = c.active_start_time
--a.schedule_id, b.name, c.active_start_time,c.name, *
from sysjobschedules a
inner join sysjobs b on a.job_id = b.job_id
inner join sysschedules c on a.schedule_id = c.schedule_id
where
(b.name like @jobname or @jobname is null)
and
( c.name like @schedulename or @schedulename is null)
order by c.active_start_time desc
select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10), @nexttime/100),2), @curr_sec = right(convert(nvarchar(10), @nexttime),2)
set @next_sec = @curr_sec
set @next_min = (@curr_min + @interval)%60
set @next_hr = @curr_hr + (@curr_min + @interval)/ 60
set @next_hr = case when @next_hr >= 24 then 0 else @next_hr end
--select @nexttime, @curr_hr , @curr_min , @curr_sec
--select @nexttime, @next_hr , @next_min , @next_sec
select @nexttime = convert(int,
(case when @next_hr < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
(case when @next_min < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
(case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
)
RETURN @nexttime
END
GO
Wednesday, July 18, 2012
list spaces used by all DBs in a server : sp_dbspaceall
use master
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dbspaceall]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_dbspaceall]
GO
/*
exec sp_dbspaceall
*/
create procedure sp_dbspaceall
as
begin
Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] bigint
)
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') as spaceused from sys.sysfiles';
With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(
SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(14,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as DataFileSizeMB,
convert(dec(14,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(14,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100) as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName) as D
CROSS APPLY (SELECT sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName) as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB
Order by D.DataFileSizeMB desc
end
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dbspaceall]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_dbspaceall]
GO
/*
exec sp_dbspaceall
*/
create procedure sp_dbspaceall
as
begin
Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] bigint
)
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') as spaceused from sys.sysfiles';
With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(
SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(14,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as DataFileSizeMB,
convert(dec(14,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(14,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100) as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName) as D
CROSS APPLY (SELECT sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName) as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB
Order by D.DataFileSizeMB desc
end
Monday, July 16, 2012
Top SQL Server Tools SQL compliance manager SQL diagnostic manager SQL Monitor SQL Sentry Performance Advisor for SQL Server SQL safe backup SSAS Interview Questions on Measures, Actions, and Storage
http://www.mssqltips.com/sqlservertip/2637/ssas-interview-questions-part-i-questions-on-basic-concepts-data-sources-and-data-source-views/
http://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii--dimensions/
http://www.mssqltips.com/sqlservertip/2683/sql-server-analysis-services-interview-questions-on-dimensions-hierarchies-and-properties/
http://www.mssqltips.com/sqlservertip/2614/ssas-interview-questions-on-measures-actions-and-storage/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012715
http://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii--dimensions/
http://www.mssqltips.com/sqlservertip/2683/sql-server-analysis-services-interview-questions-on-dimensions-hierarchies-and-properties/
http://www.mssqltips.com/sqlservertip/2614/ssas-interview-questions-on-measures-actions-and-storage/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012715
Subscribe to:
Posts (Atom)