Below is the super script to use
create table #dbfiles (
[Database] sysname,
Name sysname,
[Type] sysname,
[Filename] nvarchar(1024),
Allocated int,
Used int,
Available int
) exec sp_msforeachdb 'use [?];insert into #dbfiles
select
''?'' as [Database]
, a.Name
, dbf.type_desc as [Type]
, a.Filename
, convert(int,round(a.Size/128.000,0)) as Allocated
, convert(int,round(fileproperty(a.Name,''SpaceUsed'')/128.000,0)) as Used
, convert(int,round((a.Size-fileproperty(a.Name,''SpaceUsed''))/128.000,0)) as Available
from
dbo.sysfiles a (nolock)
inner join sys.database_files dbf (nolock)
on a.fileid = dbf.file_id
where
db_id(''?'') not in (1,2,3,4)';
select
*,
'use [' + [Database] + ']; dbcc shrinkfile (''' + [Name] + ''', ' + cast(
(Used + 1) as nvarchar(16)
) + ');'
from
#dbfiles where [type]='LOG'
order by
Available desc
drop
table #dbfiles
Below script is useful when you work with missing index in SQL server database, run below script on database it gives complete info where we have any missing indexes on the database, you need to change database name and values got get full data/fino,
select
*
, 'create index IX_'
+ replace(object_name(object_id, database_id), ' ', '_')
+ '_' + convert(varchar, mig.index_group_handle) + '_' + convert(varchar, mid.index_handle)
+ ' on ' + statement
+ ' (' + isnull (equality_columns,'')
+ case when equality_columns is not null and inequality_columns is not null
then ','
else ''
end
+ isnull (inequality_columns, '')
+ ')'
+ isnull(' include (' + included_columns + ')', '')
as create_index_statement
from
(
select
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as index_advantage
, migs.*
from
sys.dm_db_missing_index_group_stats migs
) as migs_adv
inner join sys.dm_db_missing_index_groups as mig
on migs_adv.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid
on mig.index_handle = mid.index_handle
where
database_id = DB_ID('msdb')
and
index_advantage > 10000
order by
migs_adv.index_advantage desc
=======
SELECT @@servername,getdate (),RTRIM(name) AS [Name], groupid AS [Group Id], filename AS [File Name],
CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent Used]
FROM sysfiles
-- dbcc freesystemcache ('all' ) with mark_in_use_for_removal
-- dbcc opentran
/*
USE [tempdb]
GO
DBCC SHRINKFILE (N'tempdev' , 4639)
GO
*/ 
Test format of the query:
Query to get list of articles | Pls use Independent tables instead of using whole query.
Happy learning
We can use below script to get complete SQL server informaiton
