This script will check the last backup taken and notify an alert when there is no backup of given threshold. Table: -- drop table tbl_no_full_backup_7days use dbadata drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant ) use dbadata_archive go drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant,upload_date datetime ) SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[USP_DBA_No_full_backup] Script Date: 7/6/2015 3:32:45 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* -- drop table tbl_no_full_backup_7days use dbadata drop table tbl_no_full_backup_7days CREATE TABLE [dbo].[tbl_no_full_backup_7days]( serverName varchar(50),databaseName Varchar(100),type varchar(1),last_backup_date datetime, DB_Status sql_variant ) use dbadata_archive go drop table tbl_no_full_backup_7days…
-
-
New database user creation alert notification Automon DBA SPs – AM16
This script will check the new database and user creation and notify an alert, if it is tempdb then service restarted. Table: use dbadata go go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME] [sysname] NOT NULL, [CREATE_DATE] [datetime] NULL, [TYPE] [varchar](10) NULL ) use dbadata_archive go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME] [sysname] NOT NULL, [CREATE_DATE] [datetime] NULL, [TYPE] [varchar](10) NULL, [Upload_date] [varchar](10) NULL ) SP: USE [DBAdata] GO /****** Object: StoredProcedure [dbo].[USP_DBA_GETNEW_DB_AND_LOGIN] Script Date: 9/27/2013 7:44:10 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* use dbadata go go drop table DBA_NEW_OBJECT_LOGIN_LIST go CREATE TABLE [dbo].[DBA_NEW_OBJECT_LOGIN_LIST]( [SERVER_NAME] [varchar](100), [NAME]…
-
Azure cloud SQL database single database PaaS vs Elastic Pool EP vs SQL on Virtual machine IaaS
Azure cloud SQL database single database PaaS vs Elastic Pool EP vs Virtual machine IaaS What is Cloud? Cloud is simply using third party data center, servers and services. You do not have to manage most of the tasks to spend your money, resources to build your own data center and maintenance of those etc. This post will give some overview and difference of single database PaaS vs Elastic Pool EP vs Managed Instance MI vs SQL on Virtual machine IaaS. What is on premise – It’s your local data center / server, what you are currently doing before migrating to cloud. Azure architect will take care of the Azure…
-
Memory RAM high alert notification Automon DBA SPs – AM15
This script will check the Memory RAM usage and notify an alert when the it reaches the threshold. Table: use dbadata go drop table tbl_memory_usgae_2012_New go CREATE TABLE [dbo].[tbl_memory_usgae_2012_New]( [servername] [varchar](100) NULL, [Physical_RAM_mB][bigint] NULL, [Physical_RAM_Use_mB] [bigint] NULL, [Physical_RAM_Available_mB] [bigint] NULL, [Percentage use] [numeric](23, 10) NULL, [Locked_page_RAM_mB] [bigint] NULL, [Max_RAM] sql_variant NULL, [Min_RAM] sql_variant NULL, [PLE] [bigint] NULL, [Version] [varchar](10) NULL, [cntr_type] [int] NULL ) use dbadata_archive go drop table tbl_memory_usgae_2012_New go CREATE TABLE [dbo].[tbl_memory_usgae_2012_New]( [servername] [varchar](100) NULL, [Physical_RAM_mB][bigint] NULL, [Physical_RAM_Use_mB] [bigint] NULL, [Physical_RAM_Available_mB] [bigint] NULL, [Percentage use] [numeric](23, 10) NULL, [Locked_page_RAM_mB] [bigint] NULL, [Max_RAM] sql_variant NULL, [Min_RAM] sql_variant NULL, [PLE] [bigint] NULL, [Version] [varchar](10) NULL, [cntr_type] [int] NULL, [Upload_date]…
-
No sysadmin access Microsoft SQL server error 18461
Login failed for user reason single user mode only one administrator can connect at this time Microsoft SQL server error 18461 You might get a situation that no sysadmin access to SQL server and server installed by someone. Breaking SQL server and adding SA sysadmin of yourself by SQLCMD. You should be OS administrator. First find out ‘Binn’ folder where it is installed C or D drive. Better search in my computer. Open a CMD with administrator Type: cd /d E:\Program Files\Microsoft SQL Server\130\Tools\Binn (OR) 1.Stop the SQL server service 2.Start the service in Single user mode by adding ‘;-mSQLCMD’ to the parameters from SQL server configuration manager…