Row Count

Sql server Central

Backup Verification Script - Backup when and what type

Select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),e.database_name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery') )) as Recovery_model,
database_creation_date,backup_start_date,backup_finish_date,
           
(Select  convert(varchar(25),Max(backup_finish_date) , 100)
              From msdb..backupset a
              Where a.database_name=e.database_name
                and a.server_name  = @@servername
                and type='D'
              Group by a.database_name) Database_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset b
               Where b.database_name=e.database_name
                and b.server_name  = @@servername
                 and type='L' Group by b.database_name) Log_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset c
               Where c.database_name=e.database_name
                and c.server_name  = @@servername
                 and type='I' Group by c.database_name) Diff_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset d
               Where d.database_name=e.database_name
                and  d.server_name  = @@servername
                 and type='F' Group by d.database_name) File_Bk

 From msdb..backupset e
Where e.database_name Not in ('tempdb','pubs','northwind','model')
  and e.server_name = @@Servername
  and e.database_name in (Select Distinct name from master..sysdatabases)

--never backed up
Union all
select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),

NULL, NULL , NULL, NULL, NULL, NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Script to find Recovery Model and File Location

select *  from
(select sf.fileid, sd.name, sd.cmptlevel, sf.filename,sf.filename as secfile , DATABASEPROPERTYEX (sd.name,'Recovery') as Rec_model
 from sysaltfiles sf join sysdatabases sd on sf.dbid=sd.dbid ) as s --where s.rec_model='Simple'

Script to find Recovery Model and File Location

select *  from
(select sf.fileid, sd.name, sd.cmptlevel, sf.filename,sf.filename as secfile , DATABASEPROPERTYEX (sd.name,'Recovery') as Rec_model
 from sysaltfiles sf join sysdatabases sd on sf.dbid=sd.dbid ) as s --where s.rec_model='Simple'

Databases Sizes and Current DB Table Size

The following query will create a store proc SP_PH_DATABASE_USAGE, which will give the DataBase Sizes and Table Sizes for the current DataBase.

USE MASTER
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SP_PH_DATABASE_USAGE' AND XTYPE = 'P')
DROP PROC SP_PH_DATABASE_USAGE 
GO


CREATE PROCEDURE SP_PH_DATABASE_USAGE AS
SET NOCOUNT ON
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORDB' )
DROP TABLE ##DATASPACEFORDB
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORDB' )
DROP TABLE ##TEMPFORDB




CREATE TABLE ##DATASPACEFORDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORDB
 EXEC( 'DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORDB FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORDB 
SELECT '50','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID




IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORTEMPDB' )
DROP TABLE ##DATASPACEFORTEMPDB


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORTEMPDB' )
DROP TABLE ##TEMPFORTEMPDB


CREATE TABLE ##DATASPACEFORTEMPDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORTEMPDB
 EXEC( 'USE TEMPDB DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORTEMPDB FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORTEMPDB 
SELECT '100','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMPLOGSPACE' )
DROP TABLE ##TMPLOGSPACE


CREATE TABLE ##TMPLOGSPACE 
(
DATABASENAME VARCHAR(100),
LOGSIZE VARCHAR(100),
LOGSPACEUSED VARCHAR(100),
STATUS VARCHAR(100)
)


INSERT INTO ##TMPLOGSPACE 
    EXEC ('DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS')




SELECT '                        ' AS 'DATABASENAME',* INTO ##FINALTABLEFORDB  FROM ##TEMPFORDB WHERE 1=2


INSERT INTO ##FINALTABLEFORDB  
SELECT DB_NAME(),* FROM ##TEMPFORDB 


INSERT INTO ##FINALTABLEFORDB  
 SELECT 'TEMPDB',* FROM ##TEMPFORTEMPDB


INSERT INTO ##FINALTABLEFORDB  
SELECT DATABASENAME,101,'TOTAL LOG SIZE IN GB',CONVERT(NUMERIC(20),LOGSIZE)/1024 , (CONVERT(NUMERIC(20),((CONVERT(NUMERIC(20),LOGSPACEUSED)*LOGSIZE)/100)))/1024,'',''  
FROM ##TMPLOGSPACE WHERE DATABASENAME IN (DB_NAME(),'TEMPDB' )


SELECT UPPER(DATABASENAME),FILEID,GROUPNAME,CONVERT(NUMERIC(20),TOTALSPACE_MB) AS TOTALSPACE_MB,CONVERT(NUMERIC(20),SPACEUSED_MB) AS SPACEUSED_MB ,CONVERT(NUMERIC(20),(TOTALSPACE_MB-SPACEUSED_MB)) AS 'FREESPACE_MB' , LFILENM,PFILENM 
FROM ##FINALTABLEFORDB  ORDER BY 1,2


DROP TABLE ##DATASPACEFORDB, ##TEMPFORDB
DROP TABLE ##DATASPACEFORTEMPDB, ##TEMPFORTEMPDB
DROP TABLE ##TMPLOGSPACE, ##FINALTABLEFORDB


 --temp table for further querying
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2))


-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"


-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)


-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100


-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC


-- Comment out the following line if you want to do further querying
DROP TABLE #temp


GO

Network Protocols

The VIA protocol has been marked as deprecated in SQL Server 2008 R2 and will be removed in a future version. You should not be using this in any future development or migrations.

T-Replication - ON Demand Schedule, Without SnapShot

Please check the link below,

https://docs.google.com/open?id=0B3yagczqVQKlNjU0Yjg2MTEtMjE2Ny00YWExLTk4ZmYtZmEyZjlhMjI1YTBh