Category: SQl server Administration

Following Script will help us to find space utilized by every database object, this includes number of rows and size taken by every table.

DECLARE @projected_size nvarchar(2)
SET @projected_size = NULL

declare        @db_count    bigint,
            @loop_count    bigint,
            @sqlstat    varchar(7000),
            @sqlstat2    varchar(7000),
            @sqlstat3   varchar(7000),
            @sqlstat4   varchar(7000),
            @sqlstat5   varchar(7000),
            @db_name    varchar(150)

CREATE TABLE #tablestats
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    data_size            nvarchar(38),
    reserved_size        nvarchar(38),
    unused_size            nvarchar(38),
    index_size            nvarchar(38),
    primary_key_size    nvarchar(38),
    number_rows_table    bigint
)
CREATE TABLE #alltablestats
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    data_size            nvarchar(15),
    reserved_size        nvarchar(15),
    unused_size            nvarchar(15),
    index_size            nvarchar(15),
    primary_key_size    nvarchar(15),
    number_rows_table    bigint
)
CREATE TABLE #tabpage
(
    database_name        varchar(150),
    table_name            varchar(700),
    table_id            bigint,
    page_size            dec(30,0),
    used_size            dec(30,0),
    index_size            dec(30,0)
)

CREATE TABLE #pkhold
(
    pk_id                bigint,
    pk_name              varchar(400),
    parent_id            bigint,
    pk_size                nvarchar(15)
)
CREATE TABLE #capacity
(
    tb_id                bigint identity,
    database_name        varchar(150),
    database_id            bigint
)

IF @projected_size  IS null or @projected_size = ‘0’
  SET @projected_size = ‘1’

SET @loop_count = 1

/*****************************************************************************
***get database names on server                                            ***
******************************************************************************/
INSERT INTO #capacity(database_name,database_id)
SELECT DISTINCT name,dbid
FROM master..sysdatabases

SELECT @db_count = count(*)
FROM #capacity

WHILE @loop_count <= @db_count
BEGIN
    SELECT @db_name = database_name
    FROM #capacity
    WHERE tb_id = @loop_count   

/*****************************************************************************
***get table name,id, and data size                                        ***
******************************************************************************/
    SET @sqlstat = ‘use ‘ +  @db_name   

    SET @sqlstat2 = ‘ insert into #tablestats(database_name,table_name,table_id)
             SELECT ”’+ @db_name + ”’,name, ID FROM SYSOBJECTS WHERE XTYPE =”U”’
    EXEC (@sqlstat + @sqlstat2)

    SET @sqlstat2 = ‘ insert into #tabpage(database_name,table_name,table_id,page_size,used_size)
             select distinct database_name,table_name, table_id, ”page size” = (SELECT isnull(SUM(DPAGES),0)
                  FROM SYSINDEXES
                WHERE SYSINDEXES.INDID < 2 AND
                                      SYSINDEXES.ID = #tablestats.table_id),’
    SET @sqlstat3 = ”’used size” = (SELECT ISNULL(SUM(USED),0)
                FROM SYSINDEXES
                WHERE SYSINDEXES.INDID = 255 AND
                                      SYSINDEXES.ID = #tablestats.table_id)
               from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3)

    SET @sqlstat2 = ‘ update #tablestats set data_size = (select LTRIM(STR((page_size + used_size ) ‘
    SET @sqlstat3 = ‘* 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) from  #tabpage ‘
    SET @sqlstat4 = ‘where #tabpage.table_id = #tablestats.table_id and #tabpage.table_name = #tablestats.table_name) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get reserved size                                                       ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set reserved_size = (SELECT LTRIM(STR(cast(SUM(RESERVED) as bigint) ‘
    SET @sqlstat3 = ‘  * 8192 / 1024.*’+@projected_size+’,300,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
    SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get unused size                                                         ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set unused_size = (SELECT LTRIM(STR((cast(SUM(RESERVED) as bigint)- SUM(USED)) ‘
    SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM SYSINDEXES ‘
    SET @sqlstat4 = ‘ WHERE SYSINDEXES.INDID IN (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get index size                                                          ***
******************************************************************************/
    DELETE
    FROM #tabpage

    SET @sqlstat2 = ‘ insert into #tabpage(table_id,page_size,used_size,index_size)select table_id, ‘
    SET @sqlstat3 = ‘ ”page size” = (SELECT SUM(DPAGES) FROM SYSINDEXES WHERE SYSINDEXES.INDID < 2 AND SYSINDEXES.ID = #tablestats.table_id), ‘
    SET @sqlstat4 = ‘ ”used size” = (SELECT SUM(convert(dec(15),USED)) FROM SYSINDEXES WHERE SYSINDEXES.INDID in (255) AND SYSINDEXES.ID = #tablestats.table_id), ‘
    SET @sqlstat5 = ‘ ”index_size” = (SELECT SUM(convert(dec(15),USED))FROM SYSINDEXES WHERE SYSINDEXES.INDID in (0,1,255) AND SYSINDEXES.ID = #tablestats.table_id) from #tablestats  ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4+@sqlstat5)

    SET @sqlstat2 = ‘ update #tablestats set INDEX_size = (SELECT LTRIM(STR((index_SIZE – (PAGE_SIZE + ISNULL(USED_SIZE,0))) ‘
    SET @sqlstat3 = ‘ * 8192 / 1024.*’+@projected_size+’,15,0) + ” ” + ”KB”) FROM  #TABPAGE ‘
    SET @sqlstat4 = ‘WHERE #tabPAGE.table_id = #tablestats.table_id) from #tablestats’
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)
/*****************************************************************************
***get PK size                                                             ***
******************************************************************************/
    SET @sqlstat2 = ‘ INSERT INTO #PKHOLD(PK_ID,PK_NAME,PARENT_ID) SELECT SO.ID, ‘
    SET @sqlstat3 = ‘ SO.NAME,SO.PARENT_OBJ FROM SYSOBJECTS SO INNER JOIN #tablestats TS ON ‘
    SET @sqlstat4 = ‘ SO.PARENT_OBJ = TS.TABLE_ID WHERE SO.XTYPE = ”PK”’
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)

    UPDATE #tablestats
    SET primary_key_size = index_size
    FROM #PKHOLD  pk INNER JOIN #tablestats tb
              ON pk.PARENT_ID = tb.table_id
/*****************************************************************************
***get number rows                                                         ***
******************************************************************************/
    SET @sqlstat2 = ‘ update #tablestats set number_rows_table = rows*’+@projected_size
    SET @sqlstat3 = ‘ from sysindexes si inner join #tablestats tb on si.id = tb.table_id ‘
    SET @sqlstat4 = ‘ where si.indid < 2 ‘
    EXEC (@sqlstat + @sqlstat2+@sqlstat3 + @sqlstat4)

     INSERT INTO #alltablestats(database_name,table_name,table_id,data_size,
                               reserved_size,unused_size,index_size,
                               primary_key_size,number_rows_table)
    SELECT database_name,table_name,table_id,data_size,
           reserved_size,unused_size,index_size,
           primary_key_size,number_rows_table
    FROM #tablestats
    ORDER BY database_name

     DELETE FROM #tablestats
     DELETE FROM #tabpage
    DELETE FROM #pkhold
    SET @loop_count = @loop_count + 1
 END
/*****************************************************************************
***return results to the screen                                            ***
******************************************************************************/
SELECT database_name,table_name,data_size,
       reserved_size,unused_size,index_size,
       primary_key_size,number_rows_table
FROM  #alltablestats
ORDER BY DATABASE_NAME,TABLE_NAME

use master
go
Dbcc sqlperf(logspace)
go

— ***********************************************************************************/
— Description  :    Script to check  how long the SQL Services were up  
—                        Following script will list down time for SQL Services are running and Current Status of SQL Server Agent too
— ***********************************************************************************/
SET NOCOUNT ON
DECLARE @crdate

 DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
   SELECT @crdate=crdate FROM sysdatabases WHERE NAME=’tempdb’
   SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
   IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
      SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
   ELSE
      SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
   PRINT ‘SQL Server “‘ + CONVERT(VARCHAR(20),SERVERPROPERTY(‘SERVERNAME’))+'” is Online for the past ‘+@hr+’ hours & ‘+@min+’ minutes’
   IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N’SQLAgent – Generic Refresher’)
      BEGIN
         PRINT ‘SQL Server is running but SQL Server Agent <<NOT>> running’
      END
   ELSE
      BEGIN
         PRINT ‘SQL Server and SQL Server Agent both are running’
END
go

 

now I will make trigger to prevent user to connect to sql server instance  in time between 5 pm to 7 am
and if he want to connect in this time i will save this transaction in database login in my SQL  server
i will used datatype sysname
is a built in datatype limited to 128 Unicode characters that, IIRC, is used primarily to store object names when creating scripts. It’s value cannot be NULL (sysname = nvarchar(128) not null)

— Create Login
Create Login Elmasry WITH PASSWORD = ‘egypt’
GO
Create Login Elmasry WITH PASSWORD = ‘egypt’
GO
—Create Database Login_info
CREATE DATABASE Login_Info
GO
USE Login_Info
GO
CREATE TABLE dbo.restrictedlogons
(loginid sysname not null, timestamp datetime not null)
go
use master
go
–Create Trigger
create trigger Prevent_USer_Login
on all server
for logon
as
begin
if original_login () = ‘Elmasry’ and
datepart(hh,getdate()) between 17 and 24
begin
rollback
insert  Login_Info.dbo.restrictedlogons
(loginid,timestamp)
values (original_login(),getdate())
end
end
GO
–Drop User Name
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N’Elmasry’)
DROP USER Elmasry
Go
—Drop Database
drop database Login_Info
Go
—Disable Trigger
USE Mastre;
GO
DISABLE TRIGGER Prevent_USer_Login ON All Server;
GO
—Drop Trigger
USE MASTER;
GO
DROP TRIGGER Prevent_USer_Login ON ALL SERVER ;
GO

By default, SQL Server listens on port 1433 for TCP/IP connections . But what if this was changed and what about a named instance? You can find the listener port in 3 ways:

1. Check the Windows Application event log – Event ID 26022
2. Use SQL Server configuration Manager
3. Query the Windows Registry

Windows Application Event Log

Each time an instance starts, SQL Server will record its listening port(s) in the Windows Event Viewer. To find this, open up the Windows Event Viewer and choose the ‘Application’ event log. If you filter on Event ID 26022, you should see four events associated with the start-up. Look for the event that has this in the message body:(Server is listening on any ‘1432’)

 

 

 

 

 

 

SQL Server Configuration Manger

 SQL Server Configuration Manager. Open it up and expand ‘SQL Server Network Configuration’ in the left hand pane. You’ll see menu items for protocols for your various instances. Single click one of the protocol menu items and you’ll get a list of protocols for that instance appearing in the right hand pane. Right click ‘TCP/IP’ and choose ‘Properties’:

Choose the ‘IP Addresses’ tab. Scroll to the bottom of the list and find the ‘IPAll’ section. The ‘TCP Port’ setting will have the listening port, unless the instance is set up to listen dynamically. If so, the ‘TCP Dynamic Ports’ setting will contain the listening port:

Using the Windows Registry

DECLARE @InstName VARCHAR(16)

DECLARE @RegLoc VARCHAR(100)

SELECT @InstName = @@SERVICENAME

IF @InstName = ‘MSSQLSERVER’
  BEGIN
    SET @RegLoc=’Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\’
  END
 ELSE
  BEGIN
   SET @RegLoc=’Software\Microsoft\Microsoft SQL Server\’ + @InstName + ‘\MSSQLServer\SuperSocketNetLib\Tcp\’
  END

EXEC [master].[dbo].[xp_regread] ‘HKEY_LOCAL_MACHINE’, @RegLoc, ‘tcpPort’