Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives

A script for SQL Server :

 

CREATE PROCEDURE [dbo].[sp_get_tables_sizes_all_dbs]

WITH EXECUTE AS OWNER
AS BEGIN

--sqlserver 2005 +
IF (SELECT count(*) FROM tempdb.sys.objects WHERE name = '##TABLESIZES_ALLDB')=1 BEGIN
DROP TABLE ##TABLESIZES_ALLDB;
END

CREATE TABLE ##TABLESIZES_ALLDB (
snapdate datetime,
srv nvarchar(1000),
sv nvarchar(1000),
_dbname nvarchar(1000),
nomTable nvarchar(1000),
"partition_id" bigint,
"partition_number" int,
lignes bigint,
"memory (kB)" bigint,
"data (kB)" bigint,
"indexes (kb)" bigint,
"data_compression" int,
data_compression_desc nvarchar(1000)
)

EXECUTE master.sys.sp_MSforeachdb
'USE [?];
insert into ##TABLESIZES_ALLDB
select getdate() as snapdate,cast(serverproperty(''MachineName'') as nvarchar(1000)) svr,cast(@@servicename as nvarchar(1000)) sv, ''?'' _dbname, nomTable= object_name(p.object_id),p.partition_id,p.partition_number,
lignes = sum(
CASE
When (p.index_id < 2) and (a.type = 1) Then p.rows
Else 0
END
),
''memory (kB)'' = cast(ltrim(str(sum(a.total_pages)* 8192 / 1024.,15,0)) as float),
''data (kB)'' = ltrim(str(sum(
CASE
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) * 8192 / 1024.,15,0)),
''indexes (kb)'' = ltrim(str((sum(a.used_pages)-sum(
CASE
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END) )* 8192 / 1024.,15,0)),p.data_compression,
p.data_compression_desc

from sys.partitions p, sys.allocation_units a ,sys.sysobjects s
where p.partition_id = a.container_id
and p.object_id = s.id and s.type = ''U'' -- User table type (system tables exclusion)
group by p.object_id,p.partition_id,p.partition_number,p.data_compression,p.data_compression_desc
order by 3 desc'
;

SELECT * FROM ##TABLESIZES_ALLDB

END
GO
Commentaire (0) Clics: 22

Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives

Nagios is a great free tool to supervize many many things in IT. SQL Server is one appliation that can be monitored by Nagios. A great plugin check_mssql_health contains many checks from performance counters to space used and connection time (36 checks at the time of writing).

In order to secure checks on databases, you need to create logins, users and roles on every instances and databases you want to monitor. The Consol Labs web site (Nagios pluggins & Addons editor) give you a script to create (and drop if needed) these objects on one MSSQL instance but : 
1 - We found some missings elements on the script
2 - We will give you one method to quickly launch your create script on several MSSQL instances easily using MSSQL Central Management Server or SSMS Registered Groups

Commentaire (0) Clics: 1629

Vote utilisateur: 0 / 5

Etoiles inactivesEtoiles inactivesEtoiles inactivesEtoiles inactivesEtoiles inactives

I was glad to see on mssqltips a script to generate column list for table in order to by use in hashbytes function later for table comparison : article with original get_hash_field function

But as often I needed to do more than just concatenate all fields. My need is :

  • insert a symbol between field to avoid (or limit) the "birthday paradox" : avoid to obtain the same final string if you concat "123" and "45" or you concat "12" and "345"
  • replace null field with a dummy string 'µ' for me
  • be able to exclude 2 lists of column for build the list of fields : I wanted all columns of the table except 1 or more fields nor fields in the key that was not needed for me. In the orginal function this list was static

For that I use store procedure instead of function and dynamic sql was mandatory (for the parameters @p_exception_column_list and @p_key_column_list that are strings with quotes) :

Commentaire (0) Clics: 2716
database publication

Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives

Un script de restore database ms sql server avec récupération de la dernière sauvegarde effectuée sur le serveur (ou récupération directe d'une sauvegarde via un chemin).

Ce script est capable de récupérer les différents fichiers de la sauvegarde et de construire une commande de restauration en changeant la destination des fichiers cibles 

 

Commentaire (2) Clics: 3212