Useful SQL Server scripts

Hi friends, Some actions performed on SQL Server ...

2012-03-22T22:27:21.259-07:00

Hi friends,

Some actions performed on SQL Server auditing components are intrinsically audited in a specific audit, and in these cases audit events occur automatically because the event occurred on the parent object. Thanks a lot....

Public Folders

--SAMPLE COLLECTION select top 100 * from sys.fn...

2011-10-05T08:56:01.463-07:00

--SAMPLE COLLECTION
select top 100 *
from sys.fn_get_audit_file('e:\Audit\DDL-Audit_A1024EDC-539B-48EA-A31C-1BB307415F39_0_129606570581250000.sqlaudit', DEFAULT, DEFAULT)

select * from sys.dm_audit_actions order by action_id
select * from sys.dm_audit_class_type_map order by class_type

select * from sys.server_audits
select * from sys.server_file_audits
select * from sys.dm_server_audit_status
select * from sys.server_audit_specifications
select * from sys.server_audit_specification_details

---to create index and drop index select 'cr...

2011-08-12T09:26:35.895-07:00

---to create index and drop index
select
'create '+(case when index_description like 'clustered%' then 'clustered' else '' end)+' index ['+index_name+'] on ['+dbname+'].[dbo].['+tablename+'] ('+index_keys+') '+(case when included_columns is not null then 'includes ('+included_columns+')' else '' end) as createindex
,'drop index ['+index_name+'] on ['+dbname+'].[dbo].['+tablename+']' as dropindex
,*
from #tmp2
order by tablename

use master go /* sp_dbspaceused 'ces_barclsy...

2011-08-10T10:02:07.543-07:00

use master
go

/*
sp_dbspaceused 'ces_barclsysplc'
*/

create procedure sp_dbspaceused
(
@db nvarchar(255) = 'tempdb'
)
as
begin

declare @sqlstring nvarchar(max) --@db nvarchar(255) = 'tempdb' ,

if exists(select name from master.sys.databases where name = @db)
begin

set @sqlstring = '
use ['+@db+']

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

'
print @sqlstring

execute sp_executesql @sqlstring

end
else
begin
select 'Database [' + @db + '] not exisit on [' + @@SERVERNAME + '] server'
end

end
go

--Shrink all db log files exec sp_msforeachdb &#...

2010-12-07T08:47:11.155-08:00

--Shrink all db log files

exec sp_msforeachdb
'
if ''?'' like ''ces_%''
begin
use [?]

DBCC SHRINKFILE (N''ces_default_Log'' , 0)

end
'

exec sp_msforeachdb ' if ''?''...

2010-11-18T14:57:50.865-08:00

exec sp_msforeachdb
'
if ''?'' like ''ces_%'' and ''?'' not like ''ces_master%''
begin
use [?]

if not exists(
select * from INFORMATION_SCHEMA.ROUTINES
where SPECIFIC_NAME = ''pr_sqm_queue_collection'')
begin
print @@servername+''.''+db_name()
end
end
'

exec sp_msforeachdb ' if ''?''...

2010-11-18T14:55:51.472-08:00

exec sp_msforeachdb
'
if ''?'' like ''ces_%'' and ''?'' not like ''ces_master%''
begin
use [?]
print ''use [''+''?''+'']''
print ''GO''
print ''DROP FULLTEXT INDEX ON lo_text''
print ''go''
end
'